InnoDB线程并发检查机制
InnoDB在接受MySQL线程调用能时，有一个并发线程的检查机制，通过innodb_thread_concurrency参数进行控制。如果参数设置大于0，则表示检查机制开启，允许进入的线程数就是参数的值。等于0则禁用并发检查。
在新的MySQL线程调用Innodb接口前，Innodb会检查已经接受的请求线程数，如已经超过innodb_thread_concurrency设置的限制，则该请求线程会等待innodb_thread_sleep_delay微秒后尝试重新请求，如果第二次请求还是无法获得，则该线程会进入线程队列休眠。重试两次的机制是为了减少CPU的上下文切换的次数，以降低CPU消耗，这和Oracle中latch的spin机制是同样的道理。如果请求被Innodb接受，则会获得一个次数为innodb_concurrency_tickets(默认500次)的通行证，在次数用完之前，该线程重新请求时无须再进行前面所说innodb_thread_concurrency的检查。
上述检查逻辑在源码storage/innobase/srv/srv0srv.c(Innodb很多参数都可以在该文件中找到定义)的srv_conc_enter_innodb函数中，有兴趣的可以仔细阅读一下，代码比较浅显，不难理解。另外，如果是一个已经持有lock的线程，则通过调用srv_conc_force_enter_innodb函数可以无视该检查，这是为了避免线程长时间持有锁影响性能，且可能增加死锁的机率。除此之外，slave线程也是有无视检查直接通行的权限。
简单思考一下上述机制，可以得出一个初步的推论：在数据库并发请求较小的情况下，从性能上来说禁用检查机制应该是更好的，毕竟执行检查机制本身也需要加锁(Mutex)。当并发线程很高的情况下，则开启检查机制对性能更有利。至于具体innodb_thread_concurrency设置为多少，可能就需要在不同的条件下实际的做一下测试了，不同的硬件环境，不同的MySQL版本和Innodb版本，应该都会有一些区别。
源代码中对于innodb_thread_concurrency参数的注释如下：
/* The following controls how many threads we let inside InnoDB concurrently: threads waiting for locks are not counted into the number because otherwise we could get a deadlock. MySQL creates a thread for each user session, and semaphore contention and convoy problems can occur withput this restriction. Value 10 should be good if there are less than 4 processors + 4 disks in the computer. Bigger computers need bigger values. Value 0 will disable the concurrency check. */ ulong srv_thread_concurrency = 0;
因为检查机制需要Mutex保护(Mutex-based Model)，所以开启检查本身也有性能消耗，并且扩展性也会受到限制，在MySQL5.4版本中引入了一种新的机制（Timer-based Model），这里就不讨论了，有兴趣的可以参考这里(需要翻墙)，实际上XtraDB存储引擎里已经包含Timer-based Model，通过参数innodb_thread_concurrency_timer_based可以开启，默认为OFF。在MySQL5.4的srv0srv.c的源代码中的注释中，可以看到Google和Percona的版权声明，看来MySQL5.4中吸引了很多第三方的改进代码，值得期待。
从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)
Innodb monitor介绍
学习oracle的时候，如果想了解一些内部原理和机制，可以使用一些event来生成trace文件，通过分析这些trace文件可以获得很多的信息。而MySQL在这方面比较弱，当然，另一方面来说，MySQL是开源的，可以去直接研究源代码的实现。不过源代码架构庞大，不是每个人都有足够的精力和毅力去解读。InnoDB引擎提供了一个monitor，可以通过monitor一窥其内部的一些统计信息，也可以说是了解InnoDB引擎的一个很好的窗口。
我们最熟悉的，应当就是show innodb status命令，可以直接在客户端输出很多的信息。其实InnoDB monitor一共有四种模式，show innodb status只是其一种模式的直接展现，并且只能交互式开启，无法自动循环捕获信息。另外还有一种适合四种模式的开启方式，则是通过创建一张特殊的innodb表来开启，开启后会按照固定的时间间隔循环，输出信息到log-error参数指定的错误日志文件中，通过drop对应的表，可以停止monitor。
四种monitor分别是:
- innodb_monitor：create table innodb_monitor(x int) engine=innodb;
- innodb_lock_monitor：create table innodb_lock_monitor(x int) engine=innodb;
- innodb_table_monitor：create table innodb_table_monitor(x int) engine=innodb;
- innodb_tablespace_monitor：create table innodb_tablespace_monitor(x int) engine=innodb;
根据我在5.1.36版本中实际观察到的结果，innodb_monitor/innodb_lock_monitor开启后的执行周期是16s（参考手册上说是15s），而innodb_table_monitor/innodb_tablespace_monitor的执行周期是64s。开启monitor后因为是持续周期性的运行的，在不需要的时候一定要记得drop相关表来停止monitor。如果在开启monitor的中间服务器有重启，monitor不会自动重启，并且在下次启动monitor之前，必须先执行停止操作。
其中innodb_monitor/innodb_lock_monitor两种监视器的输出结果基本类似，后者会有更多关于锁的信息，而前一个实际上就是show innodb status。innodb_table_monitor则会将系统中所有innodb的表的一些结构和内部信息输出，而innodb_tablespace_monitor则输出的是tablespace的信息，注意该monitor输出的只是共享表空间的信息，如果使用innodb_file_per_table为每个表使用独立的表空间，则这些表空间的信息是不会包含在输出中的。
以下是一些简单的示例：
innodb_monitor/innodb_lock_monitor：
===================================== 090805 22:24:48 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 19 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 312921, signal count 308229 Mutex spin waits 0, rounds 18209349, OS waits 111906 RW-shared spins 287775, OS waits 142204; RW-excl spins 175036, OS waits 19318 ------------ TRANSACTIONS ------------ Trx id counter 0 121675664 Purge done for trx's n:o < 0 121675662 undo n:o < 0 0 History list length 10 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 121462143, not started, process no 8452, OS thread id 1160767840 mysql tables in use 1, locked 1 MySQL thread id 8056144, query id 78206864 localhost root ---TRANSACTION 0 137229, not started, process no 8452, OS thread id 1158199648 MySQL thread id 50, query id 377 Has read all relay log; waiting for the slave I/O thread to update it -------- 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 (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 34 OS file reads, 80820900 OS file writes, 1263117 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 1.16 writes/s, 0.63 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 8850487, node heap has 233 buffer(s) 0.11 hash searches/s, 0.42 non-hash searches/s --- LOG --- Log sequence number 4 3697502095 Log flushed up to 4 3697502095 Last checkpoint at 4 3697502095 0 pending log writes, 0 pending chkp writes 79595438 log i/o's done, 0.47 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 4851752298; in additional pool allocated 13195520 Dictionary memory allocated 145784 Buffer pool size 262144 Free buffers 193334 Database pages 68577 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 70, created 120513, written 2829967 0.00 reads/s, 0.21 creates/s, 0.84 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 8452, id 1157658976, state: waiting for server activity Number of rows inserted 12233742, updated 57497659, deleted 1, read 69720050 0.05 inserts/s, 0.05 updates/s, 0.00 deletes/s, 0.05 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================
innodb_table_monitor：
=========================================== 090805 22:26:56 INNODB TABLE MONITOR OUTPUT =========================================== -------------------------------------- TABLE: name SYS_FOREIGN, id 0 11, columns 7, indexes 3, appr.rows 0 COLUMNS: ID: DATA_VARCHAR prtype 1835012 len 0; FOR_NAME: DATA_VARCHAR prtype 1835012 len 0; REF_NAME: DATA_VARCHAR prtype 1835012 len 0; N_COLS: DATA_INT len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; INDEX: name ID_IND, id 0 11, fields 1/6, uniq 1, type 3 root page 46, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS INDEX: name FOR_IND, id 0 12, fields 1/2, uniq 2, type 0 root page 47, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: FOR_NAME ID INDEX: name REF_IND, id 0 13, fields 1/2, uniq 2, type 0 root page 48, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: REF_NAME ID ...省略若干输出 -------------------------------------- TABLE: name test/test, id 0 81, columns 4, indexes 1, appr.rows 3 COLUMNS: i: DATA_INT DATA_BINARY_TYPE len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; INDEX: name GEN_CLUST_INDEX, id 0 23, fields 0/4, uniq 1, type 1 root page 3, appr.key vals 3, leaf pages 1, size pages 1 FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR i ----------------------------------- END OF INNODB TABLE MONITOR OUTPUT ==================================
innodb_tablespace_monitor：
================================================ 090805 22:28:16 INNODB TABLESPACE MONITOR OUTPUT ================================================ FILE SPACE INFO: id 0 size 65536, free limit 6208, free extents 89 not full frag extents 6: used pages 69, full frag extents 0 first seg id not used 0 1067667 SEGMENT id 0 1067666 space 0; page 903; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 ...省略若干输出 SEGMENT id 0 144216 space 0; page 1307; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 NUMBER of file segments: 37 Validating tablespace Validation ok --------------------------------------- END OF INNODB TABLESPACE MONITOR OUTPUT =======================================
