InnoDB table optimization w/o locking table

Question

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                      |
+----------------------------------------+------------------------+

Please add this to your question: `SHOW VARIABLES LIKE 'innodb%';` — RolandoMySQLDBA, Apr 03 '12 at 20:05

score 29 · Accepted Answer · edited Apr 06 '12 at 15:54

29

You cannot ALTER or OPTIMIZE a table without locking it. However, with the pt-online-schema-change tool from Percona Toolkit (disclaimer: my employer), you can do that, and it works quite well. To OPTIMIZE the table, simply use something like this:

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

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

edited Apr 06 '12 at 15:54

Jeremy

2,870
3
23
31

answered Apr 04 '12 at 12:51

I updated the question for clarity. I will look into pt-online-schema-change, thanks. – Jeremy Apr 04 '12 at 15:58
Reading the documentation, I will not use this tool in my InnoDB tables (all with foreign keys). Check http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html#cmdoption-pt-online-schema-change--alter-foreign-keys-method – Jose Nobile Nov 05 '13 at 15:32
@JoseNobile I use --alter-foreign-keys=drop_swap for big data with alot of columns where the "rebuild_constraints" method will fail. It works. – Apr 12 '16 at 08:26
@Baron I run command 'pt-online-schema-change D=XXX,t=YYY,h=localhost,u=root --alter="ENGINE=InnoDB" --alter-foreign-keys-method="auto" --ask-pass --execute' but got the error '`XXX`.`YYY` was not altered. 2018-08-17T17:23:22 Error copying rows from `XXX`.`YYY` to `XXX`.`_YYY_new`: Redundant argument in printf at /usr/bin/pt-online-schema-change line 3595.' – Maulik patel Aug 17 '18 at 12:01
@Baron Can I use pt-online-schema-change through Java application? – Narendra Chamoli Mar 13 '19 at 06:56

score 17 · Answer 2 · answered Mar 29 '14 at 12:32

17

From MySQL 5.6.17, MySQL supports online optimize of InnoDB tables by default.

answered Mar 29 '14 at 12:32

Aadant

179
1
2

score 2 · Answer 3 · answered Apr 04 '12 at 11:57

Basically, you are doing an OPTIMIZE on the table. For InnoDB tables, OPTIMIZE is mapped to ALTER TABLE. Quoting from the Mysql manual :

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.

When deleting 1/2 a table, an OPTIMIZE after is a really good idea.

I would make a suggestion for improving performance. Seeing that you support in your configuration the new file format BARRACUDA you should use it. Enabling it is really easy, just add in your my.cnf :

innodb_file_format=Barracuda

Restart the server and then alter your table to use the new available ROW_FORMAT = COMPRESSED :

ALTER TABLE x ROW_FORMAT=COMPRESSED;

From personal experience, when using the compressed row format, table size has reduced to half and it a significant positive impact on performance as well.

For more details try going throug :

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/

Are there downsides to using the new file format or row compression? — Jeremy, Apr 04 '12 at 15:59
There should be a minor increase in CPU usage, but all the tests I've seen indicate that there is no downside. The old format "Antelope" is kept as default mainly for compatibility with older MySQL versions. The new Barracuda file format will most likely become the default in the next major MySQL release. — capi, Apr 05 '12 at 12:12
In my experience enabling compressed row format only makes sense if your average row length is big enough (use `SHOW TABLE STATUS LIKE 'table_name'` to check) and you have at least one text field with a long content. — Denis Malinovsky, Jul 20 '15 at 17:02

InnoDB table optimization w/o locking table

3 Answers3

Linked

Related