performance improvements for dbimport?

#11

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 (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 Wed, Mar 10, 2010 at 3:45 PM, Frank Langelage <frank (AT) lafr (DOT) de> wrote:

Quote:

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

#12

On 10.03.10 21:32, Frank Langelage wrote:

Quote:

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.

I'm sorry, but I did not see a performance improvement for my dbimport
after changing those values.
The time for the dataload of the dbimport was again about 13 hours.
The index creation after wards took 3.5 hours.
As I said, during data loading one CPU is completely busy.
The dbimport process is consuming about 50% of this CPU, the two CPU-VPs
the other half.
In total the dbimport process used about 6.25 h CPU time nearly
completely during load.

So when ftp.iiug.org is vailable again, I'll take a look at Art's
export/import replacement.

#13

On 11/03/2010 21:59, Frank Langelage wrote:

Quote:

On 10.03.10 21:32, Frank Langelage 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.

I'm sorry, but I did not see a performance improvement for my dbimport
after changing those values.
The time for the dataload of the dbimport was again about 13 hours.
The index creation after wards took 3.5 hours.
As I said, during data loading one CPU is completely busy.
The dbimport process is consuming about 50% of this CPU, the two CPU-VPs
the other half.
In total the dbimport process used about 6.25 h CPU time nearly
completely during load.

So when ftp.iiug.org is vailable again, I'll take a look at Art's
export/import replacement.

Just out of interest, post
onstat -g ioq
output (i.e. are you using KAIO, are you getting the performance out the disks you should be getting)

#14

On 11 Mar, 21:59, Frank Langelage <fr... (AT) lafr (DOT) de> wrote:

Quote:

On 10.03.10 21:32, Frank Langelage 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.

I'm sorry, but I did not see a performance improvement for my dbimport
after changing those values.
The time for the dataload of the dbimport was again about 13 hours.
The index creation after wards took 3.5 hours.
As I said, during data loading one CPU is completely busy.
The dbimport process is consuming about 50% of this CPU, the two CPU-VPs
the other half.
In total the dbimport process used about 6.25 h CPU time nearly
completely during load.

So when ftp.iiug.org is vailable again, I'll take a look at Art's
export/import replacement.

This was one of the feature requests that I voted on at one of the
surveys that appeared in this group.

I have written a parallel tool simillar to this for cross platforms
migrations using insert/select, the enhancements would be:

- unload/load to file (uncluding gzip via mknod pipe to get around v7
2GB limits).
- use HPL (without or without gzip to a mknod pipe) when
systables,nrows indicates the table contains above a certain number of
rows.
(onpladm in the current releases of each product families makes this
easier).

Lets hope this feature request makes it into later versions.Oh, and
full cross platform dump load for databases (like Sybase already has).

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

performance improvements for dbimport?

comp.databases.informix comp.databases.informix

Re: performance improvements for dbimport? - 03-10-2010 , 05:40 PM

Re: performance improvements for dbimport? - 03-11-2010 , 04:59 PM

Re: performance improvements for dbimport? - 03-11-2010 , 05:33 PM

Re: performance improvements for dbimport? - 03-12-2010 , 07:52 AM