Menu
True. OPTIMIZE TABLE locks the table. But there is essentially no reason to "optimize" an InnoDB table.
OPTIMIZE TABLE is performed online for regular and partitioned InnoDB tables. Otherwise, MySQL locks the table during the time OPTIMIZE TABLE is running.
OPTIMIZE TABLE for an InnoDB table is mapped to an ALTER TABLE operation to rebuild the table and update index statistics and free unused space in the clustered index. This operation does not use fast index creation.
Locking in InnoDB InnoDB supports both table-level and row-level locking. Row-level locking offers good performance and throughput for heavy workloads where multiple transactions are reading from and/or writing to the same table simultaneously, but writing different on different rows, without making each other to wait.
I've noticed a significant amount of performance gain if I repack a table (ALTER TABLE foo ENGINE = INNODB) after some period of time, or after heavy volume of INSERT/UPDATE/DELETEs. I don't know if this is because indicies etc are rebuilt, or compacting the table space, or something else?
It strikes me that doing something like ALTER TABLE foo ENGINE = INNODB should be a part of routine table maintenance, however using OPTIMIZE or ALTER locks the table which is unacceptable, is there is a good way to do with with one database server (meaning no failing over to another instance) w/o locking the entire table?
Update: Using Percona 5.5.17-55
Update: SHOW VARIABLES LIKE 'innodb%';
+----------------------------------------+------------------------+
| Variable_name | Value |
+----------------------------------------+------------------------+
| innodb_adaptive_checkpoint | estimate |
| innodb_adaptive_flushing | OFF |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_auto_lru_dump | 120 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_shm_checksum | ON |
| innodb_buffer_pool_shm_key | 0 |
| innodb_buffer_pool_size | 30064771072 |
| innodb_change_buffering | inserts |
| innodb_checkpoint_age_target | 0 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_dict_size_limit | 0 |
| innodb_doublewrite | ON |
| innodb_doublewrite_file | |
| innodb_enable_unsafe_group_commit | 0 |
| innodb_expand_import | 0 |
| innodb_extra_rsegments | 0 |
| innodb_extra_undoslots | OFF |
| innodb_fast_checksum | OFF |
| innodb_fast_recovery | OFF |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antelope |
| innodb_file_format_check | Barracuda |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 0 |
| innodb_flush_log_at_trx_commit_session | 3 |
| innodb_flush_method | O_DIRECT |
| innodb_flush_neighbor_pages | 1 |
| innodb_force_recovery | 0 |
| innodb_ibuf_accel_rate | 100 |
| innodb_ibuf_active_contract | 1 |
| innodb_ibuf_max_size | 15032369152 |
| innodb_io_capacity | 200 |
| innodb_lazy_drop_table | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_block_size | 512 |
| innodb_log_buffer_size | 67108864 |
| innodb_log_file_size | 402653184 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 0 |
| innodb_open_files | 300 |
| innodb_overwrite_relay_log_info | OFF |
| innodb_page_size | 16384 |
| innodb_pass_corrupt_table | 0 |
| innodb_read_ahead | linear |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_recovery_stats | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_show_locks_held | 10 |
| innodb_show_verbose_locks | 0 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_auto_update | 1 |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_stats_sample_pages | 8 |
| innodb_stats_update_need_lock | 1 |
| innodb_strict_mode | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_concurrency_timer_based | OFF |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_purge_thread | 1 |
| innodb_use_sys_malloc | ON |
| innodb_use_sys_stats_table | OFF |
| innodb_version | 1.0.16-12.8 |
| innodb_write_io_threads | 4 |
+----------------------------------------+------------------------+
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With