Hi,
I have been using 5.0.32 MySQL on Debian.
Everything worked properly.
DRBD has been added and configure with MySQL and
restarted.
The tables used are InnoDB type and use
auto_increment.
After that during INSERTs and UPDATEs innodb started
to roll back
for table lock on AUTO_INC
How could that problem be fixed and still using MySQL
5.0.32 ?
Could you please help ?
Thanks in advance for your help,
Mariella
Please find below a snapshot of "show innodb status
\G"
INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15
seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 600464, signal
count 592446
Mutex spin waits 0, rounds 160295369, OS waits 262356
RW-shared spins 1496027, OS waits 14850; RW-excl spins
16362229, OS waits 104422
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
*** WE ROLL BACK TRANSACTION (2)
............
------------
TRANSACTIONS
------------
Trx id counter 0 3667761
Purge done for trx's n:o < 0 3666432 undo n:o < 0 0
History list length 449
Total number of lock structs in row lock hash table 4
....
---TRANSACTION 0 3666684, ACTIVE 8 sec, process no
8014, OS thread id 1143544160 setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 1 lock struct(s), heap size 368
MySQL thread id 634392, query id 3774951
NSERT INTO t2 VALUES (''........)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE
GRANTED:
TABLE LOCK table `t2` trx id 0 3667440 lock mode
AUTO-INC waiting
------------------
......
INSERT INTO t1 (...) VALUES (0, 4394621, 4275679,
now(), 11, 3, 1)
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE
GRANTED:
TABLE LOCK table `t1` trx id 0 3666684 lock mode
AUTO-INC waiting....
....
and so on
--------
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: 1; buffer pool: 0
507426 OS file reads, 2589974 OS file writes, 2233731
OS fsyncs
0.00 reads/s, 0 avg bytes/read, 169.99 writes/s,
169.79 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 3297, seg size 3299,
682052 inserts, 727524 merged recs, 168421 merges
Hash table size 35401603, used cells 8213520, node
heap has 13783 buffer(s)
64.06 hash searches/s, 638.36 non-hash searches/s
---
LOG
---
Log sequence number 7 3937023738
Log flushed up to 7 3937023519
Last checkpoint at 7 1938367130
1 pending log writes, 0 pending chkp writes
2173015 log i/o's done, 169.66 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 19080668522; in additional pool
allocated 24182272
Buffer pool size 1048576
Free buffers 120072
Database pages 914721
Modified db pages 208086
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 721174, created 930486, written 1646312
0.00 reads/s, 6.40 creates/s, 4.27 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
8 queries inside InnoDB, 4 queries in queue
1 read views open inside InnoDB
Main thread process no. 8014, id 1140881760, state:
sleeping
Number of rows inserted 57073968, updated 805579,
deleted 0, read 57754523
39.26 inserts/s, 52.93 updates/s, 0.00 deletes/s,
102.06 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
____________________________________________________________________________________
Pinpoint customers who are looking for what you sell.
http://searchmarketing.yahoo.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
