我有 RedHat 5.3 (Tikanga)，带有 Mysql 5.0.86，配置了 RAID 10 HW，
我运行一个查询 Mysql/InnoDB 和 MyIsam 表的应用程序，
查询速度非常快，但 Innodb 表上的一些查询有时会变慢，需要 1-3 秒以上的时间才能运行，这些查询简单且经过优化，
该问题仅在不同时间的随机查询中发生在 innodb 表上。
为什么这只发生在 Innodb 表上？
以下是Innodb状态和一些Mysql变量：
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)
第一个慢查询更新语句我将其更改为选择：
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)
答案1
您可以使用 mysqltuner、tuning-primer 或 MySQLReport 来查找瓶颈。
另外，请检查表和 ibdata1 的大小。ibdata1 过大可能会导致 InnoDB 操作缓慢。
http://www.serveradminblog.com/2011/03/tuning-mysql-performance-howto-part-1/ http://www.serveradminblog.com/2010/12/reducing-ibdata1-howto/