InnoDB内存/ CPU使用MySQL优化WordPress

我不是DBA,需要一些帮助…我在EC2实例中托管了一个高流量的WordPress站点.我们正在开发一个新的主题代码,它将具有优化的查询,但是现在我需要保留遗留代码,包括许多这样的慢查询(在每个页面加载中称为3次)：# Time: 2...

我不是DBA,需要一些帮助…我在EC2实例中托管了一个高流量的WordPress站点.我们正在开发一个新的主题代码,它将具有优化的查询,但是现在我需要保留遗留代码,包括许多这样的慢查询(在每个页面加载中称为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

我已经使用WordPress中的钩子替换了SQL_CALC_FOUND_ROWS和COUNT(*),但查询性能没有提高.

我们可以将数据库优化到我们在页面加载中具有可接受速度的点,但是它消耗了太多CPU而几乎没有内存.我们使用30GB RAM 8核盒,但它只使用3 GB内存和1-2核.所有表都是InnoDB,我想使用更多的内存和更少的CPU,因此我们可以迁移到具有16GB内存和4个内核的较小实例.

因此,根据我在Percona和类似博客中的在线研究以及stackoverflow的答案,我有一些问题：

1)增加在内存上创建的临时表,但是我已经将tmp_table_size和max_heap_table_size从32M增加到1GB而没有变化……
2)提高写日志效率……我该怎么做？
3)分区表将通过/ order改进连接/组？

我的服务器是否有您需要检查的数据详细信息？请告诉我…

解决方法:

听起来你需要一个索引

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

有关详细信息,请参阅我的帖子Possible to make MySQL use more than one core?和About single threaded versus multithreaded databases performance