Re: performance improvements for dbimport?
Frank Langelage changed BUFFERPOOL, CLEANERS, CKPTINTVL and SHMVIRTSIZE but saw no speed-up in dbimport: data load still took about 13 hours plus 3.5 hours for indexes, with one CPU saturated. thebp asked for onstat -g ioq output to check KAIO and disk performance. Another poster described his own parallel insert/select migration tool and wished for a built-in feature. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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,lru_max_dirty=10.0 to
size=2K,buffers=524288,lrus=8,lru_min_dirty=10.0,lru_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 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,lru_max_dirty=10.0 to
> size=2K,buffers=524288,lrus=8,lru_min_dirty=10.0,lru_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.
On 11/03/2010 21:59, Frank Langelage wrote:
> 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,lru_max_dirty=10.0 to
>> size=2K,buffers=524288,lrus=8,lru_min_dirty=10.0,lru_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 ioqoutput (i.e. are you using KAIO, are you getting the performance out the disks you should be getting)
On 11 Mar, 21:59, Frank Langelage <fr...@lafr.de> wrote:
> 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,lru_max_dirty=10.0 to
> > size=2K,buffers=524288,lrus=8,lru_min_dirty=10.0,lru_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).
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g