Description:
The variable "innodb_autoextend_increment" does not seem to report the proper value. The top value it will display is 1000; I assume that is bytes.
From the manual:
"The increment is 8MB at a time by default. It can be modified by changing the innodb_autoextend_increment system variable."
I have it set in my my.cnf for 128M (assuming it will grow on demand by 128 meg chunks) - however, I noticed that my database wasn't growing that large, it was always idling around 4,096KB free.
How to repeat:
Here is my configuration:
+---------------------------------+-------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------+
| have_innodb | YES |
| innodb_additional_mem_pool_size | 33554432 |
| innodb_autoextend_increment | 1000 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 536870912 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata:1024M:autoextend |
| innodb_data_home_dir | /var/lib/mysql/ |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 0 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 2 |
| innodb_thread_sleep_delay | 10000 |
+---------------------------------+-------------------------+
Easy to repeat:
mysql> show variables like 'innodb_autoextend_increment';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 1000 |
+-----------------------------+-------+
1 row in set (0.00 sec)
mysql> SET GLOBAL innodb_autoextend_increment = 15;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'innodb_autoextend_increment';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 15 |
+-----------------------------+-------+
1 row in set (0.00 sec)
mysql> SET GLOBAL innodb_autoextend_increment = 999999;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'innodb_autoextend_increment';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 1000 |
+-----------------------------+-------+
1 row in set (0.00 sec)
mysql>
obviously if this has no effect in the CLI it has no effect from my.cnf.
Suggested fix:
People suggest using innodb_autoextend_increment=50M in multiple places around the net. I've done that, but obviously can't confirm that it is working (at least from the variable itself)
From what I can tell it does not work properly either (phpMyAdmin for example reports out how many KB free the InnoDB engine/file has) - I definately think there is an issue with the variable not reporting the right status; or perhaps the variable isn't working itself. Unless there is some sort of conflicting mechanism when innodb_file_per_table is used, then there should be something in the docs about it.
