I am constantly getting stuck SELECT queries on my wordpress databases like the following:
Many of the SELECT queries that get stuck are fairly ordinary, such as pulling the last 10 posts of an author or pulling 10 posts in a category - these are normal Wordpress core queries that should run in a split second. My Wordpress databases that get afflicted with this have around 100,000 rows in the wp_posts table, with a size of around 1GB. This is an example of the largest tables from one of the databases:
My dedicated server has 4 CPU cores @ 3.4 GHz and 8 GB DDR4 RAM. For this server and these databases, should these kinds of issues be happening? What can I do to make normal Wordpress queries always run without getting stuck? I have tried changing from MyISAM to InnoDB with no effect, as well as changing different settings in my.cnf - here it is currently:
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
log-error=/var/lib/mysql/errorlog.err
performance-schema=0
default-storage-engine=MyISAM
max_allowed_packet=268435456
open_files_limit=10000
slow_query_log=ON
log_slow_verbosity=1
innodb_buffer_pool_size=1G
aria_pagecache_buffer_size=512M
query_cache_size=0
query_cache_type=0
query_cache_limit=0
join_buffer_size=512K
tmp_table_size=32M
max_heap_table_size=32M
table_definition_cache=1200
And the result of running https://github.com/major/MySQLTuner-perl:
[root@hostname ~]# perl mysqltuner.pl --host 127.0.0.1
>> MySQLTuner 1.7.19 - Major Hayden <[email protected]>
>> 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
[--] Performing tests on 127.0.0.1:3306
[OK] Currently running supported MySQL version 10.3.20-MariaDB-log
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/lib/mysql/errorlog.err exists
[--] Log file: /var/lib/mysql/errorlog.err(764K)
[OK] Log file /var/lib/mysql/errorlog.err is readable.
[OK] Log file /var/lib/mysql/errorlog.err is not empty
[OK] Log file /var/lib/mysql/errorlog.err is smaller than 32 Mb
[!!] /var/lib/mysql/errorlog.err contains 1571 warning(s).
[!!] /var/lib/mysql/errorlog.err contains 1424 error(s).
[--] 124 start(s) detected in /var/lib/mysql/errorlog.err
[--] 1) 2019-11-25 6:31:15 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2019-11-25 6:08:45 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2019-11-25 5:47:35 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2019-11-25 5:34:11 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2019-11-25 5:22:58 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2019-11-25 5:02:11 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2019-11-25 4:33:46 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2019-11-25 4:27:54 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2019-11-25 4:21:59 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2019-11-25 4:21:52 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 123 shutdown(s) detected in /var/lib/mysql/errorlog.err
[--] 1) 2019-11-25 6:31:08 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2019-11-25 6:08:26 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2019-11-25 5:47:27 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2019-11-25 5:33:59 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2019-11-25 5:22:55 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2019-11-25 5:02:05 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2019-11-25 4:33:42 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2019-11-25 4:27:47 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2019-11-25 4:21:58 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2019-11-25 4:21:48 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 2.3G (Tables: 1379)
[--] Data in InnoDB tables: 2.7G (Tables: 284)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 620 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3m 3s (36K q [199.732 qps], 1K conn, TX: 222M, RX: 33M)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is disabled
[--] Physical Memory : 7.6G
[--] Max MySQL memory : 2.1G
[--] Other process memory: 0B
[--] Total buffers: 1.7G global + 3.2M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.7G (22.90% of installed RAM)
[OK] Maximum possible memory usage: 2.1G (28.14% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (11/36K)
[OK] Highest usage of available connections: 14% (22/151)
[OK] Aborted connections: 0.00% (0/1174)
[!!] 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% (0 temp sorts / 6K sorts)
[!!] Joins performed without indexes: 50
[!!] Temporary tables created on disk: 70% (1K on disk / 2K total)
[OK] Thread cache hit rate: 98% (22 created / 1K connections)
[OK] Table cache hit rate: 98% (444 open / 450 opened)
[!!] table_definition_cache(1200) is lower than number of tables(1928)
[OK] Open file limit used: 5% (580/10K)
[OK] Table locks acquired immediately: 99% (25K immediate / 25K locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[--] Using default value is good enough for your version (10.3.20-MariaDB-log)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 35.6% (47M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/170.4M
[OK] Read Key buffer hit rate: 99.9% (22M cached / 22K reads)
[OK] Write Key buffer hit rate: 99.3% (824 cached / 818 writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 1.0G/2.7G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (9.375 %): 48.0M * 2/1.0G should be equal to 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
[--] Number of InnoDB Buffer Pool Chunk : 8 for 8 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.71% (10795186 hits/ 10826096 total)
[OK] InnoDB Write log efficiency: 98.51% (39722 hits/ 40324 total)
[OK] InnoDB log waits: 0.00% (0 waits / 602 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 512.0M/1B
[OK] Aria pagecache hit rate: 98.7% (130K cached / 1K reads)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/lib/mysql/errorlog.err file
Control error line(s) into /var/lib/mysql/errorlog.err file
MySQL was started within the last 24 hours - recommendations may be inaccurate
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Performance schema should be activated for better diagnostics
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
join_buffer_size (> 512.0K, or always use indexes with JOINs)
tmp_table_size (> 32M)
max_heap_table_size (> 32M)
table_definition_cache(1200) > 1928 or -1 (autosizing if supported)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 2.7G) if possible.
innodb_log_file_size should be (=128M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances (=1)
