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;

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.

If MySQL memory usage outstrips RAM, the system resorts to swapping, which kills performance. In Linux, swapping can be discouraged by adding ...

vm.swappiness=0 

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

Performance metrics:

Show variables;
Show Global Status;

Automate performance metrics

So, in a schema of your choice, run the following script, which creates two utility functions to fetch system variable and global status values from performance_schema, then runs a stored procedure to analyse relevant variable and status values.

MySQL internals vary across versions, so you'll need to customise your script for your version...

• With version 5.7, MySQL moved global_status and global_variables 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 write 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 PROCEDURE, ALL VERSIONS SINCE 5.5
-- INVOKE WITH: call cheatsheet();
-- --------------------------------------------
drop procedure if exists cheatsheet;
delimiter go
CREATE PROCEDURE cheatsheet()
begin
  -- CALCULATE 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;
  -- COLLECT & CALC
  set @timescale='uptime';
  set @slowlog = if( (select @@slow_query_log)=0,0,1 );
  set @slowrate = round( gsintval('slow_queries') / gsintval(@timescale), 2 );
  set @noindexq = round( gsintval('select_range_check') / gsintval(@timescale), 2 );  
  set @fullscans = round( gsintval('select_scan')/gsintval(@timescale), 2 );
  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 @tmptblrate = round( gsintval('Created_tmp_disk_tables')/gsintval(@timescale), 2 );
  select
    'Slow queries/sec' as Item,
    @slowrate as Rate,
    if( @slowlog and @slowrate,
        'Find & optimise slow queries',
        if( NOT @slowlog AND @noindexq OR @fullscans,'Enable slow query log', '' )
      ) as Suggestions
  union
  select
    'Unindexed queries/sec',
    @noindexq,
    if( @noindexq > 0.02, 'Find & optimise unindexed queries', '' )
  union
  select
    'Full table scans/sec',
    @fullscans,
    if( @fullscans, '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( hasqcache(), 'If high, grow query_cache_size up to 50', '' )
  union
  select
    'Query cache spills/sec',
    round( gsintval('Qcache_lowmem_prunes') /
           gsintval(@timescale), 2 ),
    if( hasqcache(), '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( hasqcache(), 'If < 5, consider disabling the cache', '' )
  union
  select
    'Temp tables created/sec',
    @tmptblrate,
    if( @tmptblrate, 
        'Grow tmp_table_size, max_heap_size. Use Heap tables in queries', 
        '' 
      )
  /* NOT IN PERFORMANCE_SCHEMA
   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;
  -- ----------------
  -- INNODB DASHBOARD
  -- ----------------
  select
   round(
    ( gsintval("Innodb_pages_read") +
      gsintval("Innodb_pages_written") +
      gsintval("Innodb_dblwr_writes") +
      gsintval("Innodb_buffer_pool_pages_flushed")
    ) / gsintval(@timescale), 1 ) as "InnoDB IOPS",
    concat( round( gvintval("innodb_buffer_pool_size")/1024/1024/1024, 1 ), "GB" )
    as "BuffPoolSize",
    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";
end;
go
delimiter ;
call cheatsheet();

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    |
| 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

innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64

Corrections & additional entries welcome as always.

Last updated 18 Jul 2024