Re: Inserts in MySQL 8 are slower than Inserts in MySQL 5.6

[Peter Brawley]

DB sizes
InnoDB | 550.52 GB | 47.58 GB | 598.11 GB |
CSV    | 0.00 GB   | 0.00 GB  | 0.00 GB   |
TOTALS | 550.52 GB | 47.58 GB | 598.11 GB |
MySQL version                           8.0.13          
Slow queries/sec                        0.0272          Find & optimise those queries
Queries not using indexes               0               
Queries needing full table scans/sec    0.0002          Find & optimise those queries
InnoDB buffer pool inefficiency         0.001           Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB page reads and buffer pool       0.003           Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB log file thrashing               0.0167          Best is near 0.5; grow innodb_log_file_size if well under 0.5, shrink if much larger
Time between InnoDB log rotation        0:21:00         Grow log size if < 30 min, shrink if > 60 min, but see here.
InnoDB buffer pool reads / sec          123.351         If high, increase Innodb_buffer_pool_size up to 70% of RAM
Est. best InnoDB log file size          736MB           Average hour of writes rounded up to 32MB boundary
MyISAM key buffer size                  100MB           Pre-8.0 MyISAM system tables need about 40MB
MyISAM query cache spills / sec         0               
MyISAM query cache hits / sec           0               
MyISAM cache miss ratio                 0               If > 10, grow key_buffer_size
MyISAM key write ratio                  0               Much higher than 1 suggests a disk write bottleneck
MyISAM key read rate                    0               Should be high when the buffer is full
MyISAM query cache hits/inserts         0               
Temp tables created/sec                 0               If > 20, find the queries responsible and optimise them, 
Temp tables created on disk/Questions   0               If > .05, try growing join_buffer_size, optimise responsible queries, 
Temp tables created on disk/sec         0               If high, grow tmp_table_size, max_heap_size. Avoid Blobs, use HEAP tables in queries
Database changes / connection           0               Use db.table syntax
Processes/connection                    0.27            If high, grow thread_cache_size
Files opened/sec                        0.01            If > 5, increase table_open_cache
Queries/sec                             29.63           Thousands is busy: you may need better hardware
Writes/sec                              16.09           If, over 50/sec, better disk hardware may be needed
Insert statements/sec                   1.72            
Inserts/sec                             54.68           Can be up to 20K/sec with efficient memory use, see here
Next table row requests/sec             16              If high, queries need optimising esp. with covering indexes
Sampling time                           22:17:26        If less than 2h in a typical use period, re-do
Max MySQL RAM use                       5222.5GB        Must be less than RAM available to MySQL
Available RAM                           

InnoDB basic stats
  innobuffpool  bytes read/sec  reads/sec     bytes/read  bytes written/sec  writes/sec   bytes/write    rollback %  buffpool free %
     61440MB            14          232176        16384        4490434         535           8386           0            0.2

125GB RAM is less than Max MySQL memory use, do you rwally need 5k connections?

innodb_buffer_pool_size probably needs to be 90GB or so.

Turn on the slow query log long enough to identify & optimise the slow queries---until Select_scan / Uptime_since_flush_status global vars evaluates to zero and until swap is always zero.



Edited 1 time(s). Last edit at 10/15/2019 02:13PM by Peter Brawley.