我试图修复我们的服务器上的MySQLconfiguration。 我们的应用程序的细节是大量的数据存储在单个表（目前超过3亿行）。 这个表经常用于插入（他们总是来）。
当我在该表上运行select查询需要比几秒钟更长的时间，然后所有插入（正是提交）正在等待表访问，并使我们的应用程序无响应。
据我所知InnoDB在select运行时不会对表进行任何locking。 为什么select阻塞表呢？
我试图用innotopfind一个原因，但我不知道如何解释它的输出和在哪里search。 告诉我你需要什么，我会在这里发布。
+-----+---------+-----------+--------+---------+------+----------------+-----------------------------------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+---------+-----------+--------+---------+------+----------------+-----------------------------------------------------------------------------------------------------------------------------------+ | 1 | root | localhost | dbname | Query | 29 | NULL | COMMIT | | 2 | root | localhost | dbname | Query | 30 | NULL | COMMIT | | 4 | root | localhost | dbname | Query | 29 | NULL | COMMIT | | 5 | root | localhost | dbname | Query | 29 | NULL | COMMIT | | 6 | root | localhost | dbname | Query | 25 | NULL | COMMIT | | 7 | root | localhost | dbname | Query | 0 | NULL | show full processlist | | 13 | user | localhost | dbname | Query | 25 | NULL | COMMIT | | 38 | user | localhost | dbname | Sleep | 0 | | NULL | | 39 | user | localhost | dbname | Sleep | 9017 | | NULL | | 40 | user | localhost | dbname | Query | 33 | Sorting result | SELECT * FROM `large_table` WHERE (`large_table`.`hotspot_id` = 3000064) ORDER BY discovered_at LIMIT 799000, 1000 | | 60 | user | localhost | dbname | Sleep | 1033 | | NULL | | 83 | root | localhost | dbname | Sleep | 3728 | | NULL | | 112 | root | localhost | NULL | Sleep | 6 | | NULL | +-----+---------+-----------+--------+---------+------+----------------+-----------------------------------------------------------------------------------------------------------------------------------+ ===================================== 110824 12:24:24 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 19 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 1521117, signal count 1471216 Mutex spin waits 0, rounds 20647617, OS waits 239914 RW-shared spins 2119697, OS waits 1037149; RW-excl spins 505734, OS waits 218177 ------------ TRANSACTIONS ------------ Trx id counter 0 412917332 Purge done for trx's n:o < 0 412917135 undo n:o < 0 0 History list length 48 Total number of lock structs in row lock hash table 5 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 28363, OS thread id 1092766032 MySQL thread id 83, query id 3249941 localhost root ---TRANSACTION 0 412901582, not started, process no 28363, OS thread id 1144449360 MySQL thread id 60, query id 3677008 localhost user ---TRANSACTION 0 412917189, not started, process no 28363, OS thread id 1144314192 MySQL thread id 43, query id 3905773 localhost root ---TRANSACTION 0 412534255, not started, process no 28363, OS thread id 1092630864 MySQL thread id 39, query id 14279 localhost user ---TRANSACTION 0 412917331, not started, process no 28363, OS thread id 1144179024 MySQL thread id 38, query id 3908045 localhost user ---TRANSACTION 0 412917201, not started, process no 28363, OS thread id 1092495696 MySQL thread id 13, query id 3908257 localhost user ---TRANSACTION 0 412538821, not started, process no 28363, OS thread id 1092360528 MySQL thread id 7, query id 3908258 localhost root show engine innodb status ---TRANSACTION 0 412917330, ACTIVE 6 sec, process no 28363, OS thread id 1144043856 2 lock struct(s), heap size 368, undo log entries 1 MySQL thread id 2, query id 3907373 localhost root COMMIT Trx read view will not see trx with id >= 0 412917331, sees < 0 412917131 ---TRANSACTION 0 412917328, ACTIVE 6 sec, process no 28363, OS thread id 1092225360 2 lock struct(s), heap size 368, undo log entries 1 MySQL thread id 6, query id 3907345 localhost root COMMIT Trx read view will not see trx with id >= 0 412917329, sees < 0 412917131 ---TRANSACTION 0 412917326, ACTIVE 6 sec, process no 28363, OS thread id 1091955024 2 lock struct(s), heap size 368, undo log entries 1 MySQL thread id 4, query id 3907335 localhost root COMMIT Trx read view will not see trx with id >= 0 412917327, sees < 0 412917131 ---TRANSACTION 0 412917324, ACTIVE 6 sec, process no 28363, OS thread id 1092090192 2 lock struct(s), heap size 368, undo log entries 1 MySQL thread id 5, query id 3907328 localhost root COMMIT Trx read view will not see trx with id >= 0 412917325, sees < 0 412917131 ---TRANSACTION 0 412917321, ACTIVE (PREPARED) 7 sec, process no 28363, OS thread id 1143908688 preparing 2 lock struct(s), heap size 368, undo log entries 1 MySQL thread id 1, query id 3907125 localhost root COMMIT Trx read view will not see trx with id >= 0 412917322, sees < 0 412917131 ---TRANSACTION 0 412917131, ACTIVE 20 sec, process no 28363, OS thread id 1074075984, thread declared inside InnoDB 111 mysql tables in use 1, locked 0 MySQL thread id 40, query id 3904958 localhost user Sorting result SELECT * FROM `large_table` WHERE (`large_table`.`hotspot_id` = 3000064) ORDER BY discovered_at LIMIT 848000, 1000 Trx read view will not see trx with id >= 0 412917132, sees < 0 412917132 -------- 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: 1; buffer pool: 0 3510225 OS file reads, 284998 OS file writes, 202897 OS fsyncs 1.05 reads/s, 21299 avg bytes/read, 8.10 writes/s, 7.58 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 275, free list len 13392, seg size 13668, 489950 inserts, 491830 merged recs, 10986 merges Hash table size 8850487, used cells 8127172, node heap has 32697 buffer(s) 71914.53 hash searches/s, 8701.91 non-hash searches/s --- LOG --- Log sequence number 157 3331524445 Log flushed up to 157 3331521939 Last checkpoint at 157 3326072846 1 pending log writes, 0 pending chkp writes 199025 log i/o's done, 7.53 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 4788954432; in additional pool allocated 1048576 Buffer pool size 262144 Free buffers 0 Database pages 229447 Modified db pages 1439 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 7453325, created 14887, written 118658 1.37 reads/s, 0.11 creates/s, 0.53 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue 7 read views open inside InnoDB Main thread process no. 28363, id 1091684688, state: flushing log Number of rows inserted 1093064, updated 249134, deleted 1405, read 1115880534 7.89 inserts/s, 2.47 updates/s, 0.05 deletes/s, 80953.21 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 编辑：
感谢澄清这一点。
所以我现在必须把我的问题分成两个案例。
locking这张桌子是否正常导致我的整个应用程序“挂起”。 不应该对其他表的查询响应？ 也许有些缓冲区设置得太低？
将这个表切换到MyISAM帮助？ 我根本不需要交易。 在这种情况下是不是还有其他的锁（长选+多快插入）？
EDIT2：
这就是插入查询的样子：
INSERT INTO `large_table` (`device_address`, `hotspot_id`, `minute`, `created_at`, `updated_at`, `discovered_with_hci`, `hour`, `rssi`, `day`, `device_class`, `discovered_at`) VALUES('10:40:03:90:10:40', 3000008, 1, '2011-08-22 05:01:08', '2011-08-22 05:01:08', -1, 5, -79, '2011-08-22 05:01:01', '0', '2011-08-22 05:01:01')
那是什么索引定义在它上面：
+-------------+------------+----------------------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------------------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+ | large_table | 0 | PRIMARY | 1 | id | A | 92396334 | NULL | NULL | | BTREE | | | large_table | 1 | index_large_table_on_discovered_with_hci | 1 | discovered_with_hci | A | 18 | NULL | NULL | YES | BTREE | | | large_table | 1 | index_large_table_on_hotspot_id | 1 | hotspot_id | A | 18 | NULL | NULL | YES | BTREE | | | large_table | 1 | index_large_table_on_day_and_hour_and_minute | 1 | day | A | 18 | NULL | NULL | YES | BTREE | | | large_table | 1 | index_large_table_on_day_and_hour_and_minute | 2 | hour | A | 18 | NULL | NULL | YES | BTREE | | | large_table | 1 | index_large_table_on_day_and_hour_and_minute | 3 | minute | A | 537187 | NULL | NULL | YES | BTREE | | | large_table | 1 | index_large_table_on_created_at | 1 | created_at | A | 8399666 | NULL | NULL | YES | BTREE | | | large_table | 1 | index_large_table_on_rssi | 1 | rssi | A | 18 | NULL | NULL | YES | BTREE | | +-------------+------------+----------------------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
编辑3：
为什么在这样的查询中，我的应用程序没有响应？ 它不应该只影响'large_table'吗？
也许有什么是我的MySQLconfiguration错了？ 服务器是4核心Xeon 2GHz与16GB内存。 它运行MySQL + Rails应用程序
我的configuration参数：
skip-external-locking key_buffer = 64M max_allowed_packet = 16M thread_stack = 128K thread_cache_size = 8 query_cache_size = 32M tmp_table_size = 64M max_heap_table_size = 64M table_cache = 256 read_rnd_buffer_size = 512K sort_buffer_size = 2M myisam-recover = BACKUP max_connections = 200 query_cache_limit = 1M long_query_time = 200 max_binlog_size = 100M innodb_buffer_pool_size = 4G safe-updates max_join_size=100000000
Mysqltuner脚本只build议：
long_query_time (<= 10) innodb_buffer_pool_size (>= 62G)
请仔细查看stream程列表和“show engine innodb status”。 你看到了什么 ？？？
进程ID 1,2,4,5,6,13都尝试运行COMMIT。
谁撑着一切？ 进程ID 40正在运行一个针对large_table的查询。
进程ID 40已经运行了33秒。 进程ID 1,2,4,5,6,13已经运行less于33秒。 进程ID 40正在处理某事。 什么是阻止？
首先，查询通过MVCC敲打large_table的聚簇索引 。
在进程ID内1,2,4,5,6,13是具有保护其事务隔离的MVCC Data的行。 进程ID 40具有在数据行中前进的查询。 如果在字段hotspot_id上有一个索引，则该键+聚簇索引中实际行的键必须执行内部locking。 （注意：根据devise，InnoDB中的所有非唯一索引都带有您的键（您要索引的列）+聚簇索引键。 这种独特的场景基本上是不可阻挡的力量遇到不可移动的对象。
实质上，COMMIT必须等到对large_table应用更改是安全的。 你的情况不是独一无二的，不是一次性的，也不是一个罕见的现象。
我实际上在DBA StackExchange中回答了这样的三个问题。 这些问题是由同一个人提出的同一个问题。 我的回答不是解决scheme，而是帮助提问者就如何处理他的情况达成自己的结论。
除了这些答案之外， 我还回答了另一个人关于SELECT的死锁问题 。
我希望我在这个问题上的过去的post有助于澄清发生在你身上的事情。
更新2011-08-25 08:10 EDT
这是来自进程ID 40的查询
SELECT * FROM `large_table` WHERE (`large_table`.`hotspot_id` = 3000064) ORDER BY discovered_at LIMIT 799000, 1000;
两点意见：
你正在做'SELECT *'你是否需要获取每一列？ 如果您只需要特定的列，则应标记它们，因为1000行的临时表可能比您真正需要的大。
WHERE和ORDER BY子句通常会泄露性能问题或使表格devise发光。 您需要创build一个机制，在收集数据之前加快密钥的收集。
根据这两个观察，你必须做出两个重大的改变：
主要更改＃1：重构查询
重新devise查询，以便
这是新的查询这三件事情
SELECT large_table.* FROM large_table INNER JOIN ( SELECT hotspot_id,discovered_at FROM large_table WHERE hotspot_id = 3000064 ORDER BY discovered_at LIMIT 799000,1000 ) large_table_keys USING (hotspot_id,discovered_at);
子查询large_table_keys收集您需要的1000个键。 子查询的结果是INNER JOIN到large_table。 到目前为止，键被检索而不是整行。 这仍然是799,000行读取。 有一个更好的方法来获得这些密钥，这导致我们…
MAJOR CHANGE＃2：创build支持重构查询的索引
由于重构查询只包含一个子查询，所以只需要创build一个索引。 这是指数：
ALTER TABLE large_table ADD INDEX hotspot_discovered_ndx (hotspot_id,discovered_at);
为什么这个特定的索引 看看WHERE子句。 hotspot_id是一个静态值。 这使得所有的hotspot_ids在索引中形成一个顺序列表。 现在看看ORDER BY子句。 discovered_at列可能是DATETIME或TIMESTAMP字段。
这在索引中呈现的自然顺序如下：
做这个索引也消除了临时表的内部sorting。
请把这两个重大的变化，你会看到在运行时间的差异。
试一试 ！！！
更新2011-08-25 08:15 EDT
我看着你的索引。 你仍然需要创build我build议的索引。