MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:November 11 2004 12:17pm
Subject:Re: InnoDB data files keep growing with innodb_file_per_table
View as plain text  
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