Eric,
The thread hopelessly conflates multiple discussions, apologies for not catching that in time.
It's a small system but 10.5G RAM is available to MySQL, it's using < 2GB, innodb_buffer_pool_size is best grown up to about 7GB.
And, some queries need optimising---for help with that, post the queries, the results of Explain & Explain Analyze on them, plus Show Create table results for tables referenced in each query.
MySQL version 8.0.18
Slow queries/sec 0 Find & optimise those queries
Queries not using indexes 0
Queries needing full table scans/sec 0.0005 Find & optimise those queries
InnoDB buffer pool inefficiency 0.0125 GROW INNODB_BUFFER_POOL_SIZE IF > 2% OR < 70% OF RAM
InnoDB page reads and buffer pool 0.024
InnoDB log file thrashing 12 Best is near 0.5; grow innodb_log_file_size
if well under 0.5, shrink if much larger
Time between InnoDB log rotation 3:54:05 Grow log size if < 30 min, shrink if > 60 min
InnoDB buffer pool reads / sec 0.0047 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 8MB 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 4.55 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.09 Use db.table syntax
Processes/connection 0.36 If high, grow thread_cache_size
Files opened/sec 0 If > 5, increase table_open_cache
Queries/sec 0 Thousands is busy: you may need better hardware
Writes/sec 0 If, over 50/sec, better disk hardware may be needed
Insert statements/sec 0
Inserts/sec 0.06 Can be up to 20K/sec with efficient memory use, see here
Next table row requests/sec 449 QUERIES NEED OPTIMISING ESP. WITH COVERING INDEXES
Sampling time 7:46:18 If less than 2h in a typical use period, re-do
Max MySQL RAM use 1.9GB Must be < RAM available to MySQL
Available RAM 10.52GB If less than Max MySQL memory use, more RAM needed
InnoDB basic stats
innobuffpool bytes read/sec reads/sec bytes/read bytes written/sec writes/sec bytes/write rollback % buffpool free %
8MB 0 120 16261 24 0 13364 0 43
Edited 1 time(s). Last edit at 12/19/2019 12:55PM by Peter Brawley.