Most significant issues ...
As configured, MySQL can try to use more RAM than is available; that will get you out-of-memory crashes.
Too many queries require table scans---turn on the slow query log to find them, optimise them by creating covering indexes for them.
Huge Insert rate, see the cited url.
Details---items in caps need attention ...
DB sizes
InnoDB | 2.99 GB | 1.84 GB | 4.82 GB |
MyISAM | 0.12 GB | 0.04 GB | 0.16 GB |
CSV | 0.00 GB | 0.00 GB | 0.00 GB |
TOTALS | 3.10 GB | 1.88 GB | 4.98 GB |
MySQL version 5.7.29-0
ubuntu0.18.04.1
Slow queries/sec unknown ENABLE THE SLOW QUERY LOG
Queries not using indexes 0
Queries needing full table scans/sec 7.6352 FIND & OPTIMISE THOSE QUERIES
InnoDB buffer pool inefficiency 0 Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB page reads and buffer pool 0 Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB log file thrashing 0.2116 Best is near 0.5; grow innodb_log_file_size if well under 0.5, shrink if much larger
Time between InnoDB log rotation 13:26:21 Grow log size if < 30 min, shrink if > 60 min, but see here.
InnoDB buffer pool reads / sec 15.2335 If high, increase Innodb_buffer_pool_size up to 70% of RAM
Est. best InnoDB log file size 32MB 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 hit ratio 364.18 IF < 10, GROW KEY_BUFFER_SIZE
MyISAM key write ratio 1 Much higher than 1 suggests a disk write bottleneck
MyISAM key read rate 0.01 Should be high when the buffer is full
MyISAM query cache hits/inserts 0 Query cache deprecated, turn it off
Temp tables created/sec 5.55 If > 20, find the queries responsible and optimise them,
Temp tables created on disk/Questions 0.0222 If > .05, try growing join_buffer_size, optimise responsible queries,
Temp tables created on disk/sec 1.43 If high, grow tmp_table_size, max_heap_size. Avoid Blobs, use HEAP tables in queries
Database changes / connection 0.02 Use db.table syntax
Processes/connection 0 If high, grow thread_cache_size
Files opened/sec 0.04 If > 5, increase table_open_cache
Queries/sec 66.51 Thousands is busy: you may need better hardware
Writes/sec 10.77 If, over 50/sec, better disk hardware may be needed
Insert statements/sec 2.94
Inserts/sec 12583.62 Can be up to 20K/sec with efficient memory use, see
https://dev.mysql.com/doc/refman/5.7/en/insert-optimization.html
Next table row requests/sec 86011 IF HIGH, QUERIES NEED OPTIMISING ESP. WITH COVERING INDEXES
Sampling time 0:20:45 If less than 2h in a typical use period, re-do
Max MySQL RAM use 27.5GB MUST BE LESS THAN RAM AVAILABLE TO MYSQL
Available RAM 16GB IF LESS THAN MAX MYSQL MEMORY USE, MORE MEMORY NEEDED
MySQL manual page on memory use
InnoDB basic stats
innobuffpool: 6144MB
bytes read/sec 37576
reads/sec 2
bytes/read 16341
writes/sec 18
bytes/write 24789
bytes written/sec 445945
rollback % 2.8
buffpool free 40.3%
Edited 1 time(s). Last edit at 02/13/2020 05:54PM by Peter Brawley.