ASM优先读取测试

下面的例子将显示如何部署优先读取功能与展现其一些优点。这个例子演示当asm_preferred_read_failure_groups参数没有设置时的I/O模式，以及修改参数之后对I/O的影响。

1.创建有两个故障磁盘组的ASM磁盘组 JL 其中fg1 是高性能，fg2是nas盘


2.在节点1,创建表测试表t1

SQL> create table t1 as select * from dba_tables;

Table created.

SQL> insert into t1 select * from t1;



SQL> /

...

1430528 rows created.

SQL> alter system flush buffer_cache;

System altered.



3.查询磁盘组的读写I/O累计信息，可以看到实例1中的FG1,FG2的读写分别为5816599,5837805,实例2中的FG1,FG2的读写分别为5843814,5853830

set long 9999
set linesize 9999
select inst_id, failgroup, sum(reads), sum(writes) from gv$asm_disk where failgroup in ('FG1','FG2') group by inst_id, failgroup;


set long 9999
set linesize 9999
select inst_id, failgroup, sum(reads), sum(writes) from gv$asm_disk where failgroup in ('FG1','FG2') group by inst_id, failgroup;

INST_ID FAILGROUP SUM(READS) SUM(WRITES)
---------- ------------------------------ ---------- -----------
1 FG1 5816599 56483
1 FG2 5837805 56483
2 FG1 5843814 966
2 FG2 5853830 966

4.执行查询:

SQL> show parameter asm_preferred_read_failure_groups

NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
asm_preferred_read_failure_groups string

SQL> select count(*) from sys.t1;

COUNT(*)
----------
2861056


5.再次查询磁盘组的读写I/O累计信息，可以看到实例1中的FG1,FG2的读分别增加了 781,497 实例2 分别增加了2832 ，80


INST_ID FAILGROUP SUM(READS) SUM(WRITES)
---------- ------------------------------ ---------- -----------
1 FG1 5817380 56813
1 FG2 5838302 56813
2 FG1 5846646 1013
2 FG2 5853910 1013



6.设置 asm_preferred_read_failure_groups参数，让节点1优先从故障磁盘组FG1进行读取，让节点2优先从故障磁盘组FG2进行读取

SQL> alter system set asm_preferred_read_failure_groups='DATA_NRML.FG1' scope=both sid='+ASM1';

alter system set asm_preferred_read_failure_groups='BPM.FG1','FR.FG1','JL.FG1','KFK.FG1','SC.FG1','ZHPT.FG1' scope=both;

SQL> alter system set asm_preferred_read_failure_groups='BPM.FG1','FR.FG1','JL.FG1','KFK.FG1','SC.FG1','ZHPT.FG1' scope=both;

System altered.

SQL> show parameter asm_preferred_read_failure_groups

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
asm_preferred_read_failure_groups string BPM.FG1, FR.FG1, JL.FG1, KFK.F
G1, SC.FG1, ZHPT.FG1


修改前查询时间
15:04:11 SQL> set timing on
15:04:17 SQL> select count(*) from sys.t1;

COUNT(*)
----------
2861056

Elapsed: 00:00:04.16



7.检查实例的优先读取磁盘组信息,从下面的查询结果可以看到，优先都是读FG1，其次是FG2:


SQL> select inst_id, failgroup, name, preferred_read from gv$asm_disk where failgroup in ('FG1','FG2') order by inst_id, failgroup;


INST_ID FAILGROUP NAME P
---------- ------------------------------ ------------------------------ -
1 FG1 BPM_0001 Y
1 FG1 ZHPT_0001 Y
1 FG1 FR_0001 Y
1 FG1 KFK_0001 Y
1 FG1 SC_0001 Y
1 FG1 JL_0001 Y
1 FG2 BPM_0000 N
1 FG2 KFK_0000 N
1 FG2 JL_0000 N
1 FG2 SC_0000 N
1 FG2 ZHPT_0000 N

INST_ID FAILGROUP NAME P
---------- ------------------------------ ------------------------------ -
1 FG2 FR_0000 N
2 FG1 SC_0001 Y
2 FG1 JL_0001 Y
2 FG1 ZHPT_0001 Y
2 FG1 BPM_0001 Y
2 FG1 FR_0001 Y
2 FG1 KFK_0001 Y
2 FG2 JL_0000 N
2 FG2 BPM_0000 N
2 FG2 ZHPT_0000 N
2 FG2 KFK_0000 N

INST_ID FAILGROUP NAME P
---------- ------------------------------ ------------------------------ -
2 FG2 FR_0000 N
2 FG2 SC_0000 N


12 rows selected.

8.在节点1开启会话再次执行查询

SQL> alter system flush buffer_cache;

System altered.
SQL> set timing on
15:13:53 SQL> select count(*) from SYS.t1;

COUNT(*)
----------
2861056

Elapsed: 00:00:00.73

Elapsed: 00:00:04.16
可以看到两次的执行时间存在明细差距，调整前为4s调整后为0.7s


9 查询当前的读写数据

set long 9999
set linesize 9999
select inst_id, failgroup, sum(reads), sum(writes) from gv$asm_disk where failgroup in ('FG1','FG2') group by inst_id, failgroup;

INST_ID FAILGROUP SUM(READS) SUM(WRITES)
---------- ------------------------------ ---------- -----------
1 FG1 5821644 59588
1 FG2 5839272 59586
2 FG1 5869843 1504
2 FG2 5854523 1504


再次执行sql，
select count(*) from SYS.t1;

9.再次查询磁盘组的读写I/O累计信息，可以看到 读基本都到了FG1上面 852和512，FG2的读很少 都是6


SQL> select inst_id, failgroup, sum(reads), sum(writes) from gv$asm_disk where failgroup in ('FG1','FG2') group by inst_id, failgroup;


INST_ID FAILGROUP SUM(READS) SUM(WRITES)
---------- ------------------------------ ---------- -----------
1 FG1 5822496 59647
1 FG2 5839278 59647
2 FG1 5870355 1512
2 FG2 5854529 1512