3

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.

  • Check my answer to see how to optimize these, but they do not seem very "threatening" by the expected number of rows and given the AUTO_INCREMENT=3360 value. Run show global variables like 'innodb_buffer_pool_size'; and show table status like 'wp_postmeta'\G and report the values. How big is your database? (approximately in MB or GB) – jkavalik Jun 7 '16 at 6:01
  • I'm not sure what happened to all the comments on this thread, but I fail to see how these answers address the 6 sleeping queries/sec that are being fired. My database is tiny and should be lightening fast. – user255406 Jun 12 '16 at 20:55
1

(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.

0

Do you really have any values in meta_key longer than 191 characters? If not, then make these changes:

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(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,  -- note length
  `meta_value` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `meta_key` (post_id, `meta_key`)  -- note compound, no "prefixing"
) ENGINE=InnoDB AUTO_INCREMENT=3360 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

This will let that particular query run faster because it can be done entirely in the index.

(There are other things that WP ought to change, but you cannot safely change.)

Also, lower max_connections to, say, 20. There is no use having 150 clients stumbling over each other and everyone taking longer to finish.

Also, lower the number of clients allowed into the web server. Again, 20 might be reasonable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.