PSORT_DBTEMP
Hello, IDS 11.50.FC5 AIX 5.3 I am having trouble using PSORT_DBTEMP this morning. I am attempting to build an index on a large table (450,xxx,xxx rows - 924 rowsize). I have PSORT_NPROCS set to 8 and PSORT_DBTEMP set to a colon delimited set of 8 unix directories export PSORT_NPROCS=8 export PSORT_DBTEMP=/infxtmp5/dan1:/infxtmp5/dan2:/infxtmp5/dan3:/infxtmp5/dan4:/infxtm p5/steve1:/infxtmp5/steve2:/infxtmp5/steve3:/infxtmp5/steve4 I never see any of these directories written to. I do see shared memory segments being added until SHMTOTal is reached and then die with errors 212/116. I have used PSORT successfully in the past but seem to be missing something here. Any takers? thanx, Dan
DS_NONPDQ_QUERY_MEM controls the in-memory sort size (and other things) when PDQPRIORITY is zero and DS_TOTAL_MEMORY and DS_MAX_QUERIES controls the in-memory sort size (and other things) when PDQPRIORITY is greater than zero. So, it MAY be that the sorting needed for this index build, divided by PSORT_NPROCS, is fitting entirely in memory which means that the PSORT_DBTEMP directories are not needed. You can check whether sorting is being done in memory while the index build is progressing by looking at the session's syssesprof record where you will see the columns: total_sorts decimal(32,0) yes dsksorts decimal(32,0) yes max_sortdiskspace decimal(32,0) yes If the sorts are being done in memory then total_sorts will be non-zero and dsksorts and max_sortdiskspace will be zero. All that having been said, the fact that new virtual segments are being allocated during the index build is a dead give away that the sort is being performed in memory. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ 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 Thu, Jan 13, 2011 at 11:06 AM, DAN MUELLER <dan.mueller@trnswrks.com>wrote: > Hello, > > IDS 11.50.FC5 > AIX 5.3 > > I am having trouble using PSORT_DBTEMP this morning. I am attempting to > build > an index on a large table (450,xxx,xxx rows - 924 rowsize). I have > PSORT_NPROCS set to 8 and PSORT_DBTEMP set to a colon delimited set of 8 > unix > directories > > export PSORT_NPROCS=8 > export > > PSORT_DBTEMP=/infxtmp5/dan1:/infxtmp5/dan2:/infxtmp5/dan3:/infxtmp5/dan4:/infxtm p5/steve1:/infxtmp5/steve2:/infxtmp5/steve3:/infxtmp5/steve4 > > I never see any of these directories written to. I do see shared memory > segments being added until SHMTOTal is reached and then die with errors > 212/116. > > I have used PSORT successfully in the past but seem to be missing something > here. Any takers? > > thanx, > Dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5c1eb305c6e0499bcae94
DS_NONPDQ_MEMORY was not set. I set this to match it's sister box and am now able to see sort files in my PSORT_DBTEMP directories. Thanx Art. Dan
Of course, in-memory sorting is faster than on-disk sorting, so you may have just slowed down that index build but you have reduced the server's memory footprint. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ 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 Thu, Jan 13, 2011 at 1:30 PM, DAN MUELLER <dan.mueller@trnswrks.com>wrote: > DS_NONPDQ_MEMORY was not set. I set this to match it's sister box and am > now > able to see sort files in my PSORT_DBTEMP directories. Thanx Art. > > Dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5b45f39ea9f0499bea363