我们有一个 MySQL 实例，大部分时间都运行良好。然而，我们有时会观察到奇怪的行为。发生的情况是 MySQL 连接突然达到限制（当前设置为 1000）并且 MySQL 几乎停止。当我们检查此服务器的图表时，会观察到以下内容：
该图像来自运行 MySQL 导出器的 Grafana 实例。困扰我的是为什么不重用缓存中的线程。另一个问题是为什么缓存中的线程突然降为零但同时没有创建新线程（根据第二张图 - 蓝线保持为 0）。
最终，我的目标是了解为什么突然使用 1000 个连接，以及这是 MySQL 还是应用程序的问题。这是 MySQL 文档关于线程的说法：
服务器应缓存多少线程以供重用。当客户端断开连接时，如果那里的线程少于 thread_cache_size，则客户端的线程将被放入缓存中。如果可能，通过重用从缓存中获取的线程来满足对线程的请求，并且只有当缓存为空时才会创建新线程。
任何帮助或指导将不胜感激！
我试图弄清楚为什么一个查询需要大约 5 秒才能完成以及如何优化它。以下是我到目前为止收集到的信息：
1. EXPLAIN statement for the query:
EXPLAIN EXTENDED
SELECT SUM(meta2.meta_value) FROM ****_posts as posts
LEFT JOIN ****_postmeta AS meta ON posts.ID = meta.post_id
LEFT JOIN ****_postmeta AS meta2 ON posts.ID = meta2.post_id
WHERE
meta.meta_key = '_customer_user' AND
meta.meta_value = '15448' AND
posts.post_type = 'shop_order' AND
posts.post_status IN ( 'wc-processing','wc-completed' ) AND
meta2.meta_key = '_order_total';
+----+-------------+-------+--------+--------------------------+----------+---------+------------------------------+--------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+--------------------------+----------+---------+------------------------------+--------+----------+-------------+
| 1 | SIMPLE | meta2 | ref | post_id,meta_key | meta_key | 576 | const | 141630 | 100.00 | Using where |
| 1 | SIMPLE | posts | eq_ref | PRIMARY,type_status_date | PRIMARY | 8 | barberkl_wp821.meta2.post_id | 1 | 100.00 | Using where |
| 1 | SIMPLE | meta | ref | post_id,meta_key | post_id | 8 | barberkl_wp821.meta2.post_id | 18 | 100.00 | Using where |
+----+-------------+-------+--------+--------------------------+----------+---------+------------------------------+--------+----------+-------------+
一组 3 行，1 个警告（0.06 秒）
2. pt-query-digest stats
Profile
Rank Query ID Response time Calls R/Call V/M Item
==== ================== ============= ===== ====== ===== ===============
1 0x5C7505FDD18B918C 4.7628 100.0% 1 4.7628 0.00 SELECT
Query 1: 0 QPS, 0x concurrency, ID 0x5C7505FDD18B918C at byte 0 ________
This item is included in the report because it matches --limit.
Scores: V/M = 0.00
Time range: all events occurred at 2017-06-28 07:16:34
Attribute pct total min max avg 95% stddev median
============ === ======= ======= ======= ======= ======= ======= =======
Count 100 1
Exec time 100 5s 5s 5s 5s 5s 0 5s
Lock time 100 173us 173us 173us 173us 173us 0 173us
Rows sent 100 1 1 1 1 1 0 1
Rows examine 100 2.84M 2.84M 2.84M 2.84M 2.84M 0 2.84M
Rows affecte 0 0 0 0 0 0 0 0
Bytes sent 100 77 77 77 77 77 0 77
Merge passes 0 0 0 0 0 0 0 0
Tmp tables 0 0 0 0 0 0 0 0
Tmp disk tbl 0 0 0 0 0 0 0 0
Tmp tbl size 0 0 0 0 0 0 0 0
Query size 100 412 412 412 412 412 0 412
InnoDB:
IO r bytes 0 0 0 0 0 0 0 0
IO r ops 0 0 0 0 0 0 0 0
IO r wait 0 0 0 0 0 0 0 0
pages distin 100 29.49k 29.49k 29.49k 29.49k 29.49k 0 29.49k
queue wait 0 0 0 0 0 0 0 0
rec lock wai 0 0 0 0 0 0 0 0
我正在运行 Percona 5.6，如果需要，我可以提供配置。请指导我从哪里开始。谢谢！
更新（显示创建表）：
mysql> SHOW CREATE TABLE wpn0_postmeta \G;
*************************** 1. row ***************************
Table: wpn0_postmeta
Create Table: CREATE TABLE `wpn0_postmeta` (
`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`meta_key` varchar(255) DEFAULT NULL,
`meta_value` longtext,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`),
KEY `meta_key` (`meta_key`(191)),
KEY `woo_index1` (`post_id`,`meta_key`,`meta_value`(8))
) ENGINE=InnoDB AUTO_INCREMENT=6681190 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE wpn0_posts \G;
*************************** 1. row ***************************
Table: wpn0_posts
Create Table: CREATE TABLE `wpn0_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content` longtext NOT NULL,
`post_title` text NOT NULL,
`post_excerpt` text NOT NULL,
`post_status` varchar(20) NOT NULL DEFAULT 'publish',
`comment_status` varchar(20) NOT NULL DEFAULT 'open',
`ping_status` varchar(20) NOT NULL DEFAULT 'open',
`post_password` varchar(255) NOT NULL DEFAULT '',
`post_name` varchar(200) NOT NULL DEFAULT '',
`to_ping` text NOT NULL,
`pinged` text NOT NULL,
`post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content_filtered` longtext NOT NULL,
`post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
`guid` varchar(255) NOT NULL DEFAULT '',
`menu_order` int(11) NOT NULL DEFAULT '0',
`post_type` varchar(20) NOT NULL DEFAULT 'post',
`post_mime_type` varchar(100) NOT NULL DEFAULT '',
`comment_count` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `post_name` (`post_name`(191)),
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
KEY `post_parent` (`post_parent`),
KEY `post_author` (`post_author`),
KEY `woo_index2` (`post_type`,`post_status`,`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=349189 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)