Optimisation cheatsheet

from the Artful MySQL Tips List


If you're looking for one tweak to improve MyISAM or InnoDB query performance, run these queries, then for MyISAM adjust key_buffer_size, and for InnoDB adjust innodb_buffer_pool_size accordingly:

MyISAM:
select 
  'myisam', 
  concat(round(raw/1024/1024,1),'MB') as indexLen, 
  @@key_buffer_size as key_buffer_size, 
  raw-@@key_buffer_size as 'Suggested change'
from (
  select sum(index_length) as raw
  from information_schema.tables
  where engine ='myisam'
) sum;

InnoDB: for not-too-large databases, it can be helpful to see how closely you can make InnoDB behave like an in-memory database engine. This query estiates the maximal memory load of all InnoDB data and indexes:

select sum(data_length)/1024/1024 + sum(index_length)/1024/1024
from information_schema.tables where engine='innodb'; 

If the number is 75% or less of RAM available to MySQL and you set innodb_buffer_pool_size to it, InnoDB will in fact function very like an in-memory database.

For larger databases, leave indexes out of the innodb_buffer_pool_size calculation:

select 
  'innodb', 
  concat(round(raw/1024/1024,1),'MB') as totalLen, 
  @@innodb_buffer_pool_size as innodb_buffer_pool_size, 
  round(raw-(1.1*@@innodb_buffer_pool_size),0) as 'Suggested change'
from (
  select sum(index_length) + sum(data_length) as raw
  from information_schema.tables
  where engine ='innodb'
) sum;

For a more thorough approach, run ...

Show variables;
Show Global Status;

and calculate these measures ...

Measure Formula Tweaks
Slow queries/sec Slow_queries/Uptime_since_flush_status Find & optimise those queries
Queries not using indexes Select_range_check / Uptime_since_flush_status Find & optimise those queries using indexes
Queries needing full table scans/sec Select_scan/Uptime_since_flush_status Find & optimise those queries
InnoDB buffer pool inefficiency innodb_buffer_pool_reads / innodb_buffer_pool_read_requests Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB page reads and buffer pool innodb_pages_read / innodb_buffer_pool_read_requests As above
InnoDB log file thrashing innodb_log_file_size * innodb_log_files_in_group / innodb_buffer_pool_size Grow innodb_log_file_size if > 0.5
Mins between InnoDB log rotation Uptime / 60 * innodb_log_file_size / Innodb_os_log_written Grow log size if < 30 min, shrink if > 60 min, but see here.
InnoDB buffer pool reads/sec (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) / Uptime_since_flush_status If high, increase Innodb_buffer_pool_size up to 70% of RAM
Query cache spills/sec Qcache_lowmem_prunes / Uptime_since_flush_status If high, grow query_cache_size up to 50
Query cache hits/sec Qcache_hits / ((Qcache_hits+Com_select) If < 5, try turning query cache off
Query cache hits/inserts Qcache hits/Qcache_inserts If low, consider disabling the cache
MyISAM cache hit rate key_read_requests/key_reads If < 10, grow key_buffer_size
MyISAM key read rate key_reads/@timescale Should be high when the buffer is full
Temp tables created/sec Created_tmp_disk_tables / Uptime_since_flush_status If high, grow tmp_table_size, max_heap_size. Use HEAP tables in queries
Database changes / connection Com_change_db / Connections Use db.table syntax
Processes/connection Threads_created / Connections If high, grow thread_cache_size
Files opened/sec Opened_files / Uptime_since_flush_status If > 5, increase table_open_cache
Queries/sec Queries/Uptime_since_flush_status Thousands is busy: you may need better hardware

To automate this, just run the following script, which creates two small utility functions to fetch system variable and global status values, and then runs a query to analyse a few important variable and status values.

The script might need two small touchups:

1. If the database collation isn't utf8_unicode_ci, you may need to edit or comment out the collate argument in each function.

2. To change report time scope from since-last-startup to since-last-flush-status, change @timescale from Uptime to Uptime_since_flush_status:

set global log_bin_trust_function_creators=1;

drop function if exists gsintval;
delimiter go
create function gsintval( pname varchar(128) ) returns bigint
begin
  declare ret bigint;
  select variable_value into ret 
    from information_schema.global_status 
    where variable_name=pname collate utf8_unicode_ci;
  return ret;
end;
go
delimiter ;

drop function if exists gvintval;
delimiter go
create function gvintval( pname varchar(128) ) returns bigint
begin
  declare ret bigint;
  select variable_value into ret 
    from information_schema.global_variables 
    where variable_name=pname collate utf8_unicode_ci;
  return ret;
end;
go
delimiter ;

set @timescale='uptime'; 
select  
  'Slow queries/sec' as Item,  
  round( gsintval('slow_queries') / gsintval(@timescale), 2 ) as Rate,
  'If > 0, find & optimise slow queries' as Suggestions
union 
select  
  'Unindexed queries/sec',  
  round( gsintval('select_range_check') / gsintval(@timescale), 2 ), 
  'If > 0, find & optimise slow queries'
union 
select  
  'Full table scans/sec',  
  round( gsintval('select_scan')/gsintval(@timescale), 2 ), 
  'If > 0, find & optimise slow queries'
union 
select  
  'InnoDB buffer pool inefficiency',  
  round( gsintval('innodb_buffer_pool_reads') / 
         gsintval('innodb_buffer_pool_read_requests'), 2 ),
  'Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM'
union
select  
  'InnoDB pages read and buffer pool',  
  round( gsintval('innodb_pages_read') / 
         gsintval('innodb_buffer_pool_read_requests'), 2 ),
  'As above'
union
select  
  'InnoDB log thrashing',  
  round( gvintval('innodb_log_file_size') * 
         gvintval('innodb_log_files_in_group') / 
         gvintval('innodb_buffer_pool_size'), 2 ),
  'Grow log size if < 30 min'         
union 
select  
  'Mins between InnoDB log rotation',  
  round( gsintval('uptime') / 60 *  
  gvintval('innodb_log_file_size') /  
  gsintval('Innodb_os_log_written'), 2 ),
  'Grow log size if < 30 min, shrink if > 60 min'
union 
select  
  'Query cache hits/sec',  
  round( gsintval('qcache_hits') /  
         (gsintval('qcache_hits') + gsintval('com_select')), 2 ),
  'If high, grow query_cache_size up to 50'
union 
select  
  'Query cache spills/sec',  
  round( gsintval('Qcache_lowmem_prunes') /  
         gsintval(@timescale), 2 ),
  'If < 5, try turning query cache off'
union 
select  
  'Query cache hits/inserts',  
  round( gsintval('Qcache_hits')/gsintval('Qcache_inserts'), 2 ),
  'If < 5, consider disabling the cache'
union 
select  
  'MyISAM cache hit rate',  
  round( gsintval('key_read_requests')/gsintval('key_reads'), 2 ),
  'If < 10, grow key_buffer_size'
union 
select  
  'MyISAM key read rate',  
  round( gsintval('key_reads')/gsintval(@timescale), 2 ),
  'Should be high when the buffer is full'
union 
select  
  'Temp tables created/sec',  
  round( gsintval('Created_tmp_disk_tables')/gsintval(@timescale), 2 ), 
  'Grow tmp_table_size, max_heap_size. Use Heap tables in queries'  
union 
select  
  'Database changes/connection',  
  round( gsintval('com_change_db') / gsintval('connections'), 2 ),
  'Use db.table syntax'
union 
select  
  'Processes/connection',  
  round( gsintval('threads_created') / gsintval('connections'), 2 ),
  'If high, grow thread_cache_size'
union 
select  
  'Queries/sec',  
  round( gsintval('queries') / gsintval(@timescale), 2 ),
  'Thousands is busy: you may need better hardware'
union
select  
  'InnoDB buffer reads/sec',  
  round( ( gsintval('innodb_buffer_pool_reads') +  
           gsintval('Innodb_buffer_pool_pages_flushed')) /  
           gsintval(@timescale), 2 ),
  'Thousands is busy: you may need better hardware'
union 
select 
  'Writes/sec',
   round( ( gsintval('Com_insert') + 
            gsintval('Com_delete') + 
            gsintval('Com_delete_multi') +      
            gsintval('Com_replace') + 
            gsintval('Com_update') + 
            gsintval('Com_update_multi')
          ) / gsintval(@timescale), 2 ),
   'More than 40 is busy, you may need better hardware'
union
select  
  'Files opened/sec',  
  round( gsintval('opened_files') / gsintval(@timescale), 2 ),
  'If > 5, increase table_open_cache' ;

About InnoDB log rotation see here.

Corrections & additional entries welcome as always.

Return to the Artful MySQL Tips page