MonthMarch 2015

EXADATA智能扫描

提要:
查询特定的要求:
智能扫描只可用于完整的表或索引扫描。
智能扫描只能用于直接路径读取:
直接路径读取会自动用于并行查询。
直接路径读取可以用于串行查询。
默认情况下不使用它们进行小型表的串行扫描。
使用 _serial_direct_read=TRUE 可强制执行直接路径读取。

为了查询可以获得Exadata卸载能力的优势,优化器必须决定使用全表扫描或者快速全索引扫描来执行语句,这里的用词比较笼统,一般来说,这两个词对应的是执行计划中的TABLE ACCESS FULL和INDEX FAST FULL SCAN。在Exadata中,这些类似的操作的命名做了些许更改以表明访问的是Exadata存储。新的操作名称是TABLE ACCESS STORAGE FULL和INDEX STORAGE FAST FULL SCAN。请注意,也有一些细微的变化,比如 MAT_VIEW ACCESS STORAGE FULL事件也表示可以使用智能扫描。不过你应该知道,事实上就算执行计划里面显示 TABLE ACCESS STORAGE FULL操作,也并不意味着查询就一定执行了智能扫描,它仅仅意味着前提已经满足。我们将在本章稍后探讨如何确认一个语句是否确实通过智能扫描实现了卸载操作。

直接路径读是什么意思?下面两段话可以解释
智能扫描除了必须是全扫描之外,还需要读取操作是通过Oracle直接路径读取机制来执行的。直接路径读取已经存在很长一段时间了,传统上,这种读取机制是被服务于并行查询的从属进程(Slave Process)使用的。因为并行查询最初预计将用于访问非常大量的数据(通常大到无法全部放入Oracle缓冲区),所以决定并行从属进程直接读取数据,然后放入自己的内存中(也被称为程序全局区或者PGA)。直接路径读取机制完全跳过了标准的将数据库放入缓冲区这样的Oracle缓存机制,这对海量数据是非常有效的,因为它消除了那些额外的没有帮助的工作(缓存全表扫描获取到的却又可能不会被再次用到的数据),让这些块不至于将其他的数据块刷新出缓冲区。像我们之前提到的,kcfis(Kernel File Intelligent Storage)函数是被kcbldrget( Kernel Block Direct Read GET)函数调用的,所以,智能扫描只有在使用直接路径读取机制的时候才会执行。

除了并行从属进程,只要条件允许,直接路径读取也可能用在非并行SQL语句中。有一个隐含参数_SERIAL_DIRECT_READ可以控制此功能。当此参数设置为默认值AUTO时,Oracle自动判断是否要为非并行扫描使用直接路径读取。计算基于几个因素,包括对象大小、Buffer Cache大小,以及在Buffer Cache中已存在了多少该对象的数据块。另外,还有一个隐含参数(_SMALL_TABLE_THRESHOLD)定义了如果要使用串行直接路径读取,那么表至少要为多大。对于非串行扫描决定是否要使用直接路径读取机制的算法并未公开,虽然串行直接路径读取的功能早已存在,但是只是在最近才成为比较普遍的现象。Oracle数据库11gR2在计算是否对于非并行扫描使用直接路径读取上做了一些修改,新修改的算法使Oracle 11gR2比以前的数据库版本要更频繁地采用直接路径读取。这也许是因为有了Exadata智能扫描,因此希望尽可能地触发直接路径读取,但是这样的算法在非Exadata平台上可能略显激进。

备注 My Oracle Support文档:793845.1中包含如下表述。
在11g中,关于在串行表扫描中是使用直接路径读取还是使用缓存读取,我们做了探索性的改动。在10g中,对于大表的串行扫描默认是通过缓存的,在11g中,决定是直接读取还是通过缓存读取,要基于表大小、缓冲区大小和其他多种统计信息。因为避免了闩锁(Latche),因此直接路径读取比离散读(Scattered Read)更快,对其他进程影响更小。
完全索引扫描和快速完全索引扫描的区别:

完全索引扫描: 这个性能感觉是最差的,只能单块读取,而且还要通过表去获得数据,但是因为他是按索引的顺序读取的,所以不再需要排序. 比如 select * from table order by name, 如果name 上有索引,很显然这个时候一般有2个计划, 第一个,全表扫描,然后sort, 还有一个情况,完全索引扫描就OK. 因为此时不需要排序了.
索引全扫描是根据叶节点链来进行的。进行索引全扫描首先要从根开始,找到叶节点链上的第一个数据块,然后沿着叶节点链进行扫描,由于叶节点链是根据索引键值排序的,因此这样扫描出来的数据本身就是排序的,数据读出后不需要再次排序。这种扫描方式和索引快速全扫描相比,首先要找到索引的根,然后通过枝节点找到第一个叶节点,然后再顺着叶节点链扫描整个索引。索引全扫描的IO成本比索引快速全扫描要大很多,读取根节点和叶节点的成本相对不大,不过由于顺着叶节点链扫描整个索引的时候无法使用多块读,而只能使用单块读,因此这种扫描方式的IO开销要远大于索引快速全扫描。这种索引扫描,我们如果对会话进行跟踪,会发现大量的db file sequential read等待。

快速完全索引扫描:
属先这个扫描是不用通过表得到数据的.这个是重点. 那么不通过表得到数据,很明显只能通过索引得到数据了. 那就更明显了,那你要拿的字段必须在索引中,否者如何不通过表的到数据?通常比如:select name from table; 如果name上有索引,有可能走FFS.但是如果是select name,id from table,假如id没有索引,那么不管怎么样,都是不可能走FFS的.对索引的扫描可以根据该索引的extent来进行,采用多块读的方式进行。因此在这类操作中,我们可以看到会话会大量的出现db file scattered read等待。
后者是我们希望在EXADATA上出现的索引扫描方式

impdp报错ORA-39083 ORA-02304 Object type TYPE failed to create

impdp报错ORA-39083 ORA-02304 Object type TYPE failed to create

环境
Red Hat Enterprise Linux Server release 5.8 (Tikanga)
ORACLE Release 11.2.0.3.0 Production

我用expdp,impdp复制一个shema,在impdp导入的时候报错
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE “GENIDCDEV2”.”ARRAY_TYPE2″ OID ‘0367306C82464BF1E0534E092D0A4AB5′ is array(48) of varchar2(255)

查看官方文档的impdp章节,搜索OID,发现了如下有用的内容
查看impdp help=y 可以使用一个参数:transform

TRANSFORM
Enables you to alter object creation DDL for objects being imported.

OID – If the value is specified as n, the assignment of the exported OID during the
creation of object tables and types is inhibited. Instead, a new OID is assigned.
This can be useful for cloning schemas, but does not affect referenced objects. The
default value is y.

这个参数默认是Y,如果改成N,那么导入的时候会把各个对象重新赋予新的OID。
transform=oid:n
这样,再用impdp导入的时候就不会出现
ORA-39083,ORA-02304
注意:exp/imp不支持oid转换,又是一个使用数据泵的优势。

因此我想到了两种方法可以解决问题
1.用下面impdp语句重新导入
impdp system/’*****’ directory=dir_lisx REMAP_SCHEMA=WMSUAT:GENIDCDEV2 dumpfile=wmsuat20150304.dump include=TYPE transform=oid:n logfile=impdp_wmsuat20150304_2.log

Import: Release 11.2.0.3.0 – Production on Wed Mar 4 14:55:40 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/******** directory=dir_lisx REMAP_SCHEMA=WMSUAT:GENIDCDEV2 dumpfile=wmsuat20150304.dump include=TYPE transform=oid:n logfile=impdp_wmsuat20150304_2.log
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Job “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully completed at 14:55:45
2.手工重建刚才导入失败的type
核查type的ddl语句
SELECT dbms_metadata.get_ddl(‘TYPE’,’ARRAY_TYPE2′,’WMSUAT’) from dual ;

CREATE OR REPLACE TYPE “WMSUAT”.”ARRAY_TYPE2″ is array(48) of varchar2(255) ;

记得重新编译一下新clone的schema
EXEC DBMS_UTILITY.compile_schema(schema => ‘GENIDCDEV2’);

mysql5.6配置semi_sync

测试环境:
Red Hat Enterprise Linux Server release 6.3 (Santiago)
Server version: 5.6.22-log MySQL Community Server (GPL)

mysql的replication协议是异步的,虽然异步效率、性能很好,但是却无法保证主从数据一致性,
如果master crash,已经commit的事务不会被传送到任何的slave上,
从mysql5.5之后,mysql为了保证主从库数据一致性,引进了semi-sync功能,
semi-sync意思是MASTER只需要接收到其中一台SLAVE的返回信息,就会commit;否则需等待直至切换成异步再提交。

优点:
当事务返回客户端成功后,则日志一定在至少两台主机上存在。
MySQL的Semi-sync适合小事务,且两台主机的延迟又较小,则Semi-sync可以实现在性能很小损失的情况下的零数据丢失。

缺点:
完成单个事务增加了额外的等待延迟,延迟的大小取决于网络的好坏。

配置方法:
在主库安装semisync_master插件:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’; //linux
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.dll’; //windows

在备库安装semisync_slave插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’; //linux
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;//windows
主库上,新增如下参数:
$vi /etc/my.cnf

[mysqld]

rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000

备库上新增:

$vi /etc/my.cnf

[mysqld]

rpl_semi_sync_slave_enabled=1

重启master和slave,semi_sync插件会自动加载

重启后,master显示如下:
mysql> show variables like ‘%rpl_semi%’;
+————————————+——-+
| Variable_name | Value |
+————————————+——-+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON | #表示即使没有SLAVE也会等待过期时间结束,是默认值
+————————————+——-+
4 rows in set (0.00 sec)

mysql> show status like ‘%semi%’;
+——————————————–+———-+
| Variable_name | Value |
+——————————————–+———-+
| Rpl_semi_sync_master_clients | 3 | #有多少个Semi-sync的备库,我配置了3个
| Rpl_semi_sync_master_net_avg_wait_time | 2525 | #事务提交后,等待备库响应的平均时间
| Rpl_semi_sync_master_net_wait_time | 45892342 | #总的网络等待时间
| Rpl_semi_sync_master_net_waits | 18174 | #等待网络响应的总次数
| Rpl_semi_sync_master_no_times | 0 | #一共有几次从Semi-sync跌回普通状态
| Rpl_semi_sync_master_no_tx | 0 | #备库未及时响应的事务数
| Rpl_semi_sync_master_status | ON | #主库上Semi-sync是否正常开启
| Rpl_semi_sync_master_timefunc_failures | 0 | #时间函数未正常工作的次数
| Rpl_semi_sync_master_tx_avg_wait_time | 1196 | #开启Semi-sync,事务返回需要等待的平均时间
| Rpl_semi_sync_master_tx_wait_time | 7918635 | #事务等待备库响应的总时间
| Rpl_semi_sync_master_tx_waits | 6620 | #事务等待备库响应的总次数
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 | #改变当前等待最小二进制日志的次数
| Rpl_semi_sync_master_wait_sessions | 0 | #当前有几个线程在等备库响应
| Rpl_semi_sync_master_yes_tx | 6549 | #Semi-sync模式下,成功的事务数
+——————————————–+———-+
14 rows in set (0.00 sec)

slave上显示如下:
mysql> show variables like ‘%rpl_semi%’;
+———————————+——-+
| Variable_name | Value |
+———————————+——-+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+———————————+——-+
2 rows in set (0.01 sec)

mysql> show status like ‘%semi%’;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| Rpl_semi_sync_slave_status | ON |
+—————————-+——-+
1 row in set (0.00 sec)

优化mysql slave的同步速度

测试环境:
Red Hat Enterprise Linux Server release 6.3 (Santiago)
Server version: 5.6.22-log MySQL Community Server (GPL)

我搭建了1主3从的环境,准备测试MHA架构,过程中发现,测试并发插入的时候,从库1可以跟上,从库2,3跟不上

如何判断是io thread慢还是 sql thread慢呢,有个方法,观察show slave status\G ,

判断3个参数(参数后面的值是默认空闲时候的正常值):
Slave_IO_State: Waiting for master to send event
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Seconds_Behind_Master: 0

1.sql thread慢的表现:
Seconds_Behind_Master越来越大
Slave_SQL_Running_State: Reading event from the relay log

2.io thread慢的表现:
Seconds_Behind_Master为0
Slave_SQL_Running_State: 显示正常值
Slave_IO_State:显示忙碌状态

而我观察到的值是
Slave_IO_State: Waiting for master to send event
Seconds_Behind_Master: 313
Slave_SQL_Running_State: Reading event from the relay log
因此推断是sql thread慢

为啥只有slave2,3慢,而slave1可以跟上呢,开始怀疑是参数配置的差异,比对/etc/my.cnf后发现,配置无差异
因此排除这个原因,后来用dstat观察,发现繁忙时候,slave的IO写速度上不去
slave1:
$ dstat
—-total-cpu-usage—- -dsk/total- -net/total- —paging– —system–
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
0 0 100 0 0 0|9308B 11k| 0 0 | 3B 3B| 63 63
3 4 54 40 0 0| 88k 10M| 45k 9438B| 0 0 |1857 2579
3 3 59 35 0 1| 80k 7552k| 40k 8486B| 0 0 |1675 2307
3 3 56 38 0 0| 72k 7824k| 42k 8816B| 0 0 |1727 2348
3 4 52 41 0 1| 96k 9688k| 49k 10k| 0 0 |2029 2874
3 4 54 39 0 0| 96k 8880k| 45k 9410B| 0 0 |1905 2674
3 3 53 40 0 1| 96k 9776k| 58k 10k| 0 0 |1935 2671
3 3 58 36 0 0| 64k 7848k| 40k 8420B| 0 0 |1724 2357
3 5 52 40 0 1| 96k 8936k| 49k 10k| 0 0 |1948 2680
3 4 51 42 0 1| 96k 9400k| 49k 10k| 0 0 |1988 2760
3 4 52 41 0 0| 88k 9752k| 49k 10k| 0 0 |2058 2868
4 4 51 41 0 1| 96k 9680k| 49k 9938B| 0 0 |1990 2750
3 3 59 35 0 0| 80k 7632k| 39k 8288B| 0 0 |1668 2275
3 4 52 42 0 1| 80k 8504k| 46k 9146B| 0 0 |1860 2523
3 4 51 42 0 0| 80k 8496k| 43k 8684B| 0 0 |1882 2516
2 3 65 30 0 0| 64k 5976k| 30k 6440B| 0 0 |1326 1802
3 4 53 40 0 1| 72k 8360k| 59k 10k| 0 0 |1859 2538
3 4 51 42 0 1| 96k 8840k| 53k 10k| 0 0 |1958 2648
2 4 51 43 0 0| 72k 7352k| 40k 7760B| 0 0 |1633 2219
3 4 51 42 0 1| 88k 7920k| 31k 6770B| 0 0 |1767 2373
3 3 54 40 0 0| 80k 8528k| 40k 8750B| 0 0 |1859 2549

slave2:
—-total-cpu-usage—- -dsk/total- -net/total- —paging– —system–
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
2 1 50 47 0 1|8192B 1168k| 55k 10k| 0 0 | 533 771
1 1 51 48 0 0|8192B 1048k| 33k 7046B| 0 0 | 427 622
1 1 51 48 0 0|8192B 1080k| 58k 9806B| 0 0 | 500 709
1 1 50 48 0 0| 0 1864k| 51k 8486B| 0 0 | 502 669
1 2 51 47 0 0|8192B 1120k| 42k 8156B| 0 0 | 496 674
1 1 51 47 0 0|8192B 1160k| 32k 6350B| 0 0 | 467 655
1 2 51 47 0 0| 0 1288k| 50k 10k| 0 0 | 563 797
1 1 51 47 0 0|8192B 1200k| 43k 8486B| 0 0 | 493 728
2 1 50 47 0 0|8192B 1024k| 45k 8816B| 0 0 | 481 659
1 1 50 48 0 0|8192B 1248k| 49k 9450B| 0 0 | 517 772
1 1 50 48 0 0| 0 1264k| 47k 9146B| 0 0 | 516 756
1 2 50 47 0 1|8192B 1144k| 50k 10k| 0 0 | 520 765
1 1 51 48 0 0|8192B 1200k| 51k 8156B| 0 0 | 484 716
1 2 50 48 0 0|8192B 968k| 50k 9278B| 0 0 | 470 684
1 1 50 48 0 0|8192B 1128k| 39k 7892B| 0 0 | 476 679
1 1 51 47 0 0| 0 1248k| 45k 9476B| 0 0 | 523 760
1 2 50 48 0 0|8192B 1448k| 41k 7826B| 0 0 | 552 805
1 1 50 48 0 0|8192B 1120k| 44k 8090B| 0 0 | 470 692

slave3:
—-total-cpu-usage—- -dsk/total- -net/total- —paging– —system–
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
1 1 50 49 0 0|8192B 1328k|1167B 170B| 0 0 | 385 515
1 1 51 48 0 0|8192B 1128k| 754B 170B| 0 0 | 325 449
1 1 50 49 0 0| 0 920k| 474B 314B| 0 0 | 279 381
0 1 50 49 0 0|8192B 664k|1633B 170B| 0 0 | 226 291
1 1 50 49 0 0|8192B 1200k|1250B 170B| 0 0 | 353 475
1 1 50 48 0 0| 0 1432k|1632B 170B| 0 0 | 402 551
1 1 51 48 0 0| 16k 1752k|1045B 170B| 0 0 | 487 664
1 1 50 48 0 0|8192B 1648k| 12k 170B| 0 0 | 461 636
1 1 51 48 0 0| 0 1272k| 886B 170B| 0 0 | 380 501
1 1 50 49 0 0|8192B 1000k|1023B 170B| 0 0 | 300 400
1 1 50 48 0 0|8192B 1096k| 747B 170B| 0 0 | 332 442
1 1 50 48 0 0|8192B 1448k|1003B 170B| 0 0 | 416 557
1 1 50 48 0 0| 0 1592k|1174B 170B| 0 0 | 450 614
1 1 51 48 0 0|8192B 1416k|1028B 170B| 0 0 | 404 552
0 1 50 49 0 0|8192B 1128k|1031B 170B| 0 0 | 331 447
1 1 51 48 0 0|8192B 1160k|1185B 170B| 0 0 | 340 458
1 1 50 49 0 0| 0 1120k| 633B 170B| 0 0 | 326 453
1 0 50 49 0 0|8192B 656k|8886B 170B| 0 0 | 221 288
1 1 50 49 0 0|8192B 1128k|1619B 170B| 0 0 | 335 451

slave1可以达到每秒9M的写入IO,而slave2,3只能达到每秒1M多,IO性能差很多,后来分析了下存储,发现是有很大差异的,也印证了我的推测

那么问题来了,要如何优化IO性能比较差的slave呢,其实很简单,修改两个参数
mysql> set global sync_binlog=20 ;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)

innodb_flush_log_at_trx_commit
如果innodb_flush_log_at_trx_commit设置为0,log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行.该模式下,在事务提交的时候,不会主动触发写入磁盘的操作。
如果innodb_flush_log_at_trx_commit设置为1,每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去.
如果innodb_flush_log_at_trx_commit设置为2,每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。
注意:
由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒”。

sync_binlog
sync_binlog 的默认值是0,像操作系统刷其他文件的机制一样,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
当sync_binlog =N (N>0) ,MySQL 在每写 N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。
注意:
如果启用了autocommit,那么每一个语句statement就会有一次写操作;否则每个事务对应一个写操作。
而且mysql服务默认是autocommit打开的

修改参数后,slave2,3也一样可以跟上slave1的速度了
slave2,3:
—-total-cpu-usage—- -dsk/total- -net/total- —paging– —system–
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
3 2 94 2 0 0| 32k 80k| 49k 10k| 0 0 |1042 658
3 2 94 1 0 1| 32k 72k| 49k 10k| 0 0 |1258 964
2 2 95 2 0 0| 32k 72k| 44k 9146B| 0 0 |1126 882
2 1 95 2 0 0| 32k 72k| 41k 8486B| 0 0 | 959 659
2 2 96 1 0 0| 32k 72k| 47k 9476B| 0 0 |1153 841
2 2 95 2 0 0| 24k 72k| 39k 8090B| 0 0 | 866 504
2 2 96 1 0 0| 24k 72k| 42k 7562B| 0 0 | 908 663
2 1 95 2 0 0| 40k 72k| 52k 10k| 0 0 |1084 685
3 1 94 2 0 1| 40k 80k| 54k 11k| 0 0 |1204 873
2 2 96 1 0 0| 16k 32k| 30k 6044B| 0 0 | 846 802
2 1 97 1 0 0| 24k 32k| 35k 7760B| 0 0 |1059 888
2 1 95 3 0 0| 32k 856k| 44k 9278B| 0 0 | 943 551
2 1 94 3 0 0| 32k 104k| 42k 8618B| 0 0 | 986 704
2 1 96 1 0 0| 24k 72k| 34k 7034B| 0 0 | 863 682
2 2 95 2 0 0| 32k 64k| 45k 8684B| 0 0 |1052 750
2 2 90 7 0 0| 24k 416k| 38k 7166B| 0 0 | 906 722
3 2 93 2 0 1| 32k 80k| 57k 10k| 0 0 |1069 829
3 2 94 1 0 0| 32k 72k| 42k 8486B| 0 0 |1076 942
2 1 96 1 0 0| 24k 72k| 37k 7496B| 0 0 | 859 575
2 2 94 2 0 1| 32k 64k| 43k 8684B| 0 0 |1138 1011
3 2 94 1 0 0| 32k 72k| 42k 9014B| 0 0 |1099 782
2 3 94 2 0 0| 32k 72k| 50k 10k| 0 0 |1332 1359
2 2 95 2 0 0| 24k 72k| 34k 6902B| 0 0 | 921 799
2 2 94 2 0 0| 40k 72k| 55k 11k| 0 0 |1318 1016
1 2 96 2 0 0| 32k 80k| 41k 8882B| 0 0 |1020 719
而且我观察到slave2,3的写入数量减少了两个数量级,从1M多下降到70k

© 2019 Init dba

Theme by Anders NorenUp ↑