XtraDB存储引擎 -- MySQL -- IT技术博客大学习 -- 共学习共进步！

您现在的位置：首页 --> MySQL --> XtraDB存储引擎

XtraDB存储引擎

浏览:1318次出处信息

XtraDB存储引擎是percona公司对于innodb存储引擎进行改进加强后的产品，第一个版本为1.0.2-1，发布于2008年底。XtraDB兼容innodb的所有特性，并且在IO性能，锁性能，内存管理等多个方面进行了增强。

下载MySQL源代码，当前最新的5.1版本为5.1.40
http://dev.mysql.com/downloads/mysql/5.1.html#source

下载XtraDB源代码，当前最新的版本为1.0.4-8
http://launchpad.net/percona-xtradb/release-8/1.0.4-8/+download/percona-xtradb-1.0.4-8.tar.gz

解压MySQL

tar -zxvf mysql-5.1.40.tar.gz

解压XtraDB

tar -zxvf percona-xtradb-1.0.4-8.tar.gz

将MySQL原来的innodb源码删除或者重命名

cd mysql-5.1.40/storage
mv innobase innobase_bak

将XtraDB拷贝为innobase

cp -r ../../percona-xtradb-1.0.4-8 innobase/

重新编译安装MySQL

cd ../

./configure --prefix=/home/mysql/mysql --with-extra-charsets=all
--enable-assembler --enable-profiling --enable-community-features
--with-plugins=max --with-client-ldflags=-all-static --with-plugins=all

make && make install

安装完成后，就可以按照普通的MySQL安装创建数据库流程执行相应操作即可。XtraDB实际上是基于innodb plugin的代码修改而来，所以以上安装过程和innodb plugin的安装一致。

root@information_schema 04:19:48>show plugins;
+--------------------------------+----------+--------------------+---------+---------+
| Name                           | Status   | Type               | Library | License |
+--------------------------------+----------+--------------------+---------+---------+
| binlog                         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| partition                      | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                      | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CSV                            | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                      | DISABLED | 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     |
| ndbcluster                     | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
+--------------------------------+----------+--------------------+---------+---------+

这里显示的innodb其实已经是XtraDB引擎了。可以简单看一下XtraDB引擎的特性来验证，例如XtraDB在information_schema中引入了xtradb_enhancements来记录其相对于innodb的增强特性。

root@information_schema 04:18:02>desc information_schema.xtradb_enhancements;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| name        | varchar(255) | NO   |     |         |       |
| description | varchar(255) | NO   |     |         |       |
| comment     | varchar(100) | NO   |     |         |       |
| link        | varchar(255) | NO   |     |         |       |
+-------------+--------------+------+-----+---------+-------+

root@information_schema 04:19:45>select * from information_schema.xtradb_enhancements\G
*************************** 1. row ***************************
       name: xtradb_show_enhancements
description: I_S.XTRADB_ENHANCEMENTS
    comment:
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 2. row ***************************
       name: innodb_show_status
description: Improvements to SHOW INNODB STATUS
    comment: Memory information and lock info fixes
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 3. row ***************************
       name: innodb_io
description: Improvements to InnoDB IO
    comment:
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 4. row ***************************
       name: innodb_opt_lru_count
description: Fix of buffer_pool mutex
    comment: Decreases contention on buffer_pool mutex on LRU operations
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 5. row ***************************
       name: innodb_buffer_pool_pages
description: Information of buffer pool content
    comment:
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 6. row ***************************
       name: innodb_expand_undo_slots
description: expandable maximum number of undo slots
    comment: from 1024 (default) to about 4000
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 7. row ***************************
       name: innodb_extra_rseg
description: allow to create extra rollback segments
    comment: When create new db, the new parameter allows to create more rollback segments
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 8. row ***************************
       name: innodb_overwrite_relay_log_info
description: overwrite relay-log.info when slave recovery
    comment: Building as plugin, it is not used.
       link: http://www.percona.com/docs/wiki/percona-xtradb:innodb_overwrite_relay_log_info
*************************** 9. row ***************************
       name: innodb_thread_concurrency_timer_based
description: use InnoDB timer based concurrency throttling (backport from MySQL 5.4.0)
    comment:
       link:
*************************** 10. row ***************************
       name: innodb_expand_import
description: convert .ibd file automatically when import tablespace
    comment: the files are generated by xtrabackup export mode.
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 11. row ***************************
       name: innodb_dict_size_limit
description: Limit dictionary cache size
    comment: Variable innodb_dict_size_limit in bytes
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 12. row ***************************
       name: innodb_split_buf_pool_mutex
description: More fix of buffer_pool mutex
    comment: Spliting buf_pool_mutex and optimizing based on innodb_opt_lru_count
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 13. row ***************************
       name: innodb_stats
description: Additional features about InnoDB statistics/optimizer
    comment:
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 14. row ***************************
       name: innodb_recovery_patches
description: Bugfixes and adjustments about recovery process
    comment:
       link: http://www.percona.com/docs/wiki/percona-xtradb
*************************** 15. row ***************************
       name: innodb_purge_thread
description: Enable to use purge devoted thread
    comment:
       link: http://www.percona.com/docs/wiki/percona-xtradb
15 rows in set (0.00 sec)

可以看到引入了很多新的innodb控制参数，允许用户对xtradb引擎的工作方式做更多的控制

root@information_schema 04:22:01>show 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       | 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_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                 | OFF                    |
| innodb_flush_log_at_trx_commit        | 1                      |
| innodb_flush_method                   |                        |
| innodb_flush_neighbor_pages           | 1                      |
| innodb_force_recovery                 | 0                      |
| innodb_ibuf_accel_rate                | 100                    |
| innodb_ibuf_active_contract           | 0                      |
| innodb_ibuf_max_size                  | 67092480               |
| 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_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             | 0                      |
| 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)

对比一下原版的5.1.40，innodb相关参数只有37个，整整多了33个参数。

mysql> show 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)

建议继续学习：

QQ技术交流群：445447336，欢迎加入！
扫一扫订阅我的微信号：IT技术博客大学习

<< 前一篇：账号密码包含反斜线时怎么办

后一篇：多维度分类排行榜应用：用位图索引 >>

文章信息

作者：NinGoo 来源： NinGoo.net
标签： XtraDB
发布时间：2009-11-08 16:52:33

建议继续学习

近3天十大热文