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

Hi Frank,

Not really an answer to speeding up a dbimport but it might be worth
looking at using the high performance loader for part of the load,
especially if the bulk of the data is in a couple of tables...

Any reason for using 8 lrus for you buffer pool? By setting the
lru_max_dirty to 10 you are encouraging the database to do a lot of lru
cleaning.
By increasing the #lrus and cleaners you might be better throughput?
Just a thought, not particularly experienced with IDS

James
Fonterra NZ

-----Original Message-----
From: informix-list-bounces (AT) iiug (DOT) org
[mailto:informix-list-bounces (AT) iiug (DOT) org] On Behalf Of Frank Langelage
Sent: Wednesday, March 10, 2010 10:53 AM
To: informix-list (AT) iiug (DOT) org
Subject: performance improvements for dbimport?

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 (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

I guess you can try at least,

1) lrus=8 is kind of too small for your large number of buffers.

2) lru_min_dirty=5.0,lru_max_dirty=10.0 is not good. How about try
lru_min_dirty=50.0,lru_max_dirty=60.0 , or even higher?

3) CKPTINTVL 86400 is kind of too big, Can you try 300 some thing like
that?

Good luck.

Frank



On Tue, Mar 9, 2010 at 4:52 PM, Frank Langelage <frank (AT) lafr (DOT) de> wrote:

Frank ,

What is your Virtual memory size configuration?

Frank

On Tue, Mar 9, 2010 at 4:52 PM, Frank Langelage <frank (AT) lafr (DOT) de> wrote:

On 10.03.10 01:44, FRANK wrote:

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 (AT) lafr (DOT) de> wrote:

Recommendations:

- Adjust config
- VPCLASS cpu,num=6,noage
- BUFFERPOOL
size=2K,buffers=1000000,lrus=8,lru_min_dirty=10.0, lru_max_dirty=50.0
- Make sure that CLEANERS is at least as big as the greater of LRUS
& the number of chunks.
- Set LRUS according to the number of concurrent users (won't
affect the import).
- Get my dbimport replacement utility package myexport and use it's
parallel load and HPLoader options.


Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art (AT) iiug (DOT) org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.



On Tue, Mar 9, 2010 at 4:52 PM, Frank Langelage <frank (AT) lafr (DOT) de> wrote:

Thanks for all your valuable input. I changed some parameters now:
21:09:27 Onconfig parameter BUFFERPOOL modified from
size=2K,buffers=262144,lrus=8,lru_min_dirty=5.0,lr u_max_dirty=10.0 to
size=2K,buffers=524288,lrus=8,lru_min_dirty=10.0,l ru_max_dirty=50.0.
21:09:27 Onconfig parameter CLEANERS modified from 5 to 8.
21:09:27 Onconfig parameter CKPTINTVL modified from 86400 to 3600.
21:09:27 Onconfig parameter SHMVIRTSIZE modified from 262144 to 524288.

A new dbimport with the same export and the same parameters is running now.
Tomorrow I'll know the run time improvement.

On 10.03.10 18:53, Art Kagel wrote:
Thanks for spending time on this issue.

6 CPU-VPs for only 2 single core CPUs without hyperthreading?
I read about configuring 2 CPU-VPs per physical CPU/core if the GHz
value is high enough, but 3 VP/core?
And because the standard dbimport during dataload is single threaded I
would not expect a benefit from that.

I changed some parameters (see my reply to myself).

But I'll have a look at your dbimport replacement. I was not aware that
this is capable to do things in parallel.

Frank