转载

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

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

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

正文到此结束
Loading...