title: InnoDB table optimization w/o locking table
body: <p>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?</p>
  
  <p>It strikes me that doing something <em>like</em> 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?</p>
  
  <p>Update: Using Percona 5.5.17-55</p>
  
  <p>Update: SHOW VARIABLES LIKE 'innodb%';</p>
  
  <pre><code>+----------------------------------------+------------------------+
  | 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                      |
  +----------------------------------------+------------------------+
  </code></pre>