关于 Exadata 一体机（四分之一 配） Rman备份传输速度问题

XuLiQiang

大家好！！

Exadata 一体机配置明细:

os 系统版本 linux 5u8
数据库版本:rdbms 11.2.0.3 grid:11.2.0.3

我的数据库采用Rman备份:每周日 0级全备 每周一到周六执行1级差异增量备份(所用的备份采用压缩形式)
目前总数据量为:1.6T 一次rman L0 备份要17-19小时，输出速率为4M/S
备份文件存储于asm磁盘组(NORMAL冗余)。

问题:Exadata 不是有40G/s的传输速度吗？ 为什么会这么慢呢,我该如何下手去分析呢？请各位大师指点。

Maclean Liu(刘相兵

请至少做一个exachk我们看一下， 备份输出速度为4M/s不代表 吞吐量即为4M/s

没有数据的话，猜测是没有价值的

XuLiQiang

你好 刘大！！

     这是我做的一体机健康检查，烦请帮忙分析一下  谢谢！

Maclean Liu(刘相兵

ODM FINGDING:

Database Server
Status         Type         Message         Status On         Details
FAIL        OS Check        Database control files are not configured as recommended        All Database Servers        View
FAIL        Patch Check        System may be exposed to Exadata Critical Issue DB11        All Homes        View
FAIL        OS Check        Database Server Physical Drive Configuration does not meet recommendation        All Database Servers        View
FAIL        SQL Parameter Check        Database parameter USE_LARGE_PAGES is NOT set to recommended value        All Instances        View
FAIL        SQL Parameter Check        Database parameter GLOBAL_NAMES is NOT set to recommended value        All Instances        View
FAIL        OS Check        InfiniBand network error counters are non-zero        All Database Servers        View
FAIL        SQL Check        Some data or temp files are not autoextensible        All Databases        View
FAIL        SQL Parameter Check        Database parameter _lm_rcvr_hang_allow_time is NOT set to the recommended value        All Instances        View
FAIL        SQL Parameter Check        Database parameter _kill_diagnostics_timeout is not set to recommended value        All Instances        View
WARNING        OS Check        All voting disks are not online        All Database Servers        View
WARNING        SQL Check        Some tablespaces are not using Automatic segment storage management.        All Databases        View
INFO        OS Check        ASM griddisk,diskgroup and Failure group mapping not checked.        All Database Servers        View
Storage Server
Status         Type         Message         Status On         Details
FAIL        Storage Server Check        The griddisk ASM status should match specification        dm01cel01        View
FAIL        Storage Server Check        The celldisk configuration on disk drives should match Oracle best practices        dm01cel01        View
FAIL        Storage Server Check        one or storage server has open critical alerts.        All Storage Servers        View
FAIL        Storage Server Check        Storage Server alerts are not configured to be sent via email        All Storage Servers        View
WARNING        Storage Server Check        Free space in root(/) filesystem is less than recommended on one or more storage servers.        All Storage Servers        View


粗略看了下 我认为cell存在问题的可能性不大


关于该问题：

1、上传一份备份当时的AWR
2、能否做一个calibrate看一下 包括cell上 以及DB NODE上的io calibrate

XuLiQiang

cell上 io calibrate
参考您总结的 “Calibrate测试Exadata IO”  命令 calibrate force;

DB NODE上的io calibrate
参考您总结的 “Calibrate测试Exadata IO”  DBMS_RESOURCE_MANAGER.CALIBRATE_IO

可以得到测试结果吗？？

XuLiQiang

刘老师

AWR 报告分别做了两天的

O calibrate 测试结果 烦请指点。

XuLiQiang

期待刘大指点

Maclean Liu(刘相兵

IOStat by Function summary

• 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
• ordered by (Data Read + Write) desc
  

Function Name	Reads: Data	Reqs per sec	Data per sec	Writes: Data	Reqs per sec	Data per sec	Waits: Count	Avg Tm(ms)
RMAN	2.6T	383.64	382.284	29.4G	1.47	4.19177	9175	3.08
Buffer Cache Reads	119.8G	258.35	17.0705	0M	0.00	0M	843.7K	1.98
Others	5.2G	7.77	.744432	4.2G	3.84	.600972	60.4K	1.53
DBWR	0M	0.00	0M	1.6G	15.36	.223886	7	12.71
LGWR	1M	0.01	.000139	1.4G	15.35	.203710	108.6K	0.03
Direct Writes	0M	0.00	0M	988M	0.68	.137476	0
Smart Scan	907M	0.13	.126205	0M	0.00	0M	0
Direct Reads	9M	0.16	.001252	1M	0.01	.000139	0
TOTAL:	2.7T	650.05	400.226	37.6G	36.71	5.35796	1021.9K	1.76

Maclean Liu(刘相兵

21:00~ 23:00 RMAN        读取了2.6T 数据，写出29.4G

这个时间段是增量备份？


还有

DECLARE
   lat    INTEGER;
   iops   INTEGER;
   mbps   INTEGER;
BEGIN
   DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2,
                                       20,
                                       iops,
                                       mbps,
                                       lat);
   DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
   DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
   DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || mbps);
END;
/

max_iops = 3663
latency = 18
max_mbps = 2505


你试试
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (20, 15, iops, mbps, lat);的话输出是多少

就你给出的结果 XD上的 IO不太好

XuLiQiang

请问刘老师
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (20, 15, iops, mbps, lat);
这个20 是指20个物理磁盘吗？

clot09

DBMS_RESOURCE_MANAGER.CALIBRATE_IO (
   num_physical_disks      IN  PLS_INTEGER DEFAULT 1,
   max_latency             IN  PLS_INTEGER DEFAULT 20,
   max_iops                OUT PLS_INTEGER,
   max_mbps                OUT PLS_INTEGER,
   actual_latency          OUT PLS_INTEGER);


Parameter        Description
num_physical_disks
Approximate number of physical disks in the database storage

max_latency
Maximum tolerable latency in milliseconds for database-block-sized IO requests

max_iops
Maximum number of I/O requests per second that can be sustained. The I/O requests are randomly-distributed, database-block-sized reads.

max_mbps
Maximum throughput of I/O that can be sustained, expressed in megabytes per second. The I/O requests are randomly-distributed, 1 megabyte reads.

actual_latency
Average latency of database-block-sized I/O requests at max_iops rate, expressed in milliseconds

XuLiQiang

think you clot09, how are you think my question ?

XuLiQiang

刘大师:
rman >show all:
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name MCSDB are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 3;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 30 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE '11.2.0.3.0' OPTIMIZE FOR LOAD TRUE;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DBFS_DG/dm01-cluster/CONTROLFILE_SNAPSHOP/snapcf_MCSDB.f';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DBFS_DG/dm01-cluster/controlfile_snapshop/snapcf_mcsdb.f';

rman backup script：
#!/bin/sh

FTAG=`date +%Y_%m_%d`"_incre1_bak_df"
ATAG=`date +%Y_%m_%d`"_incre1_bak_ar"
mkdir -p $1
echo "----RMAN WILL BE STARTED---ON: "`date +%Y%m%d%H%M%S` >> $1/incre_lev1.log
rman log=$1/incre_lev1.log append << EOF
connect target /
run {
crosscheck backup;
crosscheck archivelog all;

backup as compressed backupset incremental level 1 tag=tag$FTAG database;
sql "ALTER SYSTEM ARCHIVE LOG CURRENT";
backup archivelog all tag=tag$ATAG delete input;

DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE;
}
list backup summary;
exit;
EOF
echo "----RMAN backup FINISHED!---ON: "`date +%Y%m%d%H%M%S` >> $1/incre_lev1.log

DECLARE
   lat    INTEGER;
   iops   INTEGER;
   mbps   INTEGER;
BEGIN
    DBMS_RESOURCE_MANAGER.CALIBRATE_IO (20, 15, iops, mbps, lat);
   DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
   DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
   DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || mbps);
END;
/

max_iops = 2735
latency = 15
max_mbps = 2602

请刘老师指点

Maclean Liu(刘相兵

1、是否启用了block change tracking?
2、尝试启动BCT 后再次备份看看
3、这个测出的IO不好 建议你找XD的售后看下

XuLiQiang

该问题通过修改Rman configure
rman >show all:
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name MCSDB are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 3;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 30 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE '11.2.0.3.0' OPTIMIZE FOR LOAD TRUE;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DBFS_DG/dm01-cluster/CONTROLFILE_SNAPSHOP/snapcf_MCSDB.f';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DBFS_DG/dm01-cluster/controlfile_snapshop/snapcf_mcsdb.f';

将原来由HIGH 改为 BASIC,输出速度已到达17M/s
        备份大小        输出大小        输出速度        备份时间
原备份统计       
        143.21G          41.38G        4.57M/s        2:34:38
        109.39G        32.98G        3.97M/s        2:21:40
        91.53G        31.18G        4.93M/s        1:47:56
        1.57T        253.88G        4.05M/s        17:49:59
        1.58T        256.66G        4.05M/s        18:02:52
现备份统计       
        91.92G      43.68G           17.83M/s        0:41:48
        98.52G        46.91G        18.55M/s        0:43:09
        100.36G        45.83G        18.57M/s        0:42:08
        1.46T        268.79G        17.22M/s        4:26:22

Maclean Liu(刘相兵

我觉得 HIGH 改为BASIC 输出速率增长 说明可能主要的瓶颈不在备份集写出上，由于实际压缩率的存在 这样看4M/s 到17m/s 并没有提升备份的效率。

XuLiQiang

以老师的见解，还是需要对Exadata 硬件整体做分析？