Ivan,
----- Original Message ----- From: ""John B. Ivski"" <[EMAIL PROTECTED]>
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/[EMAIL PROTECTED]
