MySQL Tuning Cheat sheet

1. Verify your server version. Are you running the latest community release , or

the latest stable Enterprise release?

            Latest community release mysql 5.1.49 server.

2. Key report section.

a) Are you using the MyISAM storage engine?

The MyISAM storage engine is affected by the key buffer. The InnoDB storage engine is not.

b) Is Buffer used or Current at 75% usage or more?

Increase the key_buffer_size to allocate more key buffer to MySQL.

Key buffer used = 1 – (key_blocks_unused*key_cache_block_size)/key_buffer_size

                            = 1- ((28981*1024)/33554432)

                            = 0.1156 *100

                            = 11%

3. Questions report section.

Questions include all SQL queries as well as MySQL protocol communications. This

section is very useful for understanding how MySQL is being used by your application.

It also offers a nice overview of the effectiveness of the MySQL query cache.

a) What percentage of your queries are DMS?

DMS stand for Data Manipulation Statements, and includes SELECT, INSERT, REPLACE, UPDATE, and DELETE queries.

Select = Com_select      = 117087

Insert = Com_insert + Com_insert_select = 4940 + 23= 4963

Replace = Com_replace + Com_replace_select = 0+0=0

Update = Com_update + Com_update_multi  =  10665+1=10666

Delete = Com_delete + Com_delete_multi =  143+0=143

Percentage of DMS statements =( ($Select + $Insert + $Replace+$Update +$Delete)/Questions)*100

 = ((117087 + 4963 + 0 + 10666 + 143 )/ 393260)*100=  33.78 %

                                   

b) What percentage of your queries are QC Hits?

QC stands for Query Cache. If you don't have a QC Hits line, you have not enabled the MySQL query cache. Ideally, this should account for the majority of your questions.

Query cache hits=(Qcache_hits/(Qcache_hits + Qcache_inserts + Qcache_not_cached)*100

                              = (371/(371+391+10725))*100

                              = 3%

c) What percentage of your queries are _Com?

_Com accounts for all MySQL commands, primarily those that are protocol related. This should be very small.

d) What type of queries are happening most frequently?

All  powered web sites will see the vast majority of their queries as SELECTS.

e) What type of queries show up in the _Com section?

This only becomes important if a significant percentage of your queries are _Com.

f) What is the Percentage of Com_quit?

Com_quit =  (((Connections -2) – ( Aborted_clients/2))/questions)*100

                   =  ((( 13365-2) –(5/2))/ 393362)*100

                  =  3.39%

4. SELECT and Sort report section.

a) What percentage of your SELECT queries are Scans?

A scan is a SELECT query that required scanning the entire table instead of just a subset of it. These types of queries will often show up in your slow query log.

Percentage of select queries scan full table = (select_scan/ com_select)*100

                                                                        = (67122/116897)*100

                                                                        = 57%

b) What percentage of your SELECT queries are Full joins?

A full join is a SELECT query that joins two or more tables together, and then scans the entirety of the joined tables. Again, these types of queries will often show up in your slow query log. If you are seeing a significant number of joins, you may benefit from increasing your join buffer.

Percentage of select queries scan are full joins = (select_full_join/com_select)*100

                                                                        = (4/116897)*100

                                                                        = 0.0034%

Tunable: join_buffer_size, not that this is a perconnection memory allocation, so don't increase it too quickly and monitor it closely.

c) What percentage of your SELECT queries are Sorts?

If you have a significant number of sorts, you may benefit from increasing your sort buffer. To be certain, monitor “SHOW STATUS LIKE 'Sort_merge_passes;”. If this value is increasing, especially if it is increasing quickly, you should increase the size of your sort buffer.

Percentage of select queries are sorts = (Sort_merge_passes/com_select)

                                                            = (0/116897)

                                                            = 0%

Tunable: sort_buffer_size, note that this is a perconnection memory allocation, so don't increase it too quickly and monitor closely.

5. Query Cache report section.

a) Is your query cache enabled?

The query cache stores your SELECT query and its result in memory. If an identical SELECT query is made, the server is able to quickly return the result from the cache.

                        Have_query_cache=yes

b) Is your query cache more than 10-20% fragmented?

c) What is your Insert: Prune and Hit: Insert ratios?

Hit rate= Qcache_hits/( Qcache_hits + Qcache_inserts + Qcache_not_cached)*100

             = (371/(371+391+10725))*100

             = 3%
Insert rate = Qcache_inserts/(Qcache_inserts + com_select) * 100

                   = (557/(557+120867))*100

                 = 0.4%

Prune rate= Qcache_lowmem_prunes / Qcache_inserts

                  = 0/557

                  = 0%

d) Is your query cache too big?

There is a temptation to give MySQL's query cache as large as you possibly can. Unfortunately, there is a known locking bug in MySQL (reported as fixed in 5.0.50) in which the time spent flushing the query cache can lock up the entire server, resulting in poor performance. It is advised that you monitor your database carefully as you increase this query cache, especially if you increase the size of the cache beyond 64M in size.

Tunable: query_cache_size

6. Table Locks report section.

a) What percentage of your table locks show up as Waited?

Percentage of table locks = ( table_locks_waited * 100 / tables_locks_immediate).

                                          = (0*100/23502)

                                           = 0%

Here <1% is ok, at 1% you will to feel pain and at 3+% you are usually dying in locks or be already dead.

7. Tables report section

a) What percentage of your table cache is already used?

If your table cache is already 100% used, you may want to consider increasing it. However, monitor your total memory consumption carefully, and don't set this value larger than you need. Note that different threads can open the same table, thus on a busy database you can frequently have more tables open than actually exist in your database.

Table cache fill = (open_tables/table_open_cache) * 100

                         = (30/1024)*100

                         = 2.9% (should not be >=95%)

 Tunable: table_cache

b) How many new tables are opening per second?

If you are seeing as much or more than 1 table opened per second, this is usually a good indication you should be increasing your table cache.

No of new tables are opening per second = (open_tables/uptime)

                                                                     = 0.00001

Tunable: table_cache

8. Connections report section

a) What is the maximum number of connections you've seen used?

By default, MySQL allows 100 simultaneous connections, however on a well tuned server most queries last less than a second so even on a busy web server you rarely have more than a couple dozen connections simultaneous connections. It is generally not advisable to increase the connection limit beyond 100, unless you already have a well tuned server and still require this many simultaneous connections.

Maximum no of used connections = (max_connections/max_used_connections)

                                                         = (200/17)

                                                         = 11%

b) How many connections are opening per second?

No of connections opening per second= connections/uptime

                                                               = (13589/1909943)

                                                               = 0.0071

9. Created Temp report section

a) What is your temp table, to disk table, to file ratio?

A “temp table” is a temporary table that is created in memory. A “disk table” is a temporary table that is created on disk. Obviously you will see better performance if you have more temporary tables created in memory than on disk.

Percentage of temporary tables created in disk.

= (created_tmp_disk_tables/created_tmp_tables

+ created_tmp_disk_tables)*100

                                                = (10234/(10234+29553))*100

                                                = 25.72%

Tunables: tmp_table_size, max_heap_table_size, both need to be raised together. However, this memory is allocated perconnection, so be careful not to increase either too quickly.

10. Threads report section

a) How many threads are being created per second?

Threads created per second = threads_created/uptime

                                                = (35/ 1910515)

                                                = 0.00001

b) What percentage of your threads is using the thread cache?

There is minimal overhead in creating threads, however if you see a large number of threads being created per second that aren't using the thread cache, you can reduce your CPU load by increasing your thread cache.

Percentage of threads in thread cache = (threads_cached/thread_cache_size)*100

                                                              = (24/128)*100

                                                              = 18%

Tunable: thread_cache_size

11. Aborted report section

a) Are you seeing a high number of aborted clients?

No

b) Are you seeing a high number of aborted connections?

No

Neither should be common, and should be at 0 or close to 0/s. If either value is high, you need to determine what is causing these errors.

12. Bytes report section

13. InnoDB Buffer Pool report section

a) How much of your buffer pool is currently being used?

As a general rule, if you are using InnoDB, approximately 70% of your available RAM should be given to MySQL's InnoDB Buffer Pool. If over 80% of your buffer pool is being used, you should look into making more RAM available to MySQL. You do not ever want your Buffer Pool to be 100% full.

Buffer pool used = 100 * (Innodb_buffer_pool_pages_data

/Innodb_buffer_pool_pages_total)

                           = 100 * (9306/65536)

                           = 14%

 Tunable: innodb_buffer_pool_size

b) Is your read ratio lower than .1?

The read ration is the number of reads from disk versus the number of reads from RAM. If this is higher than .1, odds are that your Buffer Pool is too low.

c) Are you seeing significant reads from files?

If you are seeing a significant number of reads from disk, you likely should be increasing your Buffer Pool.

            Here Innodb_pages_read and Innodb_data_read should be nearly equal, then only data reads are significant. 

d) How often is your buffer pool being flushed to disk?

Percentage of buffer pool flushing = 100 * (Innodb_buffer_pool_pages_flushed

/Innodb_pages_written)

= 100 * (25172/25172)

= 100%

14. InnoDB Lock report section

a) Are you seeing a significant number of waits for locks, or a significant amount of

time being spent waiting for locks?

If innodb_row_lock_current_waits is greater than ‘0’, then more lock can be occurred.

15. InnoDB Data, Pages, Rows report section

a) What is your ratio of writes to fsyncs?

By default, InnoDB is ACID compliant. This means that each transaction needs to be flushed to disk. If you can afford to loose a second of transactions, you may want to consider telling InnoDB to only flush to disk once every second.

Percentage of writes to fsyncs = 100*(Innodb_os_log_fsyncs /Innodb_os_log_written)

                                         =100* (30150 /73060352)

                                          = 0.04%

Tunable: innodb_flush_log_at_trx_commit, set to 0 to have InnoDB only flush to disk

every second.