Re: Innodb table space getting filled up without any increase in actual rows!! - Heikki Tuuri - com.mysql.lists.mysql

16 messages in com.mysql.lists.mysqlRe: Innodb table space getting filled...

From	Sent On	Attachments
Sp.Raja	02 Mar 2004 23:41
Sp.Raja	02 Mar 2004 23:47	.txt
Boyd E. Hemphill	03 Mar 2004 08:53
Heikki Tuuri	03 Mar 2004 11:54
Sp.Raja	04 Mar 2004 06:51	.txt
Heikki Tuuri	04 Mar 2004 09:07
Sp.Raja	05 Mar 2004 04:27
Heikki Tuuri	05 Mar 2004 05:53
Sahil Aggarwal	16 Apr 2004 06:27
jeroen clarysse	16 Apr 2004 07:00
Victor Pendleton	16 Apr 2004 07:09
Victor Pendleton	16 Apr 2004 07:10
Paul DuBois	16 Apr 2004 09:12
Sahil Aggarwal	19 Apr 2004 04:48
Boyd E. Hemphill	27 Jul 2004 08:58
Michael Stassen	27 Jul 2004 09:15

Subject:	Re: Innodb table space getting filled up without any increase in actual rows!!
From:	Heikki Tuuri (Heik...@innodb.com)
Date:	03/04/2004 09:07:05 AM
List:	com.mysql.lists.mysql

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

----- Alkuperäinen viesti ----- Lähettäjä: "Sp.Raja" <spr...@isofttech.com> Vastaanottaja: "Heikki Tuuri" <Heik...@innodb.com>; <mys...@lists.mysql.com> 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" <Heik...@innodb.com> To: <mys...@lists.mysql.com> 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

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" <spr...@isofttech.com> To: "'mys...@lists.mysql.com'" <mys...@lists.mysql.com> 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??

+------------------+--------+------------+------+----------------+----------

---+-----------------+--------------+-----------+----------------+--------

+------------------+--------+------------+------+----------------+----------

---+-----------------+--------------+-----------+----------------+--------

+------------------+--------+------------+------+----------------+----------

---+-----------------+--------------+-----------+----------------+--------

-- ---+-------------+------------+----------------+----------------------+