| Bug #41211 | false val. in `information_schema`.`tables`.`data_free` column for InnoDB tables | ||
|---|---|---|---|
| Submitted: | 3 Dec 2008 19:06 | Modified: | 4 Dec 2008 19:17 |
| Reporter: | Daniel Popiniuc | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 5.1.30 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | data_free, overhead | ||
[3 Dec 2008 19:06]
Daniel Popiniuc
[4 Dec 2008 6:38]
Valeriy Kravchuk
Thank you for a problem report. Please, send the results of: show variables like 'innodb_file_per%'; Why do you think the value is weird?
[4 Dec 2008 12:29]
Daniel Popiniuc
show variables like 'innodb_file_per%'; => +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | OFF | +-----------------------+-------+ 1 row in set (0.01 sec) The value is weird because all InnoDB tables have same overhead value (different than zero) even for empty tables!
[4 Dec 2008 15:15]
Valeriy Kravchuk
With innodb_file_per_table=OFF all InnoDB tables are stored in the same tablespace. DATA_FREE (the number of allocated but unused bytes) is reported for that single tablespace. Read http://dev.mysql.com/doc/refman/5.0/en/innodb-file-space.html: "When you ask for available free space in the tablespace by issuing a SHOW TABLE STATUS statement, InnoDB reports the extents that are definitely free in the tablespace. InnoDB always reserves some extents for cleanup and other internal purposes; these reserved extents are not included in the free space." So, for InnoDB tables free space in tablespace is reported, and as all your tables share the same tablespace, the same value is the result.
[4 Dec 2008 18:28]
Daniel Popiniuc
Apparently I'm not convincing enough: There are two MySQL instances (both clean installs) having same content (1st is 5.0.72, the other is 5.1.30): Running "show variables like 'innodb_file_per%';" gives same result on both => +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | OFF | +-----------------------+-------+ 1 row in set (0.01 sec) But running "SELECT `data_free`, count(*) FROM `information_schema`.`TABLES` WHERE `ENGINE` = 'InnoDB' GROUP BY `data_free`;" gives very different results: +-----------+----------+ | data_free | count(*) | +-----------+----------+ | 0 | 20 | +-----------+----------+ 1 row in set (1.68 sec) ----------- on 5.0.72 +-----------+----------+ | data_free | count(*) | +-----------+----------+ | 139460608 | 20 | +-----------+----------+ 1 row in set (2.25 sec) ---------- on 5.1.30 That's what I'm talking about!
[4 Dec 2008 18:47]
Valeriy Kravchuk
Please, send the results of show variables like 'innodb_data_file%'; from 5.0.72 and 5.1.30.
[4 Dec 2008 19:17]
Daniel Popiniuc
Running "show variables like 'innodb%';" +---------------------------------+--------------------------+ | Variable_name | Value | +---------------------------------+--------------------------+ | innodb_additional_mem_pool_size | 4194304 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 33554432 | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | W:\www\MySQL_data\5.0.x\ | | innodb_adaptive_hash_index | ON | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | 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 | 4194304 | | innodb_log_file_size | 10485760 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | W:\www\MySQL_data\5.0.x\ | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_rollback_on_timeout | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8 | | innodb_thread_sleep_delay | 10000 | +---------------------------------+--------------------------+ 35 rows in set (0.00 sec)-------------------------------------for 5.0.72 +---------------------------------+--------------------------+ | Variable_name | Value | +---------------------------------+--------------------------+ | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 2097152 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_size | 67108864 | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | W:\www\MySQL_data\5.1.x\ | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 2 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | W:\www\MySQL_data\5.1.x\ | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 3000 | | innodb_rollback_on_timeout | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8 | | innodb_thread_sleep_delay | 10000 | +---------------------------------+--------------------------+ 33 rows in set (0.08 sec)-------------------------------------for 5.1.30 Sorry for so much information but I thought I might spare some additional questions about parameters.
[1 Oct 2010 1:56]
Jonathan Nicol
I think the InnoDB free space counter was stored in the Comments column prior to 5.1.21 (http://bugs.mysql.com/bug.php?id=11379). This probably explains the discrepancy you're seeing.
