Innodb查询缓慢

我有RedHat 5.3（Tikanga）和Mysql 5.0.86configuration了RAID 10硬件，

我运行一个查询Mysql / InnoDB和MyIsam表的应用程序，

查询速度非常快，但Innodb表中的一些查询有时候会放慢速度，并且需要超过1-3秒才能运行，而且这些查询是简单和优化的，

这个问题发生在随机查询的不同时间的innodb表。

• 在启用InnoDB引擎的同时启用 – 插件'InnoDB'初始化函数返回错误
• Ubuntu脚本用apache userdir和mysql用户名添加多个用户
• 通过ssh在mysql 5.0.xx社区和企业版之间复制
• MySQL 5.6升级 – 表mysql.proc的定义不正确
• 如何启用对AZURE虚拟机上的MySQL服务器的远程访问

为什么这只发生在Innodb桌子上呢？

下面是Innodb状态和一些Mysqlvariables：

show innodb status\G *************************** 1. row *************************** Status: ===================================== 120325 10:54:08 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 19 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 22943, signal count 22947 Mutex spin waits 0, rounds 561745, OS waits 7664 RW-shared spins 24427, OS waits 12201; RW-excl spins 1461, OS waits 1277 ------------ TRANSACTIONS ------------ Trx id counter 0 119069326 Purge done for trx's n:o < 0 119069326 undo n:o < 0 0 History list length 41 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 29093, OS thread id 1166043456 MySQL thread id 703985, query id 5807220 localhost root show innodb status -------- 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 132777 OS file reads, 689086 OS file writes, 252010 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 366, seg size 368, 62237 inserts, 62237 merged recs, 52881 merges Hash table size 8850487, used cells 3698960, node heap has 7061 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 15 3415398745 Log flushed up to 15 3415398745 Last checkpoint at 15 3415398745 0 pending log writes, 0 pending chkp writes 218214 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 4798817080; in additional pool allocated 12342784 Buffer pool size 262144 Free buffers 101603 Database pages 153480 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 151954, created 1526, written 494505 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 29093, id 1162049856, state: waiting for server activity Number of rows inserted 77675, updated 85439, deleted 0, read 14377072495 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set, 1 warning (0.02 sec) ============================= read_buffer_size = 128M sort_buffer_size = 256M tmp_table_size = 1024M innodb_additional_mem_pool_size = 20M innodb_log_file_size=10M innodb_lock_wait_timeout=100 innodb_buffer_pool_size=4G join_buffer_size = 128M key_buffer_size = 1G 

谁能帮我 ？

对不同数据库的不同查询但是他们有一个共同点就是Innodb表中出现了这个问题

一个表的结构如下：

 mysql> show create table C_A\G *************************** 1. row *************************** Table: C_A Create Table: CREATE TABLE `C_A` ( `C_no` varchar(32) NOT NULL default '', `C_t` double(11,2) default NULL, `act` enum('0','1') default '0', `us` enum('0','1') default '0', `event_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `pro` enum('0','1') default '0', `C_type` enum('pre','ep','pon') NOT NULL default 'pre', `C_number` bigint(20) NOT NULL default '0', `cur` varchar(3) default 'JOR', PRIMARY KEY (`C_no`), KEY `act_ndx` (`act`), KEY `us_ndx` (`us`), KEY `index1` (`C_no`(20),`act`), KEY `C_number_index` (`C_number`), KEY `type_index` (`C_type`), KEY `cur_index` (`cur`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) 

更新语句的第一个缓慢的查询我改变它来select：

 Count : 1 (100.00%) Time : 3 s total, 3 s avg, 3 s to 3 s max (100.00%) Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%) Rows sent : 0 avg, 0 to 0 max (0.00%) Rows examined : 0 avg, 0 to 0 max (0.00%) Database : explain select * from C_A where C_no='05de'; +----+-------------+---------------------+-------+----------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+-------+----------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | C_A | const | PRIMARY,index1 | PRIMARY | 34 | const | 1 | | +----+-------------+---------------------+-------+----------------+---------+---------+-------+------+-------+ 1 row in set (0.00 sec) 

select语句的第二个慢查询：

 Count : 1 (33.33%) Time : 22 s total, 22 s avg, 22 s to 22 s max (62.86%) Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%) Rows sent : 1 avg, 1 to 1 max (33.33%) Rows examined : 119.75k avg, 119.75k to 119.75k max (88.60%) Database : mysql> explain SELECT sum(amount) FROM C_A -> WHERE C_t > 0 AND act='1' AND us='0' AND pro='0' AND C_Type = 'pre'; +----+-------------+---------------------+------+--------------------------------+----------+---------+-------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+------+--------------------------------+----------+---------+-------+--------+-------------+ | 1 | SIMPLE | C_A | ref | act_ndx,us_ndx,type_index | us_ndx | 2 | const | 318902 | Using where | +----+-------------+---------------------+------+--------------------------------+----------+---------+-------+--------+-------------+ 1 row in set (0.00 sec)