MySQL Forums
Forum List  »  Performance

Re: Inserts in MySQL 8 are slower than Inserts in MySQL 5.6
Posted by: Peter Brawley
Date: October 15, 2019 02:12PM

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.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.