Truncate table server stall
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
Triaged
|
High
|
Unassigned | |||
5.6 |
Triaged
|
High
|
Unassigned | |||
5.7 |
Triaged
|
High
|
Unassigned | |||
8.0 |
Fix Committed
|
High
|
Unassigned |
Bug Description
I have noticed an issue with Percona Server 5.5.33 stalling during a truncate table operation.
I can recreate this issue consistently with the following test case:
- Sysbench running against a single table (sbtest1) to generate some server load and ~25,000 QPS.
- Creating an empty table and then truncating it
Here is the output of my terminal when creating and truncating the table:
root@localhost 07:55:43 DB:sbtest>CREATE TABLE `test_table` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
root@localhost 07:55:47 DB:sbtest>truncate table test_table;
Query OK, 0 rows affected (6.42 sec)
root@localhost 07:56:01 DB:sbtest>
root@localhost 07:56:42 DB:sbtest>drop table test_table;
Query OK, 0 rows affected (0.00 sec)
Here is the output of sudo mysqladmin ext -i1 | gawk '/Queries/
07:55:36, 25130
07:55:37, 25422
07:55:38, 24822
07:55:39, 25734
07:55:40, 25379
07:55:41, 25137
07:55:42, 25504
07:55:43, 25420
07:55:44, 25218
07:55:45, 26111
07:55:46, 25183
07:55:47, 25928
07:55:48, 25402
07:55:49, 25169
07:55:50, 25117
07:55:51, 25278
07:55:52, 24868
07:55:53, 25544
07:55:54, 25484
07:55:56, 17195
07:55:56, 22
07:55:57, 1
07:55:59, 1
07:55:59, 1
07:56:00, 1
07:56:02, 1
07:56:02, 23244
07:56:03, 25834
07:56:05, 25526
07:56:05, 24880
07:56:06, 25288
07:56:08, 26032
07:56:08, 26223
07:56:09, 25806
07:56:11, 25786
07:56:11, 25253
07:56:12, 25358
07:56:14, 25745
07:56:14, 25499
07:56:15, 25467
07:56:16, 25223
07:56:17, 25519
07:56:18, 25117
07:56:19, 25596
07:56:20, 25607
07:56:21, 25833
07:56:22, 25621
07:56:23, 25385
07:56:24, 25305
07:56:25, 26082
07:56:26, 25443
07:56:27, 25440
07:56:28, 25377
07:56:29, 24946
07:56:30, 25820
07:56:31, 24259
07:56:32, 25351
07:56:33, 25271
07:56:34, 25168
07:56:35, 24524
07:56:36, 25608
07:56:37, 25611
07:56:38, 25632
07:56:39, 25322
07:56:40, 25629
07:56:41, 25076
07:56:43, 25966
07:56:43, 26012
07:56:44, 25783
07:56:46, 25830
07:56:46, 24359
07:56:47, 25835
07:56:49, 25224
07:56:49, 25608
07:56:50, 25743
07:56:52, 26011
07:56:52, 24710
07:56:53, 25472
07:56:55, 24979
07:56:55, 24889
07:56:56, 25369
07:56:58, 24919
07:56:58, 25524
07:56:59, 25823
You can see that the truncate causes a severe server stall and that the drop is fine.
I have attached a graph of the data.
tags: | added: rdba |
tags: |
added: innodb reba upstream removed: rdba |
tags: |
added: rdba removed: reba |
Please, send your my.cnf file content also. Most likely this is the same problem as described in upstream http:// bugs.mysql. com/bug. php?id= 68184.