MySQL Bugs: #31592: innodb_autoextend_increment not effective for innodb_file_per

Bug #31592	innodb_autoextend_increment not effective for innodb_file_per_table
Submitted:	15 Oct 2007 2:47	Modified:	13 May 2010 16:12
Reporter:	Adam Dixon	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	6.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When using innodb_file_per_table - individual ibd files do not increment at innodb_autoextend_increment.

How to repeat:
[mysqld]
...
innodb_file_per_table
innodb_autoextend_increment=50M

Fill a table with some data.
# sysbench --test=oltp --mysql-host=localhost --mysql-port=3306 --mysql-socket=/tmp/mysql.sock --mysql-user=root  --mysql-db=test --mysql-table-engine=innodb --mysql-engine-trx=yes    --oltp-read-only  --oltp-table-size=1000000     prepare

Watch it increment, however not at innodb_autoextend_increment increments.
[root@dixon test]# ls -l
total 45116
-rw-rw---- 1 mysql mysql     8632 Oct 15 11:44 sbtest.frm
-rw-rw---- 1 mysql mysql 46137344 Oct 15 11:44 sbtest.ibd
[root@dixon test]# ls -l
total 49216
-rw-rw---- 1 mysql mysql     8632 Oct 15 11:44 sbtest.frm
-rw-rw---- 1 mysql mysql 50331648 Oct 15 11:44 sbtest.ibd
[root@dixon test]# ls -l
total 49216
-rw-rw---- 1 mysql mysql     8632 Oct 15 11:44 sbtest.frm
-rw-rw---- 1 mysql mysql 50331648 Oct 15 11:45 sbtest.ibd

Adam,

indeed, the maximum extension of an .ibd file is only 4 MB.

fsp0fsp.c in 5.0:

#define FSP_FREE_ADD            4       /* this many free extents are added
                                        to the free list from above
                                        FSP_FREE_LIMIT at a time */

...

                        at a time, but for bigger tablespaces more. It is not
                        enough to extend always by one extent, because some
                        extents are frag page extents. */

                        if (size < FSP_EXTENT_SIZE) {
                                /* Let us first extend the file to 64 pages */
                                success = fsp_try_extend_data_file_with_pages(
                                          space, FSP_EXTENT_SIZE - 1,
                                          header, mtr);
                                if (!success) {
                                        new_size = mtr_read_ulint(
                                         header + FSP_SIZE, MLOG_4BYTES, mtr);

                                        *actual_increase = new_size - old_size;

                                        return(FALSE);
                                }

                                size = FSP_EXTENT_SIZE;
                        }

                        if (size < 32 * FSP_EXTENT_SIZE) {
                                size_increase = FSP_EXTENT_SIZE;
                        } else {
                                /* Below in fsp_fill_free_list() we assume
                                that we add at most FSP_FREE_ADD extents at
                                a time */
                                size_increase = FSP_FREE_ADD * FSP_EXTENT_SIZE;
                        }

We could fix this in some version >= 6.0. We cannot change 5.0 or 5.1 behavior any more.

Why would you need autoextend increments bigger than 4 MB?

Regards,

Heikki

Please allow these kind of settings on a per table case. Specifying the initial size and grow rate (and innodb_file_per_table itself) as an option to CREATE/ALTER TABLE allows the administrator to better tune tablespaces as he knows the usage patterns of the table.

Extreme fragmentation is the reason to do this. For an easy automated rule, extending by 4MB until the size is 256MB then by innodb_autoextend_increment would work. The first 256MB rule is just to avoid wasting a lot of space for small tables. The fragmentation problem tends to be most obvious when people are dropping large tables and 256/4 just isn't enough fragments to matter for that case.

Ultimately, controlling it on a table by table basis would be nice. But more interesting than this on a per-table basis would be the ability to say where to put the tablespace and to say that an individual table should be in its own tablespace or in the system tablespace. But more painful are the limitations on moving ibd files around and I'd rather see that before changes in this area, largely because of its huge  benefit in data recovery cases.

The workarounds for this are:

1. Defragment in the filesystem. When you extend many tables the chunks for each end up very fragmented, so the defragmenting can be of benefit for table scans as well as dropping speed. If you don't have a defragmenting program, copying the files somewhere and copying them back will do the job.

2. When dropping, create a filesystem link to the IBD file. Drop the table in MySQL and the OS won't drop the file in the filesystem because the link exists, so the drop will be fast within MySQL. Remove the link and the OS will finish the deleting work in the background.