Dbspaces spanning multiple disks.

Platform AIX 4.2 IDS 7.24 We have a few high use tables fragmented round robin across four dbspaces. Until about a month ago each dbspace was made up of two 1GB chunks on the same disk. The result was that each dbspace took an entire 2GB disk. Performance was great. A month ago we ran out of space and added 300MB chunks from separate disks to a couple of these dbspaces. After this we noticed significant a performance hit (We are not seeing multiple threads per session anymore). Could the reason for our performance hit be that our dbspaces are now spanning multiple disks or should we be looking elsewhere?

"Howard, Paul" wrote: > > Platform AIX 4.2 IDS 7.24 > > We have a few high use tables fragmented round robin across four dbspaces. > Until about a month ago each dbspace was made up of two 1GB chunks on the > same disk. The result was that each dbspace took an entire 2GB disk. > Performance was great. A month ago we ran out of space and added 300MB > chunks from separate disks to a couple of these dbspaces. After this we > noticed significant a performance hit (We are not seeing multiple threads > per session anymore). Could the reason for our performance hit be that our > dbspaces are now spanning multiple disks or should we be looking elsewhere? Have you updated statistics since adding the chunks? Perhaps the new rows are throwing the optimizer off because their keys are not in the distributions. Art S. Kagel

Hi Howard, no, adding the chunks was not the reason. It must be somewhere else. Informix cannot tell one disk from another. Even YOU are not able to tell, whether a file called /dev/rchunk1 resides on the same disk as /dev/rchunk2 unless you investigate a lot of time in your manuals. Did someone change the programs ? It's a must to set PDQPRIORITY to at least 1 if you want to have parallel scans. This must be set either by the application or by an environment variable. Do the following: /bin/sh PDQPRIORITY=90 export PDQPRIORITY echo "select * from your_table" | dbaccess your_db 2>/dev/null 1>&2 & sleep 1 onstat -g ses If the query will not have multiple threads running, then it's time to check if your table is still fragmented by round robin. PS: Why did you simply add chunks to the old dbspaces ? Won't you aggree that it is often better to add additional dbspaces if the new chunks reside on a different disk ? Best regards Stefan Howard, Paul wrote: > > Platform AIX 4.2 IDS 7.24 > > We have a few high use tables fragmented round robin across four dbspaces. > Until about a month ago each dbspace was made up of two 1GB chunks on the > same disk. The result was that each dbspace took an entire 2GB disk. > Performance was great. A month ago we ran out of space and added 300MB > chunks from separate disks to a couple of these dbspaces. After this we > noticed significant a performance hit (We are not seeing multiple threads > per session anymore). Could the reason for our performance hit be that our > dbspaces are now spanning multiple disks or should we be looking elsewhere? -- Stefan Weideneder Phone: +49 89/3565478-2 --------------- --- Fax: +49 89/3565478-3 ------------- ------ mailto:/stefan@weideneder.de --- -------- http://www.weideneder.de -----