Bug #57611 ibdata file and continuous growing undo logs
Submitted: 20 Oct 2010 22:25 Modified: 10 Dec 2010 0:54
Reporter: Lig NOT_FOUND Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB Plugin storage engine Severity:S1 (Critical)
Version:5.0,5.1,5.5.6-rc OS:Any
Assigned to: Inaam Rana
Triage: Triaged: D1 (Critical) / R2 (Low) / E3 (Medium)

[20 Oct 2010 22:25] Lig NOT_FOUND
Description:
A continually growing undo log in InnoDB, which causes the main tablesapce to grow forever.

baselevel:
blade12:~$ ll ./mysql_installs/5.5.6-rc/mysql-5.5.6-rc-linux2.6-x86_64/data/test/
total 1968128
-rw-rw---- 1 lislerturmelle lislerturmelle      8632 Oct 20 18:53 sbtest1.frm
-rw-rw---- 1 lislerturmelle lislerturmelle 251658240 Oct 20 18:54 sbtest1.ibd
-rw-rw---- 1 lislerturmelle lislerturmelle      8632 Oct 20 18:53 sbtest2.frm
-rw-rw---- 1 lislerturmelle lislerturmelle 251658240 Oct 20 18:54 sbtest2.ibd
-rw-rw---- 1 lislerturmelle lislerturmelle      8632 Oct 20 18:54 sbtest3.frm
-rw-rw---- 1 lislerturmelle lislerturmelle 251658240 Oct 20 18:55 sbtest3.ibd
-rw-rw---- 1 lislerturmelle lislerturmelle      8632 Oct 20 18:54 sbtest4.frm
-rw-rw---- 1 lislerturmelle lislerturmelle 251658240 Oct 20 18:55 sbtest4.ibd
-rw-rw---- 1 lislerturmelle lislerturmelle      8632 Oct 20 18:54 sbtest5.frm
-rw-rw---- 1 lislerturmelle lislerturmelle 251658240 Oct 20 18:56 sbtest5.ibd
-rw-rw---- 1 lislerturmelle lislerturmelle      8632 Oct 20 18:55 sbtest6.frm
-rw-rw---- 1 lislerturmelle lislerturmelle 251658240 Oct 20 18:56 sbtest6.ibd
-rw-rw---- 1 lislerturmelle lislerturmelle      8632 Oct 20 18:55 sbtest7.frm
-rw-rw---- 1 lislerturmelle lislerturmelle 251658240 Oct 20 18:56 sbtest7.ibd
-rw-rw---- 1 lislerturmelle lislerturmelle      8632 Oct 20 18:55 sbtest8.frm
-rw-rw---- 1 lislerturmelle lislerturmelle 251658240 Oct 20 18:57 sbtest8.ibd

blade12:~$ ll ./mysql_installs/5.5.6-rc/mysql-5.5.6-rc-linux2.6-x86_64/data/
total 1043492
-rw-rw---- 1 lislerturmelle lislerturmelle         6 Oct 20 17:15 blade12.pid
-rw-rw---- 1 lislerturmelle lislerturmelle         0 Oct 20 17:16 error-log.err
-rw-rw---- 1 lislerturmelle lislerturmelle      8151 Oct 20 17:16 error-log.err-old
-rw-rw---- 1 lislerturmelle lislerturmelle  18874368 Oct 20 18:57 ibdata1
-rw-rw---- 1 lislerturmelle lislerturmelle 524288000 Oct 20 18:57 ib_logfile0
-rw-rw---- 1 lislerturmelle lislerturmelle 524288000 Oct 20 18:56 ib_logfile1
drwxr-xr-x 2 lislerturmelle lislerturmelle      4096 Oct 19 23:34 mysql
-rw-r--r-- 1 lislerturmelle lislerturmelle         8 Oct 19 23:34 mysql_upgrade_info
drwx------ 2 lislerturmelle lislerturmelle      4096 Oct 19 23:34 performance_schema
drwxr-xr-x 2 lislerturmelle lislerturmelle      4096 Oct 20 18:55 test

After running test:
blade12:~$ ll ./mysql_installs/5.5.6-rc/mysql-5.5.6-rc-linux2.6-x86_64/data/test/total 1968128
-rw-rw---- 1 lislerturmelle lislerturmelle      8632 Oct 20 18:53 sbtest1.frm
-rw-rw---- 1 lislerturmelle lislerturmelle 251658240 Oct 20 19:27 sbtest1.ibd
-rw-rw---- 1 lislerturmelle lislerturmelle      8632 Oct 20 18:53 sbtest2.frm
-rw-rw---- 1 lislerturmelle lislerturmelle 251658240 Oct 20 19:27 sbtest2.ibd
-rw-rw---- 1 lislerturmelle lislerturmelle      8632 Oct 20 18:54 sbtest3.frm
-rw-rw---- 1 lislerturmelle lislerturmelle 251658240 Oct 20 19:27 sbtest3.ibd
-rw-rw---- 1 lislerturmelle lislerturmelle      8632 Oct 20 18:54 sbtest4.frm
-rw-rw---- 1 lislerturmelle lislerturmelle 251658240 Oct 20 19:27 sbtest4.ibd
-rw-rw---- 1 lislerturmelle lislerturmelle      8632 Oct 20 18:54 sbtest5.frm
-rw-rw---- 1 lislerturmelle lislerturmelle 251658240 Oct 20 19:27 sbtest5.ibd
-rw-rw---- 1 lislerturmelle lislerturmelle      8632 Oct 20 18:55 sbtest6.frm
-rw-rw---- 1 lislerturmelle lislerturmelle 251658240 Oct 20 19:27 sbtest6.ibd
-rw-rw---- 1 lislerturmelle lislerturmelle      8632 Oct 20 18:55 sbtest7.frm
-rw-rw---- 1 lislerturmelle lislerturmelle 251658240 Oct 20 19:27 sbtest7.ibd
-rw-rw---- 1 lislerturmelle lislerturmelle      8632 Oct 20 18:55 sbtest8.frm
-rw-rw---- 1 lislerturmelle lislerturmelle 251658240 Oct 20 19:27 sbtest8.ibd

blade12:~$ ll ./mysql_installs/5.5.6-rc/mysql-5.5.6-rc-linux2.6-x86_64/data/total 4011892
-rw-rw---- 1 lislerturmelle lislerturmelle          6 Oct 20 17:15 blade12.pid
-rw-rw---- 1 lislerturmelle lislerturmelle          0 Oct 20 17:16 error-log.err
-rw-rw---- 1 lislerturmelle lislerturmelle       8151 Oct 20 17:16 error-log.err-old
-rw-rw---- 1 lislerturmelle lislerturmelle 3055550464 Oct 20 19:30 ibdata1
-rw-rw---- 1 lislerturmelle lislerturmelle  524288000 Oct 20 19:30 ib_logfile0
-rw-rw---- 1 lislerturmelle lislerturmelle  524288000 Oct 20 19:27 ib_logfile1
drwxr-xr-x 2 lislerturmelle lislerturmelle       4096 Oct 19 23:34 mysql
-rw-r--r-- 1 lislerturmelle lislerturmelle          8 Oct 19 23:34 mysql_upgrade_info
drwx------ 2 lislerturmelle lislerturmelle       4096 Oct 19 23:34 performance_schema
drwxr-xr-x 2 lislerturmelle lislerturmelle       4096 Oct 20 18:55 test

Output from customized innochecksum:
blade12:~/innochecksum$ ./innodbcheck ~/mysql_installs/5.5.6-rc/mysql-5.5.6-rc-linux2.6-x86_64/data/ibdata1
posix_fadvise failed: Success
0 bad checksum
15 FIL_PAGE_INDEX
182680 FIL_PAGE_UNDO_LOG    <-- Note size
2154 FIL_PAGE_INODE
0 FIL_PAGE_IBUF_FREE_LIST
1487 FIL_PAGE_TYPE_ALLOCATED
13 FIL_PAGE_IBUF_BITMAP
132 FIL_PAGE_TYPE_SYS
1 FIL_PAGE_TYPE_TRX_SYS
2 FIL_PAGE_TYPE_FSP_HDR
12 FIL_PAGE_TYPE_XDES
0 FIL_PAGE_TYPE_BLOB
0 FIL_PAGE_TYPE_ZBLOB
0 other
1 max index_id
#pages index_id
<snip>

Attaching InnoDB Status output for review.

How to repeat:
my.cnf 

 [mysqld]
 innodb_buffer_pool_size=5G
 innodb_log_file_size=500M
 innodb_flush_log_at_trx_commit=2
 innodb_doublewrite=1
 innodb_flush_method=O_DIRECT
 innodb_thread_concurrency=0
 innodb_max_dirty_pages_pct=80
 innodb_file_format=barracuda
 innodb_file_per_table
 innodb_io_capacity=1000
 innodb_thread_concurrency=64
 innodb_buffer_pool_instances=1
 max_connections=2000
 table_cache=2000
 key_buffer_size=200M

sysbench - prep to generate tables:

blade12:~$ for i in $( seq 1 8 ); do> sysbench --batch --batch-delay=60 --test=oltp  --oltp-table-size=1000000 \
>  --max-time=1200 --max-requests=0 --mysql-socket=/tmp/mysql.5.5.6-rc.sock --mysql-table-engine=innodb --mysql-db=test \
> --db-ps-mode=disable --mysql-engine-trx=yes --oltp-table-name=sbtest${i} \
> --oltp-skip-trx --oltp-test-mode=simple --oltp-point-select-all-cols \
> --oltp-simple-update --oltp-dist-type=uniform --oltp-range-size=1 \
> --num-threads=1 --seed-rng=1 prepare
> done

Take your base levels

second run:

blade12:~$ for nt in 1 2 4 8 16 32; do> > for x in $( seq 1 8 ); do
> 
> (sysbench --batch --batch-delay=60 --test=oltp --mysql-db=test --oltp-table-size=100000 \
> --max-time=1200 --max-requests=0 --mysql-socket=/tmp/mysql.5.5.6-rc.sock --mysql-table-engine=innodb \
> --db-ps-mode=disable --mysql-engine-trx=yes --oltp-table-name=sbtest${x} \
> --oltp-skip-trx --oltp-test-mode=simple --oltp-point-select-all-cols \
> --oltp-simple-update --oltp-dist-type=uniform --oltp-range-size=1 \
> --num-threads=${nt} --seed-rng=${x} run & )
> 
> done
> 
> done

Look at
1) InnoDB Status - make sure you have a small history list
2) the size of the ibdata file
3) when the history list is short, run innodbchecksum on the ibdata file

Suggested fix:
unknown
[20 Oct 2010 23:44] Calvin Sun
Why is it only growing in the 5.5.6 branch? Have previous versions 5.5.x been
confirmed to not suffer from this issue?

Regards,
-sunny
[21 Oct 2010 2:24] Mark Callaghan
I have one instance of this in production with 5.1.47 + facebook patch. The database files probably were created from 5.0.84 and then an in-place upgrade was done -- no dump&reload.
[21 Oct 2010 3:48] Inaam Rana
Ligaya,

What you have to do to actually verify this is:

1) Run the workload once. Keep an eye on the history length. My guess is that it will keep growing.
2) Once the test is finished keep the server up and you'll see the history length going down
3) When the history length is back to around 100, without stopping the server rerun the workload
4) If you see ibdata file started growing again then we have a problem. If not then it is working as expected

I tried the above on one of our boxes (with vanilla sysbench i.e.: single table) and during the first run the history length grew to 1.4 million with around 65,000 UNDO pages. When I reran the workload after cooling the server down the UNDO pages stayed steady at 65,000.

The config parameters and the sysbench options clearly indicate that we'll have a very cpu intensive test and it is not a surprise if purge is unable to keep up with that. The surprise will be if after purge has caught up we are still unable to use the 65,000 presumably freed UNDO pages.
[21 Oct 2010 19:40] Lig NOT_FOUND
secondary testing using steps provided by Inaam earlier:

Ran workload test twice.  

As expected ibdata file grew on first run with large FIL_PAGE_UNDO_LOG value at end.  History list grew and then shrunk again as purge caught up.  ibdata file size at end - 2283798528

Ran second workload test.  History list again grew to a large value. FIL_PAGE_UNDO_LOG value had *very* minor growth.  ibdata file did not grow at all.

Will upload raw files for review.
[21 Oct 2010 22:33] Mark Callaghan
I don't have much to describe the reproduction. This occurred on a production server and a test server (sysbench). The prod server did an in-place (no dump & reload) upgrade from 5.0.84 to 5.1.47 and with 5.1 uses barracuda and file-per-table -- but no files were created after upgrade. The prod server also gets frequent errors for attempts to exceed 1023 concurrent transactions (cannot find undo slot).

The ibdata file on the prod server appears to have grown at a regular rate over 3+ weeks until it reached ~900GB with ~700GB for undo. History list length on this host goes up to ~10M each night when mysqldump is run to dump a few tables. It returns to a small value afterwards.

Does InnoDB not return blocks allocated to undo back to the free block list?
Why would it grow slowly for 3 weeks? There were no transactions open for that long and I did not see any "lost" XA prepared transactions either.
[22 Oct 2010 16:08] Mark Callaghan
I modified innochecksum to collect more stats on undo blocks. From the server that runs 5.1.47 and has ~700GB of undo space.

file /data/mysql/ibdata1 = 1058650718208 bytes (64614912 pages)...
checking pages in range 0 to 64614911
0       bad checksum
17248722        FIL_PAGE_INDEX
45325414        FIL_PAGE_UNDO_LOG
533186  FIL_PAGE_INODE
10      FIL_PAGE_IBUF_FREE_LIST
1488532 FIL_PAGE_TYPE_ALLOCATED
2769    FIL_PAGE_IBUF_BITMAP
0       FIL_PAGE_TYPE_SYS
0       FIL_PAGE_TYPE_TRX_SYS
0       FIL_PAGE_TYPE_FSP_HDR
2773    FIL_PAGE_TYPE_XDES
13506   FIL_PAGE_TYPE_BLOB
0       FIL_PAGE_TYPE_ZBLOB
0       other
258     max index_id
undo type: 29 insert, 45325383 update, 2 other
undo state: 0 active, 3 cached, 44862541 to_free, 462643 to_purge, 0 prepared, 227 other

Note:
* most of the undo blocks have type TRX_UNDO_UPDATE
* most of the undo blocks have state TRX_UNDO_TO_FREE

Why are they not freed?
[22 Oct 2010 16:15] Mark Callaghan
From other servers that handle a similar workload, they have similar allocation:
* lots of undo blocks
* most undo blocks have type TRX_UNDO_UPDATE
* most undo blocks have state TRX_UNDO_TO_FREE

The server listed previously was idle. The servers listed below are in production.

-rw-rw---- 1 mysql mysql 12G Oct 21 22:41 /data/mysql/ibdata1
0       bad checksum
141     FIL_PAGE_INDEX
769642  FIL_PAGE_UNDO_LOG
8678    FIL_PAGE_INODE
0       FIL_PAGE_IBUF_FREE_LIST
191     FIL_PAGE_TYPE_ALLOCATED
48      FIL_PAGE_IBUF_BITMAP
3       FIL_PAGE_TYPE_SYS
1       FIL_PAGE_TYPE_TRX_SYS
1       FIL_PAGE_TYPE_FSP_HDR
47      FIL_PAGE_TYPE_XDES
0       FIL_PAGE_TYPE_BLOB
0       FIL_PAGE_TYPE_ZBLOB
0       other
1       max index_id
undo type: 363 insert, 769279 update, 0 other
undo state: 0 active, 493 cached, 736773 to_free, 347 to_purge, 10 prepared, 32019 other

-rw-rw---- 1 mysql mysql 124G Oct 21 22:40 /data/mysql/ibdata1
0       bad checksum
138     FIL_PAGE_INDEX
7954539 FIL_PAGE_UNDO_LOG
93583   FIL_PAGE_INODE
0       FIL_PAGE_IBUF_FREE_LIST
41376   FIL_PAGE_TYPE_ALLOCATED
492     FIL_PAGE_IBUF_BITMAP
3       FIL_PAGE_TYPE_SYS
1       FIL_PAGE_TYPE_TRX_SYS
1       FIL_PAGE_TYPE_FSP_HDR
491     FIL_PAGE_TYPE_XDES
0       FIL_PAGE_TYPE_BLOB
0       FIL_PAGE_TYPE_ZBLOB
0       other
1       max index_id
undo type: 23 insert, 7954516 update, 0 other
undo state: 0 active, 23 cached, 7953777 to_free, 739 to_purge, 0 prepared, 0 other

-rw-r--r-- 1 mysql mysql 592G Oct 21 22:40 /data/mysql/ibdata1
0       bad checksum
34882723        FIL_PAGE_INDEX
3312938 FIL_PAGE_UNDO_LOG
36921   FIL_PAGE_INODE
473     FIL_PAGE_IBUF_FREE_LIST
377080  FIL_PAGE_TYPE_ALLOCATED
1209    FIL_PAGE_IBUF_BITMAP
0       FIL_PAGE_TYPE_SYS
0       FIL_PAGE_TYPE_TRX_SYS
0       FIL_PAGE_TYPE_FSP_HDR
1209    FIL_PAGE_TYPE_XDES
166327  FIL_PAGE_TYPE_BLOB
0       FIL_PAGE_TYPE_ZBLOB
0       other
72369   max index_id
undo type: 5829 insert, 3302610 update, 4499 other
undo state: 0 active, 5 cached, 2759890 to_free, 380192 to_purge, 0 prepared, 172851 other

-rw-rw---- 1 mysql mysql 597G Oct 21 22:40 /data/mysql/ibdata1
0       bad checksum
16534528        FIL_PAGE_INDEX
20467834        FIL_PAGE_UNDO_LOG
240578  FIL_PAGE_INODE
28      FIL_PAGE_IBUF_FREE_LIST
402731  FIL_PAGE_TYPE_ALLOCATED
583     FIL_PAGE_IBUF_BITMAP
0       FIL_PAGE_TYPE_SYS
0       FIL_PAGE_TYPE_TRX_SYS
0       FIL_PAGE_TYPE_FSP_HDR
583     FIL_PAGE_TYPE_XDES
1455023 FIL_PAGE_TYPE_BLOB
0       FIL_PAGE_TYPE_ZBLOB
0       other
608806  max index_id
undo type: 291 insert, 20429861 update, 37682 other
undo state: 0 active, 111 cached, 20400059 to_free, 26266 to_purge, 0 prepared, 41398 other

-rw-rw---- 1 mysql mysql 805G Oct 21 22:41 /data/mysql/ibdata1
0       bad checksum
31277512        FIL_PAGE_INDEX
20334639        FIL_PAGE_UNDO_LOG
238664  FIL_PAGE_INODE
172     FIL_PAGE_IBUF_FREE_LIST
530657  FIL_PAGE_TYPE_ALLOCATED
0       FIL_PAGE_IBUF_BITMAP
0       FIL_PAGE_TYPE_SYS
0       FIL_PAGE_TYPE_TRX_SYS
0       FIL_PAGE_TYPE_FSP_HDR
0       FIL_PAGE_TYPE_XDES
326196  FIL_PAGE_TYPE_BLOB
0       FIL_PAGE_TYPE_ZBLOB
0       other
231118  max index_id
undo type: 675 insert, 20321566 update, 12398 other
undo state: 0 active, 30 cached, 19956753 to_free, 332515 to_purge, 0 prepared, 45341 other

-rw-rw---- 1 mysql mysql 798G Oct 21 22:41 /data/mysql/ibdata1
0       bad checksum
29481088        FIL_PAGE_INDEX
21746699        FIL_PAGE_UNDO_LOG
255501  FIL_PAGE_INODE
59      FIL_PAGE_IBUF_FREE_LIST
525471  FIL_PAGE_TYPE_ALLOCATED
0       FIL_PAGE_IBUF_BITMAP
0       FIL_PAGE_TYPE_SYS
0       FIL_PAGE_TYPE_TRX_SYS
0       FIL_PAGE_TYPE_FSP_HDR
0       FIL_PAGE_TYPE_XDES
277134  FIL_PAGE_TYPE_BLOB
0       FIL_PAGE_TYPE_ZBLOB
0       other
3800    max index_id
undo type: 785 insert, 21745694 update, 220 other
undo state: 0 active, 26 cached, 21170534 to_free, 557793 to_purge, 0 prepared, 18346 other
[22 Oct 2010 17:09] Mikhail Izioumtchenko
plugin code: TRX_UNDO_TO_FREE comes from INSERT undo.
It looks convincing that it is freed on commit.
It isn't immediately clear that it is freed on rollback,
unless rollback ends up calling trx_commit_off_kernel().
A simple test case could be tried then with a txn that does insert then rollback.
[22 Oct 2010 17:11] Mark Callaghan
What about the first branch in trx_undo_set_state_at_finish?

        if (undo->size == 1
            && mach_read_from_2(page_hdr + TRX_UNDO_PAGE_FREE)
               < TRX_UNDO_PAGE_REUSE_LIMIT) {

                /* This is a heuristic to avoid the problem of all UNDO
                slots ending up in one of the UNDO lists. Previously if
                the server crashed with all the slots in one of the lists,
                transactions that required the slots of a different type
                would fail for lack of slots. */

                if (UT_LIST_GET_LEN(rseg->update_undo_list) < 500
                    && UT_LIST_GET_LEN(rseg->insert_undo_list) < 500) {

                        state = TRX_UNDO_CACHED;
                } else {
                        state = TRX_UNDO_TO_FREE;
                }

        } else if (undo->type == TRX_UNDO_INSERT) {

                state = TRX_UNDO_TO_FREE;
        } else {
                state = TRX_UNDO_TO_PURGE;
        }
[22 Oct 2010 17:13] Mikhail Izioumtchenko
trx_finish_rollback_off_kernel() does call trx_commit_off_kernel(trx);
however there are a couple of returns before that that are worth looking at.
[22 Oct 2010 17:49] Mikhail Izioumtchenko
let's call it Verified for now since someone needs to look at the code
given the information that Mark provided. 
- it is not entirely clear that we always clean up insert undo on rollback
- in the branch that Mark spotted it is possible that we set state
to TRX_UNDO_TO_FREE even though type is not TRX_UNDO_INSERT.
If so, it's not clear that  this will ever be freed.
[22 Oct 2010 18:09] Mikhail Izioumtchenko
yet another possibility is that TRX_UNDO_TO_FREE is a good a valid state
to which we transition correctly yet when we need a new page for a rollback
segment we fail to use those TRX_UNDO_TO_FREE pages instead
extending the file.
[22 Oct 2010 18:37] Inaam Rana
Michael, Mark,

This does look suspicious:

* During commit we call trx_undo_set_state_at_finish() which, as both of you mentioned, can set the state of an TRX_UNDO_UPDATE type undo log to TRX_UNDO_TO_FREE based on some heuristic.

* trx_undo_update_cleanup() does not free up the pages as they are still needed by purge.

* When purge actually gets around to freeing the pages it does the following:

 556         if ((mach_read_from_2(seg_hdr + TRX_UNDO_STATE) == TRX_UNDO_TO_PURGE)
 557             && (mach_read_from_2(log_hdr + TRX_UNDO_NEXT_LOG) == 0)) {
 558 
 559                 /* We can free the whole log segment */
 560 
 561                 mutex_exit(&(rseg->mutex));
 562                 mtr_commit(&mtr);
 563 
 564                 trx_purge_free_segment(rseg, hdr_addr, n_removed_logs);

It does check for the state TRX_UNDO_TO_PURGE and only then calls trx_purge_free_segment() which calls fseg_free_step().

This can explain:
1) Why pages of type TRX_UNDO_UPDATE are in state TRX_UNDO_TO_FREE
2) Why TRX_UNDO_TO_FREE state pages are not getting freed

I don't have a fair idea what this heuristic is about and why we need to check page type when cleaning during purge. Sunny, is our purge and undo expert. I'll let him comment on this and either deny the above or come up with some solution.
[22 Oct 2010 18:38] Mikhail Izioumtchenko
it would be interesting to dump the pages that actually say what
is free and what is not.
insert undo freeing routine is different from the purge freeing routine
but the logic appear to be essentially the same.
[22 Oct 2010 19:17] Mikhail Izioumtchenko
Mark, I think Inaam has a good point. You could try either of the following
two fixes to see if the problem goes away:

1. replace, where we decide to free or not to free in trx0purge.c

(mach_read_from_2(seg_hdr + TRX_UNDO_STATE) == TRX_UNDO_TO_PURGE)

with 

(mach_read_from_2(seg_hdr + TRX_UNDO_STATE) == TRX_UNDO_TO_PURGE
 || mach_read_from_2(seg_hdr + TRX_UNDO_STATE) == TRX_UNDO_TO_FREE)

or an optimized equivalent

2. in the heuristics in trx_undo_set_state_at_finish()

replace

state = TRX_UNDO_TO_FREE;

with

state = undo->type == TRX_UNDO_INSERT ? TRX_UNDO_TO_FREE : TRX_UNDO_TO_PURGE;
[23 Oct 2010 1:18] Sunny Bains
Inaam,

The heuristic was added for pre 5.5 code to avoid caching all update UNDO
slots in memory. If there was a crash and all UNDO slots were in the say
update UNDO list then during recovery there would be no insert UNDO slots
to use and recovery would fail.

For updates we can't free the segment like we do for inserts at the end of a
commit like trx_undo_insert_cleanup()  because they are needed by MVCC
and purge (as you noted). trx_undo_update_cleanup() should set the state
of the page to TRX_UNDO_TO_PURGE before freeing the memory but not
delete the segment as we do in trx_undo_insert_cleanup().  Therefore this
is a bug that was introduced when the heuristic was added.

In 5.5 code we can delete the heuristics, I think I already did in my branch
and we could back port that to 5.5.
[26 Oct 2010 20:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/121976

3643 Inaam Rana	2010-10-26
      Bug #57611	ibdata file and continuous growing undo logs
      rb://498
      
      Fix handling of update_undo_logs at trx commit. Previously, when
      rseg->update_undo_list grows beyond 500 the update_undo_logs were
      marked with state TRX_UNDO_TO_FREE which should have been
      TRX_UNDO_TO_PURGE.
      
      Approved by: Sunny Bains
[26 Oct 2010 21:07] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/121979

3203 Inaam Rana	2010-10-26
        Bug #57611    ibdata file and continuous growing undo logs
        rb://498
        
        Fix handling of update_undo_logs at trx commit. Previously, when
        rseg->update_undo_list grows beyond 500 the update_undo_logs were
        marked with state TRX_UNDO_TO_FREE which should have been
        TRX_UNDO_TO_PURGE.
        In 5.5 we don't need the heuristic as we support multiple rollback
        segments.
        
        Approved by: Sunny Bains
[27 Oct 2010 14:26] Inaam Rana
Thanks to Mark C. for not only finding this bug but also helping us with debugging.
[28 Oct 2010 11:17] Daniel Fiske
Can a patch for the custom innochecksum be made available, so that we can perform similar tests?
[28 Oct 2010 15:04] Daniel Fiske
Sub question: With the bug fixed, is there or will there be a way to clear the pages that are incorrectly marked?
[28 Oct 2010 19:01] Mark Callaghan
changes to count allocation by type

Attachment: innochecksum.c.orig (, text), 18.78 KiB.

[28 Oct 2010 19:02] Mark Callaghan
I uploaded extra/innochecksum.c with changes to count allocation by type. This is the version from 5.1.50
[30 Oct 2010 4:17] James Day
Thanks Mark.

Daniel, the issue has been around since 5.0.68 was released in August 2008 so for the moment we'll watch to see whether many people report being significantly affected by it. It would be helpful for anyone who is to let us know.

It's not as simple as just removing the TRX_UNDO_FREE flag because some pages may still be needed for purge. Even after that, the shared tablespace size wouldn't be reduced, just the pages made available for reuse.

The workaround for the moment is to get the fix then do the usual mysqladump and reload, or conversion to MyISAM and erasing the InnoDB files, that is required for other ibdata size reduction requirements.
[30 Oct 2010 11:08] Simon Mudd
I've been using MySQL quite heavily and manage several boxes which despite having innodb_file_per_table set also have quite large ibdata1 files.

There's no way to clear this especially on master servers which are hard to take down for any period of time and so I'd like to suggest that any way to fix this would be most appreciated.

Yes we can live with this but it's like warts: they are ugly and you really don't want to have them. If you get rid of them you feel much more comfortable ....
[30 Oct 2010 15:21] Daniel Fiske
@James

Surely it would be fine to reset TRX_UNDO_FREE if the server was cleanly shut down? 

I do appreciate that it wouldn't reduce the size (I'll come to that in a second), but at least freeing up the pages for re-use is better than leaving them in limbo.

Regarding the size of the file, surely if a server was shutdown cleanly then the ibdata file could be rebuilt? 

I pretty much exclusively use file_per_table so on an adhoc basis it is possible to rebuild tables and reclaim space. However the shared data (esp with this bug) can be large and unrecoverable.
[30 Oct 2010 23:11] James Day
Simon, it's normal for the ibdata1 file to be quite large even when using innodb_file_per_table because the undo records can take up a lot of space even when all is working as it should. First thing to do is find out how much this bug is affecting you, if at all, by using the report. Size alone isn't sufficient to know that it is, however big ibdata1 gets, it takes verifying that there a re a significant number of pages set to TRX_UNDO_FREE as well.

Daniel, resetting after a slow shut down (innodb_fast_shutdown = 0, not the 1 default) should be safe. Rebuilding the ibdata1 file would break all of the tablespace id links so it's not completely straightforward.

Best thing to do for now is find out how much it affects you, if at all, and then let us know using the report from Mark's modified innochecksum plus the total size of the whole database, not just ibdata1. Once we have a fair range of reports we can see how much and how widespread the noticeable effects of this are.

An initial thought is that anyone who hasn't also been affected by the 1k open transaction limit in InnoDB isn't likely to be affected by this, since it looks to require at least 500 undo records of one type before the heuristic which did this can be triggered.
[7 Nov 2010 20:39] Roel Van de Paar
Using BLOB's? See bug #57980
[13 Nov 2010 16:05] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:32] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:alexander.nozdrin@oracle.com-20101113152540-gxro4g0v29l27f5x) (pib:21)
[18 Nov 2010 15:54] Bugs System
Pushed into mysql-5.1 5.1.54 (revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (version source revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (merge vers: 5.1.54) (pib:21)
[18 Nov 2010 18:49] Mark Callaghan
I am not sure the bug has been fixed. We had a server under severe load yesterday with frequent attempts to start more than 1023 concurrent transactions. The ibdata1 file grew 17GB in 1 hour. From other monitoring it has grown by 140GB over the past month.

Data from the enhanced innochecksum shows that:
* most pages are FIL_PAGE_UNDO_LOG
* most pages are in state to_free

I confirmed that the mysqld server running there has the patch.

4	FIL_PAGE_INDEX
8906492	FIL_PAGE_UNDO_LOG
104744	FIL_PAGE_INODE
0	FIL_PAGE_IBUF_FREE_LIST
54368	FIL_PAGE_TYPE_ALLOCATED
550	FIL_PAGE_IBUF_BITMAP
0	FIL_PAGE_TYPE_SYS
0	FIL_PAGE_TYPE_TRX_SYS
0	FIL_PAGE_TYPE_FSP_HDR
548	FIL_PAGE_TYPE_XDES
0	FIL_PAGE_TYPE_BLOB
0	FIL_PAGE_TYPE_ZBLOB
0	other
0	max index_id
undo type: 0 insert, 8906492 update, 0 other
undo state: 0 active, 0 cached, 8906492 to_free, 0 to_purge, 0 prepared, 0 other
[21 Nov 2010 22:09] Mikhail Izioumtchenko
Mark, this could be an independent issue, could you file a new bug?
It could be easier to track commits to two different problems,
to two different bugs even though the symptoms were the same.

What could help with the body of ignorance about the problem at hand,
from this bug:

[22 Oct 19:13] Michael Izioumtchenko

trx_finish_rollback_off_kernel() does call trx_commit_off_kernel(trx);
however there are a couple of returns before that that are worth looking at.

[22 Oct 19:49] Michael Izioumtchenko

let's call it Verified for now since someone needs to look at the code
given the information that Mark provided. 
- it is not entirely clear that we always clean up insert undo on rollback

this line of reasoning was never followed up. A note for myself
when I'm back from vacation: try to reproduce a rollback 
rich environment.
[21 Nov 2010 22:26] Mikhail Izioumtchenko
Mark, is the purge lagging behind by any chance?
[29 Nov 2010 21:54] Inaam Rana
This can't be because of purge lag. If we have that then the undo_page::state should not be 'to free'. The fact that all pages are of type 'update' and in state 'to free' is hard to explain after the fix. May be:
1) The server running this doesn't have the fix or
2) The dump restore was not performed on the server after applying the fix to reclaim the pages already lost or
3) There is some error in the enhanced innochecksum or
4) We have screwed the ::type and ::state coupling which seems very strange. The only place where state is set to TRX_UNDO_TO_FREE is in trx_undo_set_state_at_finish():

inaam@bling:~/w/mysql-5.1-innodb/storage/innodb_plugin$ grep -riIn TRX_UNDO_TO_FREE .
./include/trx0undo.h:354:#define        TRX_UNDO_TO_FREE        3       /* insert undo segment can be freed */
./trx/trx0undo.c:1325:  if (state == TRX_UNDO_TO_FREE) {
./trx/trx0undo.c:1834:          state = TRX_UNDO_TO_FREE;
./trx/trx0undo.c:1927:                || undo->state == TRX_UNDO_TO_FREE);
./trx/trx0undo.c:1960:          ut_ad(undo->state == TRX_UNDO_TO_FREE);

1832         } else if (undo->type == TRX_UNDO_INSERT) {
1833 
1834                 state = TRX_UNDO_TO_FREE;
1835         } else {
1836                 state = TRX_UNDO_TO_PURGE;
1837         }
1838 
1839         undo->state = state;
1840 
1841         mlog_write_ulint(seg_hdr + TRX_UNDO_STATE, state, MLOG_2BYTES, mtr);

I'd like to be 100% sure that this is not a false alarm.
[29 Nov 2010 22:00] Mark Callaghan
I am reasonably certain the server has the fix. Dump & reload was not done. Does that need to be done to stop the leak? This server continued to leak with the fix. I assumed the fix would not reclaim previously leaked blocks but would stop the leak from growing.
[30 Nov 2010 2:59] Inaam Rana
Mark,

The patch should fix the leak. I have to look into code to see what happens when a 'to be purged' undo log page is purged.
[10 Dec 2010 0:54] John Russell
Added to change log:

The InnoDB system tablespace could grow continually for a server
under heavy load.
[16 Dec 2010 22:32] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)
[22 Dec 2010 16:59] Mark Callaghan
I am running sysbench tests for 5.1.52+facebook-patch and 5.5.8 unmodified. Binaries don't share datafiles, so the files used by 5.5.8 are only used by it. After running the tests a few times and using file-per-table, the ibdata1 file is 247G for 5.5.8. I am running enhanced innochecksum on it now to understand why it is so big.

Does this bug exist in 5.5.8?
[22 Dec 2010 17:14] Mark Callaghan
On the bright side, my patched 5.1.52 build does not have the leak so I think the patch fixed the problem while my test of 5.1.47 unmodified also reproduces the leak.
[22 Dec 2010 17:31] Inaam Rana
Mark,

The bug reported has been fixed in 5.5.8 as well.

Having said that, there are many changes in 5.5 relating to purge and rbs. If you see unexplained growth in the ibdata file in your tests, it'd make sense if you open a new bug report to nail that down.
[22 Dec 2010 18:02] Mark Callaghan
It was purge lag. Lag is much greater on 5.5.8 at benchmark end than on 5.1.52. I will modify my scripts to wait for it to go to zero after each test and then revisit why lag gets worse on 5.5.8.
[22 Dec 2010 18:47] Mikhail Izioumtchenko
Mark, you could also try a separate purge thread available in 5.5,
--innodb-purge-threads=1.
But the fact that the purge lag became bigger in 5.5 in a real world
application is also very interesting to investigate.
If it is bigger, it could have something to do with multiple RBS in 5.5.
Or it could be because of a bigger throughput in 5.5 so the master/purge thread
can't handle it.
[18 Jan 2011 19:35] James Day
Discussion related to this continues in bug #59291.