我有一个innodb表获得大量的读取，几乎没有写入（比如，每写入约400,000读一个）。
我遇到一个很大的问题，虽然当我插入到表中。 MySQL 完全locking。 它使用100％cpu，并且每个其他表（甚至在其他数据库中）都将其状态设置为“locking”，直到INSERT完成。 这是一个很大的问题，因为MySQL保持locking长达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 这里没什么特别的
所以这是我的stream程列表。 请记住，这个问题只发生在我插入到与system_visitors_ips表完全无关的images表中时。
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'上保持lockingINSERT 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
请尝试使用以下命令，看看是否有任何问题。
SHOW ENGINE INNODB STATUS;
此外，如果其他（innodb）数据库中的每个表都被locking，那么我认为所有innodb表共享的innodb表空间文件可能会有一些问题。