MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:John B. Ivski Date:November 11 2004 5:30pm
Subject:Re: InnoDB data files keep growing with innodb_file_per_table
View as plain text  
Heikki,

Heikki Tuuri wrote:
> 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?

Certainly not ALTER TABLE... My program makes nightly backups of the database using SELECT
* INTO 
OUTFILE but they're incremental and use less than 50000 rows/table, each table being
processed in a 
separate transaction. About a fortnight ago I had to do 3-4 complete backups when
debugging the 
backup program, and the largest table has ~1.6M rows, so those big segments may be the
(still not 
freed) leftovers from those complete backups. The viewer also makes SELECT * from tables,
but it is 
rarely used.

99% of queries that involve data modification are inserts that use only one
row/transaction and 
happen at most 4-5 times/second, 1% happen every 4 hours, update about 50000 rows (total)
but never 
change more than 1000 rows/transaction.


> 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 tried 1000000 and 1000. It was worse with the former, but both performed worse than with

max_purge_lag=0.

> 
> I will try to repeat the problem by simulating your database workload.

Please let me know if there's any info I could help you with.

> Please use also innodb_table_monitor, and send the output to me.

Sending it to you directly (it's ~100k and I'm not sure the list allows or needs it ;).

Good luck,
Ivan
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