优化MariaDB for wordpress

问题描述：

我有一个2 CPU核心和1GB内存的服务器。服务器只运行一个WordPress站点。我的服务器堆栈是LEMP.I运行mysql调谐器两个星期后建立wordpress网站。下面是结果优化MariaDB for wordpress

[!!] Maximum reached memory usage: 884.8M (89.15% of installed RAM) 
[!!] Maximum possible memory usage: 1.4G (139.86% of installed RAM) 
[!!] Overall possible memory usage with other process exceeded memory 
[!!] Slow queries: 15% (629K/4M) 
[OK] Highest usage of available connections: 9% (19/200) 
[OK] Aborted connections: 0.75% (4103/548857) 
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance

这里是我的my.cnf配置

[mysql] 

# CLIENT # 
port       = 3306 
socket       = /var/lib/mysql/mysql.sock 

[mysqld] 

# GENERAL # 
user       = mysql 
default-storage-engine   = InnoDB 
socket       = /var/lib/mysql/mysql.sock 
pid-file      = /var/lib/mysql/mysql.pid 

# MyISAM # 
key-buffer-size    = 32M 
myisam-recover     = FORCE,BACKUP 

# SAFETY # 
max-allowed-packet    = 16M 
max-connect-errors    = 1000000 

# DATA STORAGE # 
datadir      = /var/lib/mysql/ 

# BINARY LOGGING # 
log-bin      = /var/lib/mysql/mysql-bin 
expire-logs-days    = 14 
sync-binlog     = 1 

# CACHES AND LIMITS # 
tmp-table-size     = 32M 
max-heap-table-size   = 32M 
query-cache-type    = 0 
query-cache-size    = 0 
max-connections    = 200 
thread-cache-size    = 20 
open-files-limit    = 65535 
table-definition-cache   = 1024 
table-open-cache    = 2048 

# INNODB # 
innodb-flush-method   = O_DIRECT 
innodb-log-files-in-group  = 2 
innodb-log-file-size   = 64M 
innodb-flush-log-at-trx-commit = 1 
innodb-file-per-table   = 1 
innodb-buffer-pool-size  = 624M 

# 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

我如何才能优化configuation修复这些问题的

答

有一个非常坏的设置：

innodb-buffer-pool-size  = 624M

在一个可能包含WP的小型1GB服务器中和MySQL？将其改为200M。并注意交换。如果有任何交换，请降低它。交换导致大量的I/O;相反，缩小设置会更好。这里是一个开始：

tmp-table-size     = 32M -> 8M 
max-heap-table-size   = 32M -> 8M 
query-cache-type    = 0 -- good 
query-cache-size    = 0 -- good 
max-connections    = 200 -> 50 
thread-cache-size    = 20 
open-files-limit    = 65535 
table-definition-cache   = 1024 -> 200 
table-open-cache    = 2048 -> 300

你有缓慢的日志打开？让我们看看最差的查询，如mysqldumpslow -s t或pt-query-digest所示。

这是另一个提示。这张重要的表格目前有糟糕的索引;这些都将帮助：

CREATE TABLE wp_postmeta (
    post_id …, 
    meta_key …, 
    meta_value …, 
    PRIMARY KEY(post_id, meta_key), 
    INDEX(meta_key) 
) ENGINE=InnoDB;

IS WORDPRESS听吗？

这里的原因：

AUTO_INCREMENT是一种浪费
这是一个更好的PK
使用191，如果有必要（5.6.3直通5.7.6）
的InnoDB的集群PK

优化MariaDB for wordpress

相关推荐