🔥 热搜 🔥
上海习近平新疆鄂州父女瓜乌鲁木齐疫情H工口小学生赛高习明泽芊川一笑图包印尼排华
分类
社会娱乐国际人权科技经济其它
🔥 热搜 🔥
上海习近平新疆鄂州父女瓜乌鲁木齐疫情H工口小学生赛高习明泽芊川一笑图包印尼排华
分类
社会娱乐国际人权科技经济其它
查看原文
其他

这样做，RMAN备份速度可提高6倍！

IT服务圈儿 2022-09-11

The following article is from 数据和云 Author 姚远

IT服务圈儿

有温度、有态度的IT自媒体平台


作者：姚远

本文经公众号：数据和云（ID：OraNews）授权转载，如需转载请联系出处


导读：本文来自读者投稿，详述一则给客户备份优化的实际案例，通过这次优化，数据库性能提高了6倍。


数据库现状

SQL> select * from v$version;
BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - ProductionSQL> set pagesize 0;select tablespace_name ,sum(bytes)/1024/1024/1024 as GB from dba_data_files group by tablespace_name;SQL> CCCC10 66.7578125SYSAUX .6640625UNDOTBS1 4.83886719CCCC3 165.399414CCCC8 66.7578125CCCC1 165.399414CCCC7 66.7578125USERS .004882813CCCC6 165.399414SYSTEM .7421875CCCC2 165.399414CCCC4 165.399414CCCC5 165.399414CCCC9 66.7578125CCCC11 66.7578125SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';Session altered.SQL> select max(LAST_ANALYZED) from dba_tables;2020-02-25 22:20:26

数据文件大小

du -sh /test-nvme/oradata/orcl1.4T /test-nvme/oradata/orcl

修改linux内核参数shmmax和shmall


修改之前共享内存

[oracle@base-test-01 ~]$ ipcs -m
------ Shared Memory Segments --------key shmid owner perms bytes nattch status0x00000000 114458624 oracle 640 4096 00x00000000 114491393 oracle 640 4096 00x00000000 114524163 oracle 640 4096 00x00000000 1212420 root 644 80 20x00000000 1245189 root 644 16384 20x00000000 1277958 root 644 280 20x00000000 297435143 root 600 524288 2 dest0x00000000 114556936 oracle 640 4096 00x00000000 114589705 oracle 640 4096 00x00000000 114622474 oracle 640 4096 00x00000000 114655243 oracle 640 4096 00x00000000 114688012 oracle 640 4096 00x00000000 114720781 oracle 640 4096 00x00000000 114753550 oracle 640 4096 00x00000000 114786319 oracle 640 4096 00x00000000 114819088 oracle 640 4096 00x00000000 114851857 oracle 640 4096 00x00000000 114884626 oracle 640 4096 00x00000000 114917395 oracle 640 4096 00x00000000 114950164 oracle 640 4096 00x00000000 114982933 oracle 640 4096 00x00000000 115015702 oracle 640 4096 00x00000000 115048471 oracle 640 4096 00x00000000 115081240 oracle 640 4096 00x00000000 115114009 oracle 640 4096 00x00000000 115146778 oracle 640 4096 00x00000000 115179547 oracle 640 4096 00x00000000 115212316 oracle 640 4096 00x00000000 115245085 oracle 640 4096 00x00000000 115277854 oracle 640 4096 00x00000000 115310623 oracle 640 4096 00x00000000 115343392 oracle 640 4096 00x00000000 115376161 oracle 640 4096 00x00000000 115408930 oracle 640 4096 00x00000000 297762851 root 600 4194304 2 dest0x00000000 297861156 root 600 524288 2 dest0x00000000 115441701 oracle 640 4096 00x00000000 115474470 oracle 640 4096 00x00000000 115507239 oracle 640 4096 00x00000000 115540008 oracle 640 4096 00x42e38fd0 115572777 oracle 640 4096 00x00000000 127959083 root 600 832920 2 dest

修改之前linux内核参数

$ cat /etc/sysctl.conf# sysctl settings are defined through files in# /usr/lib/sysctl.d/, /run/sysctl.d/, and /etc/sysctl.d/.## Vendors settings live in /usr/lib/sysctl.d/.# To override a whole file, create a new file with the same in# /etc/sysctl.d/ and put new settings there. To override# only specific settings, add a file with a lexically later# name in /etc/sysctl.d/ and put new settings there.## For more information, see sysctl.conf(5) and sysctl.d(5).fs.aio-max-nr = 1048576fs.file-max = 6815744kernel.shmall = 2097152kernel.shmmax = 4294967295kernel.shmmni = 4096kernel.sem = 250 32000 100 128net.ipv4.ip_local_port_range = 9000 65500net.core.rmem_default = 262144net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576vm.swappiness=1[oracle@base-test-01 ~]$

修改之后共享内存

[root@base-test-01 oracle]# ipcs -m
------ Shared Memory Segments --------key shmid owner perms bytes nattch status0x00000000 504037376 oracle 640 536870912 720x00000000 504070145 oracle 640 107911053312 720x42e38fd0 504102915 oracle 640 2097152 720x00000000 1212420 root 644 80 20x00000000 1245189 root 644 16384 20x00000000 1277958 root 644 280 20x00000000 297435143 root 600 524288 2 dest0x00000000 508821513 root 600 832920 2 dest0x00000000 297762851 root 600 4194304 2 dest0x00000000 297861156 root 600 524288 2 dest# sysresv -l orcl -d on
IPC Resources for ORACLE_SID "orcl" :Shared Memory:ID KEY504037376 0x00000000504070145 0x00000000504102915 0x42e38fd0

修改之后linux内核参数

[root@base-test-01 oracle]# sysctl -pfs.aio-max-nr = 1048576fs.file-max = 6815744kernel.shmall = 67108864kernel.shmmax = 256000000000kernel.shmmni = 4096kernel.sem = 250 32000 100 128net.ipv4.ip_local_port_range = 9000 65500net.core.rmem_default = 262144net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576vm.swappiness = 1

前后对比


Oracle的共享内存从35个减少到3个，碎片大大减少。


改成异步IO

SQL> SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I WHERE F.FILE#=I.FILE_NO AND FILETYPE_NAME='Data File';/test-nvme/oradata/orcl/system01.dbf ASYNC_OFF/test-nvme/oradata/orcl/sysaux01.dbf ASYNC_OFF/test-nvme/oradata/orcl/undotbs01.dbf ASYNC_OFF/test-nvme/oradata/orcl/users01.dbf ASYNC_OFF/test-nvme/oradata/orcl/cccc1.dbf ASYNC_OFF/test-nvme/oradata/orcl/cccc2.dbf ASYNC_OFF/test-nvme/oradata/orcl/cccc3.dbf ASYNC_OFF/test-nvme/oradata/orcl/cccc4.dbf ASYNC_OFF/test-nvme/oradata/orcl/cccc5.dbf ASYNC_OFF/test-nvme/oradata/orcl/cccc6.dbf ASYNC_OFF/test-nvme/oradata/orcl/cccc7.dbf ASYNC_OFF/test-nvme/oradata/orcl/cccc8.dbf ASYNC_OFF/test-nvme/oradata/orcl/cccc9.dbf ASYNC_OFF/test-nvme/oradata/orcl/cmcc10.dbf ASYNC_OFF/test-nvme/oradata/orcl/cmcc11.dbf ASYNC_OFFSQL> alter system set filesystemio_options=setall scope=spfile;
System altered.SQL> STARTUP FORCE;ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceORACLE instance started.Total System Global Area 1.0796E+11 bytesFixed Size 2266024 bytesVariable Size 5.6371E+10 bytesDatabase Buffers 5.1540E+10 bytesRedo Buffers 50450432 bytesDatabase mounted.Database opened.SQL> SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I WHERE F.FILE#=I.FILE_NO AND FILETYPE_NAME='Data File';/test-nvme/oradata/orcl/system01.dbf ASYNC_ON/test-nvme/oradata/orcl/sysaux01.dbf ASYNC_ON/test-nvme/oradata/orcl/undotbs01.dbf ASYNC_ON/test-nvme/oradata/orcl/users01.dbf ASYNC_ON/test-nvme/oradata/orcl/cmcc1.dbf ASYNC_ON/test-nvme/oradata/orcl/cmcc2.dbf ASYNC_ON/test-nvme/oradata/orcl/cmcc3.dbf ASYNC_ON/test-nvme/oradata/orcl/cmcc4.dbf ASYNC_ON/test-nvme/oradata/orcl/cmcc5.dbf ASYNC_ON/test-nvme/oradata/orcl/cmcc6.dbf ASYNC_ON/test-nvme/oradata/orcl/cmcc7.dbf ASYNC_ON/test-nvme/oradata/orcl/cmcc8.dbf ASYNC_ON/test-nvme/oradata/orcl/cmcc9.dbf ASYNC_ON/test-nvme/oradata/orcl/cmcc10.dbf ASYNC_ON/test-nvme/oradata/orcl/cmcc11.dbf ASYNC_ON
15 rows selected.
SQL>

修改成hugepage


过程比较复杂，另外写一个文档。


没有优化前备份性能


修改环境变量，将rman中的时间提示最小单位从天改到秒：

export NLS_DATE_FORMAT=‘YYYY-MM-DD HH24:MI:SS’export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK # 或：export NLS_LANG=AMERICAN

rman备份

RMAN> backup validate database;
Starting backup at 2020-02-26 14:42:44using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00005 name=/test-nvme/oradata/orcl/cmcc1.dbfinput datafile file number=00006 name=/test-nvme/oradata/orcl/cmcc2.dbfinput datafile file number=00007 name=/test-nvme/oradata/orcl/cmcc3.dbfinput datafile file number=00008 name=/test-nvme/oradata/orcl/cmcc4.dbfinput datafile file number=00009 name=/test-nvme/oradata/orcl/cmcc5.dbfinput datafile file number=00010 name=/test-nvme/oradata/orcl/cmcc6.dbfinput datafile file number=00011 name=/test-nvme/oradata/orcl/cmcc7.dbfinput datafile file number=00012 name=/test-nvme/oradata/orcl/cmcc8.dbfinput datafile file number=00013 name=/test-nvme/oradata/orcl/cmcc9.dbfinput datafile file number=00014 name=/test-nvme/oradata/orcl/cmcc10.dbfinput datafile file number=00015 name=/test-nvme/oradata/orcl/cmcc11.dbfinput datafile file number=00003 name=/test-nvme/oradata/orcl/undotbs01.dbfinput datafile file number=00001 name=/test-nvme/oradata/orcl/system01.dbfinput datafile file number=00002 name=/test-nvme/oradata/orcl/sysaux01.dbfinput datafile file number=00004 name=/test-nvme/oradata/orcl/users01.dbfchannel ORA_DISK_1: backup set complete, elapsed time: 00:31:25.........
RMAN>

从14:42:44开始到15:14:11结束，31分钟27秒，一共1887秒。


从os层查看io情况

$ iostat 60 nvme0n1
Device:            tps    kB_read/s    kB_wrtn/s   kB_read    kB_wrtnnvme0n1 5091.10 650829.00 93.44 39049740 5606
avg-cpu: %user %nice %system %iowait %steal %idle 0.84 0.00 2.11 0.75 0.00 96.30
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtnnvme0n1 5483.45 701698.13 11.38 42101888 683
avg-cpu: %user %nice %system %iowait %steal %idle 0.93 0.00 2.20 0.72 0.00 96.15
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtnnvme0n1 5589.93 715302.93 12.60 42918176 756
[oracle@base-test-01 ~]$ iostat -x 60 nvme0n1

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %utilnvme0n1 0.00 0.00 5255.30 0.93 672465.47 9.53 255.88 1.24 0.24 0.24 0.00 0.16 83.82
avg-cpu: %user %nice %system %iowait %steal %idle 0.77 0.00 2.11 0.73 0.00 96.39
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %utilnvme0n1 0.00 0.00 5425.92 1.08 694424.67 11.71 255.92 1.25 0.23 0.23 0.05 0.16 85.25

优化后的备份性能


rman备份

[oracle@base-test-01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Feb 26 18:28:40 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1560763823)
RMAN> backup validate database;
Starting backup at 2020-02-26 18:28:47using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=9391 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00005 name=/test-nvme/oradata/orcl/cmcc1.dbfinput datafile file number=00006 name=/test-nvme/oradata/orcl/cmcc2.dbfinput datafile file number=00007 name=/test-nvme/oradata/orcl/cmcc3.dbfinput datafile file number=00008 name=/test-nvme/oradata/orcl/cmcc4.dbfinput datafile file number=00009 name=/test-nvme/oradata/orcl/cmcc5.dbfinput datafile file number=00010 name=/test-nvme/oradata/orcl/cmcc6.dbfinput datafile file number=00011 name=/test-nvme/oradata/orcl/cmcc7.dbfinput datafile file number=00012 name=/test-nvme/oradata/orcl/cmcc8.dbfinput datafile file number=00013 name=/test-nvme/oradata/orcl/cmcc9.dbfinput datafile file number=00014 name=/test-nvme/oradata/orcl/cmcc10.dbfinput datafile file number=00015 name=/test-nvme/oradata/orcl/cmcc11.dbfinput datafile file number=00003 name=/test-nvme/oradata/orcl/undotbs01.dbfinput datafile file number=00001 name=/test-nvme/oradata/orcl/system01.dbfinput datafile file number=00002 name=/test-nvme/oradata/orcl/sysaux01.dbfinput datafile file number=00004 name=/test-nvme/oradata/orcl/users01.dbfchannel ORA_DISK_1: backup set complete, elapsed time: 00:05:15List of Datafiles=================File Status Marked Corrupt Empty Blocks Blocks Examined High SCN---- ------ -------------- ------------ --------------- ----------1 OK 0 15274 97280 10829000 File Name: /test-nvme/oradata/orcl/system01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 65126 Index 0 13578 Other 0 3302......
channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01List of Control File and SPFILE===============================File Type Status Blocks Failing Blocks Examined------------ ------ -------------- ---------------SPFILE OK 0 2Control File OK 0 3146Finished backup at 2020-02-26 18:34:04

5分钟15秒完成。


从OS层看磁盘的IO性能

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %utilnvme0n1 0.00 0.02 35998.80 0.45 4607693.07 5.99 255.99 90.92 2.53 2.53 0.41 0.03 100.00
avg-cpu: %user %nice %system %iowait %steal %idle 1.74 0.00 1.23 0.00 0.00 97.03
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %utilnvme0n1 0.00 0.00 35901.82 0.45 4595201.33 5.73 255.98 90.03 2.51 2.51 0.44 0.03 100.00
avg-cpu: %user %nice %system %iowait %steal %idle 1.78 0.00 1.24 0.01 0.00 96.98
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %utilnvme0n1 0.00 0.00 36015.75 0.52 4609726.14 5.54 255.98 88.58 2.46 2.46 0.26 0.03 100.00
avg-cpu: %user %nice %system %iowait %steal %idle 1.66 0.00 1.19 0.00 0.00 97.14
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %utilnvme0n1           0.00     0.00 36069.02    0.58 4616505.08     5.75   255.98    91.41    2.53    2.53    0.23   0.03 100.00


对比分析


项目优化前优化后提高
1.4T全量备份时间31分钟27秒5分钟15秒提高5.99倍
读nvme磁盘的速度672465.47rkB/s4607693.07rkB/s提高6.8倍
iostate中磁盘利用率80%100%提高20%

  1. 将同步IO改成异步IO，大大提高IO性能；

  2. 修改linux内核参数shmmax和shmall，将共享内存段从35个减少到3个，碎片大大减少；

  3. 配置hugepage，Oracle的共享内存管理单位从4K增加到2M，性能大幅提高。


作者：姚远，Oracle 10G和12C OCM，MySQL 5.6 和 5.7 OCP，现在鼎甲公司任顾问，专注于Oracle，MySQL数据库多年。


墨天轮原文链接：https://www.modb.pro/db/25395（复制到浏览器中打开或者点击“阅读原文”）



*版权声明：转载文章和图片均来自公开网络，版权归作者本人所有，推送文章除非无法确认，我们都会注明作者和来源。如果出处有误或侵犯到原作者权益，请与我们联系删除或授权事宜。

您可能也对以下帖子感兴趣

文章有问题？点此查看未经处理的缓存