InnoDB table optimization w/o locking table

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 many after a 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                      |
+----------------------------------------+------------------------+

Skriv et svar