Re: performance improvements for dbimport?
Frank ,
What is your Virtual memory size configuration?
Frank
On Tue, Mar 9, 2010 at 4:52 PM, Frank Langelage <frank@lafr.de> wrote:
> 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 86400> AUTO_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, # Txns> blocked 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 blocking> checkpoints.
> 05:25:52 Results: Transactions are being blocked until the checkpoint> is 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: 0x20502d2f> Interval: 289
> 05:25:54 Maximum server connections 21
> 05:25:54 Checkpoint Statistics - Avg. Txn Block Time 0.021, # Txns> blocked 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: 0x2415438f> Interval: 290
> 08:21:33 Maximum server connections 21
> 08:21:33 Checkpoint Statistics - Avg. Txn Block Time 0.001, # Txns> blocked 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: 0x27c927e5> Interval: 291
> 13:22:49 Maximum server connections 21
> 13:22:49 Checkpoint Statistics - Avg. Txn Block Time 0.001, # Txns> blocked 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, # Txns> blocked 0, Plog used 2314, Llog used 177795
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
On 10.03.10 01:44, FRANK wrote:
> Frank ,
> What is your Virtual memory size configuration?
> Frank
>
SHMVIRTSIZE 262144
Make sure that you start with a clean system eq fresh created dbspaces
(no initialized pages from prev
loads to avoid unnessesary physical logging)
Then i would maximize buffers , set lrumax and mindirty to 99 (so
disable it!!)
i would generate my own checkpoints using below:
dbaccess sysmaster <<!
create procedure generatechkpt()define dirty decimal (4,3);
while (1=1)
select ( sum(lru_nmod) / sum ( lru_nfree + lru_nmod ))
into dirty
from syslrus ;
if (dirty < 0.75 ) then
system "sleep 1";
else
system "onmode -c";
end if
end while ;
end procedure;
execute procedure generatechkpt();!
i would set pdpqpriority 100 in the environment so the index builds
can benefit from it
Also set shmvirtsize to a large value and the pdqmemory to a large
value make sure that this does
fit in shmvirtsize eq:
nowadays these two: DS_NONPDQ_QUERY_MEM,DS_TOTAL_MEMORY.
When the import is completed force a checkpoint manually!! then make
sure update stats is done
(using Arts stuff...)
and update stats for routines/procedures is done using pdqpriority set
to 0!!!
The above spl cut load times in half in the past... dono what it does
now though; have fun with it.
I must admit that HPL is faster.
Superboer.
Your SHMVIRTSIZE (262144 ) is small. You can increase it quite bit. It
will improve.
You have 8 GB memory, some times, depends on your data usage pattern ,
balancing the Buffer size and the SHMVIRTSIZE can give you a lot better
performance.
Frank
On Wed, Mar 10, 2010 at 1:19 AM, Frank Langelage <frank@lafr.de> wrote:
> On 10.03.10 01:44, FRANK wrote:
> > Frank ,
> > What is your Virtual memory size configuration?
> > Frank
> >
> SHMVIRTSIZE 262144
> _______________________________________________> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>