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 relates the current innodb_buffer_pool_size setting to the maximal memory load of all InnoDB data and indexes:

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;

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

If the number is far beyond any possible innodb_buffer_pool_size setting, you might consider adding RAM. An alternative is to sum data_length and index_length for just the biggest and most frequently queried tables. Or, leave indexes out of the innodb_buffer_pool_size calculation.

For a more thorough approach, run ...

Show variables;
Show Global Status;

and calculate these values ...

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 Best near 0.5. If much < 0.5, grow innodb_log_file_size, shrink if much larger
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 75% of RAM that's available to MySQL
Est. best InnoDB log file size ceil( gsintval("innodb_os_log_written")/ gsintval("Uptime_since_flush_status")/ gvintval("innodb_log_files_in_group") / 1024 / 1024 / 32 ) * 32 Avg hour of writes rounded up to 32MB boundary
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 >=20, 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
Inserts/sec (Com_insert+Com_insert_select) / Uptime_since_flush_status Tens of thousands is busy, you may need better hardware
Table scan next row seeks/sec Handler_read_rnd_next / Uptime_since_flush_status If high, there are queries that need optimising, esp. with covering indexes

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 @slowlog = if( (select @@slow_query_log)=0,0,1 );
set @innoload = ( select sum(data_length+index_length) 
                  from information_schema.tables 
                  where table_type='base table' and engine = 'innodb'
                );
set @innopool = gvintval("innodb_buffer_pool_size");
set @load2pool = round( @innoload/@innopool, 2);
set @timescale='uptime';
select
  'Slow queries/sec' as Item,
  round( gsintval('slow_queries') / gsintval(@timescale), 2 ) as Rate,
  if(@slowlog,'Find & optimise slow queries','Enable slow query log') as Suggestions
union
select
  'Unindexed queries/sec',
  round( gsintval('select_range_check') / gsintval(@timescale), 2 ),
  'Find & optimise unindexed queries'
union
select
  'Full table scans/sec',
  round( gsintval('select_scan')/gsintval(@timescale), 2 ),
  'Find & optimise queries that do full table scans'
union
select
  'Total InnoDB load / buffer pool size',
  round(@load2pool,2),
  case 
    when @load2pool < .5   then 'InnoDB buffer pool may be reduced without affecting performance'
    when @load2pool >= 1.2 then 'Increasing innodb_buffer_pool_size may improve performance'
    else 'InnoDB buffer pool can accommodate most InnoDB data & indexes'
  end
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 ),
  'Best near 0.5; if well under 0.5, grow innodb_log_file_size, shrink if much larger'
union
select
  'Est. best InnoDB log file size',
  concat( ceil( gsintval("innodb_os_log_written")/
                gsintval("Uptime_since_flush_status")/
                gvintval("innodb_log_files_in_group") / 1024 / 1024 / 32
              ) * 32, "MB" ),
  'Average hour of writes rounded up to 32MB boundary'
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
  'MyISAM 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
  'MyISAM query cache spills/sec',
  round( gsintval('Qcache_lowmem_prunes') /
         gsintval(@timescale), 2 ),
  'If < 5, try turning query cache off'
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
  'MyISAM query cache hits/inserts',
  round( gsintval('Qcache_hits')/gsintval('Qcache_inserts'), 2 ),
  'If < 5, consider disabling the cache'
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
  'Inserts/sec',
  round( ( gsintval('Com_insert') + gsintval('Com_insert_select') ) /
           gsintval(@timescale), 1 ),
  'Tens of thousands is busy, you may need better hardware'
union
select
  'Table scan next row seeks/sec',
  round(gsintval('Handler_read_rnd_next'),2) / gsintval('Uptime_since_flush_status'),
  'If high, there are queries that need optimising, esp. with covering indexes'
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
  'Files opened/sec',
  round( gsintval('opened_files') / gsintval(@timescale), 2 ),
  'If > 5, increase table_open_cache'
union
select
  'Created tmp disk tables/sec',
  round( gsintval('created_tmp_disk_tables' ) / gsintval(@timescale), 2 ),
  'If > 1, check tmp_table_size, max_heap_table_size, Text/Blob query retrievals'
union
select
  "Created_tmp_disk_tables/Queries",
  round( gsintval('created_tmp_disk_tables' ) / gsintval('Questions'), 2 ),
  'If > 4%, check tmp_table_size, max_heap_table_size, Text/Blob query retrievals' ;

select '**but see www.mysqlperformanceblog.com/2012/10/08/measuring-the-amount-of-writes-in-innodb-redo-logs' as footnote;

select
  concat( round( gvintval("innodb_buffer_pool_size")/1024/1024/1024, 1 ), "GB" ) 
  as "InnoBbufferPoolSize",
  round( 100 * 16384 * gsintval("Innodb_buffer_pool_pages_free") / 
         gvintval("innodb_buffer_pool_size"), 1 )
  as "BufferPoolPctFree",
  round( gsintval("Innodb_data_reads") / gsintval("Uptime_since_flush_status"), 0 ) 
  as "BytesRead/s",
  round( gsintval("Innodb_data_read") / gsintval("Uptime_since_flush_status"), 1 ) 
  as "Reads/s",
  round( gsintval("Innodb_data_read") / gsintval("Innodb_data_reads"), 0 ) 
  as "Bytes/Read",
  round( gsintval("Innodb_data_written") / gsintval("Uptime_since_flush_status"), 0 ) 
  as "BytesWritten/s",
  round( gsintval("Innodb_data_writes") / gsintval("Uptime_since_flush_status"), 1 ) 
  as "Writes/s",
  round( gsintval("Innodb_data_written") / gsintval("Innodb_data_writes"), 0 ) 
  as "Bytes/Write";

Corrections & additional entries welcome as always.

Return to the Artful MySQL Tips page