Re: Bad performance with DBSPACETMP enabled

Art S. Kagel wrote: > > rainer.von-bongartz@dil.de wrote: > > > > I'm currently using OnLine 7.23 on a HP K460 server. > > > > I have one big table (appr. 1 GB) for which we do an "UPDATE STATISTICS HIGH" > > on a regular basis. > > When using 3 temporary dbspaces on raw devices, the running time for this task > > is about 2 hours. > > > > If I dont't use temporary dbspaces, so that every temporary object is placed > > in the UNIX file system /tmp, the execution time drops down to 50 minutes. > > > > Anybody having any ideas about the reason for this bad performance, which also > > occurs when creating indexes. > > Should I use temporary dbspaces on raw devices at all or let OnLine better > > use /tmp ??? > > You have stumbled on a little known fact. Sorting is faster to > filesystem space than to temp table space. And since you do not care > about the safety of those sort-work files that's OK. > If your OS will support it then create a filesystem in memory and then use that for PSORT_DBTEMP. It makes an enormous difference, and if the box dies who cares about the temporary files anyway. > Even better still, for large sorts like index builds and UPDATE > STATISTICS, set the environment variable PSORT_DBTEMP to as many > different filesystems as possible (at least 3, keeping in mind that the > smallest will limit the size of the sort that is possible). Also, > contrary to the documentation, setting PSORT_NPROCS to a value between > 20 and 40 will also speed the sorting as long as you can afford to > allocate so many resources to that one task. You might see the time > drop to 15 or 20 minutes. > > Art S. Kagel -- Paul Watson # WF Software Ltd # If it ain't broke, Tel: +44 1436 674729 # fix it till it is. Fax: +44 1436 678693 # www.wfsoftware.co.uk #