Skip navigation.
Home
FromDual

Languages
Home

Performance Tuning Key for MySQL

For a pure database system tuning please go first here: Database Health Check for MySQL.

See also MySQL Performance Tuning Primer.

This document is still under construction! Thus it is still a little chaotic. Please feel free to mail recommendations, suggestions, etc. to us.

This key should give you a guide how to best tune you MySQL database systematically... It should also work similar for other RDBMS.

Caution: Some recommendations are dangerous! Dangerous means you can loose or get inconsistent data in certain cases. Only use them if you know what you are doing!!!

Acknowledgment

Thanks to the following people for hints:

  • Jens Bollmann

Efficiency of Performance Tuning measurements

Before start tuning think about the following graph:

Tuning Efficiency

And see also Relative Impact on Performance (p. 33 ff.)

Start

000. Do you have performance problems?

  • Yes: --> 001
  • No: --> Do not tune your system it only can get worse! --> back

001. Have you ever tuned your system?

002. You have already started to tune your system

Chose the area you want to tune (the following order is recommended):

  • Architecture & Design Tuning --> 100
  • SQL Query Tuning --> 200
  • Application Tuning --> 600
  • Server Tuning --> 300
  • Operating System Tuning --> 400
  • Hardware Tuning --> 500

003. You have not yet tuned your system

You can choose now between 3 tuning paths:

  • the more systematical --> 004
  • the more profitable --> 010.
  • the quick shot --> 050.

004. You choose the more systematical path

  • go through 100 ff. and then start from the beginning of this tuning key.

010. You choose the more profitable path.

Have you enabled the slow query log?

011. You did not yet enable the slow query log

Enable the slow query log with a long query time of for example 10 and then restart the server.
Then wait for a appropriate period of time which represents your typical business (for example 1 day, or 1 week or 1 month).

012. You have enabled the slow query log

Did you get any slow queries?

013. You have enabled the slow query log but no queries in it

Check if everything works fine by provoking a slow query (for example cartesian product: SELECT a.*, b.*, c.* FROM table a, table b, table c);
Have you now some queries in it?

014. Slow query log works fine but now queries in it

Decrease long query time to for example 5, then 3, then 2 and then 1 second.
Have you now some queries in it?

015. Slow query log works fine but now queries running longer than 1 second in it

Is the system performance acceptable for you now?

020. You got some queries in the slow query log

Is the amount of slow queries huge (for example > 20-50)?

021. You got a huge amount of queries in the slow query log

Treat the slow query log with the following command:
# mysqldumpslow -s t slow.log > slow.profile
For windows users: You need perl and this script from a MySQL UNIX package.
Pick the first query look for its original in the slow.log and take the query from the slow log!!!

050. You choose the quick shot path

Is your system read heavy (for example com_select / (com_insert* + com_replace* + com_update* + com_delete*) > 4)?

051. Your system is read heavy

Enable query cache. Does this help?

  • Yes: --> 000
  • No: Disable query cache again. --> 052

052. Your system is not read heavy

Do you use InnoDB tables only?

053. You use InnoDB tables only

Increase innodb_buffer_pool_size according your memory up to 80% of your RAM. Consider that system is NEVER swapping!!!

054. Do you use MyISAM tables only?

055. You use MyISAM tables only

Increase key_buffer_size according your memory up to 25-33% of your RAM. Consider that system is NEVER swapping!!!

056. You use MyISAM and InnoDB tables mixed

Split up to 80% of you RAM according the amount of your MyISAM and InnoDB data. Consider that system is NEVER swapping!!!

100. Architecture & Design Tuning

  • Index tuning --> 150
  • Data type tuning --> 160
  • Table Design tuning --> 170
  • Storage engines --> 101
  • Character sets
  • Concurrent inserts locking --> MyISAM
  • Replication --> 190
  • Architecture --> 140

101. Do you use MyISAM tables?

102. Do you use InnoDB tables?

103. Do you use other storage engine tables?

110. You use MyISAM tables

Do you use FULLTEXT indexing?

111. You use MyISAM tables and NO FULLTEXT indexing

AVG_ROW_LENGTH, MAX_ROWS, PACK_KEYS, ROW_FORMAT, myisam_data_pointer_size, myisampack

130. You use other storage engines tables

140. General database architecture tuning

Do you think your application can run without the use of a RDBMS?

  • No: --> 141
  • Yes: Try to avoid RDBMS if you do not really need it. There are some much faster solutions around! --> 000

141. Architecture tuning

The following items can help:

  • Application on same/different servers
  • MySQL Partitions (new with 5.1)
  • Replication --> 190
  • Physical partitioning of the data into different nodes.
  • MySQL Cluster
  • Upgrade to newer releases.

150. Indexing

Do you have fully redundant indices?

  • Yes: --> remove them --> 151
  • No: --> 151

151. No: more fully redundant Indexes

Do you have partially redundant indexes?

  • Yes: --> try to remove them --> 152
  • No: --> 152

152. No: more fully redundant Indexes and optimised partially redundant indexes

Do you have indices with attributes in different order?

  • Yes: --> try to reorder attributes and remove the (partial) redundant indices --> 153
  • No: --> 153

153. Indexes cleaned up

Some more hints for indexing

  • WHERE clause
  • Aggregation functions
  • ORDER BY clause
  • JOIN operations
  • High selectivity (> 10)
  • Low selectivity (< 10)
  • small tables
  • covering indexes
  • prefixed indexes
  • pk with myisam
  • cardinality

160. Data type tuning

  • Use smallest INT type (TINYINT < SMALLINT < MEDIUMINT < INT < BIGINT)
  • Use FLOAT instead of DOUBLE
  • Use VARCHAR instead of CHAR (not always better!)
  • Use BIT for status

170. Table design tuning

  • Remove not needed attributes.
  • Remove or move old/not used rows to other tables
  • For MyISAM: AVG_ROW_LENGTH, MAX_ROWS, PACK_KEYS, ROW_FORMAT, myisam_data_pointer_size, myisampack

180. You use MyISAM tables with FULLTEXT index

For large datasets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load data into a table that has an existing FULLTEXT index.

190. Replication tuning

Does the slave fall often/much behind the master?

191. Your slave is falling often/much behind the master

Which thread is the slow one. The IO_thread or the SQL_thread?

  • IO_thread --> 192
  • SQL_thread --> 193
  • None of these. --> Mail the problem.

192. Your slave is falling behind the master because of the IO_thread

Try to increase network throughput from master to slave or decrease amount of data which is shuffled from master to slave. Does it help?

193. Your slave is falling behind the master because of the SQL_thread

Try to reduce the amount of data (filtering) or buy better HW for your slave or do tuning on the slave side.

200. SQL Query Tuning

  • SELECT --> 201
  • ORDER BY --> 240
  • GROUP BY --> 250
  • INSERT/UPDATE/DELETE --> 220
  • When you think your queries are perfect --> 300

201. SELECT

--> Chap. 7.2

220. INSERT/UPDATE/DELETE

240 ORDER BY 7.2.12 Optimisation

  • --> filesort
  • Index
  • increase sort_buffer_size
  • increase read_rnd_buffer_size
  • point tmpdir to a dedicated file system

241. Is the query really needed like this?

242. Change the query

243. Check if index is used for ORDER BY (Using filesort if NOT)

  • --> Index is used (NO filesort) --> 245
  • --> Index is NOT used (filesort) --> 244

244. Try to find a index matching ORDER BY

  • --> Go to 245

245. Try to (regularly) store the rows in sorted order (ALTER TABLE ... ORDER BY ...)

  • --> Go to 246

246. Try to increase sort_buffer_size for this query

  • --> Go to 247

247. Try to increase read_rnd_buffer_size

  • --> Go to 248

248. Move tmpdir to other Disk

250 GROUP BY 7.2.13

251. Is the query really needed like this?

252. Change the query

253. Check if index is used for GROUP BY (all attributes from the same index in order, NO temporary)?

  • --> Index is used (NO temporary) --> 255
  • --> Index is NOT used (Using temporary) --> 254

254. Try to find a index matching GROUP BY or do GROUP BY in the same order than SORT BY

  • --> Go to 255

255. GROUP BY sort according expression. Do you really need a sorting?

  • --> YES --> 257
  • --> NO --> 256

256. Try to add ORDER BY NULL to your statement

257. Try to (regularly) store the rows in sorted order (ALTER TABLE ... ORDER BY ...)

  • --> Go to 258

258. When query is still to slow, try to

  • Optimise table and/or data structure (compression, static row format, exclude columns, exclude old rows, data types)
  • Create your own aggregation table
  • Partition horizontally or vertically

300. Server Tuning

http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

SHOW STATUS analysis

301. Is your MySQL server already running a reasonable amount of time (uptime, 1 period of usual business (for example 1 day or 1 week))?

  • YES --> 302
  • NO --> Wait until then. In the meantime you can enable the slow query log --> Slow query log.

302. What storage engine are you using?

  • Mixed --> 303
  • MyISAM only --> 320
  • InnoDB only --> 330


303. You are using mixed storage engines, general

 parameters


304. Connections

  • Connections
  • Aborted_clients
  • Aborted_connects
  • Threads_cached
  • Threads_connected
  • Threads_created increasing --> increase thread_cache_size
  • Threads_running
  • Max_used_connections


305. Throughput

  • Bytes_received
  • Bytes_sent
  • Questions


306. Slow query log enabled?

  • No: --> Enable slow query log first and then come back.
  • Yes: -->


307. Check for slow queries. Do you have slow queries?

 Slow_queries

  • No: -->
  • Yes: --> Go to analysing and optimising queries and then come back here.


308. IO contention:

  • Table_locks_immediate
  • Table_locks_waited


308a. IO contention:

    Have you binary logging enabled?

  • No: --> 309
  • Yes: --> 308b


308b. IO contention:

    Do you need binary logging?

  • No: --> Disable binary logging. --> 000
  • Yes: --> 308c


308c. IO contention:

    Is Binlog_cache_disk_use greater than 0?

  • No: --> 309
  • Yes: --> Increase binlog_cache_size --> 000


309. Status about temporary resources

    Do you see high created_tmp_disk_tables values (high: Created_tmp_disk_tables / Created_tmp_tables > 5%)?

  • Yes: --> increase tmp_table_size or optimise queries --> 309a
  • No: --> 309a
  • Created_tmp_files


309a. Status about temporary resources (Created_tmp_files)

  • Created_tmp_files


310. Files, tables, streams

  • Open_files
  • Open_streams
  • If Open_tables is close to table_cache or Opened_tables increasing increase table_cache.


310a. Table .frm file

    Can you afford to lose some tables/get corrupt table after instance crash?

  • Yes: --> disable syn_frm -->
  • No: -->


311. select

  • Select_full_join --> Should be zero
  • Select_full_range_join --> only critical when high
  • Select_range
  • Select_range_check
  • Select_scan


312. sort

  • Sort_merge_passes --> high then increase sort_buffer_size
  • Sort_range
  • Sort_rows
  • Sort_scan


313. Are you using the MySQL query cache?

  • Yes: -->
  • No: -->
  • Qcache_free_blocks
  • Qcache_free_memory
  • Qcache_hits
  • Qcache_inserts
  • Qcache_lowmem_prunes


314. Qcache_not_cached high even free_memory available?

  • --> YES 5.14.1 Try to avoid queries which are not cachable...
  • --> NO
  • Qcache_queries_in_cache
  • Qcache_total_blocks
  • --> flush query cache

Qcache_hits / (Qcache_hits + Com_select) * 100 = Percentage of SELECT queries which can be cached.

Qcache_not_cached / (Qcache_not_cached + Qcache_inserts) * 100 = Not cachable queries.

(Com_select + Qcache_hits)/(Com_insert* + Com_update* + Com_delete* + Com_replace*) = Read/Write ratio

Qcache_hits / Qcache_inserts = Cache hit ratio should be large (> 3)


316. delayed inserts

  • Delayed_errors
  • Delayed_insert_threads
  • Delayed_writes
  • Not_flushed_delayed_rows


320. You are using pure MyISAM

  • If you have a small database decrease myisam_data_pointer_size
  • If you do a lot of OUTER JOINS try to change myisam_stats_method
  • If Key_blocks_unused is ALWAYS high decrease key_buffer_size and use resources somewhere else.
  • If Key_blocks_used * key_cache_block_size is close to key_buffer_size increase key_buffer_size.
  • If Key_blocks_unused is near to 0 increase key_buffer_size.
  • If Key_read_requests/Key_reads is less than 100 increase key_buffer_size.

InnoDB tuning

InnoDB application tuning


330. PRIMARY KEY tuning

    Are you updating your PRIMARY KEY's?

  • No: --> 330a
  • Yes: Updating PK's is expensive. Try to avoid this. --> 330a


330a. Data load tuning

    Do you have performance problems with your data load?

  • No: --> 345
  • Yes: --> 331


331. Data load tuning for InnoDB tables

    Are you dropping indexes on InnoDB tables before loading?

  • No: --> 332
  • Yes: Load on InnoDB table is best done on table with indexes (not like MyISAM). --> 332


332. Data load tuning for InnoDB tables

    Are your rows sorted in PRIMARY KEY order?

  • Yes: --> 333
  • No: Importing is fastest if the rows are presorted in the PRIMARY KEY order. Try to sort them before loading either on OS level or dumping with ORDER BY. --> 333


333. Data load transaction tuning

    Have you AUTOCOMMIT enabled or are you committing after each DML statement?

  • No: --> 334
  • Yes: Consider to pool some or all statements into one transaction (one single disk cannot do more than 250 trx/s!) with START TRANSACTION and COMMIT. Disable AUTOCOMMIT. --> 334


334. Data load INSERT tuning

    Are you using multi row INSERT syntax?

  • Yes: --> 335
  • No: Use multi row INSERT syntax if possible. --> 335


335. Data load KEY check tuning

    Do you have UNIQUE KEYs or FOREIGN KEYs on your table?

  • No: --> 336
  • Yes: Disable UNIQUE_CHECKS and/or FOREIGN_KEY_CHECKS during import. --> 336


336. Data load InnoDB variable tuning

    Can you stop and restart your database server before/after data load?

  • No: --> 338
  • Yes: Make innodb_buffer_pool_size and innodb_log_file_size bigger during import. --> 337


337. Data load InnoDB variable tuning

    Can you manually roll back or restart your data load when system crashes during data load?

  • No: --> 338
  • Yes: Consider setting innodb_flush_log_at_trx_commit != 1 during load--> 338


338. Data load with LAST_INSERT_ID

    Are you using LAST_INSERT_ID in your data load transactions?

  • No: --> 339
  • Yes: Try to avoid LAST_INSERT_ID during data load. --> 339


339. Data load ROLLBACK or crash tuning

    Can you afford to wait for hours when transaction must be rolled back or database crashes during data load?

  • Yes: --> 340
  • No: Beware of huge ROLLBACK's (happens also after crash). This can take hours. Make smaller pieces of your transaction. --> 340


340. Delete rows from table during data load

    Do you have to delete all rows from a table?

  • No: --> 345
  • Yes: Use TRUNCATE table instead of DELETE. Disable FOREIGN KEY constraints before if necessary. --> 345

InnoDB Table Design


345. PRIMARY KEY tuning

    Do you have "long" PRIMARY KEY's (PK)?

  • No: --> 346
  • Yes: In InnoDB you should only use short PRIMARY KEY's (rule of thumb: <= 20 byte). Best is AUTO_INCREMENT [TINY|SMALL|MEDIUM]INT --> 346


346. InnoDB index tuning

    Have you indices on long CHAR or VARCHAR attributes?

  • No: --> 347
  • Yes: Try to avoid indices on long CHAR or VARCHAR attributes. Consider prefixed indices. --> 347


347. InnoDB Index tuning

    Have you defined your tables with character set = utf8?

  • No: --> 348
  • Yes: Because utf8 uses more space (3 bytes) for indices and CHAR attributes you should only define your columns with utf8 when you really need it! --> 348


348. CHAR tuning

    Do you use CHAR attributes with varying field length or utf8 character set?

  • No: --> 350
  • Yes: Choose VARCHAR instead of CHAR if you have varying field length or if you use utf8 character set. --> 350

InnoDB parameter tuning


350. Transactions and full ACID compliancy

    Do you need transactions and/or full ACID compliancy?

  • Yes: --> 355
  • No: --> 351


351. Transactions and full ACID compliancy

    Have you already considered to use MyISAM?

  • Yes: --> 352
  • No: --> 100


352. You do NOT need transactions or full ACID compliancy

    Can you afford to loose little amount of data in case of crash?

  • No: --> 355
  • Yes: Set flush_log_at_trx_commit = 2 --> 353


353. InnoDB double write tuning

    Can you risk to get corrupted data after system crash?

  • No: --> 355
  • Yes: disable innodb_doublewrite --> 355


355. You NEED transaction and/or full ACID compliancy

    Do you need distributed transactions (XA) feature (for example for replication)?

  • Yes: --> 356
  • No: set innodb_support_xa = 0 --> 356


356. InnoDB buffer pool tuning

    Do you have a lot of IO activity or is Innodb_buffer_pool_pages_free = 0 and do you have enough free memory (RAM)?

  • No: --> 357
  • Yes: --> Increase innodb_buffer_pool_size up to 50-80% of your memory (less than 1800 Mbyte on 32-bit systems) if innodb_buffer_pool_pages_free is 0. Avoid swapping. --> 357


357. InnoDB additional memory pool tuning

    Have you errors in the MySQL error log concerned with innodb_additional_mem_pool?

  • No: --> Do not touch this parameter --> 358
  • Yes: --> Innodb_additional_mem_pool is increased dynamically by MySQL. Increase this value only moderately if you have error messages in your MySQL error log --> 358


358. InnoDB log file size tuning

    Do you have a lot of I/O activity on your disk system where your InnoDB log file resides?

  • No: --> 359
  • Yes: --> Increase your innodb_log_file_size up to (innodb_buffer_pool_size / innodb_log_files_in_group) but not more than (4 Gbyte / innodb_log_files_in_group). Larger log files means longer recovery time! 359


359. InnoDB thread concurrency tuning

    Do you have more than 4 CPU's/cores (without hyper threading) or many connections?

  • No: --> 359a
  • Yes: Try to change innodb_thread_concurrency. A good point to start is: (#CPU + #Disks). Other recommendations are: 4, (#Disks * #CPU * 2) or (#CPU * 2). This parameter is very system and OS dependent you have to play around with it and benchmark your system! --> 359a


359a. InnoDB max dirty pages tuning

    Do you have several little peaks a day you want to break?

  • No: --> 359b
  • Yes: decrease innodb_max_dirty_pages_pct to 80-50% or even lower during peak time --> 359b

359b. InnoDB log buffer size tuning

    Do you have (high) Innodb_log_waits?

  • No: --> 359c
  • Yes: Increase innodb_log_buffer_size. --> 359c

359c. InnoDB flush log tuning

    Do you have IO contention?

  • No: --> 359f
  • Yes: --> 359d

359d. InnoDB flush log tuning

    Do you have have battery cache disk buffers?

  • No: --> 359e
  • Yes: set innodb_flush_log_at_trx_commit = 2 and test very good if your disk cache batteries really work! -->359e

359e. InnoDB flush log tuning

    Can you afford to lose up to 1 second of your data?

  • No: --> 359f
  • Yes: set innodb_flush_log_at_trx_commit = 0 -->359f

359f. InnoDB checksum tuning

    Can you risk to be not aware of physical data corruption?

  • No: --> 359g
  • Yes: disable innodb_checksums --> 359g

359g. InnoDB flush method tuning

    Have you ever tried to change innodb_flush_method?

  • Yes: --> 359i
  • No: Try innodb_flush_method fsync, O_DSYNC or O_DIRECT for Linux and BSD (Solaris) --> 359i

359i InnoDB binlog tuning

    Can you afford to loose some data in the binlog in case of database crash?

  • No: --> 359j
  • Yes: Disable innodb_safe_binlog and/or sync_binlog --> 359j

InnoDB data storage tuning

359j. Rebuilding InnoDB tables

    Are you doing a lot of DML statements on your InnoDB table?

  • No: --> 359k
  • Yes: --> Rebuilding the table will remove fragmentation and reduces page splits. 359k

359k. Separate InnoDB log files from InnoDB tablespace files

    Do you have high I/O on your disk and several disks in your box?

  • No: --> 359l
  • Yes: Separate your InnoDB tablespace files from your InnoDB log files (on different physical disks) if you do not have "stripe everything everywhere" disk architecture. --> 359l

359l. InnoDB tablespace tuning

    Do you have per-file tablespaces?

  • Yes: --> 359m
  • No: In MySQL 4.1, using per-file tables increases performance. --> 359m

359m. InnoDB tablespace tuning with innodb_file_per_table

    Do you have high I/O on disks where your InnoDB tablespace files reside?

  • No: --> 359n
  • Yes: Try to separate your InnoDB table with the hot spot on separate physical disk if you do not have "stripe everything everywhere" disk architecture --> 359n

InnoDB hardware and OS tuning

359n. I/O system access tuning

    Are your InnoDB tablespace file or log files stored on normal file systems?

  • No: --> 359o
  • Yes: Try to use raw devices. This should only be done as last resort. --> 359o

359o. CPU tuning

    Have your CPU's hyper threading mode?

  • No: --> 359p
  • Yes: Disable hyper threading. It helps sometimes. --> 359p

359p. Battery supplied disk cache

    Has your I/O system battery supplied disk cache?

  • No: --> 100
  • Yes: Consider to set innodb_flush_log_at_trx_commit = 2. --> 100

400. Operating System Tuning

  • Avoid Windows and old BSD kernels. Use mainstream OS like Linux, Solaris.
  • Use 2.6 Linux kernel.
  • Some FS perform not optimal. Use Reiserfs 3.x
  • Use NPTL thread library.

File System tuning


410. File System Tuning

    Have you mounted your File System with noatime and nodiratime?

  • Yes:
  • No:


500. Hardware Tuning

    Are you using hyper threading on your system?

  • Yes: --> 501
  • No: --> 502


501. You are using hyper threading

    Disable hyper threading. Does it help?

  • Yes: --> 502
  • No: --> 502


502. Disabling hyper threading did not help

    Are you using 64-bit Architecture?

  • Yes: --> End
  • No: --> 503


503. You are using 32-bit Architecture

64-bit architectures sometimes performs better. Try using a 64-bit architecture. End


600. Application tuning

    Do you have data loads which are to slow?

  • Yes: --> 650
  • No: --> 601

601. Application tuning other problems

Do you have backup/restore tuning problems?

602. Application tuning other problems

Not yet implemented. Sorry!


640. Backup/restore tuning

650. Data load tuning

  • General hints --> 651
  • MyISAM tables --> 655
  • FULLTEXT index --> 660
  • InnoDB tables --> 330

651. General hints for data load tuning

  • Use LOAD DATA INFILE (Section 13.2.5, “LOAD DATA INFILE Syntax”)
  • Use multiple-row INSERT syntax (7.2.16. Speed of INSERT Statements)
  • Temporarily turn off the uniqueness checks during the import session
  • Wrap load with table locks
  • Use INSERT DELAYED
  • Avoid SELECT FROM LAST_INSERT_ID

655. Data load tuning for MyISAM tables

  • Increase key_buffer_size

660. Data load tuning for MyISAM tables with FULLTEXT indices

For large datasets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load data into a table that has an existing FULLTEXT index.