Heikki,
Thanks for your reply.
As you said I have attached trace collected for SHOW INNODB STATUS.
Please point me the things which are going wrong.
Do you mean I have to use auto-commit transactions?
What changes should I do (or) take care when using MySQL through ODBC to avoid old
transactions ?
To be more specific my client does a lot of inserts/deletes using a ODBC connection
maintaining number of rows to be between 60 and 70.
Thanks,
Sp.Raja
>
> > ------------Original Message------------
> > From: "Heikki Tuuri" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Date: Thu, Mar-4-2004 1:30 AM
> > Subject: Re: Innodb table space getting filled up without any increase in actual
> > rows!!
> >
> > Sp. Raja,
> >
> > please check with
> >
> > SHOW INNODB STATUS\G
> >
> > if purge is still running and removing delete-marked rows. Also check that
> > you do not have old, dangling transactions, which can prevent purge from
> > running, as those old transactions could still see the delete-marked rows.
> >
> > Best regards,
> >
> > Heikki
Regards,
Sp.Raja
mysql> show table status ;
+------------------+--------+------------+------+----------------+-------------+--
---------------+--------------+-----------+----------------+-------------+--------
-----+------------+----------------+----------------------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | M
ax_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_
time | Check_time | Create_options | Comment |
+------------------+--------+------------+------+----------------+-------------+--
---------------+--------------+-----------+----------------+-------------+--------
-----+------------+----------------+----------------------+
| axactivealarmtbl | InnoDB | Dynamic | 503 | 195 | 98304 |
NULL | 98304 | 0 | NULL | NULL | NULL
| NULL | | InnoDB free: 9216 kB |
+------------------+--------+------------+------+----------------+-------------+--
---------------+--------------+-----------+----------------+-------------+--------
-----+------------+----------------+----------------------+
1 row in set (0.05 sec)
mysql> SHOW INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
700102 21:22:53 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 13573, signal count 13569
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 10445, OS waits 5172; RW-excl spins 8575, OS waits 8401
------------
TRANSACTIONS
------------
Trx id counter 0 835529
Purge done for trx's n:o < 0 832240 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 832247, not started, OS thread id 94
MySQL thread id 47, query id 1036187 localhost root
---TRANSACTION 0 832339, not started, OS thread id 114
MySQL thread id 46, query id 1036443 localhost root
---TRANSACTION 0 735412, not started, OS thread id 133
MySQL thread id 38, query id 1041882 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 835528, ACTIVE 0 sec, OS thread id 137
1 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 52, query id 1041881 localhost root
---TRANSACTION 0 832338, ACTIVE 297 sec, OS thread id 65
MySQL thread id 41, query id 1036449 localhost root
Trx read view will not see trx with id >= 0 832339, sees < 0 832214
---TRANSACTION 0 832337, ACTIVE 297 sec, OS thread id 57
MySQL thread id 42, query id 1036436 localhost root
Trx read view will not see trx with id >= 0 832338, sees < 0 832214
---TRANSACTION 0 832214, ACTIVE 325 sec, OS thread id 110
MySQL thread id 51, query id 1036076 localhost root
Trx read view will not see trx with id >= 0 832215, sees < 0 832215
--------
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
1048 OS file reads, 314001 OS file writes, 241185 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 6.00 writes/s, 4.94 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 524, node heap has 2 buffer(s)
1233.49 hash searches/s, 156.94 non-hash searches/s
---
LOG
---
Log sequence number 0 65252795
Log flushed up to 0 65252639
Last checkpoint at 0 65252397
0 pending log writes, 0 pending chkp writes
208561 log i/o's done, 4.50 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 21926048; in additional pool allocated 1048448
Buffer pool size 512
Free buffers 0
Database pages 510
Modified db pages 6
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1037, created 661, written 98813
0.00 reads/s, 0.00 creates/s, 1.83 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 51, state: sleeping
Number of rows inserted 86389, updated 9, deleted 86245, read 5049869
2.44 inserts/s, 0.00 updates/s, 1.94 deletes/s, 151.60 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.03 sec)
mysql> show table status ;
+------------------+--------+------------+-------+----------------+-------------+-
----------------+--------------+-----------+----------------+-------------+-------
------+------------+----------------+----------------------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update
_time | Check_time | Create_options | Comment |
+------------------+--------+------------+-------+----------------+-------------+-
----------------+--------------+-----------+----------------+-------------+-------
------+------------+----------------+----------------------+
| axactivealarmtbl | InnoDB | Dynamic | 10172 | 156 | 1589248 |
NULL | 983040 | 0 | NULL | NULL | NULL
| NULL | | InnoDB free: 5120 kB |
+------------------+--------+------------+-------+----------------+-------------+-
----------------+--------------+-----------+----------------+-------------+-------
------+------------+----------------+----------------------+
1 row in set (0.08 sec)
mysql> SHOW INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
700102 22:58:23 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 54 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 15155, signal count 15150
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 11408, OS waits 5652; RW-excl spins 9701, OS waits 9503
------------
TRANSACTIONS
------------
Trx id counter 0 890944
Purge done for trx's n:o < 0 832240 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 844099, not started, OS thread id 105
MySQL thread id 53, query id 1056363 localhost root
---TRANSACTION 0 832247, not started, OS thread id 94
MySQL thread id 47, query id 1036187 localhost root
---TRANSACTION 0 832339, not started, OS thread id 114
MySQL thread id 46, query id 1036443 localhost root
---TRANSACTION 0 735412, not started, OS thread id 133
MySQL thread id 38, query id 1135777 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 890942, COMMITTED IN MEMORY, OS thread id 137 committing
, undo log entries 1
MySQL thread id 52, query id 1135776 localhost root
COMMIT
---TRANSACTION 0 832338, ACTIVE 6027 sec, OS thread id 65
MySQL thread id 41, query id 1036449 localhost root
Trx read view will not see trx with id >= 0 832339, sees < 0 832214
---TRANSACTION 0 832337, ACTIVE 6027 sec, OS thread id 57
MySQL thread id 42, query id 1036436 localhost root
Trx read view will not see trx with id >= 0 832338, sees < 0 832214
---TRANSACTION 0 832214, ACTIVE 6055 sec, OS thread id 110
MySQL thread id 51, query id 1036076 localhost root
Trx read view will not see trx with id >= 0 832215, sees < 0 832215
--------
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: doing file i/o (write thread)
Pending normal aio reads: 0, aio writes: 2,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1050 OS file reads, 343815 OS file writes, 260756 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 4.31 writes/s, 2.33 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 7913, node heap has 10 buffer(s)
10163.65 hash searches/s, 158.18 non-hash searches/s
---
LOG
---
Log sequence number 0 69278905
Log flushed up to 0 69278647
Last checkpoint at 0 69273861
1 pending log writes, 0 pending chkp writes
226149 log i/o's done, 2.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 21927856; in additional pool allocated 1048448
Buffer pool size 512
Free buffers 0
Database pages 502
Modified db pages 5
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1039, created 892, written 112362
0.00 reads/s, 0.04 creates/s, 2.38 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 51, state: flushing buffer pool pages
Number of rows inserted 94859, updated 9, deleted 94717, read 5580231
1.11 inserts/s, 0.00 updates/s, 0.85 deletes/s, 47.85 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.19 sec)
mysql> select count(*) from axactivealarmtbl;
+----------+
| count(*) |
+----------+
| 38 |
+----------+
1 row in set (0.84 sec)
mysql> show table status ;
+------------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+----------------------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time |
Update_time | Check_time | Create_options | Comment |
+------------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+----------------------+
| axactivealarmtbl | InnoDB | Dynamic | 7559 | 348 | 2637824 |
NULL | 2064384 | 0 | NULL | NULL | NULL |
NULL | | InnoDB free: 3072 kB |
+------------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+----------------------+
1 row in set (0.60 sec)
mysql> show table status ;
+------------------+--------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+----------------------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time |
Update_time | Check_time | Create_options | Comment |
+------------------+--------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+----------------------+
| axactivealarmtbl | InnoDB | Dynamic | 11848 | 134 | 1589248 |
NULL | 2080768 | 0 | NULL | NULL | NULL |
NULL | | InnoDB free: 4096 kB |
+------------------+--------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+----------------+----------------------+
1 row in set (0.22 sec)
mysql> SHOW INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
700102 23:18:13 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 15498, signal count 15493
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 11602, OS waits 5749; RW-excl spins 9952, OS waits 9749
------------
TRANSACTIONS
------------
Trx id counter 0 897948
Purge done for trx's n:o < 0 832240 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 844099, not started, OS thread id 105
MySQL thread id 53, query id 1056363 localhost root
---TRANSACTION 0 832247, not started, OS thread id 94
MySQL thread id 47, query id 1036187 localhost root
---TRANSACTION 0 832339, not started, OS thread id 114
MySQL thread id 46, query id 1036443 localhost root
---TRANSACTION 0 891666, not started, OS thread id 133
MySQL thread id 38, query id 1147674 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 897947, ACTIVE 1 sec, OS thread id 137 fetching rows, thread declared
inside InnoDB 496
mysql tables in use 1, locked 0
MySQL thread id 52, query id 1147673 localhost root Sending data
select * from axactivealarmtbl where axalarmid=-1 order by axsequencenum
Trx read view will not see trx with id >= 0 897948, sees < 0 832214
---TRANSACTION 0 832338, ACTIVE 7217 sec, OS thread id 65
MySQL thread id 41, query id 1036449 localhost root
Trx read view will not see trx with id >= 0 832339, sees < 0 832214
---TRANSACTION 0 832337, ACTIVE 7217 sec, OS thread id 57
MySQL thread id 42, query id 1036436 localhost root
Trx read view will not see trx with id >= 0 832338, sees < 0 832214
---TRANSACTION 0 832214, ACTIVE 7245 sec, OS thread id 110
MySQL thread id 51, query id 1036076 localhost root
Trx read view will not see trx with id >= 0 832215, sees < 0 832215
--------
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
1050 OS file reads, 348179 OS file writes, 263383 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 4.19 writes/s, 2.75 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 8713, node heap has 11 buffer(s)
9947.82 hash searches/s, 146.30 non-hash searches/s
---
LOG
---
Log sequence number 0 69763565
Log flushed up to 0 69763565
Last checkpoint at 0 69759920
0 pending log writes, 0 pending chkp writes
228383 log i/o's done, 2.31 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 21928960; in additional pool allocated 1048448
Buffer pool size 512
Free buffers 0
Database pages 501
Modified db pages 16
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1039, created 919, written 114573
0.00 reads/s, 0.06 creates/s, 1.94 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
Main thread id 51, state: sleeping
Number of rows inserted 95915, updated 9, deleted 95776, read 5648545
0.75 inserts/s, 0.00 updates/s, 1.19 deletes/s, 61.81 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.18 sec)
mysql> select count(*) from axactivealarmtbl;
+----------+
| count(*) |
+----------+
| 46 |
+----------+
1 row in set (0.97 sec)
2Hours test--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
