MySQL Forums
Forum List  »  InnoDB

innodb table taking huge time for insert and update
Posted by: Raghu reddy
Date: March 05, 2014 12:08AM

Hi all,
when i insert/update/ delete on innodb table its taking huge time and getting time out exceed.I have checked the query by explain and the query is accessing indexes.However if restart mysql server and do the same operations then its working fine. Dont know what is happening inside.


=====================================
2014-03-05 11:06:27 1770 INNODB MONITOR OUTPUT
=====================================
Per SECOND averages calculated FROM the LAST 19 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 12 srv_active, 0 srv_shutdown, 67 srv_idle
srv_master_thread LOG FLUSH AND writes: 79
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation COUNT 15
OS WAIT ARRAY INFO: signal COUNT 17
MUTEX spin waits 3, rounds 61, OS waits 2
RW-shared spins 13, rounds 390, OS waits 13
RW-excl spins 0, rounds 21, OS waits 0
Spin rounds per WAIT: 20.33 MUTEX, 30.00 RW-shared, 21.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 1776075
PURGE done FOR trx's n:o < 1776067 undo n:o < 0 state: running but idle
History list length 534
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 8, OS thread handle 0x1770, query id 2982 localhost ::1 root init
show engine innodb status
---TRANSACTION 1776074, not started
MySQL thread id 7, OS thread handle 0xec8, query id 2977 172.20.38.25 ofrs_dev cleaning up
---TRANSACTION 1776056, not started
MySQL thread id 6, OS thread handle 0x11a4, query id 2350 172.20.38.25 ofrs_dev cleaning up
---TRANSACTION 1776035, not started
MySQL thread id 5, OS thread handle 0xfc8, query id 2225 172.20.38.25 ofrs_dev cleaning up
---TRANSACTION 1775986, not started
MySQL thread id 4, OS thread handle 0x1a0, query id 880 172.20.38.25 ofrs_dev cleaning up
---TRANSACTION 1775978, not started
MySQL thread id 3, OS thread handle 0xda4, query id 823 172.20.38.25 ofrs_dev cleaning up
---TRANSACTION 1775964, not started
MySQL thread id 2, OS thread handle 0x640, query id 656 172.20.38.25 ofrs_dev cleaning up
---TRANSACTION 1775888, not started
MySQL thread id 1, OS thread handle 0x860, query id 75 172.20.38.25 ofrs_dev cleaning up
--------
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 (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
791 OS file reads, 406 OS file writes, 212 OS fsyncs
3.53 reads/s, 16384 avg bytes/read, 14.63 writes/s, 7.37 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 12749767, node heap has 8 buffer(s)
3.84 hash searches/s, 84.47 non-hash searches/s
---
LOG
---
Log sequence number 8088140605
Log flushed up to 8088140605
Pages flushed up to 8088140605
Last checkpoint at 8088140605
0 pending log writes, 0 pending chkp writes
84 log i/o's done, 2.89 LOG i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 6596591616; in additional pool allocated 0
Dictionary memory allocated 879030
Buffer pool size 393210
Free buffers 392388
Database pages 814
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 732, created 82, written 241
2.84 reads/s, 3.68 creates/s, 8.58 writes/s
Buffer pool hit rate 990 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 814, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 43690
Free buffers 43599
Database pages 90
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 82, created 8, written 39
0.32 reads/s, 0.42 creates/s, 1.26 writes/s
Buffer pool hit rate 994 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 90, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 43690
Free buffers 43637
Database pages 52
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 44, created 8, written 9
0.11 reads/s, 0.42 creates/s, 0.42 writes/s
Buffer pool hit rate 979 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 52, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 43690
Free buffers 43660
Database pages 29
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 21, created 8, written 8
0.16 reads/s, 0.42 creates/s, 0.42 writes/s
Buffer pool hit rate 997 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 29, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 43690
Free buffers 43575
Database pages 114
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 100, created 14, written 29
0.21 reads/s, 0.53 creates/s, 0.53 writes/s
Buffer pool hit rate 987 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 114, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 43690
Free buffers 43513
Database pages 176
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 168, created 8, written 49
1.32 reads/s, 0.42 creates/s, 2.26 writes/s
Buffer pool hit rate 986 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 176, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 43690
Free buffers 43616
Database pages 73
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 65, created 8, written 26
0.26 reads/s, 0.42 creates/s, 1.11 writes/s
Buffer pool hit rate 980 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 73, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 43690
Free buffers 43603
Database pages 86
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 78, created 8, written 26
0.21 reads/s, 0.42 creates/s, 1.05 writes/s
Buffer pool hit rate 991 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 86, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 43690
Free buffers 43612
Database pages 77
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 65, created 12, written 24
0.26 reads/s, 0.42 creates/s, 1.00 writes/s
Buffer pool hit rate 978 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 77, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 8
Buffer pool size 43690
Free buffers 43573
Database pages 117
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 109, created 8, written 31
0.00 reads/s, 0.21 creates/s, 0.53 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 117, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread id 5020, state: sleeping
Number of rows inserted 54, updated 6, deleted 3, read 7438
1.95 inserts/s, 0.16 updates/s, 0.16 deletes/s, 30.16 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Please give me a solution for not to restart for everytime.
Thanks in advance.

regard's
Raghu

Options: ReplyQuote


Subject
Views
Written By
Posted
innodb table taking huge time for insert and update
3488
March 05, 2014 12:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.