Mysql – Temporary tables created on disk: is huge

mariadbMySQLWordpress

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

and 

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 🙂

Thanks

Best Answer

You "can't tune your way out of a performance problem". So let's look for a villain and fix it.

Change long_query_time down to 1. Wait a day. Then run pt-query-digest.

More: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

A query that is running often or long will show up in the slowlog; let's look at the first couple of them.

A likely candidate is postmeta: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

After edit

First query:

IN ( SELECT ... ) is notoriously inefficient. Change to a JOIN .. ON.

And change the index(es) as indicated in the indexing link above.

Is LIMIT 29999 realistic? Suggest lowering that significantly.

Second query:

INDEX(post_type, post_date)
Related Question