my.cnf
~~~~~~
Re shared memory see
https://dev.mysql.com/blog-archive/improving-the-performance-of-mysql-on-windows/
Re innodb_file_per_table: Please see
https://bugs.mysql.com/bug.php?id=96340 re this & partitioning
cheatsheet
~~~~~~~~~~
An extremely busy system.
It says your demand may be 8TB RAM, which you likely don't have. Re max mysql RAM use, the formula is ...
innodb_ft_total_cache_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size + tmp_table_size + key_buffer_size + max_connections * ( thread_stack + max( max_allowed_packet, net_buffer_length ) + net_buffer_length + read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size )
Is there significant swapping? Possibly your value is distorted by your MyISAM settings, obviously you don't have 8TB RAM, you'll need to investigate that. Otherwise you need more and/or better hardware, but first you will need to address the startup table scan issue mentioned above
Other issues needing attention in caps ...
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