XtraDB存储引擎

XtraDB是由Percona开发的一款MySQL数据库的高性能存储引擎，其目的是用来代替InnoDB存储引擎，可用于需要更高性能的环境。XtraDB可以看作是InnoDB存储引擎的增强版本，它在InnoDB上进行了大量的修改和patched，它完全兼容InnoDB，且提供了很多InnoDB不具备的有用的功能。

例如：在多核CPU上面的性能和伸缩性要更好；对于内存的分配和使用也要更好；也解除了InnoDB的很多限制；提供了比InnoDB更多的配置和性能监控参数。

Percona自己对新引擎的描述：“Percona XtraDB includes all of InnoDB's robust, reliable ACID-compliant design and advanced MVCC architecture, and builds on that solid foundation with more features, more tunability, more metrics, and more scalability. In particular, it is designed to scale better on many cores, to use memory more efficiently, and to be more convenient and useful. The new features are especially designed to alleviate some of InnoDB's limitations.”

对于高负载的MySQL应用来说，如果不需要MySQL官方技术支持的话，完全可以使用XtraDB来代替InnoDB存储引擎。此外，Percona还提供了打了补丁的MySQL版本，这些补丁对MySQL很多方面进行了改进，性能提高，增加更多监控参数等等。据说JavaEye的数据库就是使用了Percona的版本。

当前XtraDB的最新版本是1.0.4-8(release-8)，2009.10.13发布：

Ÿ   该release基于InnoDB plugin 1.0.4版本

Ÿ   以MySQL 5.1.39作为base release

修复以下bug：

Ÿ   Bugs#413858: Crash from failed assertion in dict0dict.c

Ÿ   Bug#417751: XtraDB crashes on startup on windows

Ÿ   fix-import-extern-pages

Ÿ   Number of mysql-tests are fixed

XtraDB的插件：

mysql>show plugins;

+--------------------------------+--------+--------------------+---------+---------+

| Name                           | Status | Type               | Library | License |

+--------------------------------+--------+--------------------+---------+---------+

| binlog                         | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

| CSV                            | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

| MEMORY                         | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

| InnoDB                         | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

| INNODB_RSEG                    | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_BUFFER_POOL_PAGES       | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_BUFFER_POOL_PAGES_BLOB  | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_TRX                     | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_LOCKS                   | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_LOCK_WAITS              | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_CMP                     | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_CMP_RESET               | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_CMPMEM                  | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_CMPMEM_RESET            | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_TABLE_STATS             | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_INDEX_STATS             | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| XTRADB_ENHANCEMENTS            | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| MyISAM                         | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

| MRG_MYISAM                     | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

+--------------------------------+--------+--------------------+---------+---------+

20 rows in set (0.00 sec)

XtraDB的变量：

mysql> show global variables like '%innodb%';

+---------------------------------------+-----------------------------------+

| Variable_name                         | Value                             |

+---------------------------------------+-----------------------------------+

| have_innodb                           | YES                               |

| ignore_builtin_innodb                 | OFF                               |

| innodb_adaptive_checkpoint            | none                              |

| innodb_adaptive_flushing              | ON                                |

| innodb_adaptive_hash_index            | ON                                |

| innodb_additional_mem_pool_size       | 20971520                          |

| innodb_autoextend_increment           | 8                                 |

| innodb_autoinc_lock_mode              | 1                                 |

| innodb_buffer_pool_size               | 3221225472                        |

| innodb_change_buffering               | inserts                           |

| 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_enable_unsafe_group_commit     | 0                                 |

| innodb_expand_import                  | 0                                 |

| innodb_extra_rsegments                | 0                                 |

| innodb_extra_undoslots                | OFF                               |

| innodb_fast_recovery                  | OFF                               |

| innodb_fast_shutdown                  | 1                                 |

| innodb_file_format                    | Antelope                          |

| innodb_file_format_check              | Antelope                          |

| innodb_file_io_threads                | 4                                 |

| innodb_file_per_table                 | ON                                |

| innodb_flush_log_at_trx_commit        | 2                                 |

| innodb_flush_method                   | O_DIRECT                          |

| innodb_flush_neighbor_pages           | 1                                 |

| innodb_force_recovery                 | 0                                 |

| innodb_ibuf_accel_rate                | 100                               |

| innodb_ibuf_active_contract           | 0                                 |

| innodb_ibuf_max_size                  | 1610596352                        |

| innodb_io_capacity                    | 200                               |

| innodb_lock_wait_timeout              | 120                               |

| innodb_locks_unsafe_for_binlog        | OFF                               |

| innodb_log_buffer_size                | 8388608                           |

| innodb_log_file_size                  | 268435456                         |

| innodb_log_files_in_group             | 3                                 |

| innodb_log_group_home_dir             |                                   |

| innodb_max_dirty_pages_pct            | 90                                |

| innodb_max_purge_lag                  | 0                                 |

| innodb_mirrored_log_groups            | 1                                 |

| innodb_open_files                     | 300                               |

| innodb_overwrite_relay_log_info       | OFF                               |

| innodb_read_ahead                     | linear                            |

| innodb_read_ahead_threshold           | 56                                |

| innodb_read_io_threads                | 4                                 |

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

| innodb_use_sys_malloc                 | ON                                |

| innodb_version                        | 1.0.4-7                           |

| innodb_write_io_threads               | 4                                 |

+---------------------------------------+-----------------------------------+

70 rows in set (0.00 sec)

InnoDB Plugin的变量：
mysql>show global variables like '%innodb%';

+————————————————-+————————————+

| Variable_name                   | Value                  |

+————————————————-+————————————+

| have_innodb                     | YES                    |

| ignore_builtin_innodb           | OFF                    |

| innodb_adaptive_flushing        | ON                     |

| innodb_adaptive_hash_index      | ON                     |

| innodb_additional_mem_pool_size | 8388608                |

| innodb_autoextend_increment     | 8                      |

| innodb_autoinc_lock_mode        | 1                      |

| innodb_buffer_pool_size         | 134217728              |

| innodb_change_buffering         | inserts                |

| innodb_checksums                | ON                     |

| innodb_commit_concurrency       | 0                      |

| innodb_concurrency_tickets      | 500                    |

| innodb_data_file_path           | ibdata1:10M:autoextend |

| innodb_data_home_dir            |                        |

| innodb_doublewrite              | ON                     |

| innodb_fast_shutdown            | 1                      |

| innodb_file_format              | Antelope               |

| innodb_file_format_check        | Antelope               |

| innodb_file_io_threads          | 4                      |

| innodb_file_per_table           | OFF                    |

| innodb_flush_log_at_trx_commit  | 1                      |

| innodb_flush_method             |                        |

| innodb_force_recovery           | 0                      |

| innodb_io_capacity              | 200                    |

| innodb_lock_wait_timeout        | 50                     |

| innodb_locks_unsafe_for_binlog  | OFF                    |

| innodb_log_buffer_size          | 8388608                |

| innodb_log_file_size            | 5242880                |

| 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_open_files               | 300                    |

| innodb_read_ahead_threshold     | 56                     |

| innodb_read_io_threads          | 4                      |

| innodb_replication_delay        | 0                      |

| innodb_rollback_on_timeout      | OFF                    |

| innodb_spin_wait_delay          | 6                      |

| innodb_stats_on_metadata        | ON                     |

| innodb_stats_sample_pages       | 8                      |

| innodb_strict_mode              | OFF                    |

| innodb_support_xa               | ON                     |

| innodb_sync_spin_loops          | 30                     |

| innodb_table_locks              | ON                     |

| innodb_thread_concurrency       | 0                      |

| innodb_thread_sleep_delay       | 10000                  |

| innodb_use_sys_malloc           | ON                     |

| innodb_version                  | 1.0.4                  |

| innodb_write_io_threads         | 4                      |

+————————————————-+————————————+

50 rows in set (0.00 sec)

InnoDB的变量：

mysql> show global variables like '%innodb%';

+-----------------------------------------+------------------------+

| Variable_name                           | Value                  |

+-----------------------------------------+------------------------+

| have_innodb                             | YES                    |

| ignore_builtin_innodb                   | OFF                    |

| innodb_adaptive_hash_index              | ON                     |

| innodb_additional_mem_pool_size         | 1048576                |

| innodb_autoextend_increment             | 8                      |

| innodb_autoinc_lock_mode                | 1                      |

| innodb_buffer_pool_size                 | 8388608                |

| innodb_checksums                        | ON                     |

| innodb_commit_concurrency               | 0                      |

| innodb_concurrency_tickets              | 500                    |

| innodb_data_file_path                   | ibdata1:10M:autoextend |

| innodb_data_home_dir                    |                        |

| innodb_doublewrite                      | ON                     |

| innodb_fast_shutdown                    | 1                      |

| innodb_file_io_threads                  | 4                      |

| innodb_file_per_table                   | OFF                    |

| innodb_flush_log_at_trx_commit          | 1                      |

| innodb_flush_method                     |                        |

| innodb_force_recovery                   | 0                      |

| innodb_lock_wait_timeout                | 50                     |

| innodb_locks_unsafe_for_binlog          | OFF                    |

| innodb_log_buffer_size                  | 1048576                |

| innodb_log_file_size                    | 5242880                |

| innodb_log_files_in_group               | 2                      |

| innodb_log_group_home_dir               | ./                     |

| innodb_max_dirty_pages_pct              | 90                     |

| innodb_max_purge_lag                    | 0                      |

| innodb_mirrored_log_groups              | 1                      |

| innodb_open_files                       | 300                    |

| innodb_rollback_on_timeout              | OFF                    |

| innodb_stats_on_metadata                | ON                     |

| innodb_support_xa                       | ON                     |

| innodb_sync_spin_loops                  | 20                     |

| innodb_table_locks                      | ON                     |

| innodb_thread_concurrency               | 8                      |

| innodb_thread_sleep_delay               | 10000                  |

| innodb_use_legacy_cardinality_algorithm | ON                     |

+-----------------------------------------+------------------------+

37 rows in set (0.00 sec)

对比一下，是不是发现多出了不少选项。

参考：

https://launchpad.net/percona-xtradb

http://www.percona.com/docs/wiki/percona-xtradb:start

 

附：InnoDB Plugin 1.0.4的新特性(适用于5.1.24-5.1.37的源码编译)

引自http://www.lovebsd.com/index.php/archives/174

InnoDB Plugin 1.0.4 这次加入了不少第三方的代码，个人比较在意的是 Google 和 Percona 提供的部分。Innodb从而实现了性能上很大的提升，不想以前在并发稍大（比如>8)，吞吐量升值会下将，现在却又很大的提升。

个人关注的改进部分有：

1： Multiple Background Threads

最初由Google提供的补丁(本人注：根据官方说明应该是Percona的补丁)，参数innodb_file_io_threads 可以设置io threads的数量，之前是假的…

2：Master Thread I/O Capacity Tuning

之前innodb在代码里面写死了 innodb_io_capacity 为100，但是现在db服务器很多都是用多块硬盘做raid10，IOPS 一般都远不止100，因此这次改进之后  innodb_io_capacity 变成可以动态调整的参数，用于DBA选择一个合适的值。(本人注：基于Google的补丁实现并做了一些改动)

3：Group Commit

这个据说以前4.x之前就支持的，现在又回来了，支持多个事务同时提交（主要是redo log，之前是因为binlog的2-phase commit protocal的原因中止的），从而提高吞吐量。

4：Adaptive Flushing

这个非常有用，大家知道在脏数据到达设置的阀值比例之后，会开始主动做checkpoint，当checkpoint无可避免的时候，这时候会堵塞用户线程，从而出现性能的突然下降。现在这个问题得到巧妙的解决，会根据算法动态的调整checkpoint的速率，避免出现性能的突然降低。

本人注：它使用一种启发式的方法，基于buffer cache 中dirty pages 的数量和redo产生的速度。根据这种启发式的方法，master thread会确定每秒buffer cache刷新的dirty pages数,这就是所谓的adaptive flushing，可以应付工作负载的突变并提供平滑的I/O速率和事务吞吐量。

REFERENCE:

http://www.innodb.com/wp/2009/08/11/innodb-plugin-104-released/

http://www.innodb.com/wp/products/innodb_plugin/license/third-party-contributions-in-innodb-plugin-1-0-4/