1

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

  • If you would take the two queries above and EXPLAIN SELECT SQL_NO_CACHE DISTINCT ...... post the complete explain results, we would quickly get an idea of the scope of the effort to return results. – Wilson Hauck Nov 28 '17 at 19:27
  • what version of mysql or mariadb are you using? – Evan Carroll Nov 29 '17 at 1:48
1

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)
  • Hi Ricky, i have added the query which took more than 1 sec can you please check and let me know if i need to index for those query as i can't change the code of the caller for those query and i think they only solution is to find way to index them if possible: excuse my inexperience with mysql :) – kashalo Nov 28 '17 at 10:04
0

my.cnf things to do:

in [mysqld] section, REMOVE to allow system DEFAULTS to work for you:

sort_buffer_size
join_buffer_size
    both of your lines for
max_allowed_packet
   one of your two
open_files_limit

the remaining

open_files_limit = 30000 # would be reasonable

add a line for

innodb_buffer_pool_instances = 8 # to smooth contention across 8 parts of RAM

WHEN you NEED a 256M max_allowed_packet for processing, in your SESSION,

SET @max_allowed_packet=268452456 # and follow with
LOAD DATA LOCAL INFILE ..... (to avoid rollbacks. adjust up to 1G)

note, you can NOT use 256M to set your session limit, like you can in my.cnf/ini

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.