我有一张 innodb 表，它有大量的读取操作但几乎没有写入操作（例如，每大约 400,000 次读取才会有 1 次写入）。
然而，当我在表中执行 INSERT 操作时，我遇到了一个相当大的问题。MySQL完全地锁定。它使用 100% 的 CPU，并且每个其他表（甚至在其他数据库中）的状态都设置为“已锁定”，直到 INSERT 完成。这是一个大问题，因为 MySQL 最多会保持锁定状态 4 分钟。
我正在使用版本 5.1.47（来自 mysql.com 的 rpm）。
有任何想法吗？
编辑：
CREATE TABLE `images` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`posted_on` datetime NOT NULL,
`hash` binary(20) NOT NULL,
`struct` char(64) NOT NULL,
`category_id` smallint(5) unsigned NOT NULL,
`rating` smallint(6) NOT NULL,
`order_id` mediumint(8) unsigned NOT NULL,
`icon_type` enum('jpg','gif','png') NOT NULL,
`icon_width` smallint(5) unsigned NOT NULL,
`icon_height` smallint(5) unsigned NOT NULL,
`thumb_type` enum('jpg','gif','png') NOT NULL,
`thumb_width` smallint(5) unsigned NOT NULL,
`thumb_height` smallint(5) unsigned NOT NULL,
`medium_type` enum('jpg','gif','png') NOT NULL,
`medium_width` smallint(5) unsigned NOT NULL,
`medium_height` smallint(5) unsigned NOT NULL,
`full_type` enum('jpg','gif','png') NOT NULL,
`full_width` smallint(5) unsigned NOT NULL,
`full_height` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `struct` (`struct`),
UNIQUE KEY `hash` (`hash`),
KEY `category_id` (`category_id`,`order_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5118 DEFAULT CHARSET=latin1
这里没什么特别的。
以下是我的流程列表。请记住，这个问题仅当我在表中执行 INSERT 操作时才会发生images，这与system_visitors_ips表完全无关。
mysql> show processlist;
+--------+----------------+----------------------+-------------------+-------------+-------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+----------------+----------------------+-------------------+-------------+-------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 2775 | xxxxxxx | xxx.xxx.xx.xxx:44108 | NULL | Binlog Dump | 62866 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 446944 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 22 | update | INSERT INTO images SET struct = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', |
| 446945 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 20 | end | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446946 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 20 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446947 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 17 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446948 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 446949 | xxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 16 | end | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446950 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxxxxxx | Query | 14 | update | INSERT INTO data_bkt_r (interval_start, creative_id, zone_id, count) VALUES |
| 446951 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxxxxxx | Query | 14 | Locked | INSERT INTO data_bkt_r (interval_start, creative_id, zone_id, count) VALUES |
| 446952 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxxxxxx | Query | 13 | update | INSERT INTO data_bkt_m (interval_start, creative_id, zone_id, count) VALUES |
| 446953 | xxxxxxxxxxxx | localhost | xxxxxxxxxxxx | Query | 12 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446954 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 11 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446955 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 9 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446956 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 9 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446957 | xxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 8 | update | INSERT LOW_PRIORITY INTO system_visitors_referrals_hits SET referral_id = ' |
| 446958 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 8 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446959 | xxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 8 | Locked | INSERT LOW_PRIORITY INTO system_visitors_referrals_hitsSET referral_id = ' |
| 446960 | xxxxxxxxxxxxxx | localhost | xxxxxxxxxxxxxx | Query | 6 | end | INSERT INTO `wp_slim_browsers` ( `browser`, `version`, `platform`, `css_version` ) SELECT 'Firef |
| 446961 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 6 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446962 | xxxxxxxxxxxx | localhost | xxxxxxxxxxxx | Query | 6 | Locked | INSERT LOW_PRIORITY INTO system_visitors_referrals_hits SET referral_id = ' |
| 446963 | xxxxxxxxxxxx | localhost | xxxxxxxxxxxx | Query | 4 | Locked | INSERT LOW_PRIORITY INTO system_visitors_referrals_hitsSET referral_id = ' |
| 446964 | xxxxxxxxxxxx | localhost | xxxxxxxxxxxx | Query | 4 | Locked | INSERT LOW_PRIORITY INTO system_visitors_referrals_hitsSET referral_id = ' |
| 446965 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxxxxxx | Query | 4 | Locked | INSERT INTO data_bkt_r (interval_start, creative_id, zone_id, count) VALUES |
| 446966 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxxxxxx | Query | 4 | Locked | INSERT INTO data_bkt_r (interval_start, creative_id, zone_id, count) VALUES |
| 446967 | xxxxxxxxxxxxx | localhost | xxxxxxxxxxxxx | Query | 2 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446968 | xxxxxxxxxxxx | localhost | xxxxxxxxxxxx | Query | 0 | Locked | UPDATE system_visitors_ips SET posted_on = NOW() WHERE user_id |
| 446969 | xxxxxxxxxxx | localhost | xxxxxxxxxxx | Query | 0 | update | INSERT INTO xxxxxxxxxx SET cache='xxxxxxxxxxxxxxxxxx', date=NOW(), user_id='', ip |
+--------+----------------+----------------------+-------------------+-------------+-------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
27 rows in set (0.00 sec)
INNODB 状态，在INSERT INTO images SET struct = 'xxxxxxxxxxx', posted_on = NOW(), category_id = 'xxx'查询上保持锁定 20 多秒：
=====================================
100530 11:17:07 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 12 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 131393, signal count 125882
Mutex spin waits 0, rounds 1583362, OS waits 8189
RW-shared spins 225133, OS waits 108608; RW-excl spins 34921, OS waits 10644
------------
TRANSACTIONS
------------
Trx id counter 0 10511023
Purge done for trx's n:o < 0 10414917 undo n:o < 0 0
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 22925, OS thread id 1180911936
MySQL thread id 1851720, query id 51638294 localhost xxxxxxxxxxxxx
---TRANSACTION 0 0, not started, process no 22925, OS thread id 1087560000
MySQL thread id 1851511, query id 51632276 localhost xxxxxxxxxxxxx
---TRANSACTION 0 0, not started, process no 22925, OS thread id 1169463616
MySQL thread id 1851279, query id 51626132 localhost xxxxxxxxxxxxx
---TRANSACTION 0 0, not started, process no 22925, OS thread id 1180645696
MySQL thread id 1851202, query id 51624553 localhost xxxxxxxxxxxxx
---TRANSACTION 0 0, not started, process no 22925, OS thread id 1175054656
MySQL thread id 1851031, query id 51620527 localhost xxxxxxxxxxxxx
---TRANSACTION 0 10510698, not started, process no 22925, OS thread id 1202211136
MySQL thread id 1850961, query id 51618811 localhost xxxxxxxxxxxxx
---TRANSACTION 0 0, not started, process no 22925, OS thread id 1086970176
MySQL thread id 1849803, query id 51640040 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 10511022, ACTIVE 21 sec, process no 22925, OS thread id 1187301696, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368, 0 row lock(s), undo log entries 1
MySQL thread id 1851783, query id 51639713 localhost xxxxxxxxxxxx update
INSERT INTO images SET struct = 'xxxxxxxxxxx', posted_on = NOW(), category_id = 'xxx'
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1278545 OS file reads, 48082 OS file writes, 37866 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
13884 inserts, 13884 merged recs, 682 merges
Hash table size 17393, node heap has 14 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 11 3107465089
Log flushed up to 11 3107465089
Last checkpoint at 11 3107465089
0 pending log writes, 0 pending chkp writes
29690 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 20704954; in additional pool allocated 989440
Dictionary memory allocated 430344
Buffer pool size 512
Free buffers 0
Database pages 498
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 2507340, created 36023, written 48970
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 22925, id 1156950336, state: sleeping
Number of rows inserted 676146, updated 41, deleted 14, read 587606968
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
答案1
请尝试使用以下命令，看看是否存在任何问题。
SHOW ENGINE INNODB STATUS;
此外，如果其他（innodb）数据库中的每个表也被锁定，那么我认为所有 innodb 表共享的 innodb 表空间文件可能存在一些问题。