Percona XtraDB Cluster reached max connection with all query in 'query end' state
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC |
Expired
|
Undecided
|
Unassigned |
Bug Description
Galera Cluster Reached max Connection Frequently , i find that all the query in 'query end' state at that time.
Its an Head Eache since last 15 days.
Show Proceslist Output,
1 system user NULL Sleep 56685 wsrep aborter idle NULL 0 0 0
2 system user NULL Sleep 583 committed 751467581 NULL 0 0 0
3 system user NULL Sleep 584 committed 751467579 NULL 0 0 0
389112 user hostname:53200 db1 Query 580 sleeping commit 0 0 0
389119 user hostname:53556 db1 Execute 580 query end INSERT INTO `xyz_news` (`feed_id`, `title`, `category_name`, `image`, `image_thumb`, `image_land`, `url`, `status`, `one_liner`, `description`, `guid`, `author`, `pub_date`, `load_status`, `app_status`, `image_caption`, `image_author`, `image_count`, `in_order_id`, `ti_article_
old=/gallery.
389229 user hostname:59270 db1 Execute 580 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('387464', 'BBB5AF58-
389241 user hostname:60137 db1 Execute 577 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('505120', '94F8799A-
389243 user hostname:60333 db1 Execute 576 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('203717', '2BAD0A15-
389245 user hostname:60486 db1 Execute 576 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('60207', '3C8B65A3-
389256 user hostname:60940 db1 Execute 574 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('26912', '6E12FBFE-
389262 user hostname:33415 db1 Execute 572 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`) VALUES ('389778', '6970B901-
389275 user hostname:34191 db2 Query 565 query end update xyz_news
\n set load_status='D' where app_status='A' and feed_id=54 0 50 50
389281 user hostname:34685 db1 Execute 568 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('13245', '0725987B-
389289 user hostname:35059 db1 Execute 567 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('78452', 'C9ED9A7C-
389293 user hostname:35315 db1 Execute 567 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('ffffffff-
389299 user hostname:35633 db2 Query 528 query end update GM_news
\n set load_status='D' where app_status='A' and feed_id=10 0 50 50
389312 user hostname:36771 db1 Execute 562 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('00000000-
389318 user hostname:37248 db1 Execute 561 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('547825', 'FA979C27-
389327 user hostname:37967 db1 Execute 559 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('521109', 'F1A60CAF-
389344 user hostname:39463 db1 Execute 554 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('574689', 'AB1BBABD-
389353 user hostname:40083 db1 Execute 552 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('28128', '794E7B7A-
389362 user hostname:40864 db1 Execute 550 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('507682', '5752C2FB-
389366 user hostname:41157 db1 Execute 549 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('ffffffff-
389375 user hostname:41622 db1 Execute 547 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('79640', '4F489514-
389376 user hostname:41702 db1 Execute 547 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('280747', 'F2A0159F-
389383 user hostname:42075 db1 Execute 546 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('242488', '887CA68F-
389386 user hostname:42228 db1 Execute 545 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('213905', '179DB6BE-
389392 user hostname:42607 db1 Execute 544 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('130062', 'F5E8E698-
389395 user hostname:42990 db1 Execute 543 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('00000000-
389396 user hostname:43127 db2 Query 542 query end update GM_news
\n set load_status='D' where app_status='A' and feed_id=61 0 49 49
389407 user hostname:44259 db1 Execute 539 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('109987', '61ED4D92-
389410 user hostname:44509 db1 Execute 538 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('599568', 'D64E8666-
389415 user hostname:44994 db1 Execute 537 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('72087', 'DB1CD111-
389416 user hostname:45031 db1 Execute 536 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('00000000-
389426 user hostname:45821 db1 Execute 534 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('ffffffff-
389433 user hostname:46378 db1 Execute 532 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('28219', 'B63749AA-
389451 user hostname:48102 db1 Execute 527 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('468818', 'D42D27A3-
389454 user hostname:48220 db1 Execute 527 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('ffffffff-
389458 user hostname:48754 db1 Execute 525 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('302135', '2F0FF8D1-
389472 user hostname:49819 db1 Execute 523 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('ffffffff-
389483 user hostname:51002 db1 Execute 520 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('00000000-
389485 user hostname:51217 db1 Execute 519 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('458056', '31DDA0E7-
389487 user hostname:51398 db1 Execute 518 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('298085', 'C8FBF341-
389493 user hostname:51747 db1 Execute 518 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('32669', 'F672DE6F-
389495 user hostname:52063 db1 Execute 517 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('484427', '4C64C5BD-
389497 user hostname:52260 db1 Execute 516 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('00000000-
389499 user hostname:52507 db1 Execute 516 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('ffffffff-
389503 user hostname:52754 db1 Execute 515 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`) VALUES ('124014', '456689CF-
389505 user hostname:52848 db1 Execute 515 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('257228', '705086DF-
389510 user hostname:53378 db1 Execute 513 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('184741', '692F14DD-
389513 user hostname:53604 db1 Execute 513 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('60207', '3C8B65A3-
389514 user hostname:53628 db1 Execute 512 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('236870', '45680954-
389521 user hostname:54294 db1 Execute 511 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('14794', '89F446E6-
389522 user hostname:54402 db1 Execute 510 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('ffffffff-
389526 user hostname:54797 db1 Execute 509 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('128144', 'D47CEC0C-
389533 user hostname:55719 db1 Execute 506 query end INSERT INTO `deviceTrack` (`devicePrimaryId`, `deviceId`, `lastLoggedIn`, `isResuming`, `session_type`, `session_count`) VALUES ('421760', '4DBF4B8A-
Looks like replication issue, and most likely it is replication paused due to some node lagging behind.
SHOW STATUS LIKE 'wsrep%' from all nodes and a corresponding log snippet from at least one should help to diagnose.