RE: PDQ

You want to be careful about dumping huge amounts of data down to /tmp if it is really a memory filesystem. Remember they are major difference between a tmpfs and ufs/vxfs etc. I can bring our E10000 12CPU domain to standstill when generating .5GB file in /tmp Paul Watson # WF Software Ltd # Any answer given Tel: +44 1463 674729 # could be totally Fax: +44 1463 678729 # fictitious www.wfsoftware.com # > -----Original Message----- > From: wisse [mailto:wisse@kabelfoon.nl] > Sent: 23 March 1999 23:10 > To: informix-list@iiug.org > Subject: Re: PDQ > > > Art, > > I have PSORT_DBTEMP set to /tmp and that is implemented on > physical memory, > mounted on swap space.Make it, in this case sense to create mutiple > directories, /tmp/psort1, /tmp/psort2,.. and set PSORT_DBTEMP to them. > > I create four indexes in parallel. Does it speeds up to give > them all a > different filesystem for the sort-work files. > > Albert H. Wisse > > > "Art S. Kagel" wrote: > > > wisse wrote: > > > > > > I noticed a performance decrease instead of a performance increase > > > when using PDQ with creating indexes. > > > > > > The create indexes are done one/with: > > > > > > * Informix 7.23.UC6 on Solaria 2.5.1. > > > * System: SUN E4000 with 6 CPU's and 5.3 GB internal memory. > > > * Informix has four CPU, NOAGE is set, AFFINITY is set > for all 4 > > > Informix CPU's. > > > * Tables are fragmented round robbin on two dbspaces. > > > * Indexes has their own dbspace and disk/diskchannel > > > * All dbspaces are on raw devices > > > > > > During the creations of indexes nobody is using the > system. When using > > > PDQ the index creation takes around 10% longer. That is not what I > > > want. > > > Monitoring onstat -g mgm tells me hat I have PDQ > resources enough. And > > > other measurements show me that the system has plenty of resources > > > left. > > > > > > The question is this a feature of Informix 7.23.UC6 or do > I something > > > wrong. > > > > To take best advantage of parallelism for index creation > you also have > > to set the PSORT parameters. PDQ wil get you additional > scan threads > > but not additional sort threads. Use PSORT_NPROCS to set > the number of > > sort threads used with PDQ (documentation says no more than 12 but I > > have seen performance gains with values up to 40). Also since sort > > work files are temporary you can take advantage of a quiet > filesystem > > by placing sort-work files in filesystem space by setting > PSORT_DBTEMP > > to a list of at least 3 filesystems prefereably on different drives. > > This will allow writes to sort-work files at memory speeds > rather than > > the synchronous writes to even temp dbspaces. > > > > Art S. Kagel >

Watson, Paul wrote: [SNIP] > > -----Original Message----- > > From: wisse [mailto:wisse@kabelfoon.nl] > > Sent: 23 March 1999 23:10 > > To: informix-list@iiug.org > > Subject: Re: PDQ > > > > > > Art, > > > > I have PSORT_DBTEMP set to /tmp and that is implemented on > > physical memory, > > mounted on swap space.Make it, in this case sense to create mutiple > > directories, /tmp/psort1, /tmp/psort2,.. and set PSORT_DBTEMP to them. I did not see your reply so I am hijacking Paul's response to that in order to respond to you. Ideally the directories listed in PSORT_DBTEMP should each be on a different filesystem and remember that the these are used round robin so the total sort-work space available for use will be limited by the filesystem with the LEAST free space available. So if PSORT_DBTEMP= /tmp/psort1:/usr/psort2:/opt/psort3 and your df -k listing is in part: filesystem kbytes used avail capacity mounted on ... /dev/dsk/disk1 1222222 222222 1000000 78% /usr /dev/dsk/disk2 2000000 100000 1900000 95% /opt /dev/dsk/disk3 100000 20000 80000 80% /tmp ... You will only have 240MB of sort work space that is useable since the /tmp filesystem only has 80MB free and that will limit any sort that includes it in PSORT_DBTEMP so while using all three filesystems will be faster limiting PSORT_DBTEMP to only /usr and /opt will increase the maximum sort set to 2GB (2x the free capacity of /usr). > > I create four indexes in parallel. Does it speeds up to give > > them all a > > different filesystem for the sort-work files. Yes but it will speed things even more if you give them the same four filesystem or better different combinations of three of four filesystems. You need to balance the advantage of one sort not competing with another for I/O bandwidth with the advantage of using several filesystems. When Informix merges sort-work files it will read two files from different FSs and write to the third FS for this reason at least three filesystems give best performance. > > "Art S. Kagel" wrote: > > > > > wisse wrote: > > > > > > > > I noticed a performance decrease instead of a performance increase > > > > when using PDQ with creating indexes. > > > > > > > > The create indexes are done one/with: > > > > > > > > * Informix 7.23.UC6 on Solaria 2.5.1. > > > > * System: SUN E4000 with 6 CPU's and 5.3 GB internal memory. > > > > * Informix has four CPU, NOAGE is set, AFFINITY is set > > for all 4 > > > > Informix CPU's. > > > > * Tables are fragmented round robbin on two dbspaces. > > > > * Indexes has their own dbspace and disk/diskchannel > > > > * All dbspaces are on raw devices > > > > > > > > During the creations of indexes nobody is using the > > system. When using > > > > PDQ the index creation takes around 10% longer. That is not what I > > > > want. > > > > Monitoring onstat -g mgm tells me hat I have PDQ > > resources enough. And > > > > other measurements show me that the system has plenty of resources > > > > left. > > > > > > > > The question is this a feature of Informix 7.23.UC6 or do > > I something > > > > wrong. > > > > > > To take best advantage of parallelism for index creation > > you also have > > > to set the PSORT parameters. PDQ wil get you additional > > scan threads > > > but not additional sort threads. Use PSORT_NPROCS to set > > the number of > > > sort threads used with PDQ (documentation says no more than 12 but I > > > have seen performance gains with values up to 40). Also since sort > > > work files are temporary you can take advantage of a quiet > > filesystem > > > by placing sort-work files in filesystem space by setting > > PSORT_DBTEMP > > > to a list of at least 3 filesystems prefereably on different drives. > > > This will allow writes to sort-work files at memory speeds > > rather than > > > the synchronous writes to even temp dbspaces. > > > > > > Art S. Kagel > >