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 4:47	Modified:	17 Oct 2007 19:10
Reporter:	Adam Dixon
Status:	Verified
Category:	Server: InnoDB	Severity:	S4 (Feature request)
Version:	6.0	OS:	Any
Assigned to:	Vasil Dimov	Target Version:
Triage:	Triaged: D5 (Feature request)

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

This is a feature request that is going to be implemented in 6.0.
5.0 and 5.1 are frozen for such changes.

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.