Optimisation cheatsheet

from the Artful MySQL Tips List


If you're looking for simple tweaks to improve MyISAM and InnoDB query performance, set key_buffer_size high enough to contain all MyISAM indexes, and set innodb_buffer_pool_size high enough to contain all InnoDB data and indexes:

Database load:

select engine,data,indexes,total
from (
  select 
    ifnull(engine,'TOTALS') as engine, 
    concat(data,' GB') as data, 
    concat(indexes,' GB') as indexes, 
    concat(tot,' GB') as total,
    if(engine is null,-1,tot) as ord
  from (
    select   
      engine,  
      round( sum(data_length)/1024/1024/1024, 2 ) as data,  
      round( sum(index_length)/1024/1024/1024, 2 ) as indexes,  
      round( sum(data_length+index_length)/1024/1024/1024, 2 ) as tot
    from information_schema.tables
    where engine is not null and engine not in('information_schema','performance_schema')
    group by engine with rollup
  ) sums
) list
order by list.ord desc;
Beware that on 32-bit systems the limit is 4G. On 64-bit systems, the limit is OS-dependent. If the InnoDB total size 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. Of course it may be that you never query all or even most data and indexes at one time, so you may want to pro-rate the numbers to calculate likely load. In Linux, swapping can be discouraged by adding ...
vm.swappiness=0 
... on its own line in /etc/sysctl.conf.

If the data and index loads are far beyond any possible setting, you may have to add RAM.

For a more thorough approach, read https://dev.mysql.com/doc/refman/8.0/en/memory-use.html (or the iteration of that manual page for your MySQL version), then 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 available to MySQL
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 or if version=5.7, disable. Removed in 8.0
Query cache hits/sec Qcache_hits / ((Qcache_hits+Com_select) If < 5 or if version=5.7, disable. Removed in 8.0
Query cache hits/inserts Qcache hits/Qcache_inserts If low or if version=5.7, disable. Removed in 8.0
MyISAM key buffer size key_buffer_size Pre-8 MySQL needs about 40MB for system tables, plus user table load
MyISAM buffer miss ratio key_read_requests/key_reads If > 10, grow key_buffer_size
MyISAM key read rate key_reads/@timescale Should be high when buffer is full
MyISAM key writes key_write_requests/key_writes Much higher than 1 suggests a disk write bottleneck
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 Over 1K is busy: you may need better hardware
Inserts/sec (Com_insert+Com_insert_select) / Uptime_since_flush_status Over 10K is busy, you may need better hardware
Writes/sec (Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi) / Uptime_since_flush_status If > 50/sec, better disk hardware may be needed
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
Max MySQL RAM use innodb_buffer_pool_size + innodb_ft_total_cache_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size + tmp_table_size + key_buffer_size + max_connections * ( thread_stack + greatest( max_allowed_packet, net_buffer_length ) + net_buffer_length + read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size ) Must be less than RAM available to MySQL

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.

You may need to customise the script as follows ...

  • With version 5.7, MySQL moved variable tables from information_schema to performance_schema, and 8.0 changed collations of the the variable table, so there are different versions of gsintval() and gvintval() functions for MySQL 5.6 and earlier, for MySQL 5.7, and for MySQL 8.0. Why not just have one version of each function adjusting itself to the current version? The short answer is that MySQL functions don't support dynamic SQL (they're woefully underdeveloped in other ways too, but that's another story). The functions that match your version will compile, others will not.
  • If the database collation isn't utf8_unicode_ci pre-8.0 for utf8 or utf8mb4_0900_ai_ci in 8.0 for utf8mb4, edit the collate argument in each function.
  • 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;

-- FUNCTIONS FOR MYSQL 5.6 OR EARLIER
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 ifnull(ret,0);
end;
go
delimiter ;

drop function if exists hasqcache;
create function hasqcache() returns boolean return true;


-- FUNCTIONS FOR MYSQL 5.7
drop function if exists gvintval;
delimiter go
create function gvintval( pname varchar(128) ) returns bigint
begin
  declare ret bigint default 0;
  select variable_value into ret 
    from performance_schema.global_variables 
    where variable_name=pname collate utf8_unicode_ci;
  return ret;
end;
go
delimiter ;

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 performance_schema.global_status 
    where variable_name=pname collate utf8_unicode_ci;
  return ret;
end;
go
delimiter ;

drop function if exists hasqcache;
create function hasqcache() returns boolean return true;


-- FUNCTIONS FOR MYSQL 8.0
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 performance_schema.global_variables 
    where variable_name=pname collate utf8mb4_0900_ai_ci;
  return ifnull(ret,0);
end;
go
delimiter ;

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 performance_schema.global_status 
    where variable_name=pname collate utf8mb4_0900_ai_ci;
  return ret;
end;
go
delimiter ;

drop function if exists hasqcache;
create function hasqcache() RETURNS boolean
return false;

-- FUNCTIONS FOR MARIADB
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_general_ci;
  return ret;
end;
go
delimiter ;
-- TEST
select gsintval('innodb_buffer_pool_reads');

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_general_ci;
  return ifnull(ret,0);
end;
go
delimiter ;
-- TEST
select gvintval('innodb_buffer_pool_size');

drop function if exists hasqcache;
create function hasqcache() RETURNS boolean
return false;

-- CHEATSHEET DML, ALL VERSIONS SINCE 5.5
set @slowlog = if( (select @@slow_query_log)=0,0,1 );
set @innoload = ( select sum(data_length)+sum(index_length) 
                  from information_schema.tables 
                  where 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
  '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
  'MyISAM buffer hit ratio',
  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 key write ratio',
  round( gsintval('key_write_requests')/gsintval('key_writes'), 1 ),
  'Much higher than 1 suggests a disk write bottleneck'
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 ),
  'Over 1K is busy: you may need better hardware'
union
select
  'Inserts/sec',
  round( ( gsintval('Com_insert') + gsintval('Com_insert_select') ) /
           gsintval(@timescale), 1 ),
  'Over 10K is busy, you may need better hardware'
union
select
  'Table scan next row seeks/sec',
  round( gsintval('Handler_read_rnd_next') / 
         gsintval('Uptime_since_flush_status'), 2 ),
  '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 ),
  'Over 1K 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' 
union
select 
  "Max MySQL RAM use", 
  round( ( gvintval("innodb_buffer_pool_size") + 
           gvintval("innodb_ft_total_cache_size") +   
           gvintval("innodb_log_buffer_size") +   
           gvintval("key_buffer_size") + gvintval("tmp_table_size") +
           if( hasqcache(), gvintval("query_cache_size"), 0 ) +
           ((gvintval("thread_stack") + 
             greatest( gvintval("max_allowed_packet"),
                       gvintval("net_buffer_length") 
                     ) + 
             gvintval("net_buffer_length") +
             gvintval("read_buffer_size") + 
             gvintval("read_rnd_buffer_size") + 
             gvintval("sort_buffer_size") + 
             gvintval("join_buffer_size")
           ) * gvintval("max_connections") 
         )
       ) /(1024*1024*1024), 2 ),
  "Must not be greater than RAM available to MySQL";

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 "InnoBuffPoolSize",
  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";

Script result from a small low-demand system, comments suppressed ...

+--------------------------------------+---------+
| Item                                 | Rate    |
+--------------------------------------+---------+
| Slow queries/sec                     | 0.00    |
| Unindexed queries/sec                | 0.00    |
| Full table scans/sec                 | 0.01    |
| Total InnoDB load / buffer pool size | 2.32    |
| InnoDB buffer pool inefficiency      | 0.01    |
| InnoDB pages read and buffer pool    | 0.01    |
| InnoDB log thrashing                 | 0.03    |
| Est. best InnoDB log file size       | 32MB    |
| Mins between InnoDB log rotation     | 42.89   |
| Query cache hits/sec                 | 0.00    |
| Query cache spills/sec               | 0.00    |
| MyISAM buffer hit ratio              | 16.82   |
| MyISAM key read rate                 | 0.00    |
| MyISAM key write ratio               | 1.0     |
| MyISAM query cache hits/inserts      | NULL    |
| Temp tables created/sec              | 0.00    |
| Database changes/connection          | 4.28    |
| Processes/connection                 | 0.01    |
| Queries/sec                          | 0.02    |
| Inserts/sec                          | 0.0     |
| Table scan next row seeks/sec        | 0.52    |
| InnoDB buffer reads/sec              | 0.00    |
| Files opened/sec                     | 0.02    |
| Created tmp disk tables/sec          | 0.00    |
| Created_tmp_disk_tables/Queries      | 0.09    |
| Max MySQL RAM use                    | 11.44   |
+--------------------------------------+---------+

Optimising for multiple cores

Current wisdom is to leave multi-core optimisation to MySQL with ...
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
More tweaking suggestions here.

Corrections & additional entries welcome as always.

Last updated 22 Oct 2020


Return to the Artful MySQL Tips page