联系: 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%;
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