Sp. Raja, you have 3 dangling transactions that have been active for almost 2 hours. They prevent purge from removing those delete-marked rows.
---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 You should commit these transactions. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html ----- Alkuperäinen viesti ----- Lähettäjä: "Sp.Raja" <[EMAIL PROTECTED]> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Lähetetty: Thursday, March 04, 2004 4:38 PM Aihe: Re: Innodb table space getting filled up without any increase in actual rows!! 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 > Innobase Oy > http://www.innodb.com > InnoDB - transactions, row level locking, and foreign keys for MySQL > InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM > tables > > Register now for the 2004 MySQL Users Conference! > http://www.mysql.com/events/uc2004/index.html > > > List:MySQL General Discussion« Previous MessageNext Message » > From:Sp.RajaDate:March 3 2004 8:48am > Subject:Fw: Innodb table space getting filled up without any increase in > actual rows!! > > I missed attaching the trace file! > Sorry !! > > Regards, > Sp.Raja > > > ------------Original Message------------ > > From: "Sp.Raja" <[EMAIL PROTECTED]> > > To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > Date: Wed, Mar-3-2004 1:13 PM > > Subject: Innodb table space getting filled up without any increase in > actual rows!! > > > > Hi List, > > > > My tablespace is getting filled up so quick when no. of transactions > increase without number of rows increasing considerably. > > I have a test client which adds and deletes row dynamically maintaining > number of rows at any instant between 20 and 60. > > > > When I run this test client, after some time the client emits the > following error: > > > > "[MySQL][ODBC 3.51 Driver][mysqld-4.0.15a-debug]The table > 'axactivealarmtbl' is full." > > > > I was confused on seeing this. So ran the test again but this time > monitoring table status. I noticed that Rows, Data_length and Index_length > column increased monotonically and InnoDB free decreased. I was not able to > reason why ? > > > > But when I used select count(*) from <tablename> it consistently gave me > numbers between 20 and 60 > > > > I have attached output of "show table status"(trace.txt) as I observed > taken at increasing time. > > > > Any Pointers/Ideas on this to help me resolve this issue?? > > > > Thanks, > > Sp.Raja > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql?unsub=1com > > > > > > +------------------+--------+------------+------+----------------+---------- > ---+-----------------+--------------+-----------+----------------+-------- -- > ---+-------------+------------+----------------+----------------------+ > | 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 | 467 | 210 | > 98304 | NULL | 98304 | 0 | NULL | NULL > | NULL | NULL | | InnoDB free: 9216 kB | > +------------------+--------+------------+------+----------------+---------- > ---+-----------------+--------------+-----------+----------------+-------- -- > ---+-------------+------------+----------------+----------------------+ > > > +------------------+--------+------------+------+----------------+---------- > ---+-----------------+--------------+-----------+----------------+-------- -- > ---+-------------+------------+----------------+----------------------+ > | 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 | 4946 | 321 | > 1589248 | NULL | 622592 | 0 | NULL | NULL > | NULL | NULL | | InnoDB free: 6144 kB | > +------------------+--------+------------+------+----------------+---------- > ---+-----------------+--------------+-----------+----------------+-------- -- > ---+-------------+------------+----------------+----------------------+ > > > +------------------+--------+------------+------+----------------+---------- > ---+-----------------+--------------+-----------+----------------+-------- -- > ---+-------------+------------+----------------+----------------------+ > | 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 | 5873 | 270 | > 1589248 | NULL | 851968 | 0 | NULL | NULL > | NULL | NULL | | InnoDB free: 5120 kB | > +------------------+--------+------------+------+----------------+---------- > ---+-----------------+--------------+-----------+----------------+-------- -- > ---+-------------+------------+----------------+----------------------+ > > > +------------------+--------+------------+------+----------------+---------- > ---+-----------------+--------------+-----------+----------------+-------- -- > ---+-------------+------------+----------------+----------------------+ > | 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 | 6774 | 234 | > 1589248 | NULL | 1998848 | 0 | NULL | NULL > | NULL | NULL | | InnoDB free: 4096 kB | > +------------------+--------+------------+------+----------------+---------- > ---+-----------------+--------------+-----------+----------------+-------- -- > ---+-------------+------------+----------------+----------------------+ > > > +------------------+--------+------------+------+----------------+---------- > ---+-----------------+--------------+-----------+----------------+-------- -- > ---+-------------+------------+----------------+----------------------+ > | 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 | 6735 | 235 | > 1589248 | NULL | 2031616 | 0 | NULL | NULL > | NULL | NULL | | InnoDB free: 4096 kB | > +------------------+--------+------------+------+----------------+---------- > ---+-----------------+--------------+-----------+----------------+-------- -- > ---+-------------+------------+----------------+----------------------+ > > > +------------------+--------+------------+-------+----------------+--------- > ----+-----------------+--------------+-----------+----------------+------- -- > ----+-------------+------------+----------------+----------------------+ > | 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 | 12117 | 217 | > 2637824 | NULL | 2048000 | 0 | NULL | NULL > | NULL | NULL | | InnoDB free: 3072 kB | > +------------------+--------+------------+-------+----------------+--------- > ----+-----------------+--------------+-----------+----------------+------- -- > ----+-------------+------------+----------------+----------------------+ > > > -- > 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]
