performance improvements for dbimport?
Posted in 2010
I'm running IDS Server 11.50.FC5W4 using Solaris SPARC 10 on a SunBlade
2000 workstation with two 1.2 GHz CPUs and 8 GB of memory.
The export size of the database to import is 22 GB.
The dbimport took about 16 h in total, 13 h for data loading and 3 h for
index creation.
During this period of time one CPU is completely busy, so the systems
CPU utilization is about 55 %. No I/O bottleneck or something like that.
Any hints how to improve the time for the data loading?
A parallelization is probably not available during load but are there
options to decrease the CPU use and improve the throughput?
Some setting from my onconfig:
VPCLASS cpu,num=2,max=4,aff=(0,1),noage
NETTYPE ipcstr,1,32,NET
BUFFERPOOL size=2K,buffers=262144,lrus=8,
lru_min_dirty=5.0,lru_max_dirty=10.0
CKPTINTVL 86400AUTO_CKPTS 1
MAX_PDQPRIORITY 100
RA_PAGES 64
RA_THRESHOLD 16
LOGBUFF 512
The online.log file for the load phase:
Sat Feb 27 02:14:06 2010
02:14:06 Checkpoint Completed: duration was 1 seconds.
02:14:06 Sat Feb 27 - loguniq 167, logpos 0x3184c018, timestamp:0xc0170b7 Interval: 288
02:14:06 Maximum server connections 21
02:14:06 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txnsblocked 1, Plog used 882282, Llog used 257057
03:50:59 Logical Log 167 Complete, timestamp: 0x174c472d.
05:25:52 Performance Advisory: Long transactions are causing blockingcheckpoints.
05:25:52 Results: Transactions are being blocked until the checkpointis complete.
05:25:52 Action: Increase the logical log space size.
05:25:53 Logical Log 168 Complete, timestamp: 0x20502d29.
05:25:54 Checkpoint Completed: duration was 2 seconds.
05:25:54 Sat Feb 27 - loguniq 169, logpos 0x18, timestamp: 0x20502d2fInterval: 289
05:25:54 Maximum server connections 21
05:25:54 Checkpoint Statistics - Avg. Txn Block Time 0.021, # Txnsblocked 0, Plog used 2959, Llog used 845694
06:43:35 Logical Log 169 Complete, timestamp: 0x225577b7.
08:21:29 Logical Log 170 Complete, timestamp: 0x24154389.
08:21:33 Checkpoint Completed: duration was 4 seconds.
08:21:33 Sat Feb 27 - loguniq 171, logpos 0x18, timestamp: 0x2415438fInterval: 290
08:21:33 Maximum server connections 21
08:21:33 Checkpoint Statistics - Avg. Txn Block Time 0.001, # Txnsblocked 0, Plog used 2899, Llog used 1048522
10:28:16 Logical Log 171 Complete, timestamp: 0x25d52dfe.
13:22:45 Logical Log 172 Complete, timestamp: 0x27c927e0.
13:22:49 Checkpoint Completed: duration was 4 seconds.
13:22:49 Sat Feb 27 - loguniq 173, logpos 0x18, timestamp: 0x27c927e5Interval: 291
13:22:49 Maximum server connections 21
13:22:49 Checkpoint Statistics - Avg. Txn Block Time 0.001, # Txnsblocked 0, Plog used 1601, Llog used 1048522
13:52:48 Checkpoint Completed: duration was 0 seconds.
13:52:48 Sat Feb 27 - loguniq 173, logpos 0x2b683018, timestamp:0x28f1730e Interval: 292
13:52:48 Maximum server connections 21
13:52:48 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txnsblocked 0, Plog used 2314, Llog used 177795