从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_threadsinnodb_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)

无觅相关文章插件,快速提升流量