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