I have an issue with a huge number of Temp tables writing on disk. I have tried many things to avoid this but no luck and I really am giving up. I though I might need some expertise to help solve this issue.
My machine is dedicated to four wordpress sites. The server has 8 cores and 64GB RAM.
Every time I run mysqltunner I get this message
Temporary tables created on disk: 84% (3K on disk / 3K total)
and the number keeps increasing.
From PHPmyadmin I notice this message:
Issue: Too many sorts are causing temporary tables. Temporary tables average: 1.24 per minute, this value should be less than 1 per hour.
Here is my configuration
[mysqld] log-error=/var/lib/mysql/server.ict-hardware.com.err default-storage-engine=InnoDB performance-schema=ON skip-name-resolve=1 # MyISAM # key-buffer-size = 110M myisam-recover = FORCE,BACKUP join_buffer_size = 4M sort_buffer_size = 4M # SAFETY # max-allowed-packet = 16M max-connect-errors = 1000000 # CACHES AND LIMITS # tmp-table-size = 512M max-heap-table-size = 512M query-cache-type = 0 query-cache-size = 0 max-connections = 500 thread-cache-size = 100 open-files-limit = 65535 table-definition-cache = 8096 table-open-cache = 8096 # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 3075M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 30G innodb_buffer_pool_instances = 30 # LOGGING # log-error = /var/lib/mysql/./mysql-error.log log-queries-not-using-indexes = 1 slow-query-log = 1 slow-query-log-file = /var/lib/mysql/./mysql-slow.log long_query_time = 10 max_allowed_packet=268435456 open_files_limit=2048
After Several Investigation i found I have two main query that take more than 1 sec
SELECT DISTINCT(wp_posts.post_parent) as ID FROM wp_posts INNER JOIN wp_postmeta AS pf1 ON (wp_posts.ID = pf1.post_id) INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE wp_posts.post_type = "product_variation" AND pf1.meta_key IN ("attribute_pa_women-clothes") AND pf1.meta_value IN ("s","") AND ( wp_posts.ID IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN ( 401 ) ) ) AND ( wp_posts.ID IN ( SELECT post_id FROM wp_postmeta WHERE meta_key LIKE "attribute_pa_%" GROUP BY post_id HAVING COUNT( DISTINCT meta_key ) = 1 ) ) GROUP BY pf1.post_id HAVING COUNT(DISTINCT pf1.meta_key) = 1 LIMIT 29999
SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month FROM wp_posts WHERE post_type = 'attachment' ORDER BY post_date DESC
Any help please :)