IDS Features - MAX_PDQPRIORITY

Hello,

Aside from fragment elimination, are there any other features that are
enabled by IDS that result from setting MAX_PDQPRIORITY to 1 (or
greater)??

Any help would be greatly appreciated.

--Dave

Parallel scans.

j.

-----Original Message-----
From: informix-list-bounces (AT) iiug (DOT) org
[mailto:informix-list-bounces (AT) iiug (DOT) org]On Behalf Of Dave
Sent: Thursday, December 27, 2007 4:37 PM
To: informix-list (AT) iiug (DOT) org
Subject: IDS Features - MAX_PDQPRIORITY


Hello,

Aside from fragment elimination, are there any other features that are
enabled by IDS that result from setting MAX_PDQPRIORITY to 1 (or
greater)??

Any help would be greatly appreciated.

--Dave
_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

On Dec 27, 4:36 pm, Dave <In4Mix... (AT) gmail (DOT) com> wrote:

Hello,

Aside from fragment elimination, are there any other features that are
enabled by IDS that result from setting MAX_PDQPRIORITY to 1 (or
greater)??

Any help would be greatly appreciated.

At PDQPRIORITY 1 only fragment elimination is activated. At
PDQPRIORITY 2 or greater all other parallelization features are
enabled with the degree of parallelism controlled by the
MAXPDQPRIORITY governor and using the resulting PDQ level as a
percentage of available resources. So, if there are 24 CPU VPS and
you have and effective PDQ level of 10% (PDQPRIORITY * MAXPDQPRIORTY)
then you will get parallel query enabled with as many threads as
required but only 2 CPU VPs running those threads. With effective PDQ
level 50% you'll have up to 12 CPU VPs processing your threads. MGM
memory is similarly doled out according to the number of active PDQ
Queries and the PDQ level.

Note that if a PDQ query cannot get access to the level of resources
it requires due to competition from other PDQ queries it will wait
until the required resources are available. This means that if a
query is running that has requested a PDQPRIORITY of 100 it cannot run
until all preexisting PDQ queries have completed and no new PDQ
queries can run until it completes. They will be placed on the wait
queue.

Art S. Kagel

--Dave

Hi

As far as I remember fragment elimination is always ON. What is
activated at PDQPRIORITY of 1 is parallel scans.

regards

Gustavo

Art S. Kagel wrote:

On Dec 27, 4:36 pm, Dave <In4Mix... (AT) gmail (DOT) com> wrote:

Hello,

Aside from fragment elimination, are there any other features that are
enabled by IDS that result from setting MAX_PDQPRIORITY to 1 (or
greater)??

Any help would be greatly appreciated.


At PDQPRIORITY 1 only fragment elimination is activated. At
PDQPRIORITY 2 or greater all other parallelization features are
enabled with the degree of parallelism controlled by the
MAXPDQPRIORITY governor and using the resulting PDQ level as a
percentage of available resources. So, if there are 24 CPU VPS and
you have and effective PDQ level of 10% (PDQPRIORITY * MAXPDQPRIORTY)
then you will get parallel query enabled with as many threads as
required but only 2 CPU VPs running those threads. With effective PDQ
level 50% you'll have up to 12 CPU VPs processing your threads. MGM
memory is similarly doled out according to the number of active PDQ
Queries and the PDQ level.

Note that if a PDQ query cannot get access to the level of resources
it requires due to competition from other PDQ queries it will wait
until the required resources are available. This means that if a
query is running that has requested a PDQPRIORITY of 100 it cannot run
until all preexisting PDQ queries have completed and no new PDQ
queries can run until it completes. They will be placed on the wait
queue.

Art S. Kagel


--Dave


_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

Hi,

Did you find this information in an official Informix manual?. If this
is the case please let us know the name and page in the manual to open a
documentation defect. Fragment elimination is INDEPENDANT of PDQPRIORITY
settings. The following small testcase proves this. Notice that
PDQPRIORITY is set to zero, the selects obviouly skip the fragments that
do not meet the domain. The text in red shows the elimination of the
correct fragments

Regards

$ cat fragment.sql
drop database gusprueba;

create database gusprueba in rootdbs;


create table t1 (c1 integer, c2 char(32))
fragment by expression

c1 < 1000 in dbspace1,
c1 >=1000 in dbspace2;


insert into t1 values(1, "gustavo" );
insert into t1 values(1000, "castro" );

set PDQPRIORITY 0;
set explain on;
select * from t1 where c1 < 1000;
select * from t1 where c1 >= 1000;



$ dbaccess - fragment.sql

Database dropped.


Database created.


Table created.


1 row(s) inserted.


1 row(s) inserted.


PDQ Priority set.


Explain set.



c1 c2

1 gustavo

1 row(s) retrieved.



c1 c2

1000 castro

1 row(s) retrieved.


Database closed.

$ cat sqexplain.out

QUERY:
------
select * from t1 where c1 < 1000

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) informix.t1: SEQUENTIAL SCAN (Serial, fragments: 0)

Filters: informix.t1.c1 < 1000


QUERY:
------
select * from t1 where c1 >= 1000

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) informix.t1: SEQUENTIAL SCAN (Serial, fragments: 1)

Filters: informix.t1.c1 >= 1000



Art Kagel wrote:

Just double checked. We're both wrong. With PDQPRIORITY zero no
parallelism is enabled. PDQPRIORITY 1 or LOW enables parallel scans
ONLY. PDQPRIORITY 2 or greater enables fragment elimination, parallel
sorting, and MGM memory use.

On Dec 28, 2007 10:47 AM, Gustavo Castro <gcastroc (AT) bellsouth (DOT) net
mailto:gcastroc (AT) bellsouth (DOT) net>> wrote:

Hi

As far as I remember fragment elimination is always ON. What is
activated at PDQPRIORITY of 1 is parallel scans.

regards

Gustavo

Art S. Kagel wrote:
On Dec 27, 4:36 pm, Dave < In4Mix... (AT) gmail (DOT) com
mailto:In4Mix... (AT) gmail (DOT) com>> wrote:

Hello,

Aside from fragment elimination, are there any other features
that are
enabled by IDS that result from setting MAX_PDQPRIORITY to 1 (or
greater)??

Any help would be greatly appreciated.


At PDQPRIORITY 1 only fragment elimination is activated. At
PDQPRIORITY 2 or greater all other parallelization features are
enabled with the degree of parallelism controlled by the
MAXPDQPRIORITY governor and using the resulting PDQ level as a
percentage of available resources. So, if there are 24 CPU VPS and
you have and effective PDQ level of 10% (PDQPRIORITY *
MAXPDQPRIORTY)
then you will get parallel query enabled with as many threads as
required but only 2 CPU VPs running those threads. With
effective PDQ
level 50% you'll have up to 12 CPU VPs processing your threads.
MGM
memory is similarly doled out according to the number of active PDQ
Queries and the PDQ level.

Note that if a PDQ query cannot get access to the level of resources
it requires due to competition from other PDQ queries it will wait
until the required resources are available. This means that if a
query is running that has requested a PDQPRIORITY of 100 it
cannot run
until all preexisting PDQ queries have completed and no new PDQ
queries can run until it completes. They will be placed on the wait
queue.

Art S. Kagel


--Dave


_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org <mailto:Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list
http://www.iiug.org/mailman/listinfo/informix-list

On 1 Jan, 01:18, Gustavo Castro <gcast... (AT) bellsouth (DOT) net> wrote:

Hi,

Did you find this information in an official Informix manual?. If this
is the case please let us know the name and page in the manual to open a
documentation defect. Fragment elimination is INDEPENDANT of PDQPRIORITY
settings. The following small testcase proves this. Notice that
PDQPRIORITY is set to zero, the selects obviouly skip the fragments that
do not meet the domain. The text in red shows the elimination of the
correct fragments

Can you please open a different documentation defect?:

http://publib.boulder.ibm.com/infoce...oc/sqlr266.htm

"LOW
Data values are fetched from fragmented tables in parallel."

further down in the "Using PDQPRIORITY with Dynamic Server" section on
the same page we have
"When you specify LOW, Dynamic Server uses no forms of parallelism.".

Which is it "Dynamic Server uses no forms of parallelism." or "Data
values are fetched from fragmented tables in parallel."??

http://publib.boulder.ibm.com/infoce...oc/sqls838.htm
is clearer -

"LOW
Data values are fetched from fragmented tables in parallel. (In
Dynamic Server, when you specify LOW, the database server uses no
other forms of parallelism.) "

and "Value 1 is the same as LOW".


I would like more documentation on
http://publib.boulder.ibm.com/infoce...oc/sqlr266.htm
-

"When PDQPRIORITY is set to HIGH, Dynamic Server determines an
appropriate value to use for PDQPRIORITY
based on several criteria. These include the number of available
processors, the fragmentation of tables queried,
the complexity of the query, and additional factors."

It would be nice to get a clear formula for this!

Hi DAVID,

Thanks a lot for your input I have requested a modification in the
documentation.

Regarding the last part of your request (HIGH):

LOW means 1, HIGH means 100 in IDS. I have requested this to be
documented as well.

regards

Gustavo


david (AT) smooth1 (DOT) co.uk wrote:

On 1 Jan, 01:18, Gustavo Castro <gcast... (AT) bellsouth (DOT) net> wrote:

Hi,

Did you find this information in an official Informix manual?. If this
is the case please let us know the name and page in the manual to open a
documentation defect. Fragment elimination is INDEPENDANT of PDQPRIORITY
settings. The following small testcase proves this. Notice that
PDQPRIORITY is set to zero, the selects obviouly skip the fragments that
do not meet the domain. The text in red shows the elimination of the
correct fragments



Can you please open a different documentation defect?:

http://publib.boulder.ibm.com/infoce...oc/sqlr266.htm

"LOW
Data values are fetched from fragmented tables in parallel."

further down in the "Using PDQPRIORITY with Dynamic Server" section on
the same page we have
"When you specify LOW, Dynamic Server uses no forms of parallelism.".

Which is it "Dynamic Server uses no forms of parallelism." or "Data
values are fetched from fragmented tables in parallel."??

http://publib.boulder.ibm.com/infoce...oc/sqls838.htm
is clearer -

"LOW
Data values are fetched from fragmented tables in parallel. (In
Dynamic Server, when you specify LOW, the database server uses no
other forms of parallelism.) "

and "Value 1 is the same as LOW".


I would like more documentation on
http://publib.boulder.ibm.com/infoce...oc/sqlr266.htm
-

"When PDQPRIORITY is set to HIGH, Dynamic Server determines an
appropriate value to use for PDQPRIORITY
based on several criteria. These include the number of available
processors, the fragmentation of tables queried,
the complexity of the query, and additional factors."

It would be nice to get a clear formula for this!



_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list