Description:
Multi-threaded mysqld lockup issue with ~50-55 second timeout (simultaneous states "updating/"Waiting for table metadata lock"/"Waiting for table flush"). Some connections run/lockup more then 55 seconds, depending on the situation (random testing reveals different results).
How to repeat:
Testcase:
1. Branch lp:percona-qa and cd pquery
2. Startup mysqld (I used Percona-Server-5.6.20-rel68.0-654.Linux.x86_64-debug)
3. Execute:
export LD_LIBRARY_PATH=/sda/mysql-5.6.20-linux-glibc2.5-x86_64/lib/
./pquery --infile=./out.2 --database=test --threads=100 --verbose --user=root --socket=/sda/mysql-5.6.20-linux-glibc2.5-x86_64/socket.sock
4. Connect with mysql cli client and repeat SHOW PROCESSLIST;
5. Wait a while till lockup starts appearing. It will look like this (I've removed a few redundant columns to make paste clearer):
mysql> show processlist;
+------+---------+------+---------------------------------+----------------------------------------+
| Id | Command | Time | State | Info |
+------+---------+------+---------------------------------+----------------------------------------+
| 1238 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1239 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1240 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1241 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1242 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1243 | Query | 47 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1244 | Query | 47 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1245 | Query | 47 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1246 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1247 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1248 | Query | 47 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1249 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1250 | Query | 47 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1251 | Query | 47 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1252 | Query | 47 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1253 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1254 | Query | 47 | updating | DELETE FROM t2 LIMIT 1 |
| 1255 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1256 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1257 | Query | 47 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1258 | Query | 47 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1259 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1260 | Query | 47 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1261 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1262 | Query | 47 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1263 | Query | 46 | Waiting for table flush | FLUSH TABLES |
| 1264 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1265 | Query | 47 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1266 | Query | 47 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1267 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1268 | Query | 47 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1269 | Query | 47 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1270 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1271 | Query | 47 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1272 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1273 | Query | 47 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1274 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1275 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1276 | Query | 46 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1277 | Query | 47 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1278 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1279 | Query | 47 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1280 | Query | 47 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1281 | Query | 47 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1282 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1283 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1284 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1285 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1286 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1287 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1288 | Query | 47 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1289 | Query | 47 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1290 | Query | 47 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1291 | Query | 47 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1292 | Query | 47 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1293 | Query | 47 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1294 | Query | 47 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1295 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1296 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1297 | Query | 47 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1298 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1299 | Query | 47 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1300 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1301 | Query | 47 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1302 | Query | 47 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1303 | Query | 47 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1304 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1305 | Query | 47 | Waiting for table flush | INSERT INTO t2 VALUES (1,2,3) |
| 1306 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1307 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1308 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1309 | Query | 47 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1310 | Query | 47 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1311 | Query | 47 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1312 | Query | 47 | updating | DELETE FROM t2 LIMIT 1 |
| 1313 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1314 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1315 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1316 | Query | 47 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1317 | Query | 47 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1318 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1319 | Query | 47 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1320 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1321 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1322 | Query | 47 | updating | DELETE FROM t2 LIMIT 1 |
| 1323 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1324 | Query | 47 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1325 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1326 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1327 | Query | 47 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1328 | Query | 47 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1329 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1330 | Query | 47 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1331 | Query | 47 | updating | DELETE FROM t2 LIMIT 1 |
| 1332 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1333 | Query | 47 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1334 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1335 | Query | 47 | Waiting for table flush | FLUSH TABLES |
| 1336 | Query | 47 | Waiting for table metadata lock | DROP TABLE t2 |
| 1337 | Query | 47 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1338 | Query | 0 | init | show processlist |
+------+---------+------+---------------------------------+----------------------------------------+
101 rows in set (0.00 sec)
And, after some sort of (hardcoded?) 50-53 second timeout is hit (the threads with 0-1 second time are fresh commands - they are not relevant):
mysql> show processlist;
+------+---------+------+---------------------------------+----------------------------------------+
| Id | Command | Time | State | Info |
+------+---------+------+---------------------------------+----------------------------------------+
| 1238 | Query | 1 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1239 | Query | 0 | Waiting for table metadata lock | DELETE FROM t1 LIMIT 1 |
| 1240 | Query | 0 | Waiting for table metadata lock | INSERT INTO t1 VALUES (1,2,3) |
| 1241 | Query | 1 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1242 | Query | 1 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1243 | Query | 52 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1244 | Query | 52 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1245 | Query | 52 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1246 | Query | 1 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1247 | Query | 0 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1248 | Query | 52 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1249 | Query | 1 | Waiting for table metadata lock | CREATE TABLE t1 (c1 INT,c2 INT,c3 INT) |
| 1250 | Query | 52 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1251 | Query | 52 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1252 | Query | 52 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1253 | Query | 0 | Waiting for table metadata lock | DROP TABLE t1 |
| 1254 | Query | 1 | creating table | CREATE TABLE t1 (c1 INT,c2 INT,c3 INT) |
| 1255 | Query | 1 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1256 | Query | 1 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1257 | Query | 52 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1258 | Query | 52 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1259 | Query | 0 | Waiting for table metadata lock | SELECT c1 FROM t1 |
| 1260 | Query | 52 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1261 | Query | 0 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1262 | Query | 52 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1263 | Query | 0 | Waiting for table metadata lock | SELECT c1 FROM t1 |
| 1264 | Query | 0 | Waiting for table metadata lock | SELECT c1 FROM t1 |
| 1265 | Query | 52 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1266 | Query | 52 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1267 | Query | 1 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1268 | Query | 52 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1269 | Query | 52 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1270 | Query | 0 | Waiting for table metadata lock | CREATE TABLE t1 (c1 INT,c2 INT,c3 INT) |
| 1271 | Query | 52 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1272 | Query | 0 | init | COMMIT |
| 1273 | Query | 52 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1274 | Query | 1 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1275 | Query | 1 | Waiting for table metadata lock | CREATE TABLE t1 (c1 INT,c2 INT,c3 INT) |
| 1276 | Query | 51 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1277 | Query | 52 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1278 | Query | 1 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1279 | Query | 52 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1280 | Query | 52 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1281 | Query | 52 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1282 | Query | 0 | Waiting for table metadata lock | CREATE TABLE t1 (c1 INT,c2 INT,c3 INT) |
| 1283 | Query | 1 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1284 | Query | 0 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1285 | Query | 1 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1286 | Query | 0 | Waiting for table metadata lock | INSERT INTO t1 VALUES (1,2,3) |
| 1287 | Query | 0 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1288 | Query | 52 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1289 | Query | 52 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1290 | Query | 52 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1291 | Query | 52 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1292 | Query | 52 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1293 | Query | 52 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1294 | Query | 52 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1295 | Query | 1 | Waiting for table metadata lock | CREATE TABLE t1 (c1 INT,c2 INT,c3 INT) |
| 1296 | Query | 1 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1297 | Query | 52 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1298 | Query | 0 | closing tables | DROP TABLE t2 |
| 1299 | Query | 52 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1300 | Query | 0 | Waiting for table metadata lock | CREATE TABLE t1 (c1 INT,c2 INT,c3 INT) |
| 1301 | Query | 52 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1302 | Query | 52 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1303 | Query | 52 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1304 | Query | 0 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1305 | Query | 0 | Waiting for table metadata lock | SELECT c1 FROM t1 |
| 1306 | Query | 0 | Waiting for table metadata lock | SELECT c1 FROM t1 |
| 1307 | Query | 1 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1308 | Query | 1 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1309 | Query | 52 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1310 | Query | 52 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1311 | Query | 52 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1312 | Query | 0 | Waiting for table metadata lock | DROP TABLE t1 |
| 1313 | Query | 0 | Waiting for table metadata lock | DELETE FROM t1 LIMIT 1 |
| 1314 | Query | 0 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1315 | Query | 1 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1316 | Query | 52 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1317 | Query | 52 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1318 | Query | 1 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1319 | Query | 52 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1320 | Query | 1 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1321 | Query | 0 | Waiting for table metadata lock | SELECT c1 FROM t1 |
| 1322 | Query | 1 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1323 | Query | 0 | Waiting for table metadata lock | DROP TABLE t1 |
| 1324 | Query | 52 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1325 | Query | 1 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1326 | Query | 0 | Waiting for table metadata lock | DROP TABLE t2 |
| 1327 | Query | 52 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1328 | Query | 52 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1329 | Query | 0 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1330 | Query | 52 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1331 | Query | 1 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1332 | Query | 0 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1333 | Query | 52 | Waiting for table metadata lock | CREATE TABLE t2 (c1 INT,c2 INT,c3 INT) |
| 1334 | Query | 0 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1335 | Query | 0 | Waiting for table metadata lock | INSERT INTO t2 VALUES (1,2,3) |
| 1336 | Query | 1 | Waiting for table metadata lock | SELECT c1 FROM t2 |
| 1337 | Query | 52 | Waiting for table metadata lock | DELETE FROM t2 LIMIT 1 |
| 1338 | Query | 0 | init | show processlist |
+------+---------+------+---------------------------------+----------------------------------------+
101 rows in set (0.00 sec)
The lockup issue is consistently repeatable, and it's always around 50-55 seconds that the state "resets", though as mentioned above at times threads keep running past 55 seconds while others have already reset around the 50-55 seconds time.
Suggested fix:
No lockups.