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"" <[EMAIL PROTECTED]>
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"" <[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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
