从show innodb status看XtraDB的增强特性
XtraDB存储引擎相对于标准的InnoDB引擎做了很多的改进，从show innodb status的结果可以初步看到部分增强的特性。下面是一个XtraDB测试环境(Linux)的结果：
root@(none) 11:25:18>show innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
091106 11:26:11 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 53 seconds
----------
BACKGROUND THREAD
----------
srv_master_thread loops: 8 1_second, 8 sleeps, 0 10_second, 15 background, 15 flush
srv_master_thread log flush and writes: 8 log writes only: 39
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 9, signal count 9
Mutex spin waits 2, rounds 60, OS waits 0
RW-shared spins 9, OS waits 9; RW-excl spins 0, OS waits 0
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
25 OS file reads, 48 OS file writes, 32 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.09 writes/s, 0.08 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 553193, node heap has 1 buffer(s)
0.00 hash searches/s, 0.02 non-hash searches/s
---
LOG
---
Log sequence number 48613
Log flushed up to 48613
Last checkpoint at 48613
Max checkpoint age 7782360
Modified age 0
Checkpoint age 0
0 pending log writes, 0 pending chkp writes
24 log i/o's done, 0.04 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 136806400; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
Adaptive hash index 2233316 (2212772 + 20544)
Page hash 69556
Dictionary cache 576800 (553912 + 22888)
File system 41904 (41336 + 568)
Lock system 167076 (166436 + 640)
Recovery system 0 (0 + 0)
Threads 41524 (41348 + 176)
Dictionary memory allocated 22888
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 8170
Database pages 20
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 19, created 2, written 45
0.00 reads/s, 0.00 creates/s, 0.04 writes/s
Buffer pool hit rate 1000 / 1000
LRU len: 20, unzip_LRU len: 0
I/O sum[2]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 16905, id 2788809632, state: waiting for server activity
Number of rows inserted 4, updated 0, deleted 0, read 0
0.02 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
------------
TRANSACTIONS
------------
Trx id counter 509
Purge done for trx's n:o < 506 undo n:o < 0
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, process no 16905, OS thread id 2777967520
MySQL thread id 6, query id 196 dxmonitor 172.19.68.250 root
show innodb status
---TRANSACTION 508, ACTIVE 2 sec, process no 16905, OS thread id 2777766816
1 lock struct(s), heap size 320, 0 row lock(s), undo log entries 1
MySQL thread id 7, query id 195 dxmonitor 172.19.68.250 root
TABLE LOCK table `test`.`test` trx id 508 lock mode IX
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set, 1 warning (0.00 sec)
从上面的结果可以看到：
- 增加了BACKGROUND THREAD部分，显示一些后台线程的统计信息
- SEMAPHORES部分，最后增加了一个关于spin的统计信息。引入了一个新的innodb_spin_wait_delay的参数对spin的行为进行控制
- FILE I/O部分，innodb默认开启4个IO线程，由参数innodb_file_io_threads控制，但在unix/linux平台该参数无效，只能是默认的4个。而XtraDB/innodb plugin默认开启8个IO线程，并且读线程和写线程可以分别由参数innodb_read_io_threads和innodb_write_io_threads控制。在多核CPU环境中，如果CPU和磁盘本身没有成为瓶颈，开启更多线程应该对提升IO性能有很好的作用。而读写线程的单独控制，则可以针对不同应用环境下的要求进行灵活配置。
- LOG部分，增加了max checkpoint age/Modified age/Checkpoint age信息，这些信息可用于调整一些IO相关的参数，具体可参考这里。
- BUFFER POOL AND MEMORY部分，XtraDB更加详细的列出buffer pool中不同部分的详细信息，还有buffer pool的命中率和LRU的信息等，对内存的使用情况一目了然，拒绝黑箱操作。
关于XtraDB的show innodb status的更多改进，请参考这里。
XtraDB存储引擎
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)
