MySQL cheatsheet

ENGINE  DATA       INDEXES    total
------  ---------  ---------  ------------
InnoDB  891.73 GB  420.84 GB  1312.57 GB
MyISAM  1.53 GB    1.24 GB    2.76 GB
CSV     0.00 GB    0.00 GB    0.00 GB
TOTALS  (NULL)     (NULL)     (NULL)
TOTALS  893.26 GB  422.07 GB  1315.33 GB


Item                                  Rate      Suggestions
------------------------------------  --------  ------------------------------------------------------------------------------------
Slow queries/sec                      0.46      Find & optimise slow queries
Unindexed queries/sec                 2.38      Find & optimise unindexed queries
Full table scans/sec                  112.39    Find & optimise queries that do full table scans
Total InnoDB load / buffer pool size  1.74      Increasing innodb_buffer_pool_size may improve performance
InnoDB buffer pool inefficiency       0.00      Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB pages read and buffer pool     0.01      As above
InnoDB log thrashing                  0.04      Best near 0.5; if well under 0.5, grow innodb_log_file_size, shrink if much larger
Est. best InnoDB log file size        32MB      Average hour of writes rounded up to 32MB boundary
Mins between InnoDB log rotation      34.48     Grow log size if < 30 min, shrink if > 60 min**
Query cache hits/sec                  (NULL)    If high, grow query_cache_size up to 50
Query cache spills/sec                0.00      If < 5, try turning query cache off
MyISAM buffer hit ratio               49.69     If < 10, grow key_buffer_size
MyISAM key read rate                  219.55    Should be high when the buffer is full
MyISAM key write ratio                2.4       Much higher than 1 suggests a disk write bottleneck
MyISAM query cache hits/inserts       (NULL)    If < 5, consider disabling the cache
Temp tables created/sec               14.60     Grow tmp_table_size, max_heap_size. Use Heap tables in queries
Database changes/connection           (NULL)    Use db.table syntax
Processes/connection                  0.00      If high, grow thread_cache_size
Queries/sec                           29289.68  Over 1K is busy: you may need better hardware
Inserts/sec                           (NULL)    Over 10K is busy, you may need better hardware
Table scan next row seeks/sec         86453.90  If high, there are queries that need optimising, esp. with covering indexes
InnoDB buffer reads/sec               1294.02   Over 1K is busy: you may need better hardware
Files opened/sec                      206.52    If > 5, increase table_open_cache
Created tmp disk tables/sec           14.60     If > 1, check tmp_table_size, max_heap_table_size, Text/Blob query retrievals
Created_tmp_disk_tables/Queries       0.00      If > 4%, check tmp_table_size, max_heap_table_size, Text/Blob query retrievals
Max MySQL RAM use                     8073.23   Must not be greater than RAM available to MySQL


InnoDB IOPS  BuffPoolSize  BufferPoolPctFree  BytesRead/s  Reads/s  Bytes/Read  BytesWritten/s  Writes/s  Bytes/Write
-----------  ------------  -----------------  -----------  -------  ----------  --------------  --------  -------------
2602.7       98.0GB        0.1                5            1157.8   214         711             278.4     3