Re: PDQ question

Lambros Papadopoulos wrote: > > We have a fragmented table (round robin) in two dbspaces with two detached > indexes in a third dbspace. All dbspaces are on different disks. > > PDQ priority is set to 100, the machine has two processors > > Selecting all rows (sequential scan) results in 3 Informix threads and > evenly distributed load on the two disks where the table resides. The table > data is being accessed in parallel. > > Selecting all rows and forcing an index path, results in 2 Informix threads > and uneven distribution of load on the three disks: The disk where the index > resides is being accessed all the time and the disks where the table resides > are never accessed in parallel. Activity alternates between the two. One > disk is accessed for a period of time (5-10 seconds) and then activity moves > to the other disk and so on. > In essence the table is read serially. > > onstat -D was used to monitor the activity on each dbspace.> > 'Set explain' on both sqls results in "(Parallel, fragments: ALL)" as the > access method for the table. > > At the same time if the fragmented table is used as the inner table in a > nested loop join and accessed through the same index, the table data is > again read serially. > > The question is whether this is normal behavior or is it a setting in our > set up that prevents parallel access through an index. > > The following script can be used to replicate the issue: > > Create table t1 (serno serial, Key1 integer, data1 char(20), data2 Char(20)) > fragment by round robin in dbspace2, dbspace3;> > Create Procedure fillup_t1() > Define i, key integer;> for i = 1 to 5000000 > let key = mod(i,2); > insert into t1 (Key1, data1, data2) values (key, 'data1' ,> 'data2'); > End for; > End Procedure; > > execute procedure fillup_t1();> > drop procedure fillup_t1;> > create unique index t1unq on t1(serno) in dbspace1; > create index t1x01 on t1(Key1) in dbspace1;> > update statistics high for table t1(serno); > update statistics high for table t1(key1);> > -- SQL results in parallel access of table > set pdqpriority 100;> select {+ explain} > key1, data1, data2 > from t1 > into temp t2 with no log; > > sqexplain: > > DIRECTIVES FOLLOWED:> EXPLAIN > DIRECTIVES NOT FOLLOWED:> > Estimated Cost: 86430 > Estimated # of Rows Returned: 500000 > Maximum Threads: 2 > > 1) cap.t1: SEQUENTIAL SCAN (Parallel, fragments: ALL) > > -- SQL results in serial access of table > set pdqpriority 100;> select {+ index(t1 t1x01), explain} > key1, data1, data2 > from t1 > into temp t2 with no log; > > sqexplain: > > DIRECTIVES FOLLOWED: > INDEX ( t1 t1x01 )> EXPLAIN > DIRECTIVES NOT FOLLOWED:> > Estimated Cost: 175332 > Estimated # of Rows Returned: 1000000 > Maximum Threads: 1 > > 1) cap.t1: INDEX PATH > > (1) Index Keys: key1 (Parallel, fragments: ALL) > > Any help would be welcome. You have used ROUND ROBIN fragmentation which gives a very even distribution of records, not necessarily IO. However, your select is looking at the entire table (no WHERE clause), so the optimiser is quite rightly going to ignore any indexes and perform a sequential scan. This will be much faster than checking index pages as well. So in this case, your fragmentation scheme is ideal, and as you have seen, you get a very balanced IO as well. When you force the use of the index, then obviously records will be read in index order, regardless of which fragment they reside in. This will firstly increase the access time, because you now have to trawl through index pages. Secondly, you will tend to see bursts of activity on each fragment, as rows are returned in index order. The ultimate test, instead of telling the optimiser you can do it better, is to time each and see which is faster. If you plan to access a subset of this table (using a WHERE clause) then you might consider fragmenting by expression based on the index key columns. However, this would increase load times, if that is the nature of the table. Also, from what you have said, you only have one temp dbspace. You may improve performance by adding a second temp dbspace so that your temp table can be fragmented. If that doesn't help, then tell us what you are trying to achieve, and we'll attempt to tell you the best way to get there. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+

> > When you force the use of the index, then obviously records will be read > in index order, regardless of which fragment they reside in. This will > firstly increase the access time, because you now have to trawl through > index pages. Secondly, you will tend to see bursts of activity on each > fragment, as rows are returned in index order. > I would agree that an index access should result in bursts of activity on each fragment as the records are not necessarily balanced in the two dbspaces. It seems very strange however that activity alternates between fragments with very short periods of parallel access. As you browse the data in index order you would expect to be accessing the fragments in a random order, some times in parallel, some times one fragment more than the other. What I seem to be getting is systematic access of one fragment and then a jump to the other fragment and so on. The only explanation is that the data is arranged in this manner on the disks. It is not easy to investigate this. I cannot find any way of identifying what data sits on what fragment (appart from using oncheck -pd, which is not very practical). > If that doesn't help, then tell us what you are trying to achieve, and > we'll attempt to tell you the best way to get there. > My concern was that the query with the index access was running at the same time no matter what the PDQ setting was. I would expect faster timing when PDQ is used. The real sql that I am running reads a small proportion of the fragmented table and, as a result the fragmented table is the inner table of a nested loop join. That sql runs in the same time with or without PDQ! Any more input will be wellcome. Thanks for your help anyway! Lambros