Re: performance improvements for dbimport?
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@iiug.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@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 18:53, Art Kagel wrote:
> Recommendations:
> * Adjust config
> o VPCLASS cpu,num=6,noage
> o 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
>
>
> On Tue, Mar 9, 2010 at 4:52 PM, Frank Langelage <frank@lafr.de
> <mailto: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.
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
Experimentation has demonstrated that a CPU VP can use about 450MHZ max, so
if you have 1.2GHZ processor cores they can each run three CPU VPs as long
as the machine is dedicated to IDS and you don't have any other significant
processes running on it.
If you want to try myexport/myimport you will also need to download my
utils2_ak package (for myschema), Jonathan Leffler's sqlcmd package, and if
you want to use the HPLoader options you'll need Ravi Krishna's myonpload
package. Everything is in the IIUG Repository. FYI, my next project is to
make myexport/myimport work with external tables. 8^o
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.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 Wed, Mar 10, 2010 at 3:45 PM, Frank Langelage <frank@lafr.de> wrote:
> On 10.03.10 18:53, Art Kagel wrote:
> > Recommendations:
> > * Adjust config
> > o VPCLASS cpu,num=6,noage
> > o 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
> >
> >
> > On Tue, Mar 9, 2010 at 4:52 PM, Frank Langelage <frank@lafr.de
> > <mailto: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.
>
> 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
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>