【SQL开源代码栏目提醒】:本文主要为网学会员提供“MySQL Performance Tuning Best Practices - 数据库”,希望对需要MySQL Performance Tuning Best Practices - 数据库网友有所帮助,学习一下!
1Copyright MySQL ABThe World’s Most Popular Open Source Database1The World’s Most Popular Open Source DatabasePerformance Tuning Best PracticesJay PipesCommunity Relations Manager North Americajaymysql.com2Copyright MySQL ABThe World’s Most Popular Open Source Database2The World’s Most Popular Open Source DatabaseOverview●Benchmarking and Profiling Concepts●Sources of Problems●Schemas and Indexes●SQL Coding●Server Parameters3Copyright MySQL ABThe World’s Most Popular Open Source Database3The World’s Most Popular Open Source DatabaseBenchmarking Concepts●Provides??a??track??record??of??changes??Baseline??is??the??starting??point??Testing??done??iteratively??Deltas??between??tests??show??difference??that??the??changes??made●Stress/Load??testing??of??application??and/or??database●Harness??or??framework??useful??to??automate??many??benchmark??tasks4Copyright MySQL ABThe World’s Most Popular Open Source Database4The World’s Most Popular Open Source DatabaseBenchmarking Tips●Always??give??yourself??a??target●Record??everything??Schema??dump??my.cnf??files??hardware/os??configuration??files??as??needed●Isolate??the??problem??Shut??down??unnecessary??programs??Stop??network??traffic??to??machine??Disable??the??query??cache??Change??one??thing??at??a??time5Copyright MySQL ABThe World’s Most Popular Open Source Database5The World’s Most Popular Open Source DatabaseBenchmarking Toolbox●SysBench ??http://sysbench.sourceforge.net/●mysqlslap 5.1??http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html●Apache Bench ab●supersmack ??http://www.vegan.net/tony/supersmack/●MyBench●http://jeremy.zawodny.com/mysql/mybench/6Copyright MySQL ABThe World’s Most Popular Open Source Database6The World’s Most Popular Open Source DatabaseProfiling Concepts●Diagnose a running system●Low hanging fruit??Diminishing returns??Be careful not to over-optimize●Identify performance bottlenecks in??Memory??CPU??I/O Disk??Network and OS7Copyright MySQL ABThe World’s Most Popular Open Source Database7The World’s Most Popular Open Source DatabaseProfiling Toolbox●SHOW Commands??SHOW??PROCESSLIST????STATUS????INNODB??STATUS??http://dev.mysql.com/show●EXPLAIN??http://dev.mysql.com/explain●MyTop??http://jeremy.zawodny.com/mysql/mytop/●Whole host of Linux power tools??gprof / oprofile??vmstat / ps / top / mpstat / procinfo●apd for PHP developers??http://pecl.php.net/package/apd8Copyright MySQL ABThe World’s Most Popular Open Source Database8The World’s Most Popular Open Source DatabaseSlow Query Log●Slow Query Log●log_slow_queries/var/lib/mysql/slow??queries.log●long_query_time2●log_long_format●Use mysqldumpslow to parse the file●5.1 Can log directly to a table plus does not require restart of server●SET??GLOBAL??SLOW_QUERY_LOG??????ON????OFF??●http://dev.mysql.com/doc/refman/5.1/en/log-tables.html9Copyright MySQL ABThe World’s Most Popular Open Source Database9The World’s Most Popular Open Source DatabaseProfiling Tips●Get very familiar with EXPLAIN??Access types??Learn the type key ref rows Extra columns●Low hanging fruit diminishing returns●Use MyTop to catch locking and long-running queries in real-time10Copyright MySQL ABThe World’s Most Popular Open Source Database10The World’s Most Popular Open Source DatabaseSources of Problems●Inefficient or bloated schema design●Poor or nonexistent indexing●Bad SQL Coding Practices●Server variables not tuned properly●Hardware and/or network bottlenecks11Copyright MySQL ABThe World’s Most Popular Open Source Database11The World’s Most Popular Open Source DatabaseSchema Guidelines●Inefficient schema a great way to kill performance●Use the smallest data types necessary??Do you really need that BIGINT●Fewer fields Narrow rows More records per block●Normalize first denormalize only in extreme cases...12Copyright MySQL ABThe World’s Most Popular Open Source Database12The World’s Most Popular Open Source Databasehttp://thedailywtf.com/forums/thread/75982.aspxAhh normalization...13Copyright MySQL ABThe World’s Most Popular Open Source Database13The World’s Most Popular Open Source DatabaseSchema Tips●Consider horizontally splitting many-columned tables example ahead●Consider vertically partitioning many-rowed tables??Merge tables MyISAM only??Homegrown email example??Partitioning 5.1●Use AUTO_INCREMENT columns vs. homegrown sequences. Faster and built-in to the database.●Use “counter” tables to mitigate query cache issues example ahead??Essential for InnoDB14Copyright MySQL ABThe World’s Most Popular Open Source Database14The World’s Most Popular Open Source DatabaseHorizontal Partitioning ExampleCREATE??TABLE??Users??????user_id??INT??NOT??NULL??AUTO_INCREMENT??email??VARCHAR80??NOT??NULL??display_name??VARCHAR50??NOT??NULL??password??CHAR41??NOT??NULL??first_name??VARCHAR25??NOT??NULL??last_name??VARCHAR25??NOT??NULL??address??VARCHAR80??NOT??NULL??city??VARCHAR30??NOT??NULL??province??CHAR2??NOT??NULL??postcode??CHAR7??NOT??NULL??interests??TEXT??NULL??bio??TEXT??NULL??signature??TEXT??NULL??skills??TEXT??NULL??company??TEXT??NULL??PRIMARY??KEY??user_id??UNIQUE??INDEX??email??ENGINEInnoDBCREATE??TABLE??Users??????user_id??INT??NOT??NULL??AUTO_INCREMENT??email??VARCHAR80??NOT??NULL??display_name??VARCHAR50??NOT??NULL??password??CHAR41??NOT??NULL??PRIMARY??KEY??user_id??UNIQUE??INDEX??email??ENGINEInnoDBCREATE??TABLE??UserExtra??????user_id??INT??NOT??NULL??first_name??VARCHAR25??NOT??NULL??last_name??VARCHAR25??NOT??NULL??address??VARCHAR80??NOT??NULL??city??VARCHAR30??NOT??NULL??province??CHAR2??NOT??NULL??postcode??CHAR7??NOT??NULL??interests??TEXT??NULL??bio??TEXT??NULL??signature??TEXT??NULL??skills??TEXT??NULL??company??TEXT??NULL??PRIMARY??KEY??user_id??ENGINEInnoDB15Copyright MySQL ABThe World’s Most Popular Open Source Database15The World’s Most Popular Open Source DatabaseHorizontal Partitioning Benefits●Main table has narrow rows so... ??More records fit into a single data page ??Fewer reads from memory/disk to get same number of records●Less frequently queried data doesnt take up memory●More possibilities for indexing and different storage engines??Allows targeted multiple MyISAM key caches for hot and cold data example ahead16Copyright MySQL ABThe World’s Most Popular Open Source Database16The World’s Most Popular Open Source DatabaseMultiple MyISAM Key Caches//??init.sql//??Setup??the??hot??cacheSET??GLOBAL??hot_cache.key_buffer_size128K//??Cache??the??postcode??lookupCACHE??INDEX??zip_lookup??TO??hot_cache//??Preload??the??index??sequentiallyLOAD??INDEX??INTO??CACHE??zip_lookup//??Control??cache??invalidationSET??GLOBAL??default.key_cache_division_limit70●Method of controlling key cache invalidation●“Pins” key cache blocks●Allows you to place frequently accessed table indexes into a hot cache●Destroyed upon server restart so use init_file●Preload hot cache with LOAD??INDEX??INTO??CACHE●Control index scan invalidation with division limit//??/etc/my.cnfmysql.serverinit_file/path/to/datadir/init.sql17Copyright MySQL ABThe World’s Most Popular Open Source Database17The World’s Most Popular Open Source DatabaseCounter Table ExampleCREATE??TABLE??Products??????product_id??INT??NOT??NULL??AUTO_INCREMENT??name??VARCHAR80??NOT??NULL??unit_cost??DECIMAL72??NOT??NULL??description??TEXT??NULL??image_path??TEXT??NULL??num_views??INT??UNSIGNED??NOT??NULL??num_in_stock??INT??UNSIGNED??NOT??NULL??num_on_order??INT??UNSIGNED??NOT??NULL??PRIMARY??KEY??product_id??INDEX??name20??ENGINEInnoDB??//??Or??MyISAM//??Getting??a??simple??COUNT??of??products//??easy??on??MyISAM??terrible??on??InnoDBSELECT??COUNTFROM??ProductsCREATE??TABLE??Products??????product_id??INT??NOT??NULL??AUTO_INCREMENT??name??VARCHAR80??NOT??NULL??unit_cost??DECIMAL72??NOT??NULL??description??TEXT??NULL??image_path??TEXT??NULL??PRIMARY??KEY??product_id??INDEX??name20??ENGINEInnoDB??//??Or??MyISAMCREATE??TABLE??ProductCounts??????product_id??INT??NOT??NULL??num_views??INT??UNSIGNED??NOT??NULL??num_in_stock??INT??UNSIGNED??NOT??NULL??num_on_order??INT??UNSIGNED??NOT??NULL??PRIMARY??KEY??product_id??ENGINEInnoDBCREATE??TABLE??ProductCountSummary??????total_products??INT??UNSIGNED??NOT??NULL??ENGINEMEMORY18Copyright MySQL ABThe World’s Most Popular Open Source Database18The World’s Most Popular Open Source DatabaseCounter Table Benefits●Critical for InnoDB because of complications of MVCC●Allows query cache to cache specific data set which will be invalidated only infrequently●Allows you to target SQL_NO_CACHE for SELECTs against counter tables freeing query cache●Allows MEMORY storage engine for summary counters since stats can be rebuilt19Copyright MySQL ABThe World’s Most Popular Open Source Database19The World’s Most Popular Open Source DatabaseSchema Tips contd●Ensure small clustering key InnoDB●Dont use artificial keys when a naturally occurring primary key exists●Example of what not to do:CREATE??TABLE??Products2Tags??????record_id??INT??UNSIGNED??NOT??NULL??AUTO_INCREMENT??product_id??INT??UNSIGNED??NOT??NULL??tag_id??INT??UNSIGNED??NOT??NULL??PRIMARY??KEY??record_id??UNIQUE??INDEX??product_id??tag_id??ENGINEInnoDB20Copyright MySQL ABThe World’s Most Popular Open Source Database20The World’s Most Popular Open Source DatabaseIndexing Guidelines●Poor or missing index fastest way to kill a system●Ensure good selectivity on field example ahead●Look for covering index opportunities example ahead●On multi-column indexes pay attention to the order of the fields in the index example ahead●As database grows examine distribution of values within indexed field●Remove redundant indexes for faster write performance21Copyright MySQL ABThe World’s Most Popular Open Source Database21The World’s Most Popular Open Source DatabaseSelectivity●The relaive uniqueness of the index values to each other●SI d/n●Multiple columns in an index multiple levels of selectivity●So how do we determine selectivity22Copyright MySQL ABThe World’s Most Popular Open Source Database22The World’s Most Popular Open Source DatabaseDetermining Selectivity●The hard way:●For each table in your schema:SELECT??COUNTDISTINCT??field??/??COUNTFROM??my_table//??or...SHOW??INDEX??FROM??my_table23Copyright MySQL ABThe World’s Most Popular Open Source Database23The World’s Most Popular Open Source DatabaseDetermining Selectivity●The easy way: use INFORMATION_SCHEMASELECT????t.TABLE_SCHEMA????t.TABLE_NAME????s.INDEX_NAME????s.COLUMN_NAME????s.SEQ_IN_INDEX??????????SELECT??MAXSEQ_IN_INDEX??????FROM??INFORMATION_SCHEMA.STATISTICS??s2??????WHERE??s.TABLE_SCHEMA????s2.TABLE_SCHEMA??????AND??s.TABLE_NAME????s2.TABLE_NAME??????AND??s.INDEX_NAME????s2.INDEX_NAME??????AS??quotCOLS_IN_INDEXquot????s.CARDINALITY??AS??quotCARDquot????t.TABLE_ROWS??AS??quotROWSquot????ROUNDs.CARDINALITY??/??IFNULLt.TABLE_ROWS??0.01????100??2??AS??quotSEL??quotFROM??INFORMATION_SCHEMA.STATISTICS??s??INNER??JOIN??INFORMATION_SCHEMA.TABLES??t????ON??s.TABLE_SCHEMA????t.TABLE_SCHEMA????AND??s.TABLE_NAME????t.TABLE_NAMEWHERE??t.TABLE_SCHEMA????mysqlAND??t.TABLE_ROWS??gt??10AND??s.CARDINALITY??IS??NOT??NULLAND??s.CARDINALITY??/??IFNULLt.TABLE_ROWS??0.01??lt??1.00ORDER??BY??t.TABLE_SCHEMA??t.TABLE_NAME??s.INDEX_NAME??quotSEL??quotLIMIT??524Copyright MySQL ABThe World’s Most Popular Open Source Database24The World’s Most Popular Open Source DatabaseCommon Index ProblemCREATE??TABLE??Tags??????tag_id??INT??NOT??NULL??AUTO_INCREMENT??tag_text??VARCHAR50??NOT??NULL??PRIMARY??KEY??tag_id??ENGINEMyISAMCREATE??TABLE??Products??????product_id??INT??NOT??NULL??AUTO_INCREMENT??name??VARCHAR100??NOT??NULL//??many??more??fields...??PRIMARY??KEY??product_id??ENGINEMyISAM??CREATE??TABLE??Products2Tags??????product_id??INT??NOT??NULL??tag_id??INT??NOT??NULL??PRIMARY??KEY??product_id??tag_id??ENGINEMyISAM//??This??top??query??uses??the??index//??on??Products2TagsSELECT??p.name??COUNT??as??tagsFROM??Products2Tags??p2tINNER??JOIN??Products??pON??p2t.product_id????p.product_idGROUP??BY??p.name//??This??one??does??not??because//??index??order??prohibits??itSELECT??t.tag_text??COUNT??as??productsFROM??Products2Tags??p2tINNER??JOIN??Tags??tON??p2t.tag_id????t.tag_idGROUP??BY??t.tag_text25Copyright MySQL ABThe World’s Most Popular Open Source Database25The World’s Most Popular Open Source DatabaseCommon Index Problem SolvedCREATE??TABLE??Tags??????tag_id??INT??NOT??NULL??AUTO_INCREMENT??tag_text??VARCHAR50??NOT??NULL??PRIMARY??KEY??tag_id??ENGINEMyISAMCREATE??TABLE??Products??????product_id??INT??NOT??NULL??AUTO_INCREMENT??name??VARCHAR100??NOT??NULL//??many??more??fields...??PRIMARY??KEY??product_id??ENGINEMyISAM??CREATE??TABLE??Products2Tags??????product_id??INT??NOT??NULL??tag_id??INT??NOT??NULL??PRIMARY??KEY??product_id??tag_id??ENGINEMyISAMCREATE??INDEX??ix_tag??ON??Products2Tags??tag_id//??or...??create??a??covering??index:CREATE??INDEX??ix_tag_prodON??Products2Tags??tag_id??product_id//??But??only??if??not??InnoDB...??why26Copyright MySQL ABThe World’s Most Popular Open Source Database26The World’s Most Popular Open Source DatabaseSQL Coding Topics●Change the way you think about SQL programming●Coding guidelines●Common Pitfalls●Bulk loading performance●The DELETE statement27Copyright MySQL ABThe World’s Most Popular Open Source Database27The World’s Most Popular Open Source DatabaseThinking In Terms of Sets●SQL programming procedural programming●Break an English-language request into a group of sets either intersecting or joining●Example: “Show the maximum price that each product was sold along with the product description for each product”●Were dealing with two sets of data:●Set of product descriptions●Set of maximum sold prices●NOT:●for each...28Copyright MySQL ABThe World’s Most Popular Open Source Database28The World’s Most Popular Open Source DatabaseCoding Guidelines●Use “chunky” coding habits KISS●Use stored procedures for a performance boost 5.0●Isolate indexed fields on one side of equation example ahead●Use calculated fields if necessary example ahead●Learn to use joins ??Eliminate correlated subqueries using standard joins examples ahead●Dont try to outthink the optimizer??Sergey Timour and Igor are really really smart...29Copyright MySQL ABThe World’s Most Popular Open Source Database29The World’s Most Popular Open Source DatabaseCorrelated Subquery Conversion Example//??Bad??practiceSELECT??p.name????SELECT??MAXprice????????FROM??OrderItems????????WHERE??product_id????p.product_idAS??max_sold_priceFROM??Products??p//??Good??practiceSELECT??p.name??MAXoi.price??AS??max_sold_priceFROM??Products??p??INNER??JOIN??OrderItems??oi????ON??p.product_id????oi.product_idGROUP??BY??p.name??Task: convert a correlated subquery in the SELECT clause to a standard join30Copyright MySQL ABThe World’s Most Popular Open Source Database30The World’s Most Popular Open Source DatabaseDerived Table Example//??Bad??performanceSELECTc.company??o.FROMCustomers??c??INNER??JOIN??Orders??o????ON??c.customer_id????o.customer_id????WHERE??order_date??????SELECT??MAXorder_date??FROM??Orders????WHERE??customer????o.customerGROUP??BY??c.company//??Good??performanceSELECTc.company??o.FROMCustomers??c??INNER??JOIN??????SELECT????????????customer_id????????MAXorder_date??as??max_order????FROM??Orders????GROUP??BY??customer_id????AS??m??????ON??c.customer_id????m.customer_id??INNER??JOIN??Orders??o????ON??c.customer_id????o.customer_id????AND??o.order_date????m.max_orderGROUP??BY??c.company??Task: convert a correlated subquery in the WHERE clause to a standard join on a derived table31Copyright MySQL ABThe World’s Most Popular Open Source Database31The World’s Most Popular Open Source DatabaseDemonstration :●What did I show earlier that used a correlated subquery●Do you think we can rewrite it to use a better performing block of SQL code●Cool. Lets do it.32Copyright MySQL ABThe World’s Most Popular Open Source Database32The World’s Most Popular Open Source DatabaseAvoid.