Hello Heikki,
> why you do not look with SHOW INNODB STATUS if there are dangling
> transactions which could still see the delete-marked rows? Purge cannot
> remove them then.
This is what I see:
mysql> show table status like 'sccchangelog';
+--------------+--------+------------+--------+----------------+-------------+--
---------------+--------------+-----------+----------------+-------------+------
-------+------------+----------------+-------------------------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | M
ax_data_length | Index_length | Data_free | Auto_increment | Create_time | Updat
e_time | Check_time | Create_options | Comment |
+--------------+--------+------------+--------+----------------+-------------+--
---------------+--------------+-----------+----------------+-------------+------
-------+------------+----------------+-------------------------+
| sccchangelog | InnoDB | Fixed | 122279 | 754 | 92274688 |
NULL | 0 | 0 | NULL | NULL | NULL
| NULL | | InnoDB free: 4132864 kB |
+--------------+--------+------------+--------+----------------+-------------+--
---------------+--------------+-----------+----------------+-------------+------
-------+------------+----------------+-------------------------+
1 row in set (0.03 sec)
Sorry about the misformatted output, but you see that this query at least thinks that
there are still 122K rows in that table. Then I do:
mysql> select count(*) from sccchangelog;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (1.80 sec)
Which shows no rows, which is correct. Then I see the following after doing show
innodb status:
=====================================
030703 12:35:38 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 9 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 221001038, signal count 154999565
Mutex spin waits 156240618, rounds 3124266815, OS waits 28324
RW-shared spins 521739352, OS waits 220819701; RW-excl spins 155821, OS waits 15
2983
---------------------------------------------------------------
LATEST UNIQUE KEY ERROR (is masked in REPLACE or INSERT IGNORE)
---------------------------------------------------------------
030619 13:16:46 Transaction:
TRANSACTION 0 126991236, ACTIVE 0 sec, OS thread id 1520 inserting, thread decla
red inside InnoDB 0
2 lock struct(s), heap size 320
MySQL thread id 82, query id 15457410 leroy.con.securecomputing.com 172.26.101.1
07 root update
INSERT INTO SccChangeLog (sccModificationDateTime,sccActionType,sccEntryId,sccT
ableId,sccNextDone,sccPrevDone,sccSequence) VALUES ('2003/06/19 20:16:
Unique key constraint fails for table swpadata/sccchangelog.
Trying to add in index PRIMARY (1 fields unique) tuple:
0: len 128; hex 323030332f30362f31392032303a31363a33372e3634382028474d54292d303
030312d41726368697665206d616e616765722020202020202020202020202020202020202020202
02020202020202020202020202020202020202020202020202020202020202020202020202020202
020202020202020202020202020202020; asc 2003/06/19 20:16:37.648 (GMT)-0001-Archiv
e manager
;; 1: len 6; hex 00000791bb84; asc ...æ╗ä;; 2: len 7; hex 80000003040084;
asc Ç.....ä;; 3:
But there is already a record:
RECORD: info bits 0 0: len 128; hex 323030332f30362f31392032303a31363a33372e3634
382028474d54292d303030312d41726368697665206d616e61676572202020202020202020202020
20202020202020202020202020202020202020202020202020202020202020202020202020202020
2020202020202020202020202020202020202020202020202020; asc 2003/06/19 20:16:37.64
8 (GMT)-0001-Archive manager
;; 1: len 6; hex 00000791bb83; asc ...æ╗â;; 2: len 7;
hex 80000003040084; asc Ç.....ä;; 3:
------------
TRANSACTIONS
------------
Trx id counter 0 127621251
Purge done for trx's n:o < 0 125987858 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 127621250, not started, OS thread id 1528
MySQL thread id 86, query id 16134301 localhost 127.0.0.1 root
---TRANSACTION 0 127621247, not started, OS thread id 1820
MySQL thread id 90, query id 16134298 localhost 127.0.0.1 root
---TRANSACTION 0 127621242, not started, OS thread id 1228
MySQL thread id 89, query id 16134289 localhost 127.0.0.1 root
---TRANSACTION 0 127621248, not started, OS thread id 1416
MySQL thread id 88, query id 16134297 localhost 127.0.0.1 root
---TRANSACTION 0 127621206, not started, OS thread id 1232
MySQL thread id 2, query id 16134302 localhost 127.0.0.1 root
show innodb status
---TRANSACTION 0 126995019, ACTIVE 1207002 sec, OS thread id 1520
MySQL thread id 82, query id 15462643 leroy.con.securecomputing.com 172.26.101.1
07 root
Trx read view will not see trx with id >= 0 126995020, sees < 0 125987852
---TRANSACTION 0 126992533, ACTIVE 1207125 sec, OS thread id 1320
MySQL thread id 83, query id 15459677 leroy.con.securecomputing.com 172.26.101.1
07 root
Trx read view will not see trx with id >= 0 126992534, sees < 0 125987852
---TRANSACTION 0 125987854, ACTIVE 1217427 sec, OS thread id 820
MySQL thread id 87, query id 13724870 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 0 125987855, sees < 0 125987850
---TRANSACTION 0 125987852, ACTIVE 1217449 sec, OS thread id 1712
MySQL thread id 84, query id 13724857 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 0 125987853, sees < 0 125987850
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio
I/O thread 1 state: wait Windows aio
I/O thread 2 state: wait Windows aio
I/O thread 3 state: wait Windows aio
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
638734499 OS file reads, 4811707 OS file writes, 4414743 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 for space 0: size 1, free list len 7, seg size 9,
104565 inserts, 104565 merged recs, 5893 merges
Hash table size 69257, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 3 4094423293
Log flushed up to 3 4094423293
Last checkpoint at 3 4094423293
0 pending log writes, 0 pending chkp writes
4377481 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 32935741; in additional pool allocated 515968
Buffer pool size 1024
Free buffers 0
Database pages 1023
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 638734371, created 95546, written 413925
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
Main thread id 1080, state: waiting for server activity
Number of rows inserted 1021589, updated 1146622, deleted 1024241, read 25023053
7
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
Unfortunately, I'm not 100% clear how to interpret the TRANSACTIONS section. I've
read the manual, but am still confused. Is it telling me that there are dangling
transactions? If so, can I tell what they are? (I thought that '0 quieries inside
InnoDB' meant that there weren't?)
I've let the DB sit in this state for days and it's still not purging. There's one
process connected to it that does periodic queries about once a minute, plus the
'mysql' client, but that's it. Could you shed some light on what's going on?
Thank you in advance,
Alex
__________________________________________________________________
McAfee VirusScan Online from the Netscape Network.
Comprehensive protection for your entire computer. Get your free trial today!
http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397
Get AOL Instant Messenger 5.1 free of charge. Download Now!
http://aim.aol.com/aimnew/Aim/register.adp?promo=380455
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
