show innodb status
查询总是很慢,表中大约有20万条记录,有时候select count(*) from table 需要2秒钟,
叶叔帮忙看看有什么问题。
=====================================
070118 14:39:38 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 5 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3580, signal count 3542
Mutex spin waits 2823, rounds 43988, OS waits 621
RW-shared spins 5613, OS waits 2675; RW-excl spins 301, OS waits 265
------------
TRANSACTIONS
------------
Trx id counter 0 410695
Purge done for trx's n:o < 0 410679 undo n:o < 0 0
History list length 22
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 410632, not started, OS thread id 1816
MySQL thread id 11, query id 644522 localhost 127.0.0.1 echunt
---TRANSACTION 0 410693, not started, OS thread id 3836
MySQL thread id 13, query id 644923 localhost 127.0.0.1 echunt
---TRANSACTION 0 409222, not started, OS thread id 884
MySQL thread id 3, query id 644924 localhost 127.0.0.1 root
show innodb status
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (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
2808 OS file reads, 117816 OS file writes, 67116 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 6.80 writes/s, 3.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
15 inserts, 15 merged recs, 3 merges
Hash table size 291199, used cells 5252, node heap has 6 buffer(s)
6.20 hash searches/s, 33.79 non-hash searches/s
---
LOG
---
Log sequence number 0 248216146
Log flushed up to 0 248216146
Last checkpoint at 0 248206649
0 pending log writes, 0 pending chkp writes
54523 log i/o's done, 2.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 75032648; in additional pool allocated 252032
Buffer pool size 4032
Free buffers 1
Database pages 4025
Modified db pages 15
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 2798, created 1489, written 58061
0.00 reads/s, 0.00 creates/s, 4.40 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 3796, state: sleeping
Number of rows inserted 98004, updated 49002, deleted 0, read 14184649
3.20 inserts/s, 1.60 updates/s, 0.00 deletes/s, 7.20 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
beike
周四, 2007/01/18 - 15:17
Permalink
faint了 这么长的东东
faint了
这么长的东东,发表后只能看到一段。
yejr
周四, 2007/01/18 - 16:32
Permalink
帮你重新编辑了一下
帮你重新编辑了一下,innodb表没有类似myisam的表统计信息,因此执行COUNT(*)时会比较慢,建议你增加一个查询条件,例如:
SELECT COUNT(*) FROM table_name WHERE ID>0;
另外,查询慢跟很多因素都有关,多看看本站关于优化方面的文章吧。
MySQL中文网: http://imysql.cn
Google MySQL中文用户群:http://groups.google.com/group/imysql
给你的祝福,要让你招架不住!
beike
周五, 2007/01/19 - 00:28
Permalink
thanks
thanks