MySQL Bugs: #870: abnormal innodb data growth after upgrading from 3.23.56 to 4.0.13

Bug #870	abnormal innodb data growth after upgrading from 3.23.56 to 4.0.13
Submitted:	18 Jul 2003 3:41	Modified:	29 Jan 2004 13:08
Reporter:	Marek Les	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.13	OS:	Linux (Linux 2.4.19)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
After upgrading our production server from 3.23.56 to 4.0.13 we encountered an enormous innodb data growth. Where on mysql 3 the innodb backup (after using ibbackup tool) was 500MB, after 1 day of running on version 4 the size is over 2 GB and still growing (lineary).

Also, when issuing "top" I can see this :

31614 mysql     17   0  750M 750M  3776 R    92.3 37.3 207:10 mysqld

I'm not sure if it's ok that there's this so cpu intensive process with so much cpu time, is it possible deadlock?

Also I have this lines in the error log, probably from the time there was no innodb tablespace left:

030717 11:33:02  InnoDB: Error: cannot find the clustered index record
InnoDB: for a secondary index record in table xxx/all_users index username.
InnoDB: Secondary index record RECORD: info bits 0 0: len 7; hex 4164696e612e31; asc Adina.1;; 1: len 4; hex 0016a
976; asc ...v;;.
InnoDB: The table is probably corrupt. Please run CHECK TABLE on it.
InnoDB: You can try to repair the table by dump + drop + reimport.
InnoDB: Send a detailed bug report to mysql@lists.mysql.com.
Error: index username contains 1200 entries, should be 1190

We're considering a downgrade now, unless we find something we can do about this...

How to repeat:
I don't know.

Marek,

use

SHOW TABLE STATUS FROM databasename;

to monitor what table is growing so fast.

Also show us the output of SHOW INNODB STATUS and SHOW PROCESSLIST.

I have not heard of a data growth problem from anyone else. With SHOW INNODB STATUS you see if there are active transactions lingering for hours. Then purge cannot remove delete-marked rows.

You should also do

CREATE TABLE innodb_tablespace_monitor(a INT)TYPE=INNODB;

<wait 60 sec here>

DROP TABLE innodb_tablespace_monitor;

Look from the .err log what it printed.

This output:

030717 11:33:02  InnoDB: Error: cannot find the clustered index record
InnoDB: for a secondary index record in table xxx/all_users index
username.
InnoDB: Secondary index record RECORD: info bits 0 0: len 7; hex
4164696e612e31; asc Adina.1;; 1: len 4; hex 0016a
976; asc ...v;;.
InnoDB: The table is probably corrupt. Please run CHECK TABLE on it.
InnoDB: You can try to repair the table by dump + drop + reimport.
InnoDB: Send a detailed bug report to mysql@lists.mysql.com.
Error: index username contains 1200 entries, should be 1190

cannot result from insufficient disk space. Is this the first time you have experienced table corruption? Did you upgrade the hardware or the OS, too?

Regards,

Heikki

So we 'solved' it. Since the growth was so fast, we decided to take some action and so we changed all tables to MyISAM, deleted all ib* files and then converted the tables back to Innodb. Fortunately, the size went back to its original size. Also, the suspicious cpu-intensive process disappeared. 
There was probably some corruption during the upgrade, even though it wasn't detected by show table status.

Thank you for suggestions.

Hi!

I looked at the output. There are no ACTIVE transactions at all, so no lingering transactions.

Trx id counter 0 2221651826
Purge done for trx's n:o < 0 2174833429 undo n:o < 0 0

Purge is seriously lagging behind. It has still 45 million transactions to purge. That could have caused that strange growth of the tablespace if you have lots of deletes and updates. Since you deleted (?) the tablespace, we cannot know if purge was stuck in a loop, or did it progress. The most common cause for this is to forget a transaction open for days. Then purge cannot proceed. SHOW INNODB STATUS prints the oldest ACTIVE transactions last.

There are very many allocated segments in the tablespace: 93170. I assume you do not have > 10000 tables? Those segments are probably undo logs accumulated to the history list, because purge could not clean them up.

The following bug fixed in 4.0.14 might be behind the reported table corruption, though probably not, because also CHECK TABLE reported corruption.

"
Fixed a bug: InnoDB could complain that it cannot find the clustered index record, or in rare cases return an extraneous row if a rollback, purge, and a SELECT coincided. 
"

If you experience new problems, please add more comments to this bug report.

Regards,

Heikki

Hi!

Changing the status of this bug report to 'No feedback'.

Regards,

Heikki