Alex,
> ---TRANSACTION 0 125987852, ACTIVE 1217449 sec, OS thread id 1712
you have transactions which have been active 1.2 million seconds, that is,
15 days!
You should commit those transactions.
You can also use SHOW PROCESSLIST to show those open sessions.
Regards,
Heikki
----- Original Message -----
From: <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Thursday, July 03, 2003 10:48 PM
Subject: Re: Unexpected empty table performance problem with MySQL and
InnoDB
> 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
>
030312d41726368697665206d616e61676572202020202020202020202020202020202020202
0202
>
0202020202020202020202020202020202020202020202020202020202020202020202020202
0202
> 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
>
382028474d54292d303030312d41726368697665206d616e6167657220202020202020202020
2020
>
2020202020202020202020202020202020202020202020202020202020202020202020202020
2020
> 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]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
