在磁碟上創建的臨時表：很大

我在磁碟上寫入大量臨時表時遇到問題。我已經嘗試了很多方法來避免這種情況，但沒有運氣，我真的要放棄了。我雖然可能需要一些專業知識來幫助解決這個問題。

我的機器專用於四個 wordpress 站點。該伺服器有 8 個核心和 64GB RAM。

每次我執行 mysqltunner 我都會收到此消息

在磁碟上創建的臨時表：84%（磁碟上 3K / 總共 3K）

並且數量不斷增加。

從 PHPmyadmin 我注意到這條消息：

問題：太多的排序導致臨時表。臨時表平均：每分鐘 1.24 個，這個值應該小於每小時 1 個。

這是我的配置

   [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

經過多次調查後，我發現我有兩個主要查詢需要超過 1 秒

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

請提供任何幫助:)

謝謝

您“無法擺脫性能問題”。因此，讓我們尋找一個惡棍並解決它。

更改long_query_time為 1。等待一天。然後執行pt-query-digest。

更多：http: //mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

經常或長時間執行的查詢將顯示在慢日誌中；讓我們看一下其中的前幾個。

一個可能的候選人是：http postmeta: //mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

編輯後

第一個查詢：

IN ( SELECT ... )效率低下是出了名的。更改為JOIN .. ON.

並按照上面的索引連結中的指示更改索引。

LIMIT 29999現實嗎？建議大幅降低。

第二個查詢：

INDEX(post_type, post_date)

my.cnf要做的事情：

在

$$ mysqld $$部分，刪除以允許系統預設值為您工作：

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

其餘

open_files_limit = 30000 # would be reasonable

添加一行

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

當您需要 256M max_allowed_pa​​cket 進行處理時，在您的 SESSION 中，

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

請注意，您不能像在 my.cnf/ini 中那樣使用 256M 來設置會話限制

引用自：https://dba.stackexchange.com/questions/191727