Exadata X5 上测试单进程impdp导入数据的效率

联系:QQ(5163721)

标题:Exadata X5 上测试单进程impdp导入数据的效率

作者:Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

===========================================
单进程,每分钟: 16G(每小时960G)
7分钟,导完LUNAR_P201404_1
请注意下面的过程 parallel=1,表示单进程测试
(只能测试单进程,是因为跟我同事的导出方式有关系,明天测试多进程……)

===========================================

[oracle@dm01db01 lunar]$ nohup impdp LUNAR/passwd content=DATA_ONLY DIRECTORY=lunar JOB_NAME=impdp_LUNAR_P201404_1 parallel=1 TABLES=LUNAR.LUNAR DUMPFILE=LUNAR_P201404_01.dmp,LUNAR_P201404_02.dmp,LUNAR_P201404_03.dmp,LUNAR_P201404_04.dmp,LUNAR_P201404_05.dmp,LUNAR_P201404_06.dmp,LUNAR_P201404_07.dmp REMAP_TABLE=LUNAR.LUNAR:LUNAR_P201404_1 logfile=impdp_LUNAR_P201404_1.log &
[1] 26837
[oracle@dm01db01 lunar]$ nohup: ignoring input and appending output to `nohup.out'

[oracle@dm01db01 lunar]$ jobs
[1]+  Running                 nohup impdp LUNAR/passwd content=DATA_ONLY DIRECTORY=lunar JOB_NAME=impdp_LUNAR_P201404_1 parallel=1 TABLES=LUNAR.LUNAR DUMPFILE=LUNAR_P201404_01.dmp,LUNAR_P201404_02.dmp,LUNAR_P201404_03.dmp,LUNAR_P201404_04.dmp,LUNAR_P201404_05.dmp,LUNAR_P201404_06.dmp,LUNAR_P201404_07.dmp REMAP_TABLE=LUNAR.LUNAR:LUNAR_P201404_1 logfile=impdp_LUNAR_P201404_1.log &
[oracle@dm01db01 lunar]$ jobs
[1]+  Running                 nohup impdp LUNAR/passwd content=DATA_ONLY DIRECTORY=lunar JOB_NAME=impdp_LUNAR_P201404_1 parallel=1 TABLES=LUNAR.LUNAR DUMPFILE=LUNAR_P201404_01.dmp,LUNAR_P201404_02.dmp,LUNAR_P201404_03.dmp,LUNAR_P201404_04.dmp,LUNAR_P201404_05.dmp,LUNAR_P201404_06.dmp,LUNAR_P201404_07.dmp REMAP_TABLE=LUNAR.LUNAR:LUNAR_P201404_1 logfile=impdp_LUNAR_P201404_1.log &
[oracle@dm01db01 lunar]$ tail -f nohup.out 
Import: Release 11.2.0.4.0 - Production on Thu Apr 23 00:05:53 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "LUNAR"."IMPDP_LUNAR_P201404_1" successfully loaded/unloaded
Starting "LUNAR"."IMPDP_LUNAR_P201404_1":  LUNAR/******** content=DATA_ONLY DIRECTORY=lunar JOB_NAME=impdp_LUNAR_P201404_1 parallel=1 TABLES=LUNAR.LUNAR DUMPFILE=LUNAR_P201404_01.dmp,LUNAR_P201404_02.dmp,LUNAR_P201404_03.dmp,LUNAR_P201404_04.dmp,LUNAR_P201404_05.dmp,LUNAR_P201404_06.dmp,LUNAR_P201404_07.dmp REMAP_TABLE=LUNAR.LUNAR:LUNAR_P201404_1 logfile=impdp_LUNAR_P201404_1.log 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

导入过程中进行检测:
可以看到exadata聪明的选择了直接裸盘,没有走Flashcache,每秒钟吞吐量大概460MB左右(一起开始时候会有一个高峰大概有2倍的这个值,猜测是由于分配空间等等)

Current Time: Thu Apr 23 00:06:30 CST 2015               ONLY RECEIVED DATA FROM 4 NODES, INSTEAD OF 5 NODES!
               <---------------Disks------------------><---------------Flash------------------><--------CPU----------><-----------Memory------->
                MBRead Reads RSize MBWrit Writes WSize  MBRead Reads RSize MBWrit Writes WSize  User Sys Wait Irq Run  FreeMB SwapMB SwIn SwOut 
dm01cel01         0    38     3    221    913   248       0     0     0      0      0     0     5   4    0   0   3   49851      0    0     0 
dm01cel02         0    50     3    242    986   252       0     0     0      0      0     0     1   2    0   0   0   51249      0    0     0 
dm01cel03         0    39     3    245   1011   248       0     0     0      0      0     0     1   1    0   0   0   50961      0    0     0 
TotalIO: 708 MB/s;   DiskRead: 0 MB/s;  DiskWrite: 708 MB/s;   FlashRead: 0 MB/s;  FlashWrite: 0 MB/s;  Average CPU: 5%;
            <--------CPU----------><---------------Disks------------------><-----------Memory------->
             User Sys Wait Irq Run  MBRead Reads RSize MBWrit Writes WSize  FreeMB SwapMB SwIn SwOut 
dm01db01        2   1    0   0   1       3    21   153      0      8    26    2802      1    0     0 
Average CPU: 3%;

大概2~3分钟后,存储节点总的吞吐量稳定在每秒钟460MB:


Current Time: Thu Apr 23 00:08:24 CST 2015               ONLY RECEIVED DATA FROM 4 NODES, INSTEAD OF 5 NODES!
               <---------------Disks------------------><---------------Flash------------------><--------CPU----------><-----------Memory------->
                MBRead Reads RSize MBWrit Writes WSize  MBRead Reads RSize MBWrit Writes WSize  User Sys Wait Irq Run  FreeMB SwapMB SwIn SwOut 
dm01cel01         0    20     1    148    604   251       0     0     0      0      0     0     5   3    0   0   2   50039      0    0     0 
dm01cel02         0    26     1    152    627   248       0     0     0      0      0     0     1   0    0   0   2   51247      0    0     0 
dm01cel03         0    25     1    164    673   250       0     0     0      0      0     0     1   0    0   0   1   50958      0    0     0 
TotalIO: 464 MB/s;   DiskRead: 0 MB/s;  DiskWrite: 464 MB/s;   FlashRead: 0 MB/s;  FlashWrite: 0 MB/s;  Average CPU: 3%;
            <--------CPU----------><---------------Disks------------------><-----------Memory------->
             User Sys Wait Irq Run  MBRead Reads RSize MBWrit Writes WSize  FreeMB SwapMB SwIn SwOut 
dm01db01        1   1    0   0   2     124   502   253      0      5    69    1362      1    0     0 
Average CPU: 2%;

===========================================
测试结果:
在数据库中实测的数据(按照每分钟这个表的增长大小来计算)
单进程,导入速度每分钟: 16G
7分钟,导完LUNAR_P201404_1,该表大概309GB(按照这个测试,每小时大概2.5TB左右)

===========================================


SYS@lunar1>set timing on
SYS@lunar1>set time on
00:07:25 SYS@lunar1>col segment_name format a45 heading "Segment Name"
00:07:32 SYS@lunar1>select sum(bytes)/1024/1024/1024     "Size In GB"
00:07:32   2  from dba_segments
00:07:32   3  where owner in upper('LUNAR')
00:07:32   4  order by 1;

      Size In GB
----------------
  236.7705078125

Elapsed: 00:00:00.13

      Size In GB
----------------
  252.2080078125

Elapsed: 00:00:00.03
00:08:31 SYS@lunar1>
。。。。。
00:13:25 SYS@lunar1>/

      Size In GB
----------------
309.794738769531  --------------改表总共309GB

Elapsed: 00:00:00.03
00:13:27 SYS@lunar1>

[oracle@dm01db01 lunar]$ tail -f nohup.out 
Import: Release 11.2.0.4.0 - Production on Thu Apr 23 00:05:53 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "LUNAR"."IMPDP_LUNAR_P201404_1" successfully loaded/unloaded
Starting "LUNAR"."IMPDP_LUNAR_P201404_1":  LUNAR/******** content=DATA_ONLY DIRECTORY=lunar JOB_NAME=impdp_LUNAR_P201404_1 parallel=1 TABLES=LUNAR.LUNAR DUMPFILE=LUNAR_P201404_01.dmp,LUNAR_P201404_02.dmp,LUNAR_P201404_03.dmp,LUNAR_P201404_04.dmp,LUNAR_P201404_05.dmp,LUNAR_P201404_06.dmp,LUNAR_P201404_07.dmp REMAP_TABLE=LUNAR.LUNAR:LUNAR_P201404_1 logfile=impdp_LUNAR_P201404_1.log 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "LUNAR"."LUNAR_P201404_1":"LUNAR_P201404_5"  25.19 GB 69742631 rows
. . imported "LUNAR"."LUNAR_P201404_1":"LUNAR_P201404_1"      0 KB       0 rows
. . imported "LUNAR"."LUNAR_P201404_1":"LUNAR_P201404_4"  25.11 GB 69877929 rows
. . imported "LUNAR"."LUNAR_P201404_1":"LUNAR_P201404_3"  24.34 GB 67662725 rows
. . imported "LUNAR"."LUNAR_P201404_1":"LUNAR_P201404_2"  17.17 GB 47756673 rows
Job "LUNAR"."IMPDP_LUNAR_P201404_1" successfully completed at Thu Apr 23 00:12:55 2015 elapsed 0 00:07:02

^C
[1]+  Done                    nohup impdp LUNAR/passwd content=DATA_ONLY DIRECTORY=lunar JOB_NAME=impdp_LUNAR_P201404_1 parallel=1 TABLES=LUNAR.LUNAR DUMPFILE=LUNAR_P201404_01.dmp,LUNAR_P201404_02.dmp,LUNAR_P201404_03.dmp,LUNAR_P201404_04.dmp,LUNAR_P201404_05.dmp,LUNAR_P201404_06.dmp,LUNAR_P201404_07.dmp REMAP_TABLE=LUNAR.LUNAR:LUNAR_P201404_1 logfile=impdp_LUNAR_P201404_1.log
[oracle@dm01db01 lunar]$ 


===========================================
注意上面: elapsed 0 00:07:02 也就是说,7分钟导入数据309GB

此条目发表在 POC和性能调整 分类目录,贴了 , 标签。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注