MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:November 11 2004 12:37pm
Subject:Re: InnoDB data files keep growing with innodb_file_per_table
View as plain text  
Ivan,

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.

Anyway, if we get the ibdata files, it should be relatively easy to find out 
what is wrong.

Regards,

Heikki

----- Original Message ----- 
From: "Heikki Tuuri" <Heikki.Tuuri@stripped>
To: <mysql@stripped>
Sent: Thursday, November 11, 2004 2:17 PM
Subject: Re: InnoDB data files keep growing with innodb_file_per_table


> Ivan,
>
> there is something very strange in your tablespace monitor output:
>
> ================================================
> 041111  5:35:51 INNODB TABLESPACE MONITOR OUTPUT
> ================================================
> FILE SPACE INFO: id 0
> size 120832, free limit 120064, free extents 3
> not full frag extents 1: used pages 20, full frag extents 1462
> first seg id not used 0 88882
> SEGMENT id 0 5635 space 0; page 461; res 1 used 1; full ext 0
> fragm pages 1; free extents 0; not full extents 0: pages 0
> SEGMENT id 0 5636 space 0; page 461; res 1 used 1; full ext 0
> fragm pages 1; free extents 0; not full extents 0: pages 0
> SEGMENT id 0 5637 space 0; page 461; res 1 used 1; full ext 0
> fragm pages 1; free extents 0; not full extents 0: pages 0
>
> ...
> the numbers increase by 1 all the way up from 5637 to 88879; res and used 
> values are
> mostly 1,
> sometimes 2 or 3. Rarely (in like, 50 cases) they're pretty big, e.g.
>
> SEGMENT id 0 75994 space 0; page 82136; res 2720 used 2680; full ext 41
> fragm pages 32; free extents 0; not full extents 1: pages 24
> SEGMENT id 0 76006 space 0; page 82136; res 2592 used 2569; full ext 39
> fragm pages 32; free extents 0; not full extents 1: pages 41
> ...
>
> SEGMENT id 0 88879 space 0; page 119802; res 1 used 1; full ext 0
> fragm pages 1; free extents 0; not full extents 0: pages 0
> SEGMENT id 0 88880 space 0; page 119802; res 1 used 1; full ext 0
> fragm pages 1; free extents 0; not full extents 0: pages 0
> SEGMENT id 0 88881 space 0; page 119802; res 1 used 1; full ext 0
> fragm pages 1; free extents 0; not full extents 0: pages 0
> NUMBER of file segments: 82815
> Validating tablespace
> Validation ok
> ---------------------------------------
> END OF INNODB TABLESPACE MONITOR OUTPUT
> =======================================
>
> You say that the segment id's go from 5635 to 88881. But at the tablespace 
> creation, InnoDB allocates several segments for foreign key system tables 
> etc. There should be segments with id's 1, 2, 3, like in the output that I 
> posted yesterday. Looks like the tablespace management data structures are 
> corrupt.
>
> The fact that segments are not being freed at a trx commit or a purge, may 
> be a result of this corruption.
>
> Not a single bug has been found from fsp0fsp.c in 4 years. This might also 
> be corruption caused by the hardware or the OS.
>
> If you can zip your ibdata files into moderate size, can you upload them 
> with ftp to
>
> support.mysql.com    /pub/mysql/secret
>
> My guess is that if you rebuild the tablespace, the leak problem will go 
> away.
>
> Regards,
>
> Heikki
>
> ----- Original Message ----- 
> From: ""Heikki Tuuri"" <Heikki.Tuuri@stripped>
> Newsgroups: mailing.database.myodbc
> Sent: Thursday, November 11, 2004 10:16 AM
> Subject: Re: InnoDB data files keep growing with innodb_file_per_table
>
>
>> Ivan,
>>
>> ----- Original Message ----- 
>> From: ""John B. Ivski"" <ivski@stripped>
>> Newsgroups: mailing.database.myodbc
>> Sent: Thursday, November 11, 2004 4:55 AM
>> Subject: Re: InnoDB data files keep growing with innodb_file_per_table
>>
>>
>>> Heikki,
>>>
>>>>
>>>> the output shows that there are no dangling transactions, and purge is
>>>> not lagging behind.
>>>
>>> Yes, that's what I thought... weird, huh :/
>>>
>>>>
>>>> If you update a secondary index column, that requires purge to clean up
>>>> the index.
>>>
>>> The tables have structure similar to the following:
>> ...
>>>> Then do
>>>> SHOW TABLE STATUS FROM test;
>>>>
>>>> What does it print as the InnoDB free space for the table test.t? That
>>>> is the free space in the system tablespace.
>>>
>>> Unfortunately I won't be able to shut down the server until this 
>>> weekend.
>>> Will let you know the results.
>>>
>>
>> no need to do that any more, because the free space info can also be seen
>> from the output of the innodb_tablespace_monitor.
>>
>> InnoDB is clearly leaking 'segments' in the system tablespace. They are
>> probably undo logs. For some reason, a trx commit or purge fails to free
>> them.
>>
>> SEGMENT id 0 75994 space 0; page 82136; res 2720 used 2680; full ext 41
>> fragm pages 32; free extents 0; not full extents 1: pages 24
>> SEGMENT id 0 76006 space 0; page 82136; res 2592 used 2569; full ext 39
>> fragm pages 32; free extents 0; not full extents 1: pages 41
>>
>> These big segments are about 40 MB in size. Did you do ALTER TABLE or 
>> some
>> other big transaction involving a million rows?
>>
>> ...
>>
>> SEGMENT id 0 88879 space 0; page 119802; res 1 used 1; full ext 0
>> fragm pages 1; free extents 0; not full extents 0: pages 0
>> SEGMENT id 0 88880 space 0; page 119802; res 1 used 1; full ext 0
>> fragm pages 1; free extents 0; not full extents 0: pages 0
>> SEGMENT id 0 88881 space 0; page 119802; res 1 used 1; full ext 0
>> fragm pages 1; free extents 0; not full extents 0: pages 0
>>
>> Most segments seem to be undo logs resulting from small transactions.
>>
>> You said that you tried max_purge_lag, and it caused lengthy waits? What
>> value did you try?
>>
>> I will try to repeat the problem by simulating your database workload.
>>
>> Please use also innodb_table_monitor, and send the output to me.
>>
>>> Thanks for the advice.
>>>
>>> Good luck,
>>> Ivan
>>
>> 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/
>>
>>
>> -- 
>> 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