InnoDB表优化没有锁定表

Question

如果我在一段时间后重新包装表（ALTER TABLE foo ENGINE = INNODB），或者在大量INSERT/UPDATE之后重新包装表， /删除操作。我不知道这是因为指示等被重建，还是压缩了表空间或其他东西？

这令我做的事情像 ALTER TABLE FOO ENGINE = INNODB应该是常规表维护的一部分，然而利用优化或者改变锁定这是不可接受的表，是有做了一个好办法一个数据库服务器（意味着没有故障转移到另一个实例）没有锁定整个表？

更新：使用Percona的5.5.17-55

更新：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      | 
+----------------------------------------+------------------------+ 

Answer 1

你不能改变或优化表，而将其锁定。但是，使用Percona Toolkit的pt-online-schema-change工具（免责声明：我的雇主），您可以做到这一点，并且工作得非常好。优化表，只需使用这样的事情：

pt-online-schema-change <options> --alter='ENGINE=InnoDB' 

http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

Answer 2

基本上，你正在做一个OPTIMIZE在桌子上。对于InnoDB表，OPTIMIZE映射到ALTER TABLE。从MySQL手册引用：

对于InnoDB表，OPTIMIZE TABLE被映射到ALTER TABLE，它重建表来更新索引统计信息并释放聚集索引中未使用的空间。

当删除1/2表时，OPTIMIZE后是一个真正的好主意。

我会提出一个改善性能的建议。看到您在配置中支持新文件格式BARRACUDA，您应该使用它。启用它是很容易，只需添加在您的my.cnf：

innodb_file​_format=Barracuda 

重新启动服务器，然后改变你的表来使用新的可用ROW_FORMAT = COMPRESSED：使用压缩行时

ALTER TABLE x ROW_FORMAT=COMPRESSED; 

从个人的经验，格式，表格大小减少到一半，对性能也有显着的积极影响。

欲了解更多详情请尝试通过量：

http://dev.mysql.com/doc/refman/5.5/en/innodb-compression-usage.html

http://www.mysqlperformanceblog.com/2008/04/23/testing-innodb-barracuda-format-with-compression/

Answer 3

从MySQL 5.6.17，MySQL的默认支持InnoDB表的在线优化。