前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >这样做，RMAN备份速度可提高6倍！

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

作者头像
数据和云
发布于 2020-06-01 15:03:45
1.4K0
发布于 2020-06-01 15:03:45
举报
文章被收录于专栏：数据和云数据和云

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

数据库现状

代码语言：javascript
复制
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE  11.2.0.4.0  Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> set pagesize 0;
select tablespace_name ,sum(bytes)/1024/1024/1024 as GB from dba_data_files group by tablespace_name;
SQL> CCCC10             66.7578125
SYSAUX         .6640625
UNDOTBS1           4.83886719
CCCC3             165.399414
CCCC8             66.7578125
CCCC1             165.399414
CCCC7             66.7578125
USERS             .004882813
CCCC6             165.399414
SYSTEM         .7421875
CCCC2             165.399414
CCCC4             165.399414
CCCC5             165.399414
CCCC9             66.7578125
CCCC11             66.7578125
SQL> 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

数据文件大小

代码语言：javascript
复制
 du -sh  /test-nvme/oradata/orcl
1.4T  /test-nvme/oradata/orcl

修改linux内核参数shmmax和shmall

修改之前共享内存

代码语言：javascript
复制
[oracle@base-test-01 ~]$ ipcs -m

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

修改之前linux内核参数

代码语言：javascript
复制
$ 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 = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
vm.swappiness=1
[oracle@base-test-01 ~]$

修改之后共享内存

代码语言：javascript
复制
[root@base-test-01 oracle]# ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 504037376  oracle     640        536870912  72
0x00000000 504070145  oracle     640        107911053312 72
0x42e38fd0 504102915  oracle     640        2097152    72
0x00000000 1212420    root       644        80         2
0x00000000 1245189    root       644        16384      2
0x00000000 1277958    root       644        280        2
0x00000000 297435143  root       600        524288     2          dest
0x00000000 508821513  root       600        832920     2          dest
0x00000000 297762851  root       600        4194304    2          dest
0x00000000 297861156  root       600        524288     2          dest
#  sysresv -l orcl -d on

IPC Resources for ORACLE_SID "orcl" :
Shared Memory:
ID    KEY
504037376  0x00000000
504070145  0x00000000
504102915  0x42e38fd0

修改之后linux内核参数

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

前后对比

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

改成异步IO

代码语言：javascript
复制
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_OFF
SQL>   alter system set filesystemio_options=setall  scope=spfile;

System altered.
SQL> STARTUP FORCE;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.0796E+11 bytes
Fixed Size        2266024 bytes
Variable Size     5.6371E+10 bytes
Database Buffers   5.1540E+10 bytes
Redo Buffers       50450432 bytes
Database 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中的时间提示最小单位从天改到秒：

代码语言：javascript
复制
export NLS_DATE_FORMAT=‘YYYY-MM-DD HH24:MI:SS’
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK   # 或：export NLS_LANG=AMERICAN

rman备份

代码语言：javascript
复制
RMAN>  backup validate database;

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

RMAN>

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

从os层查看io情况

代码语言：javascript
复制
$ iostat 60 nvme0n1

Device:            tps    kB_read/s    kB_wrtn/s   kB_read    kB_wrtn
nvme0n1        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_wrtn
nvme0n1        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_wrtn
nvme0n1        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  %util
nvme0n1           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  %util
nvme0n1           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备份

代码语言：javascript
复制
[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:47
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9391 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/test-nvme/oradata/orcl/cmcc1.dbf
input datafile file number=00006 name=/test-nvme/oradata/orcl/cmcc2.dbf
input datafile file number=00007 name=/test-nvme/oradata/orcl/cmcc3.dbf
input datafile file number=00008 name=/test-nvme/oradata/orcl/cmcc4.dbf
input datafile file number=00009 name=/test-nvme/oradata/orcl/cmcc5.dbf
input datafile file number=00010 name=/test-nvme/oradata/orcl/cmcc6.dbf
input datafile file number=00011 name=/test-nvme/oradata/orcl/cmcc7.dbf
input datafile file number=00012 name=/test-nvme/oradata/orcl/cmcc8.dbf
input datafile file number=00013 name=/test-nvme/oradata/orcl/cmcc9.dbf
input datafile file number=00014 name=/test-nvme/oradata/orcl/cmcc10.dbf
input datafile file number=00015 name=/test-nvme/oradata/orcl/cmcc11.dbf
input datafile file number=00003 name=/test-nvme/oradata/orcl/undotbs01.dbf
input datafile file number=00001 name=/test-nvme/oradata/orcl/system01.dbf
input datafile file number=00002 name=/test-nvme/oradata/orcl/sysaux01.dbf
input datafile file number=00004 name=/test-nvme/oradata/orcl/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:15
List 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 set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2
Control File OK     0              3146
Finished backup at 2020-02-26 18:34:04

5分钟15秒完成。

从OS层看磁盘的IO性能

代码语言：javascript
复制
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nvme0n1           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  %util
nvme0n1           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  %util
nvme0n1           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  %util
nvme0n1           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/s

4607693.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数据库多年。

本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2020-05-25，如有侵权请联系 cloudcommunity@tencent.com 删除
oracle
linux
数据库
sql
云数据库 SQL Server

本文分享自 数据和云 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ，欢迎热爱写作的你一起参与！

oracle
linux
数据库
sql
云数据库 SQL Server
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • 数据库现状
  • 修改linux内核参数shmmax和shmall
    • 修改之前共享内存
      • 修改之前linux内核参数
        • 修改之后共享内存
          • 修改之后linux内核参数
            • 前后对比
            • 改成异步IO
            • 修改成hugepage
            • 没有优化前备份性能
            • 优化后的备份性能
            相关产品与服务
            云数据库 SQL Server
            腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
            产品介绍产品文档
            云数据库采购特惠，高性能、高可靠、高安全，超值优惠等你享！
            领券

            腾讯云开发者

            扫码关注腾讯云开发者

            扫码关注腾讯云开发者

            领取腾讯云代金券

            热门产品

            热门推荐

            更多推荐

            Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

            深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

            腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

            问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

            Copyright © 2013 - 2024 Tencent Cloud.

            All Rights Reserved. 腾讯云 版权所有

            登录 后参与评论