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, not from any plugin. 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 is 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 myf - 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 :
[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 /
>> 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 .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
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)
I've tried these suggestions along with every other possible thing it feels like (caching, optimizing tables etc) and still queries get stuck over and over, causing connections to be stuck in apache with a 'sending reply' status and causing websites not to load. What else can I try? Should I upgrade to a more powerful server?
Edit: This question has nothing to do with the apparent 'duplicate' question as SQL_CALC_FOUND_ROWS wordpress queries are not what are getting stuck here.