How can tune MySQL for Wordpress sites?

2020-02-14 mysql wordpress ubuntu

I'm facing a problem that my MySQL database is slow, so I decided to check with mysqltuner.pl, I don't want to make any changes before I'm sure what I'm doing.

I'm running MySQL 5.7 on Ubuntu 18.04 LTS server.

This is a respone from mysqltuner:

    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 17h 51m 40s (400K q [6.226 qps], 15K conn, TX: 1G, RX: 63M)
    [--] Reads / Writes: 98% / 2%
    [--] Binary logging is disabled
    [--] Physical Memory     : 7.8G
    [--] Max MySQL memory    : 352.4M
    [--] Other process memory: 1.6G
    [--] Total buffers: 192.0M global + 1.1M per thread (151 max threads)
    [--] P_S Max memory usage: 72B
[OK] Maximum reached memory usage: 207.9M (2.61% of installed RAM)
[OK] Maximum possible memory usage: 352.4M (4.43% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/400K)
[OK] Highest usage of available connections: 9% (15/151)
[OK] Aborted connections: 0.01%  (1/15256)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 338K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 37K sorts)
[!!] Joins performed without indexes: 494
[!!] Temporary tables created on disk: 78% (15K on disk / 20K total)
[OK] Thread cache hit rate: 99% (30 created / 15K connections)
[OK] Table cache hit rate: 29% (1K open / 6K opened)
[OK] Open file limit used: 2% (136/5K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K 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.2% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/126.0K
[!!] Read Key buffer hit rate: 68.8% (16 cached / 5 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/77.4M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 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: 99.96% (12403660 hits/ 12408102 total)
[!!] InnoDB Write Log efficiency: 51.81% (14419 hits/ 27833 total)
[OK] InnoDB log waits: 0.00% (0 waits / 13414 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:
    Control warning line(s) into /var/log/mysql/error.log file
    Control error line(s) into /var/log/mysql/error.log file
    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
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=64M) if possible.

All of my websites are running on latest version Wordpress, and most of the content is static - like bussines websites.

How can I improve performance of MySQL based on mysqltuner analyze?

Answers

Your box is plenty big enough, no worries there. I can't tell how fast your disk drives are. If you have SSDs you're all set. If you have a drive scavenged from an old laptop, not so much. Upgrade it.

You should start by doing OPTIMIZE TABLE on the WordPress tables for each WordPress instance. It can help a lot, especially for busy sites. I've had success using the WP Clean Up plugin for that.

If you need to do more, use the mysqldumpslow command to find out what's in your server's slow query log. It will identify the worst performing queries.

Then you can prefix each slow query with EXPLAIN to see how the query is handled. Looking at the query and its EXPLAIN output can help you see what table indexes might help sped things up. (An explanation of how to do that is way beyond the scope of an SO answer, but see for many good examples.)

Can you pin the poor performance on a particular plugin? If so, ask the plugin author about it.

Suggestions for your my.cnf [mysqld] section from the incomplete MySQLTuner posted report,

thread_cache_size=64
tmp_table_size=32M
max_heap_table_size=32M
table_open_cache=4K
innodb_open_files=4K  # to reduce opened_tables count

Please consider posting additional information requested about 24 hours ago and complete MySQLTuner report after shutdown; restart and minimum 24 hours regular workday use.

The "meta" tables are poorly implemented by WP. Here is my fix for that: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Also, if you provide Wilson's request, I will check the tuning. 77MB of data is rather small, so I don't expect much will need improving.

Related