CODEWITHSUNDEEP
mysqlwordpressmariadb
user2650277
user2650277

Reputation: 6739

Optimizing MariaDB for wordpress

I have a server with 2 CPU cores and 1GB of RAM.The server only run one wordpress site.My Server Stack is LEMP.I ran mysql tuner two weeks after setting up the wordpress site. Here are the results 

[!!] 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

Here is my my.cnf configuration

 [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

How can i optimize the configuation to fix those issues

Upvotes: 4

Views: 7019

Answers (1)

Rick James
Rick James

Reputation: 142238

There is one terribly bad setting:

innodb-buffer-pool-size        = 624M

in a tiny 1GB server that probably includes both WP and MySQL? Change that to 200M. And watch for swapping. If there is any swapping, lower it more. Swapping leads to a huge amount of I/O; it is better to shrink the settings instead. Here's a head start:

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

You have the slow log turned on? Let's see the worst query, as indicated by mysqldumpslow -s t or pt-query-digest.

Here's another tip. This vital table currently has lousy indexes; these will help:

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

IS WORDPRESS LISTENING?

Here's why:

  • AUTO_INCREMENT was a waste
  • This is a much better PK
  • Use 191 if necessary (5.6.3 thru 5.7.6)
  • InnoDB for clustered PK

More details: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Upvotes: 3

Related Questions