Hi,
after having noticed occasional load spikes I created an Innodb monitor;
follows an excerpt of the output where I can read that certain
transactions don't start
and that accessing a table (H.albero) with a very low amount of records
(185)
seems to take a lot of time.
What's wrong with these logs? why those transactions don't start?
Thanks,
Nico
=====================================
060529 16:27:40 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 44025032, signal count 43473647
Mutex spin waits 794955636, rounds 1522695948, OS waits 9162350
RW-shared spins 66265323, OS waits 32297148; RW-excl spins 1644124, OS
waits 379573
------------
TRANSACTIONS
------------
Trx id counter 0 678088914
Purge done for trx's n:o < 0 678083128 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 678088912, not started, process no 30735, OS thread id
1210591600
MySQL thread id 6460882, query id 173503655 192.168.65.105 pat_trento0844
---TRANSACTION 0 678088911, not started, process no 30735, OS thread id
1207380336
MySQL thread id 6460881, query id 173503653 192.168.65.105 pat_trento0844
---TRANSACTION 0 678088913, not started, process no 30735, OS thread id
1193732464 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 0
MySQL thread id 6460880, query id 173503656 192.168.65.107 h3r4user
Sorting result
SELECT
H.albero.ordinamento,
H.albero.REF,
H.albero.titolo,
H.albero.categoria,
H.albero.tabella,
H.albero.url,
---TRANSACTION 0 678087842, not started, process no 30735, OS thread id
1175267696
MySQL thread id 6460844, query id 173502395 192.168.65.107 r1minse4
---TRANSACTION 0 678087713, not started, process no 30735, OS thread id
1164028272
MySQL thread id 6460837, query id 173502245 192.168.65.105 pat_trento0844
---TRANSACTION 0 678082967, not started, process no 30735, OS thread id
1204169072
MySQL thread id 6460594, query id 173496341 192.168.65.107 h3r4user
---TRANSACTION 0 678081965, not started, process no 30735, OS thread id
1179281776
MySQL thread id 6460592, query id 173495169 192.168.65.105 pat_trento0844
---TRANSACTION 0 678081879, not started, process no 30735, OS thread id
1150380400
MySQL thread id 6460570, query id 173495043 192.168.65.108 cntdb3
---TRANSACTION 0 678057066, not started, process no 30735, OS thread id
1192126832
MySQL thread id 6459537, query id 173464972 192.168.65.105 pat_trento0844
---TRANSACTION 0 678051381, not started, process no 30735, OS thread id
1195137392
MySQL thread id 6459290, query id 173458071 192.168.65.108 cntdb3
---TRANSACTION 0 678045374, not started, process no 30735, OS thread id
1182894448
MySQL thread id 6459003, query id 173450765 192.168.65.105 prgdb_1
--------
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
99506233 OS file reads, 1810896 OS file writes, 808231 OS fsyncs
266.30 reads/s, 33214 avg bytes/read, 1.75 writes/s, 1.12 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 5, seg size 7,
195942 inserts, 195942 merged recs, 7446 merges
Hash table size 34679, used cells 6972, node heap has 13 buffer(s)
4850.07 hash searches/s, 1614.59 non-hash searches/s
---
LOG
---
Log sequence number 4 1251143669
Log flushed up to 4 1251143659
Last checkpoint at 4 1251140386
0 pending log writes, 0 pending chkp writes
1203908 log i/o's done, 1.12 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 364710794; in additional pool allocated 25165824
Buffer pool size 1024
Free buffers 0
Database pages 1011
Modified db pages 11
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 234480945, created 286926, written 1159843
539.84 reads/s, 0.00 creates/s, 0.62 writes/s
Buffer pool hit rate 995 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 30735, id 1147169136, state: sleeping
Number of rows inserted 16849245, updated 39287, deleted 16105063, read
32922121960
0.06 inserts/s, 0.06 updates/s, 0.00 deletes/s, 135719.89 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
--
Email.it, the professional e-mail, gratis per te: http://www.email.it/f
Sponsor:
Corso base ASP Gratis - Video con trucchi e segreti per imparare a creare il
tuo Sito Web Dinamico con la tecnologia ASP
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5146&d=29-5
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
