Heikki,
I recreated the tablespace more than a week ago, set datafile size to 10M:autoextend and its size hasn't changed since the first data import, so the problem was indeed caused by corrupted datafile. It's great that you added more debug logging, because the weirdest thing about the problem was that MySQL didn't report any errors or warnings.
Thanks for looking into this issue!
Good luck, Ivan
Heikki Tuuri wrote:
Ivan,
I have now analyzed your ibdata1 file.
As I suspected, the 'history list' was corrupt. It was broken at about transaction 1 500 000. Current trx id was already 20 million. The history list length was 8.5 million!
Breakpoint 12, trx_purge_rseg_get_next_history_log (rseg=0x402c5268)
at trx0purge.c:644
644 rseg->last_page_no = FIL_NULL;
(gdb) print log_hdr
$12 = (trx_ulogf_t *) 0x482dd7a6 ""
(gdb) x/50b log_hdr
0x482dd7a6: 0x00 0x00 0x00 0x00 0x00 0x18 0x06 0xf4
0x482dd7ae: 0x00 0x00 0x00 0x00 0x00 0x18 0x06 0xf5
0x482dd7b6: 0x00 0x00 0x17 0xd4 0x00 0x00 0x00 0x00
0x482dd7be: 0x8c 0x00 0x00 0x00 0x18 0x01 0x17 0xf6
0x482dd7c6: 0x16 0xc4 0xff 0xff 0xff 0xff 0x00 0x00
0x482dd7ce: 0x00 0x00 0x03 0x0f 0x16 0xe6 0x17 0xf6
0x482dd7d6: 0x1c 0x01
I do not know why the list had broken. The prev field is 0xffffffff, which means FIL_NULL.
I have now added to 4.1.8 some debug code to track this. SHOW INNODB STATUS now prints the history list length. And if the list length exceeds 20 000 when purge thinks it has purged everything, mysqld will print to the .err log a warning like below:
041121 15:40:33 InnoDB: Warning: purge reached the head of the history list,
InnoDB: but its length is still reported as 8546148! Make a detailed bug
InnoDB: report, and post it to bugs.mysql.com
We will see how common the history list corruption is. My tests did not produce any corruption.
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php
Order MySQL technical support from https://order.mysql.com/
----- Original Message ----- From: ""Heikki Tuuri"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Thursday, November 11, 2004 9:59 PM
Subject: Re: InnoDB data files keep growing with innodb_file_per_table
John,
please zip ibdata1, which is 'only' 100 MB, and upload it when you have shut
down mysqld.
I have been simulating your workload, but I only get 25 segments. No leak seen.
Regards,
Heikki
----- Original Message ----- From: ""John B. Ivski"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, November 11, 2004 8:17 PM Subject: Re: InnoDB data files keep growing with innodb_file_per_table
Heikki,
Heikki Tuuri wrote:
hmm... could it be that segments 0 1, 0 2, 0 3, etc. were printed close to the end of the output? The print routine first prints inode pages that are completely used, and after that other inode pages. Since the tablespace validation said the tablespace is ok, I guess the segments really are there.
You're absolutely right, they're there - I must've missed them when looking through the output. They're not at the end but around 0 17000, though.
SEGMENT id 0 16683 space 0; page 11430; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 16684 space 0; page 11430; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 1 space 0; page 2; res 2 used 2; full ext 0 fragm pages 2; free extents 0; not full extents 0: pages 0 SEGMENT id 0 2 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 3 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 4 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 5 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 6 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 7 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 8 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 9 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 10 space 0; page 2; res 4 used 4; full ext 0 fragm pages 4; free extents 0; not full extents 0: pages 0 SEGMENT id 0 11 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 12 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 13 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 14 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 15 space 0; page 2; res 160 used 160; full ext 2 fragm pages 32; free extents 0; not full extents 0: pages 0 SEGMENT id 0 17259 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 17 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 18 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 19 space 0; page 2; res 1 used 1; full ext 0
Anyway, if we get the ibdata files, it should be relatively easy to find
out what is wrong.
I'll delete the whole tablespace this weekend and reimport data from
backup. If it keeps growing
I'll upload the data files (will be easier to do with them occupying much
less than 2GB, too ;)
Good luck, Ivan
-- 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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
