XtraDB存储引擎
浏览:1321次 出处信息
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技术博客大学习
扫一扫订阅我的微信号:IT技术博客大学习
<< 前一篇:账号密码包含反斜线时怎么办
后一篇:多维度分类排行榜应用:用位图索引 >>
文章信息
- 作者:NinGoo 来源: NinGoo.net
- 标签: XtraDB
- 发布时间:2009-11-08 16:52:33
近3天十大热文
- [71] Twitter/微博客的学习摘要
- [68] IOS安全–浅谈关于IOS加固的几种方法
- [66] 如何拿下简短的域名
- [65] android 开发入门
- [64] find命令的一点注意事项
- [63] Go Reflect 性能
- [61] 流程管理与用户研究
- [61] Oracle MTS模式下 进程地址与会话信
- [59] 图书馆的世界纪录
- [58] 读书笔记-壹百度:百度十年千倍的29条法则