帶有 MySQL 的 WordPress 的 InnoDB 記憶體/cpu 使用優化

我不是 DBA，在這裡需要一些幫助……我有一個高流量的 WordPress 站點，託管在 EC2 實例中。我們正在開發一個新的主題程式碼，該程式碼將優化查詢，但現在我需要保留舊程式碼，其中包含許多這樣的慢查詢（在每個頁面載入中呼叫 3 次）：

# Time: 2017-08-25T17:10:29.753525Z
# User@Host: xxx[xxx] @ localhost []  Id:   442
# Query_time: 13.548223  Lock_time: 0.000147 Rows_sent: 6  Rows_examined: 188232
SET timestamp=1503681029;
SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1  AND (
 wp_term_relationships.term_taxonomy_id IN (3)
) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 6;
/usr/sbin/mysqld, Version: 5.7.19-0ubuntu0.16.04.1-log ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock

這些是我在配置中使用的值

default-storage-engine = InnoDB
key_buffer_size         = 32M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
max_connections        = 1000
query_cache_type        = 0
query_cache_limit       = 0
query_cache_size        = 0
sort_buffer_size        = 4M
join_buffer_size        = 4M
tmp_table_size = 1G
max_heap_table_size = 1G
table_open_cache = 512M
table_definition-cache = 1024
thread-cache-size              = 50
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 1
innodb-log-file-size           = 1500M
innodb-log-buffer-size         = 8M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 12G
innodb-buffer-pool-instances   = 12
innodb-buffer-pool-dump-at-shutdown = 1
innodb-buffer-pool-load-at-startup = 1
innodb_flush_log_at_trx_commit = 2

和“mysqltuner.pl”輸出

>>  MySQLTuner 1.7.2 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.7.19-0ubuntu0.16.04.1-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(0B)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[!!] Log file /var/log/mysql/error.log is empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn't contain any warning.
[OK] /var/log/mysql/error.log doesn't contain any error.
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in InnoDB tables: 1G (Tables: 68)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'debian-sys-maint@localhost' has no password set.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 20h 12m 2s (6M q [93.845 qps], 301K conn, TX: 8G, RX: 930M)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory     : 31.4G
[--] Max MySQL memory    : 21.4G
[--] Other process memory: 1.0G
[--] Total buffers: 13.0G global + 8.6M per thread (1000 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 13.3G (42.24% of installed RAM)
[OK] Maximum possible memory usage: 21.4G (68.11% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/6M)
[OK] Highest usage of available connections: 2% (28/1000)
[OK] Aborted connections: 0.00%  (4/301157)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (69 temp sorts / 1M sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 77% (871K on disk / 1M total)
[OK] Thread cache hit rate: 99% (28 created / 301K connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 0% (61/1M)
[OK] Table locks acquired immediately: 100% (151 immediate / 151 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/43.0K
[!!] Read Key buffer hit rate: 92.7% (96 cached / 7 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 12.0G/1.7G
[OK] InnoDB log file size / InnoDB Buffer pool size: 1.5G * 2/12.0G should be equal 25%
[OK] InnoDB buffer pool instances: 12
[--] Number of InnoDB Buffer Pool Chunk : 96 for 12 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% (16013769931 hits/ 16013822393 total)
[!!] InnoDB Write Log efficiency: 18.62% (55219 hits/ 296557 total)
[OK] InnoDB log waits: 0.00% (0 waits / 241338 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
   Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
   MySQL started within last 24 hours - recommendations may be inaccurate
   Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
   Temporary table size is already large - reduce result set size
   Reduce your SELECT DISTINCT queries without LIMIT clauses

我已經用SQL_CALC_FOUND_ROWSWordPressCOUNT(*)中的鉤子替換了，但查詢性能沒有提高。

我們可以對數據庫進行大量優化，使頁面載入速度達到可以接受的程度，但是它消耗了太多的 CPU 並且幾乎沒有記憶體。我們使用的是 30GB RAM 8 核盒子，但它只使用 3GB 記憶體和 1-2 個核心。所有的表都是 InnoDB，我想使用更多的記憶體和更少的 CPU，這樣我們就可以遷移到一個具有 16GB 記憶體和 4 個核心的更小的實例。

因此，根據我在 Percona 和類似部落格中線上研究的內容以及 stackoverflow 的答案，我有一些問題：

1. 增加在記憶體上創建的臨時表，但我已經將 tmp_table_size 和 max_heap_table_size 從 32M 增加到 1GB，沒有任何變化… 2) 提高寫入日誌效率… 我該怎麼做？3) 分區表會改善連接/分組方式/排序方式嗎？

您是否需要檢查來自我的伺服器的任何數據詳細資訊？請告訴我…

聽起來你需要一個索引

ALTER TABLE wp_posts ADD INDEX type_status_date_ndx (post_type,post_status,post_date);

需要此索引，因為查詢具有靜態值，而post_type具有已排序的範圍。post_status``post_date

至於 CPU 使用率，請在 my.cnf 中添加（需要重啟）

innodb_read_io_threads=8
innodb_write_io_threads=8

請參閱我的文章可能使 MySQL 使用多個核心？和關於單執行緒與多執行緒數據庫性能以獲取更多資訊

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