MySQL hundreds of sleep processes giving “Error establishing a database connection” error

MySQLWordpress

I'm running a LAMP with ubuntu server 12, mysql innodb_version 5.5.49 on a WordPress platform (3 websites virtualized). Has been running without issue for about 2 years, but I just started getting an error "Error establishing a database connection" error. My other virtualized sites get a "max connections exceeded" error occasionally as well. No new plugins, no db changes.

When I log into mysql as root and run show full processlist; I get a list of 150 Sleep processes spawned by only one of my site's primary login. Of course, 150 is my max_connections limit, so these processes are dragging the entire server down. Given the time each one is up, it appears that about 6 are being spawned every second.

My problem is that I've run out of troubleshooting ideas. I'm a DBA neophyte to be sure. Here's what I've tried so far:

  • Looked at current traffic to the site to see if I was getting attacked. Nothing out of the ordinary there. Just in case, I changed where my DNS pointed for a brief time, but the processes were still being spawned. I think this rules out an attack or SQL injection.
  • Tried setting wait_timeout=60, but even this wasn't short enough given the rate of process spawning. No difference.
  • Updated WP, disabled all WP plugins and switched themes. No change. AFAIK, WP doesn't even use persistent db connections, so it shouldn't even be a WP issue.

For now I've limited the max processes that that single site can run to 50% of the max_connections, so my other sites are actually running. Willing to try just about anything at this point. Debugging insight please.

Here's the output from the MySQL log file that I briefly enabled:

7990364 Query   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 669
7990364 Query   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 667
7990364 Query   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 591
7990364 Query   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 568
7990364 Query   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 561
7990364 Query   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 553
7990364 Query   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 541
7990364 Query   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 461
7990364 Query   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 439
7990364 Query   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 393
7990364 Query   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 359
7990364 Query   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 301
7990364 Query   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 299
7990364 Query   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 297
7990364 Query   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 13
7990364 Query   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 1

Here are the results from:

EXPLAIN SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 669;

 1 | SIMPLE      | wp_postmeta | ref  | post_id,meta_key | post_id | 8       | const |    5 | Using where

Results from show create table wp_postmeta;

| wp_postmeta | CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=3360 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

Also:

innodb_buffer_pool_size: 134217728 
*************************** 1. row ***************************
           Name: wp_postmeta
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1388
 Avg_row_length: 247
    Data_length: 344064
Max_data_length: 0
   Index_length: 131072
      Data_free: 72351744
 Auto_increment: 3360
    Create_time: 2016-04-22 13:46:34
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_unicode_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

The size of the database is about 5MB.

This is a tiny DB, I honestly think this is potentially a problem with the WP code. It's not a matter of individual queries taking a long time, but that the max_connections is filled up. Every time I check, max_connections is always maxed out. Spawning 6/second.

Best Answer

(Partial answer only for the queries shown, you maybe identify other offenders when this is fixed.)

SELECT COUNT(*)
FROM wp_postmeta
WHERE meta_key = '_loved' AND post_id = 669

For this query the best index would be (post_id, meta_key), but because meta_key is defined as varchar(255) with the utf8mb4 charset, such index would be too big with standard settings.

You can instead use

ALTER TABLE wp_postmeta ADD INDEX(`post_id`, `meta_key`(100));

To create index which is not as optimal but still gives a bit better performance by accessing less rows from the table.

You might try to create the optimal index if you check and change the innodb_large_prefix setting (but it has some additional requirements about the row format as mentioned in the manual). That would lead to index-only ref access which is as fast as a query can be optimized using indexes.

Related Question