MySQL Lists are EOL. Please join:

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

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