mobile logo

Optimizing MariaDB for wordpress

Solution:

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