MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:November 21 2004 4:41pm
Subject:Re: InnoDB data files keep growing with innodb_file_per_table
View as plain text  
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"" <Heikki.Tuuri@stripped>
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"" <ivski@stripped>
> 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/mysql?unsub=1
>>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
> http://lists.mysql.com/mysql?unsub=1
> 

Thread
InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski9 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri9 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski10 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableSasha Pachev11 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri21 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski23 Nov