hellreturn
Active member
Any thoughts on improving write log efficiency? I noticed MySQL CPU is spiking up... when IO spikes up. Which makes sense but what I can't figure out is how to improve write log efficiency.
Code:
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 25d 14h 26m 3s (163M q [73.731 qps], 4M conn, TX: 2504G, RX: 72G)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory : 62.6G
[--] Max MySQL memory : 25.0G
[--] Other process memory: 0B
[--] Total buffers: 14.6G global + 71.2M per thread (150 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 19.4G (31.04% of installed RAM)
[OK] Maximum possible memory usage: 25.0G (39.93% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (290/163M)
[OK] Highest usage of available connections: 46% (70/150)
[OK] Aborted connections: 0.01% (217/4077399)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (9K cached / 141M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (229K temp sorts / 27M sorts)
[!!] Joins performed without indexes: 2525453
[!!] Temporary tables created on disk: 30% (2M on disk / 8M total)
[OK] Thread cache hit rate: 99% (70 created / 4M connections)
[OK] Table cache hit rate: 99% (403M hits / 406M requests)
[OK] table_definition_cache(4000) is upper than number of tables(1753)
[OK] Open file limit used: 1% (71/5K)
[OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.7% (1.1G used / 6.0G cache)
[OK] Key buffer size / total MyISAM indexes: 6.0G/120.5M
[OK] Read Key buffer hit rate: 99.0% (67M cached / 668K reads)
[!!] Write Key buffer hit rate: 18.0% (837K cached / 150K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 8.0G/5.3G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 1.0G * 2/8.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 64 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (61175061249 hits/ 61175324447 total)
[!!] InnoDB Write Log efficiency: 85.57% (35143817 hits/ 41070474 total)
[OK] InnoDB log waits: 0.00% (0 waits / 5926657 writes)
