Description:
I am seeing odd locking issues when partitioning a table by hash in 5.5.
MySQl version: 5.5.1-m2-log
OS: 2.6.18-128.el5 #1 SMP Wed Jan 21 10:41:14 EST 2009 x86_64 x86_64 x86_64 GNU/Linux
Server: Quad-Core AMD Opteron(tm) Processor 2376
FileSYS: ext3
One table partitioned by hash (256 partitions).
REATE TABLE `fast_path_blob_properties` (
`blob_id` bigint(20) unsigned NOT NULL,
`mime_type_id` smallint(5) unsigned NOT NULL,
`blob_size` int(10) unsigned NOT NULL,
`volume_id_0` smallint(5) unsigned NOT NULL DEFAULT '0',
`volume_id_1` smallint(5) unsigned NOT NULL DEFAULT '0',
`volume_id_2` smallint(5) unsigned NOT NULL DEFAULT '0',
`volume_id_3` smallint(5) unsigned NOT NULL DEFAULT '0',
`volume_id_4` smallint(5) unsigned NOT NULL DEFAULT '0',
`volume_id_5` smallint(5) unsigned NOT NULL DEFAULT '0',
`access_control` smallint(5) unsigned NOT NULL DEFAULT '0',
`created_dt` int(10) unsigned NOT NULL,
PRIMARY KEY (`blob_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (blob_id)
PARTITIONS 256 */
Example data:
blob_id mime_type blob_size volume_id0 volume_id1 volume_id2 volume_id3 volume_id4 volume_id5 access created_dt
0 38 96497641 4 157 216 366 452 541 0 1210041457
2 19 21347345 96 126 284 365 484 590 0 1253650365
4 32 82852582 57 176 288 390 403 586 0 1206278021
6 13 86715030 55 128 225 341 469 590 0 1152135628
8 6 139898291 33 150 277 341 406 510 0 1256629919
10 17 114375689 20 186 281 347 485 574 0 1254901642
12 34 148221964 23 179 212 342 489 570 0 1242575844
14 11 5136152 37 117 220 308 404 533 0 1224813440
16 1 63018921 78 102 279 343 484 580 0 1261165655
I filled this table with 150 million rows.
How to repeat:
/bin/mysqlslap --create-schema=canoe --query=test7.txt --delimiter=";" --user=root --socket=/tmp/mysql.3306.sock --concurrency=4 --iterations=1
test7.txt data:
select blob_id,mime_type_id,blob_size,volume_id_0,volume_id_1,volume_id_2,volume_id_3,volume_id_4,volume_id_5,access_control,created_dt from fast_path_blob_properties where blob_id = 1355172;
select blob_id,mime_type_id,blob_size,volume_id_0,volume_id_1,volume_id_2,volume_id_3,volume_id_4,volume_id_5,access_control,created_dt from fast_path_blob_properties where blob_id = 13874114;
select blob_id,mime_type_id,blob_size,volume_id_0,volume_id_1,volume_id_2,volume_id_3,volume_id_4,volume_id_5,access_control,created_dt from fast_path_blob_properties where blob_id = 42872560;
select blob_id,mime_type_id,blob_size,volume_id_0,volume_id_1,volume_id_2,volume_id_3,volume_id_4,volume_id_5,access_control,created_dt from fast_path_blob_properties where blob_id = 26745652;
select blob_id,mime_type_id,blob_size,volume_id_0,volume_id_1,volume_id_2,volume_id_3,volume_id_4,volume_id_5,access_control,created_dt from fast_path_blob_properties where blob_id = 4357262;
select blob_id,mime_type_id,blob_size,volume_id_0,volume_id_1,volume_id_2,volume_id_3,volume_id_4,volume_id_5,access_control,created_dt from fast_path_blob_properties where blob_id = 28236520;
select blob_id,mime_type_id,blob_size,volume_id_0,volume_id_1,volume_id_2,volume_id_3,volume_id_4,volume_id_5,access_control,created_dt from fast_path_blob_properties where blob_id = 23511374;
select blob_id,mime_type_id,blob_size,volume_id_0,volume_id_1,volume_id_2,volume_id_3,volume_id_4,volume_id_5,access_control,created_dt from fast_path_blob_properties where blob_id = 38446154;
select blob_id,mime_type_id,blob_size,volume_id_0,volume_id_1,volume_id_2,volume_id_3,volume_id_4,volume_id_5,access_control,created_dt from fast_path_blob_properties where blob_id = 48731106;
select blob_id,mime_type_id,blob_size,volume_id_0,volume_id_1,volume_id_2,volume_id_3,volume_id_4,volume_id_5,access_control,created_dt from fast_path_blob_properties where blob_id = 31152458;
The sample queries were run with 1,000,000 rows.
What to expect:
====================================
100124 2:22:58 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 60 seconds
----------
BACKGROUND THREAD
----------
srv_master_thread loops: 240 1_second, 239 sleeps, 23 10_second, 2 background, 2 flush
srv_master_thread log flush and writes: 239
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 69776, signal count 24806
--Thread 1223592256 has waited at btr/btr0cur.c line 523 for 0.00 seconds the semaphore:
S-lock on RW-latch at 0x2aaab192b818 created in file buf/buf0buf.c line 680
a writer (thread id 1225177408) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file not yet reserved line 0
Last time write locked in file buf/buf0buf.c line 2833
--Thread 1224120640 has waited at btr/btr0cur.c line 523 for 0.00 seconds the semaphore:
S-lock on RW-latch at 0x2aaab192b818 created in file buf/buf0buf.c line 680
a writer (thread id 1225177408) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file not yet reserved line 0
Last time write locked in file buf/buf0buf.c line 2833
--Thread 1224649024 has waited at btr/btr0cur.c line 523 for 0.00 seconds the semaphore:
S-lock on RW-latch at 0x2aaab192b818 created in file buf/buf0buf.c line 680
a writer (thread id 1225177408) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file not yet reserved line 0
Last time write locked in file buf/buf0buf.c line 2833
Mutex spin waits 46233, rounds 1291130, OS waits 1979
RW-shared spins 67468, OS waits 67383; RW-excl spins 195, OS waits 1
Spin rounds per wait: 27.93 mutex, 30.00 RW-shared, 5.06 RW-excl
mysql> show processlist;
+----+------+-------------------+-------+-------------+------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-------------------+-------+-------------+------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 1 | repl | :34690 | NULL | Binlog Dump | 1312 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 2 | root | localhost | canoe | Query | 0 | NULL | show processlist |
| 4 | root | localhost | canoe | Query | 0 | System lock | select blob_id,mime_type_id,blob_size,volume_id_0,volume_id_1,volume_id_2,volume_id_3,volume_id_4,vo |
| 5 | root | localhost | canoe | Query | 0 | statistics | select blob_id,mime_type_id,blob_size,volume_id_0,volume_id_1,volume_id_2,volume_id_3,volume_id_4,vo |
| 6 | root | localhost | canoe | Query | 0 | System lock | select blob_id,mime_type_id,blob_size,volume_id_0,volume_id_1,volume_id_2,volume_id_3,volume_id_4,vo |
| 7 | root | localhost | canoe | Query | 0 | System lock | select blob_id,mime_type_id,blob_size,volume_id_0,volume_id_1,volume_id_2,volume_id_3,volume_id_4,vo |
+----+------+-------------------+-------+-------------+------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
