InnoDB的多版本一致性读的实现
InnoDB是支持MVCC多版本一致性读的，因此和其他实现了MVCC的系统如Oracle，PostgreSQL一样，读不会阻塞写，写也不会阻塞读。虽然同样是MVCC，各家的实现是不太一样的。Oracle通过在block头部的事务列表，和记录中的锁标志位，加上回滚段，个人认为实现上是最优雅的方式。 而PostgreSQL则更是将多个版本的数据都放在表中，而没有单独的回滚段，导致的一个结果是回滚非常快，却付出了查询性能降低的代价。
InnoDB的实现更像Oracle，同样是将数据的旧版本存放在单独的回滚段中，但是也有不同。之前还以为整体实现都会跟Oracle不会有太大的出入，也一直没有太在意去看具体实现。今晚晚上下班准备回家时，刚好路过几个同事在交流分享这个问题，遇到一个疑问：
我们知道，InnoDB表会有三个隐藏字段，6字节的DB_ROW_ID，6字节的DB_TX_ID，7字节的DB_ROLL_PTR（指向对应回滚段的地址），这个可以通过innodb monitor看到，当然如果你熟悉innodb文件结构，也可以直接od ibd文件来验证。一致性读主要跟后两者有关系。InnoDB内部维护了一个递增的tx id counter，其当前值可以通过show engine innodb status获得
echo "show engine innodb status\G" | mysql -uroot | grep "Trx id counter"
假设有一个表，当前已经有两条记录。这时候我们开始一个实验，开启两个session，A和B，都设置autocommit=0
MySQL>set global autocommit=0;
T1时间：
A开始一个事务，执行一条select，可以看到已有的两条记录，show engine innodb status可以知道A的tx id，假设为7430
T2时间：
B开始一个事务，执行一条select，可以看到已有的两条记录，可以知道B的tx id，为7431
T3时间：
A中insert一条记录，此时A再select能看到，所以返回三条记录，而B无法看到，还是返回两条记录。
T4时间：
A中执行commit提交事务，分别在A和B中select，得到的结果和T3时间相同。
Ok，假设一致性读是根据事务先后，也就是tx id来比较的话，如果B事务的一致性读是通过B的tx id即7431来和A事务中insert的这条记录的tx id即7430来比较的话，由于A.tx_id < B.tx_id，那么B应该能都到A的记录（tx id是递增的，所以越小说明事务越早开始），但如果能读到，则显然不符合多版本一致性。
因此结果是正确的，那么就是InnoDB的一致性读的实现方式不是像我们按照经验来测试的那样了。通过google和察看代码，原来InnoDB还真是有一个感觉上很山寨的设计，由于tx id是事务一开始就分配的，事务中的变化也没有记录一个类似于Oracle的SCN的逻辑时钟，于是由了如下的实现：
InnoDB每个事务在开始的时候，会将当前系统中的活跃事务列表（trx_sys->trx_list）创建一个副本（read view），然后一致性读去比较记录的tx id的时候，并不是根据当前事务的tx id，而是根据read view最早一个事务的tx id（read view->up_limit_id）来做比较的，这样就能确保在事务B之前没有提交的所有事务的变更，B事务都是看不到的。当然，这里还有个小问题要处理一下，就是当前事务自身的变更还是需要看到的。
有兴趣的可以去仔细看看代码的实现，在storage/innobase/read/read0read.c中实现了创建read view的函数read_view_open_now，在storage/innobase/include/read0read.ic中实现了判断一致性读是否可见的read_view_sees_trx_id。以下代码摘自5.5.8：
UNIV_INTERN
read_view_t*
read_view_open_now(
/*===============*/
trx_id_t cr_trx_id, /*!< in: trx_id of creating
transaction, or 0 used in purge */
mem_heap_t* heap) /*!< in: memory heap from which
allocated */
{
read_view_t* view;
trx_t* trx;
ulint n;
ut_ad(mutex_own(&kernel_mutex));
view = read_view_create_low(UT_LIST_GET_LEN(trx_sys->trx_list), heap);
view->creator_trx_id = cr_trx_id;
view->type = VIEW_NORMAL;
view->undo_no = 0;
/* No future transactions should be visible in the view */
view->low_limit_no = trx_sys->max_trx_id;
view->low_limit_id = view->low_limit_no;
n = 0;
trx = UT_LIST_GET_FIRST(trx_sys->trx_list);
/* No active transaction should be visible, except cr_trx */
while (trx) {
if (trx->id != cr_trx_id
&& (trx->conc_state == TRX_ACTIVE
|| trx->conc_state == TRX_PREPARED)) {
read_view_set_nth_trx_id(view, n, trx->id);
n++;
/* NOTE that a transaction whose trx number is <
trx_sys->max_trx_id can still be active, if it is
in the middle of its commit! Note that when a
transaction starts, we initialize trx->no to
IB_ULONGLONG_MAX. */
if (view->low_limit_no > trx->no) {
view->low_limit_no = trx->no;
}
}
trx = UT_LIST_GET_NEXT(trx_list, trx);
}
view->n_trx_ids = n;
if (n > 0) {
/* The last active transaction has the smallest id: */
view->up_limit_id = read_view_get_nth_trx_id(view, n - 1);
} else {
view->up_limit_id = view->low_limit_id;
}
UT_LIST_ADD_FIRST(view_list, trx_sys->view_list, view);
return(view);
}
UNIV_INLINE
ibool
read_view_sees_trx_id(
/*==================*/
const read_view_t* view, /*!< in: read view */
trx_id_t trx_id) /*!< in: trx id */
{
ulint n_ids;
ulint i;
if (trx_id < view->up_limit_id) {
return(TRUE);
}
if (trx_id >= view->low_limit_id) {
return(FALSE);
}
/* We go through the trx ids in the array smallest first: this order
may save CPU time, because if there was a very long running
transaction in the trx id array, its trx id is looked at first, and
the first two comparisons may well decide the visibility of trx_id. */
n_ids = view->n_trx_ids;
for (i = 0; i < n_ids; i++) {
trx_id_t view_trx_id
= read_view_get_nth_trx_id(view, n_ids - i - 1);
if (trx_id <= view_trx_id) {
return(trx_id != view_trx_id);
}
}
return(TRUE);
}
参考：
http://dev.mysql.com/doc/refman/5.1/en/innodb-multi-versioning.html
http://wangyuanzju.blog.163.com/blog/static/130292009107101544125/
http://bbs.chinaunix.net/thread-1773206-1-1.html
Oracle如何监控表的DML次数
在数据库技术大会上，做了《构建高可用数据库监控系统》的分享以后，很多朋友对北斗如何实现表的DML次数监控有兴趣，会上因为时间的原因，我只是说有系统视图可以查到这个信息，因此有了本文，可以稍微详细一点来说明是如何实现的。
我说的系统视图，具体指的是dba_tab_modifications/all_tab_modifications/user_tab_modifications，这几个视图收集了表自从上一次分析之后的DML累积次数。但是要注意，考虑到性能的影响，Oracle并不是实时统计这个数据的，在Oracle9i之前，约3个小时SMON进程会刷新一次数据，而Oracle9i以后这个时间间隔变成了15分钟。
因此以较高的频率来实时监控这个表的话，得到的并不是当前的准确数据。Oracle在dbms_stat包中提供了一个过程来手动刷新统计数据，假如在一天的业务低峰期采集一次数据的话，可以先执行该过程，就能得到较为准确的数据。但是，不建议在业务高峰期执行该过程。
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
在Oracle10g之前，必须手工开启表的monitoring属性，才会将DML统计信息收集到这个视图中。可以通过dba_tables.monitoring列查看表是否已经开启了监控。关于这个变化，可以参考Metalink ID 252597.1
alter table test monitoring;
Oracle10g之后，只要statistics_level是TYPICAL（默认）或者ALL，就能自动收集信息了，即使给表设置为nomonitoring也不能阻止，这个表的属性已经被废弃了。
desc dba_tab_modifications Name Null? Type ----------------------------------------- -------- ---------------------------- TABLE_OWNER VARCHAR2(30) TABLE_NAME VARCHAR2(30) PARTITION_NAME VARCHAR2(30) SUBPARTITION_NAME VARCHAR2(30) INSERTS NUMBER UPDATES NUMBER DELETES NUMBER TIMESTAMP DATE TRUNCATED VARCHAR2(3) DROP_SEGMENTS NUMBER
遭遇Oracle11gR2 ASM文件无法扩展的Bug
在一个11gR2+ASM的环境中，因为产生了大量归档，导致控制文件需要扩展，结果数据库报错：
Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_arc0_11146.trc: ORA-00202: control file: '+DATA/control01.ctl' ORA-17505: ksfdrsz:1 Failed to resize file to size 1920 blocks ORA-15061: ASM operation not supported [41] Control file expansion from 1600 blocks to 1920 blocks denied by OS
这是ASM的一个bug 8898852，可以在Oracle Support上找到对应的小patch，经过验证可以解决该问题。该patch已经包含在前两天刚发布的ASM的PSU中，只需要安装该PSU即可。
从目前我们使用11gR2的一些经验来看，bug还是比较多，尤其是一些影响比较大的bug，还是让人对11gR2无法完全放心，只能在特定的环境，和有足够容错方案的环境中，才能冒着风险来试用。
除了这个ASM文件不能扩展，有几个从10.2.0.4升级到11.2.0.1的库，在switchover中碰到了两次ORA-600 [ktbdchk1: bad dscn]，出现问题后无法执行DML，非常严重的一个问题，并且暂时还没有好的办法解决，只能通过重建有问题的表的方式绕过，因为两次都是在切换后出现，初步推断是Active Data Guard带来的bug，开了SR和Oracle扯了很久，还在继续研究中。
据说Oracle 11.2.0.2将在年中发布，希望这个新的版本能更稳定些吧。
4月份，Oracle11.2.0.1的PSU和ASM(Grid Infrastructure)的第一个PSU都已经发布了，如果需要在产品环境中使用，建议这些PSU都打上吧
| Oracle Database PSU | Unix Patch | Comments |
|---|---|---|
|
11.2.0.1.1 |
||
|
11.2.0.1.1 for GI |
||
|
11.1.0.7.3 |
||
|
11.1.0.7.2 for CRS |
Released in January 2010 |
|
|
10.2.0.4.4 |
||
|
10.2.0.4.4 for CRS |
注：该表格摘自My Oracle Support Note: 854428.1l
tbstat:实时监控数据库统计状态的小工具
用perl写了一个简单的工具，用于实时查看数据库的统计状态信息，展现信息主要来源于Oracle数据字典中v$systat和v$system_event。写这个工具的初衷，是因为目前我们对于数据库的监控，更多的是分钟级别抽样的数据来绘制的图形，粒度相对还比较粗，有一些比较深的问题，需要更加细粒度的数据。而如果把监控的粒度做到秒级，则收集的数据量就会非常大，因此需要一个平衡，平时采用分钟级别的抽样数据已经足够用于预警，而秒级的则用于某个具体问题的分析。
当前tbstat功能还比较简单，类似于iostat/vmstat等os工具，tbstat可以通过指定抽样间隔和抽样次数，来循环抓取Oracle的统计状态信息。tbstat支持三个参数 -i 表示间隔时间 -c 表示循环次数 -n 表示需要查看的统计信息的名字(使用前后%的like来查询)
- tbstat -i 2 -c 10 表示间隔时间2s，循环次数10次，展示经过人工筛选的36项统计信息
- tbstat -i 2 -c 10 -n parse 表示间隔时间2s，循环次数10次，展示所有名字包含parse的统计信息
- tbstat -i 2 -c 10 -n all 表示间隔时间2s，循环次数10次，展示所有不为零的统计信息
也可以使用简化的参数输入方法，第一位表示间隔时间，第二位表示循环次数，第三位表示统计信息名。直接敲入tbstat，则默认的参数为间隔时间10s，次数无限，经过挑选过滤的一些常用的v$sysstat中的统计信息。如果name参数传入的值是event，则展示v$system_event中的等待事件的信息。
$tbstat 1 0
--------------------------------------------------------------------------
-- tbstat v0.3.3 --- a tool for oracle system statistics and event.
-- Powered by NinGoo.net
--------------------------------------------------------------------------
CPU used by this session: 40 CR blocks created: 5
DBWR checkpoint buffers written: 569 DBWR undo block writes: 64
bytes received via SQL*Net from client: 314297 bytes sent via SQL*Net to client: 2761660
cleanouts only - consistent read gets: 4 consistent gets: 48855
db block changes: 2122 db block gets: 3714
enqueue requests: 900 enqueue waits: 7
execute count: 3145 free buffer requested: 1402
index crx upgrade (positioned): 3 index fast full scans (full): 0
leaf node 90-10 splits: 0 leaf node splits: 0
logons cumulative: 1 parse count (failures): 0
parse count (hard): 0 physical reads: 1546
physical reads cache prefetch: 0 physical writes: 603
redo size: 618436 redo synch time: 16
redo synch writes: 181 redo write time: 15
redo writes: 174 rollbacks only - consistent read gets: 0
sorts (disk): 0 sorts (memory): 259
table scans (long tables): 0 table scans (short tables): 9
transaction rollbacks: 0 user commits: 182
$tbstat 1 0 event
-------------------------------------------------------------------------------
-- tbstat v0.3.3 --- a tool for oracle system statistics and event.
-- Powered by NinGoo.net
-------------------------------------------------------------------------------
Event Name: waits time Event Name: waits time
--------------------------------------------------------------------------------------------
LGWR wait for redo copy: 1 0.01 SQL*Net more data from client: 151 19.95
SQL*Net more data to client: 1218 0.01 buffer busy waits: 2 0.01
control file parallel write: 1 0.51 control file sequential read: 1 0.26
cursor: pin S: 0 0.00 cursor: pin S wait on X: 0 0.00
db file parallel read: 0 0.00 db file parallel write: 0 0.00
db file scattered read: 0 0.00 db file sequential read: 2040 3.43
direct path read: 269 0.71 direct path read temp: 0 0.00
direct path write: 23 0.26 direct path write temp: 0 0.00
enq: CF - contention: 0 0.00 enq: HW - contention: 7 9.00
enq: SQ - contention: 0 0.00 enq: TX - allocate ITL entry: 0 0.00
enq: TX - index contention: 0 0.00 enq: TX - row lock contention: 0 0.00
latch free: 0 0.00 latch: cache buffers chains: 0 0.00
latch: library cache: 0 0.00 latch: redo writing: 0 0.00
latch: session allocation: 0 0.00 library cache lock: 0 0.00
log buffer space: 0 0.00 log file parallel write: 145 0.60
log file sequential read: 145 0.53 log file switch completion: 0 0.00
log file sync: 147 0.78 os thread startup: 0 0.00
read by other session: 0 0.00 row cache lock: 0 0.00
undo segment extension: 0 0.00
如果输入的name是精确匹配到只有一条统计信息的，会在后面打印出间隔时间内排名前10的sid的值。利用此功能，可以很方便的抓到造成某些统计信息异常的会话和SQL语句，会话和SQL信息是通过关联v$session来获取的。因此需要注意，如果统计信息对应的事件持续时间很短，从v$session里抓取到的sql可能并不是造成统计信息升高的罪魁祸首，但是sid一般来说还是准确的，因为应用采用的大多是连接池来连接数据库的，因此还是可以更具sid和machine信息来看看造成异常的是哪个具体的应用。
例如，全表扫描一般会导致physical reads cache prefetch等待事件，因此可以通过查看该事件对应的top sid来获得具体的语句，当然，不是所有的physical reads cache prefetch都是全表扫描导致的，因此对于获得的结果，还需要DBA根据具体情况做进一步分析：
$tbstat 1 0 'physical reads cache prefetch'
-------------------------------------------------------------------------------
-- tbstat v0.3.3 --- a tool for oracle system statistics and event.
-- Powered by NinGoo.net
-------------------------------------------------------------------------------
physical reads cache prefetch: 526
sid value % machine sql_id
---------- ----------- ----- ------------------- --------------
2928 302 69.7 test11 79db58a3dg921
4902 67 15.5 test71 79db58a3dg921
4821 64 14.8 test33 3afdq50xt03ch
4544 0 0.0 test54 3afdq50xt03ch
1801 0 0.0 test06 79db58a3dg921
2830 0 0.0 test12 79db58a3dg921
898 0 0.0 test09 4n7675hwwcndc
1031 0 0.0 test16 79db58a3dg921
463 0 0.0 test04 3afdq50xt03ch
1364 0 0.0 test08 cq749u66x06uj
1408 0 0.0 test27 39rbqj3ck76w3
722 0 0.0 test37 26hdkf07336uf
当然，tbstat只是一个用于抽取统计状态的小工具而已，如果要用于故障诊断，则还是要求DBA对于v$systat和v$system_event中各种统计和事件非常的熟悉。tbstat使用了DBD::Oracle以sysdba身份来连接数据库，因此需要为Perl安装DBI和DBD::Oracle模块，并且在数据库服务器本机上执行。如果你对于这个工具有兴趣，可以在这里下载源代码，使用过程中，如果有什么建议和需求，欢迎告诉我。
