Re: performance improvements for dbimport?

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).