smaill iostat values for informix 
Author Message
 smaill iostat values for informix

Hi All gurus,

This posting is in fact related to my previous post : "help me to run a query faster than oracle with
informix.".

As a summary , on a test system i have a query which takes 2min. with oracle and 15min.with informix.I
am responsible for running this query as fast as oracle.

After some work, i have found something strange. (Or it is strange for me.)

If i have a query which does a full sequental scan and finds no row (no result set) , iostat shows i have a
14mb. read / per second.
But if i have a result set from the same query , iostat shows i have poor 600kb. read per second.

(one point , oracle also does sequental scan . )

These are my query plans:

Fast one:
QUERY:        (FIRST_ROWS OPTIMIZATION)
select *
from hplandet
where borc1<0

Estimated Cost: 1192782
Estimated # of Rows Returned: 1

  1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
fragments: ALL)

        Filters: informix.hplandet.borc1 < 0.00

Bu da digeri:

Slow one:

QUERY:        (FIRST_ROWS OPTIMIZATION)

------
select *
from hplandet
where borc1>=0

Estimated Cost: 1192782
Estimated # of Rows Returned: 3235377

  1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
fragments: ALL)

        Filters: informix.hplandet.borc1 >= 0.00

And iostat values:

----------------------
Both sql statements do sequental scan.
(hplandet has 3,000,000 rows, fragmented 4 dbspaces by
round robin)
These sql statements run without pdq.

select *
from hplandet
where borc1<0    // there is no record like this  Fast
one

select *
from hplandet
where borc1>=0   // all the table    Deadly slow!!

First sqls iostat output:

device       r/s    w/s   kr/s   kw/s wait actv  svc_t
 %w  %b  us sy wt id
 24  8  0 68
sd307      474.1    0.0 15022.1    0.0  0.0  2.4  
5.0   0  39
sd889      475.5    0.0 15045.0    0.0  0.0  1.7  
3.7   0  39
                  extended device statistics          
             cpu
device       r/s    w/s   kr/s   kw/s wait actv  svc_t
 %w  %b  us sy wt id
 26  8  0 67
sd307      484.1    0.3 15371.3    0.7  0.0  3.4  
7.0   0  50
sd889      483.1    0.0 15368.7    0.0  0.0  2.4  
5.0   0  47

second sqls iostat output:

device       r/s    w/s   kr/s   kw/s wait actv  svc_t
 %w  %b  us sy wt id
 29  2  0 69
sd307       10.7    0.7  341.3    1.3  0.0  0.1    5.0
  0   1
sd889       11.3    0.7  342.6    5.7  0.0  0.0    2.8
  0   1

This is a great difference!

There is only 1 difference. The first sql statement
finds no row so no
data is returned. But the second sql statement finds
data.
I have made other tests.If the query has something for
the result set , the performance is poor. (2. iostat
output)

Am i doing something wrong? Do i need to set a
configuration problem ?
Or is this a known problem?
----------------------------------
Table Name          hplandet
Owner               informix
Row Size            649
Number of Rows      3235377
Number of Columns   86
Date Created        08/11/2001

MULTIPROCESSOR  1               # 0 for
single-processor, 1 for multi-processor
NUMCPUVPS       4               # Number of user (cpu)
vps
SINGLE_CPU_VP   0               # If non-zero, limit
number of cpu vps to one

NOAGE           0               # Process aging
AFF_SPROC       0               # Affinity start
processor
AFF_NPROCS      0               # Affinity number of
processors

# Shared Memory Parameters

LOCKS           200000            # Maximum number of
locks
BUFFERS         200000            # Maximum number of
shared buffers
NUMAIOVPS       8               # Number of IO vps
PHYSBUFF        512              # Physical log buffer
size (Kbytes)
LOGBUFF         512              # Logical log buffer
size (Kbytes)
LOGSMAX         60              # Maximum number of
logical log files
CLEANERS        8               # Number of buffer
cleaner processes
SHMBASE         0x10a000000       # Shared memory base
address
SHMVIRTSIZE     128000            # initial virtual
shared memory segment size
SHMADD          64000            # Size of new shared
memory segments (Kbytes)
SHMTOTAL        0               # Total shared memory
(Kbytes). 0=>unlimited
CKPTINTVL       300             # Check point interval
(in sec)
LRUS            8               # Number of LRU queues
LRU_MAX_DIRTY   95              # LRU percent dirty
begin cleaning limit
LRU_MIN_DIRTY   90              # LRU percent dirty
end cleaning limit
LTXHWM          50              # Long transaction
high water mark percentage
LTXEHWM         60              # Long transaction
high water mark (exclusive)
TXTIMEOUT       0x12c             # Transaction
timeout (in sec)
STACKSIZE       32              # Stack size (Kbytes)

RA_PAGES      512                 # Number of pages to
attempt to read ahead
RA_THRESHOLD  500                # Number of pages
left before next group

I have also searched the newsgroup for similiar problems, and found people who had this problem. But i did
not see any posting about a solution.

Kind Regards,
hope

==================================
Poster's IP address: 212.253.3.50
Posted via http://www.***.com/
Linux Programmer's Site



Sat, 12 Jun 2004 00:29:25 GMT
 smaill iostat values for informix

turn off the first row optimisation and see what happens.  First row
stuff is normally detrimental in these queries ie returning all the
rows

Quote:

> Hi All gurus,

> This posting is in fact related to my previous post : "help me to run a query faster than oracle with
> informix.".

> As a summary , on a test system i have a query which takes 2min. with oracle and 15min.with informix.I
> am responsible for running this query as fast as oracle.

> After some work, i have found something strange. (Or it is strange for me.)

> If i have a query which does a full sequental scan and finds no row (no result set) , iostat shows i have a
> 14mb. read / per second.
> But if i have a result set from the same query , iostat shows i have poor 600kb. read per second.

> (one point , oracle also does sequental scan . )

> These are my query plans:

> Fast one:
> QUERY:        (FIRST_ROWS OPTIMIZATION)
> select *
> from hplandet
> where borc1<0

> Estimated Cost: 1192782
> Estimated # of Rows Returned: 1

>   1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
> fragments: ALL)

>         Filters: informix.hplandet.borc1 < 0.00

> Bu da digeri:

> Slow one:

> QUERY:        (FIRST_ROWS OPTIMIZATION)

> ------
> select *
> from hplandet
> where borc1>=0

> Estimated Cost: 1192782
> Estimated # of Rows Returned: 3235377

>   1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
> fragments: ALL)

>         Filters: informix.hplandet.borc1 >= 0.00

> And iostat values:

> ----------------------
> Both sql statements do sequental scan.
> (hplandet has 3,000,000 rows, fragmented 4 dbspaces by
> round robin)
> These sql statements run without pdq.

> select *
> from hplandet
> where borc1<0    // there is no record like this  Fast
> one

> select *
> from hplandet
> where borc1>=0   // all the table    Deadly slow!!

> First sqls iostat output:

> device       r/s    w/s   kr/s   kw/s wait actv  svc_t
>  %w  %b  us sy wt id
>  24  8  0 68
> sd307      474.1    0.0 15022.1    0.0  0.0  2.4
> 5.0   0  39
> sd889      475.5    0.0 15045.0    0.0  0.0  1.7
> 3.7   0  39
>                   extended device statistics
>              cpu
> device       r/s    w/s   kr/s   kw/s wait actv  svc_t
>  %w  %b  us sy wt id
>  26  8  0 67
> sd307      484.1    0.3 15371.3    0.7  0.0  3.4
> 7.0   0  50
> sd889      483.1    0.0 15368.7    0.0  0.0  2.4
> 5.0   0  47

> second sqls iostat output:

> device       r/s    w/s   kr/s   kw/s wait actv  svc_t
>  %w  %b  us sy wt id
>  29  2  0 69
> sd307       10.7    0.7  341.3    1.3  0.0  0.1    5.0
>   0   1
> sd889       11.3    0.7  342.6    5.7  0.0  0.0    2.8
>   0   1

> This is a great difference!

> There is only 1 difference. The first sql statement
> finds no row so no
> data is returned. But the second sql statement finds
> data.
> I have made other tests.If the query has something for
> the result set , the performance is poor. (2. iostat
> output)

> Am i doing something wrong? Do i need to set a
> configuration problem ?
> Or is this a known problem?
> ----------------------------------
> Table Name          hplandet
> Owner               informix
> Row Size            649
> Number of Rows      3235377
> Number of Columns   86
> Date Created        08/11/2001

> MULTIPROCESSOR  1               # 0 for
> single-processor, 1 for multi-processor
> NUMCPUVPS       4               # Number of user (cpu)
> vps
> SINGLE_CPU_VP   0               # If non-zero, limit
> number of cpu vps to one

> NOAGE           0               # Process aging
> AFF_SPROC       0               # Affinity start
> processor
> AFF_NPROCS      0               # Affinity number of
> processors

> # Shared Memory Parameters

> LOCKS           200000            # Maximum number of
> locks
> BUFFERS         200000            # Maximum number of
> shared buffers
> NUMAIOVPS       8               # Number of IO vps
> PHYSBUFF        512              # Physical log buffer
> size (Kbytes)
> LOGBUFF         512              # Logical log buffer
> size (Kbytes)
> LOGSMAX         60              # Maximum number of
> logical log files
> CLEANERS        8               # Number of buffer
> cleaner processes
> SHMBASE         0x10a000000       # Shared memory base
> address
> SHMVIRTSIZE     128000            # initial virtual
> shared memory segment size
> SHMADD          64000            # Size of new shared
> memory segments (Kbytes)
> SHMTOTAL        0               # Total shared memory
> (Kbytes). 0=>unlimited
> CKPTINTVL       300             # Check point interval
> (in sec)
> LRUS            8               # Number of LRU queues
> LRU_MAX_DIRTY   95              # LRU percent dirty
> begin cleaning limit
> LRU_MIN_DIRTY   90              # LRU percent dirty
> end cleaning limit
> LTXHWM          50              # Long transaction
> high water mark percentage
> LTXEHWM         60              # Long transaction
> high water mark (exclusive)
> TXTIMEOUT       0x12c             # Transaction
> timeout (in sec)
> STACKSIZE       32              # Stack size (Kbytes)

> RA_PAGES      512                 # Number of pages to
> attempt to read ahead
> RA_THRESHOLD  500                # Number of pages
> left before next group

> I have also searched the newsgroup for similiar problems, and found people who had this problem. But i did
> not see any posting about a solution.

> Kind Regards,
> hope

> ==================================
> Poster's IP address: 212.253.3.50
> Posted via http://nodevice.com
> Linux Programmer's Site

--
Paul Watson             #          
Oninit Ltd              # Growing old is mandatory
Tel: +44 1436 672201    # Growing up is optional
Fax: +44 1436 678693    #
www.oninit.com          #


Sat, 12 Jun 2004 01:04:17 GMT
 smaill iostat values for informix
I can almost certainly say that your RA parameters are too high, even
for what you're doing.

What happens with PDQPRIORITY 1? >1?

Have you done UPDATE STATS?

What is your hardware config?

On Mon, 24 Dec 2001 16:29:25 GMT, 212.253.3.50

Quote:

>Hi All gurus,

>This posting is in fact related to my previous post : "help me to run a query faster than oracle with
>informix.".

>As a summary , on a test system i have a query which takes 2min. with oracle and 15min.with informix.I
>am responsible for running this query as fast as oracle.

>After some work, i have found something strange. (Or it is strange for me.)

>If i have a query which does a full sequental scan and finds no row (no result set) , iostat shows i have a
>14mb. read / per second.
>But if i have a result set from the same query , iostat shows i have poor 600kb. read per second.

>(one point , oracle also does sequental scan . )

>These are my query plans:

>Fast one:
>QUERY:        (FIRST_ROWS OPTIMIZATION)
>select *
>from hplandet
>where borc1<0

>Estimated Cost: 1192782
>Estimated # of Rows Returned: 1

>  1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
>fragments: ALL)

>        Filters: informix.hplandet.borc1 < 0.00

>Bu da digeri:

>Slow one:

>QUERY:        (FIRST_ROWS OPTIMIZATION)

>------
>select *
>from hplandet
>where borc1>=0

>Estimated Cost: 1192782
>Estimated # of Rows Returned: 3235377

>  1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
>fragments: ALL)

>        Filters: informix.hplandet.borc1 >= 0.00

>And iostat values:

>----------------------
>Both sql statements do sequental scan.
>(hplandet has 3,000,000 rows, fragmented 4 dbspaces by
>round robin)
>These sql statements run without pdq.

>select *
>from hplandet
>where borc1<0    // there is no record like this  Fast
>one

>select *
>from hplandet
>where borc1>=0   // all the table    Deadly slow!!

>First sqls iostat output:

>device       r/s    w/s   kr/s   kw/s wait actv  svc_t
> %w  %b  us sy wt id
> 24  8  0 68
>sd307      474.1    0.0 15022.1    0.0  0.0  2.4  
>5.0   0  39
>sd889      475.5    0.0 15045.0    0.0  0.0  1.7  
>3.7   0  39
>                  extended device statistics          
>             cpu
>device       r/s    w/s   kr/s   kw/s wait actv  svc_t
> %w  %b  us sy wt id
> 26  8  0 67
>sd307      484.1    0.3 15371.3    0.7  0.0  3.4  
>7.0   0  50
>sd889      483.1    0.0 15368.7    0.0  0.0  2.4  
>5.0   0  47

>second sqls iostat output:

>device       r/s    w/s   kr/s   kw/s wait actv  svc_t
> %w  %b  us sy wt id
> 29  2  0 69
>sd307       10.7    0.7  341.3    1.3  0.0  0.1    5.0
>  0   1
>sd889       11.3    0.7  342.6    5.7  0.0  0.0    2.8
>  0   1

>This is a great difference!

>There is only 1 difference. The first sql statement
>finds no row so no
>data is returned. But the second sql statement finds
>data.
>I have made other tests.If the query has something for
>the result set , the performance is poor. (2. iostat
>output)

>Am i doing something wrong? Do i need to set a
>configuration problem ?
>Or is this a known problem?
>----------------------------------
>Table Name          hplandet
>Owner               informix
>Row Size            649
>Number of Rows      3235377
>Number of Columns   86
>Date Created        08/11/2001

>MULTIPROCESSOR  1               # 0 for
>single-processor, 1 for multi-processor
>NUMCPUVPS       4               # Number of user (cpu)
>vps
>SINGLE_CPU_VP   0               # If non-zero, limit
>number of cpu vps to one

>NOAGE           0               # Process aging
>AFF_SPROC       0               # Affinity start
>processor
>AFF_NPROCS      0               # Affinity number of
>processors

># Shared Memory Parameters

>LOCKS           200000            # Maximum number of
>locks
>BUFFERS         200000            # Maximum number of
>shared buffers
>NUMAIOVPS       8               # Number of IO vps
>PHYSBUFF        512              # Physical log buffer
>size (Kbytes)
>LOGBUFF         512              # Logical log buffer
>size (Kbytes)
>LOGSMAX         60              # Maximum number of
>logical log files
>CLEANERS        8               # Number of buffer
>cleaner processes
>SHMBASE         0x10a000000       # Shared memory base
>address
>SHMVIRTSIZE     128000            # initial virtual
>shared memory segment size
>SHMADD          64000            # Size of new shared
>memory segments (Kbytes)
>SHMTOTAL        0               # Total shared memory
>(Kbytes). 0=>unlimited
>CKPTINTVL       300             # Check point interval
>(in sec)
>LRUS            8               # Number of LRU queues
>LRU_MAX_DIRTY   95              # LRU percent dirty
>begin cleaning limit
>LRU_MIN_DIRTY   90              # LRU percent dirty
>end cleaning limit
>LTXHWM          50              # Long transaction
>high water mark percentage
>LTXEHWM         60              # Long transaction
>high water mark (exclusive)
>TXTIMEOUT       0x12c             # Transaction
>timeout (in sec)
>STACKSIZE       32              # Stack size (Kbytes)

>RA_PAGES      512                 # Number of pages to
>attempt to read ahead
>RA_THRESHOLD  500                # Number of pages
>left before next group

>I have also searched the newsgroup for similiar problems, and found people who had this problem. But i did
>not see any posting about a solution.

>Kind Regards,
>hope

>==================================
>Poster's IP address: 212.253.3.50
>Posted via http://nodevice.com
>Linux Programmer's Site



Sat, 12 Jun 2004 04:58:05 GMT
 smaill iostat values for informix

Drop your read ahead params - 32 and 28 should be ample.
Turn off first  rows optimization

increase LRU's  (127) - 8 is probably not enough for 200 K buffers
increase CLEANERS (41) 8 is probably not enough for so many buffers/lru's

Drop Lru_Max_Dirty (10)
Drop Lru Min_dirty (2)

also try the queries again with an "onstat -z" before the query and post the
ouput of onstat -p after the query.

Jo


Quote:

> Hi All gurus,

> This posting is in fact related to my previous post : "help me to run a

query faster than oracle with
Quote:
> informix.".

> As a summary , on a test system i have a query which takes 2min. with

oracle and 15min.with informix.I
Quote:
> am responsible for running this query as fast as oracle.

> After some work, i have found something strange. (Or it is strange for
me.)

> If i have a query which does a full sequental scan and finds no row (no

result set) , iostat shows i have a
Quote:
> 14mb. read / per second.
> But if i have a result set from the same query , iostat shows i have poor

600kb. read per second.
Quote:

> (one point , oracle also does sequental scan . )

> These are my query plans:

> Fast one:
> QUERY:        (FIRST_ROWS OPTIMIZATION)
> select *
> from hplandet
> where borc1<0

> Estimated Cost: 1192782
> Estimated # of Rows Returned: 1

>   1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
> fragments: ALL)

>         Filters: informix.hplandet.borc1 < 0.00

> Bu da digeri:

> Slow one:

> QUERY:        (FIRST_ROWS OPTIMIZATION)

> ------
> select *
> from hplandet
> where borc1>=0

> Estimated Cost: 1192782
> Estimated # of Rows Returned: 3235377

>   1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
> fragments: ALL)

>         Filters: informix.hplandet.borc1 >= 0.00

> And iostat values:

> ----------------------
> Both sql statements do sequental scan.
> (hplandet has 3,000,000 rows, fragmented 4 dbspaces by
> round robin)
> These sql statements run without pdq.

> select *
> from hplandet
> where borc1<0    // there is no record like this  Fast
> one

> select *
> from hplandet
> where borc1>=0   // all the table    Deadly slow!!

> First sqls iostat output:

> device       r/s    w/s   kr/s   kw/s wait actv  svc_t
>  %w  %b  us sy wt id
>  24  8  0 68
> sd307      474.1    0.0 15022.1    0.0  0.0  2.4
> 5.0   0  39
> sd889      475.5    0.0 15045.0    0.0  0.0  1.7
> 3.7   0  39
>                   extended device statistics
>              cpu
> device       r/s    w/s   kr/s   kw/s wait actv  svc_t
>  %w  %b  us sy wt id
>  26  8  0 67
> sd307      484.1    0.3 15371.3    0.7  0.0  3.4
> 7.0   0  50
> sd889      483.1    0.0 15368.7    0.0  0.0  2.4
> 5.0   0  47

> second sqls iostat output:

> device       r/s    w/s   kr/s   kw/s wait actv  svc_t
>  %w  %b  us sy wt id
>  29  2  0 69
> sd307       10.7    0.7  341.3    1.3  0.0  0.1    5.0
>   0   1
> sd889       11.3    0.7  342.6    5.7  0.0  0.0    2.8
>   0   1

> This is a great difference!

> There is only 1 difference. The first sql statement
> finds no row so no
> data is returned. But the second sql statement finds
> data.
> I have made other tests.If the query has something for
> the result set , the performance is poor. (2. iostat
> output)

> Am i doing something wrong? Do i need to set a
> configuration problem ?
> Or is this a known problem?
> ----------------------------------
> Table Name          hplandet
> Owner               informix
> Row Size            649
> Number of Rows      3235377
> Number of Columns   86
> Date Created        08/11/2001

> MULTIPROCESSOR  1               # 0 for
> single-processor, 1 for multi-processor
> NUMCPUVPS       4               # Number of user (cpu)
> vps
> SINGLE_CPU_VP   0               # If non-zero, limit
> number of cpu vps to one

> NOAGE           0               # Process aging
> AFF_SPROC       0               # Affinity start
> processor
> AFF_NPROCS      0               # Affinity number of
> processors

> # Shared Memory Parameters

> LOCKS           200000            # Maximum number of
> locks
> BUFFERS         200000            # Maximum number of
> shared buffers
> NUMAIOVPS       8               # Number of IO vps
> PHYSBUFF        512              # Physical log buffer
> size (Kbytes)
> LOGBUFF         512              # Logical log buffer
> size (Kbytes)
> LOGSMAX         60              # Maximum number of
> logical log files
> CLEANERS        8               # Number of buffer
> cleaner processes
> SHMBASE         0x10a000000       # Shared memory base
> address
> SHMVIRTSIZE     128000            # initial virtual
> shared memory segment size
> SHMADD          64000            # Size of new shared
> memory segments (Kbytes)
> SHMTOTAL        0               # Total shared memory
> (Kbytes). 0=>unlimited
> CKPTINTVL       300             # Check point interval
> (in sec)
> LRUS            8               # Number of LRU queues
> LRU_MAX_DIRTY   95              # LRU percent dirty
> begin cleaning limit
> LRU_MIN_DIRTY   90              # LRU percent dirty
> end cleaning limit
> LTXHWM          50              # Long transaction
> high water mark percentage
> LTXEHWM         60              # Long transaction
> high water mark (exclusive)
> TXTIMEOUT       0x12c             # Transaction
> timeout (in sec)
> STACKSIZE       32              # Stack size (Kbytes)

> RA_PAGES      512                 # Number of pages to
> attempt to read ahead
> RA_THRESHOLD  500                # Number of pages
> left before next group

> I have also searched the newsgroup for similiar problems, and found people

who had this problem. But i did

- Show quoted text -

Quote:
> not see any posting about a solution.

> Kind Regards,
> hope

> ==================================
> Poster's IP address: 212.253.3.50
> Posted via http://nodevice.com
> Linux Programmer's Site



Sat, 12 Jun 2004 07:47:04 GMT
 smaill iostat values for informix

Quote:

> turn off the first row optimisation and see what happens.  First row
> stuff is normally detrimental in these queries ie returning all the
> rows

Also, try bouncing the server before you run each query - if you run
them one after the other on a "cold" engine, you will see all sorts of
caching effects...

Chris
--
**************************************************************

Orbis                       Tel: +44 208 742 1600
http://www.OrbisUK.com      Fax: +44 208 742 2649



Sun, 13 Jun 2004 22:11:11 GMT
 smaill iostat values for informix
Hi Everybody,

First , i wish you a very happy year .
And thank you very much for all your replies.

Second , my original posting was 2 months ago , but yesteray i saw
suddenly that it appeared again in newsgroups an this time i am lucky
i have lots of responses......

Let me give you more information:

-----------

Quote:
>I can almost certainly say that your RA parameters are too high, even
>for what you're doing.
>What happens with PDQPRIORITY 1? >1?
>Have you done UPDATE STATS?
>What is your hardware config?

----------

The machine is a test system. No user , only my query is run. It is
sun solaris 2.8 , 9.21.fc4 . Upate statistics is run. Table has no
index. I do use sequental scan. If i use the defaults for RA_xx , the
response time is bad.
With Light scan i have better performance. With pdq>1 the performance
is better. But , my aim is to compare the performance against oracle.
If the customer asks me "hey , i want this query run faster , do what
you can " , then
i have couple of ways to do this. One of them is to use PDQ + better
fragmentation. Basically on test system , even with 1 large disk and 4
fragments for the table (round robin)  , my query takes 3.5 min .
(without pdq, it takes 15min...) Alas, the customer asked me run this
without pdq.
So , new database instance , no user , only one table with 4 fragments
, 300mb.memory . (All these settings are same with oracle on the same
server.)
My observation is , whenever i add more columns inside the sum , my
iorate decreases.  The oracle behaviour is not like this. For just 1
fiel for sum ,
by using RA_parameters or light scans , i can get much better values
than oracle. (For example , 20mb.read per second with informix an
5mb.read /sec with oracle. This 5mb. is always same with oracle. But
for informix the more fiels in sum , the lower bytes rea /sec. )

---

Quote:
>turn off the first row optimisation and see what happens.  First row
>stuff is normally detrimental in these queries ie returning all the
>rows

--------
At first it was off. I just trie it. The results are same. I am
testing without first row optimization.
---------

Quote:
>Also, try> bouncing the server before you run each query - if you run
>them one after the other on a "cold" engine, you will see all sorts
of
>caching effects...

--------
I have already done like this. For every query , i restart the db
server.

--------

Quote:
>Drop your read ahead params - 32 and 28 should be ample.
>Turn off first  rows optimization
>increase LRU's  (127) - 8 is probably not enough for 200 K buffers
>increase CLEANERS (41) 8 is probably not enough for so many
buffers/lru's
>Drop Lru_Max_Dirty (10)
>Drop Lru Min_dirty (2)
>also try the queries again with an "onstat -z" before the query and
post the
>ouput of onstat -p after the query.

-------------
     I have no activity on this server . So lru_max /min has no effect
.
I have alreay run onstat -z before each query.

Kind Regards,
hope

Quote:

> Drop your read ahead params - 32 and 28 should be ample.
> Turn off first  rows optimization

> increase LRU's  (127) - 8 is probably not enough for 200 K buffers
> increase CLEANERS (41) 8 is probably not enough for so many buffers/lru's

> Drop Lru_Max_Dirty (10)
> Drop Lru Min_dirty (2)

> also try the queries again with an "onstat -z" before the query and post the
> ouput of onstat -p after the query.

> Jo



> > Hi All gurus,

> > This posting is in fact related to my previous post : "help me to run a
>  query faster than oracle with
> > informix.".

> > As a summary , on a test system i have a query which takes 2min. with
>  oracle and 15min.with informix.I
> > am responsible for running this query as fast as oracle.

> > After some work, i have found something strange. (Or it is strange for
>  me.)

> > If i have a query which does a full sequental scan and finds no row (no
>  result set) , iostat shows i have a
> > 14mb. read / per second.
> > But if i have a result set from the same query , iostat shows i have poor
>  600kb. read per second.

> > (one point , oracle also does sequental scan . )

> > These are my query plans:

> > Fast one:
> > QUERY:        (FIRST_ROWS OPTIMIZATION)
> > select *
> > from hplandet
> > where borc1<0

> > Estimated Cost: 1192782
> > Estimated # of Rows Returned: 1

> >   1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
> > fragments: ALL)

> >         Filters: informix.hplandet.borc1 < 0.00

> > Bu da digeri:

> > Slow one:

> > QUERY:        (FIRST_ROWS OPTIMIZATION)

> > ------
> > select *
> > from hplandet
> > where borc1>=0

> > Estimated Cost: 1192782
> > Estimated # of Rows Returned: 3235377

> >   1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
> > fragments: ALL)

> >         Filters: informix.hplandet.borc1 >= 0.00

> > And iostat values:

> > ----------------------
> > Both sql statements do sequental scan.
> > (hplandet has 3,000,000 rows, fragmented 4 dbspaces by
> > round robin)
> > These sql statements run without pdq.

> > select *
> > from hplandet
> > where borc1<0    // there is no record like this  Fast
> > one

> > select *
> > from hplandet
> > where borc1>=0   // all the table    Deadly slow!!

> > First sqls iostat output:

> > device       r/s    w/s   kr/s   kw/s wait actv  svc_t
> >  %w  %b  us sy wt id
> >  24  8  0 68
> > sd307      474.1    0.0 15022.1    0.0  0.0  2.4
> > 5.0   0  39
> > sd889      475.5    0.0 15045.0    0.0  0.0  1.7
> > 3.7   0  39
> >                   extended device statistics
> >              cpu
> > device       r/s    w/s   kr/s   kw/s wait actv  svc_t
> >  %w  %b  us sy wt id
> >  26  8  0 67
> > sd307      484.1    0.3 15371.3    0.7  0.0  3.4
> > 7.0   0  50
> > sd889      483.1    0.0 15368.7    0.0  0.0  2.4
> > 5.0   0  47

> > second sqls iostat output:

> > device       r/s    w/s   kr/s   kw/s wait actv  svc_t
> >  %w  %b  us sy wt id
> >  29  2  0 69
> > sd307       10.7    0.7  341.3    1.3  0.0  0.1    5.0
> >   0   1
> > sd889       11.3    0.7  342.6    5.7  0.0  0.0    2.8
> >   0   1

> > This is a great difference!

> > There is only 1 difference. The first sql statement
> > finds no row so no
> > data is returned. But the second sql statement finds
> > data.
> > I have made other tests.If the query has something for
> > the result set , the performance is poor. (2. iostat
> > output)

> > Am i doing something wrong? Do i need to set a
> > configuration problem ?
> > Or is this a known problem?
> > ----------------------------------
> > Table Name          hplandet
> > Owner               informix
> > Row Size            649
> > Number of Rows      3235377
> > Number of Columns   86
> > Date Created        08/11/2001

> > MULTIPROCESSOR  1               # 0 for
> > single-processor, 1 for multi-processor
> > NUMCPUVPS       4               # Number of user (cpu)
> > vps
> > SINGLE_CPU_VP   0               # If non-zero, limit
> > number of cpu vps to one

> > NOAGE           0               # Process aging
> > AFF_SPROC       0               # Affinity start
> > processor
> > AFF_NPROCS      0               # Affinity number of
> > processors

> > # Shared Memory Parameters

> > LOCKS           200000            # Maximum number of
> > locks
> > BUFFERS         200000            # Maximum number of
> > shared buffers
> > NUMAIOVPS       8               # Number of IO vps
> > PHYSBUFF        512              # Physical log buffer
> > size (Kbytes)
> > LOGBUFF         512              # Logical log buffer
> > size (Kbytes)
> > LOGSMAX         60              # Maximum number of
> > logical log files
> > CLEANERS        8               # Number of buffer
> > cleaner processes
> > SHMBASE         0x10a000000       # Shared memory base
> > address
> > SHMVIRTSIZE     128000            # initial virtual
> > shared memory segment size
> > SHMADD          64000            # Size of new shared
> > memory segments (Kbytes)
> > SHMTOTAL        0               # Total shared memory
> > (Kbytes). 0=>unlimited
> > CKPTINTVL       300             # Check point interval
> > (in sec)
> > LRUS            8               # Number of LRU queues
> > LRU_MAX_DIRTY   95              # LRU percent dirty
> > begin cleaning limit
> > LRU_MIN_DIRTY   90              # LRU percent dirty
> > end cleaning limit
> > LTXHWM          50              # Long transaction
> > high water mark percentage
> > LTXEHWM         60              # Long transaction
> > high water mark (exclusive)
> > TXTIMEOUT       0x12c             # Transaction
> > timeout (in sec)
> > STACKSIZE       32              # Stack size (Kbytes)

> > RA_PAGES      512                 # Number of pages to
> > attempt to read ahead
> > RA_THRESHOLD  500                # Number of pages
> > left before next group

> > I have also searched the newsgroup for similiar problems, and found people
>  who had this problem. But i did
> > not see any posting about a solution.

> > Kind Regards,
> > hope

> > ==================================
> > Poster's IP address: 212.253.3.50
> > Posted via http://nodevice.com
> > Linux Programmer's Site



Tue, 15 Jun 2004 06:49:03 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Using iostat tune informix databases

2. dbcc sqlperf(iostats)

3. iostat and Rollback Segments

4. iostat

5. What do IOSTATS, SQLSTATS, TABLESPACE_STATS?

6. Informix ISell Value?

7. Getting values of serial columns from informix tables with VB/ADO 2.6

8. Datetime type values substracting problem in Informix 5.x


 
Powered by phpBB® Forum Software