I have redHat 5.3 (Tikanga) with Mysql 5.0.86 configued with RIAD 10 HW,
I run an application inquiries from Mysql/InnoDB and MyIsam tables,
the queries are super fast,but some quires on Innodb tables sometime slow down and took more than 1-3 seconds to run and these queries are simple and optimized,
this problem occurred just on innodb tables in different time with random queries.
Why is this happening only to Innodb tables?
the below is the Innodb status and some Mysql variables:
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
can any one help me ?
