DB Tuning question
A user on IDS 9.40 asked why simple SELECTs were slow and whether LOCKS/CKPTINTVL needed tuning. Advice given: LOCKS only affect DML, not query speed; the high bufwaits ratio suggested raising LRUS (to ~128), and the very high seqscan count pointed to missing indexes and stale UPDATE STATISTICS (Art Kagel's dostats recommended). Also noted: 9.40 optimized ANSI joins poorly, so move filters into the ON clause or use old Informix join syntax; OPTCOMPIND should be 0 for OLTP; PDQPRIORITY affects only fragment parallelism, and nested-loop join via index is fine for OLTP. The user adjusted SHMVIRTSIZE/SHMADD; no confirmed fix was reported, as stats were too fresh to judge.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All,
I have this configuration in my IDS 9.4. Problem it takes time for the select
statement to completed. Is my configuration correct? please see below:
# Shared Memory Parameters
LOCKS 300000 # Maximum number of locks#BUFFERS 150000 # Maximum number of shared buffers
BUFFERS 50000 # Max number of shared buf
NUMAIOVPS 1 # Number of IO vps
PHYSBUFF 64 # Physical log buffer size (Kbytes)
LOGBUFF 32 # Logical log buffer size (Kbytes)
CLEANERS 16 # Number of buffer cleaner processes#SHMBASE 0xa000000 # Shared memory base address
SHMBASE 0x40000000 # Shared memory base address
SHMVIRTSIZE 20000 # initial virtual shared memory segment size
SHMADD 20000 # Size of new shared memory segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL 300 # Check point interval (in sec)
LRUS 16 # Number of LRU queues
LRU_MAX_DIRTY 3.000000 # LRU percent dirty begin
LRU_MIN_DIRTY 1.000000 # LRU percent dirty end
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)
onstat -p output
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
500948 502638 116682547 99.57 1762 3162 10296 82.89
isamtot open start read write rewrite delete commit rollbk
14577441 130688 2378995 4714570 1332 434 2501 805 0
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
0 0 0 0 0 0 0
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 19117.68 599.85 29 104
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
141579 0 1082752862 0 0 6 111 410141
ixda-RA idx-RA da-RA RA-pgsused lchwaits
320392 42 683 321023 458
My questions are:
1. Does the LOCKS need to be increased?
2. CHKPOINT Interval, do i need to put in on 120 seconds?
What are other things can you see? except for the fact that DB needs an
upgrade?
Thank you.
Hi,
first thing is: You did not name the statements which take too much time.
Which tables are involved (how many rows) ? Are your statistics up to date ?
Have you checked the slow queries with set explain, if the indexes are used ?
The config looks like a small system (32 bit, Buffers is very low and SHMSIZE
also).
Depending on your hardware, you should increase these to much higher values
(if there is enough memory
in the machine). The actual configuration would only allocate about 150 MB of
RAM.
In case of a system which is dedicated to the DB, you should give it more
buffers.
The LOCKS parameter does not affect query performance, it is only used for
modifying
SQL (update/delete/insert) and limits the number of lock entries which can be
held in open transactions
to lock the modified rows (or pages, depending on the lock level).
Read statistics show a good caching rate, so maybe you should go in the system
with set explain
for the long lasting queries.
Hope this helps,
Marcus
----- Ursprüngliche Mail -----
Von: "JACK PAPA" <informix2009@gmail.com>
An: ids@iiug.org
Gesendet: Mittwoch, 20. März 2013 09:44:19
Betreff: DB Tuning question [29786]
Hi All,
I have this configuration in my IDS 9.4. Problem it takes time for the select
statement to completed. Is my configuration correct? please see below:
# Shared Memory Parameters
LOCKS 300000 # Maximum number of locks#BUFFERS 150000 # Maximum number of shared buffers
BUFFERS 50000 # Max number of shared buf
NUMAIOVPS 1 # Number of IO vps
PHYSBUFF 64 # Physical log buffer size (Kbytes)
LOGBUFF 32 # Logical log buffer size (Kbytes)
CLEANERS 16 # Number of buffer cleaner processes#SHMBASE 0xa000000 # Shared memory base address
SHMBASE 0x40000000 # Shared memory base address
SHMVIRTSIZE 20000 # initial virtual shared memory segment size
SHMADD 20000 # Size of new shared memory segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL 300 # Check point interval (in sec)
LRUS 16 # Number of LRU queues
LRU_MAX_DIRTY 3.000000 # LRU percent dirty begin
LRU_MIN_DIRTY 1.000000 # LRU percent dirty end
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)
onstat -p output
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
500948 502638 116682547 99.57 1762 3162 10296 82.89
isamtot open start read write rewrite delete commit rollbk
14577441 130688 2378995 4714570 1332 434 2501 805 0
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
0 0 0 0 0 0 0
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 19117.68 599.85 29 104
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
141579 0 1082752862 0 0 6 111 410141
ixda-RA idx-RA da-RA RA-pgsused lchwaits
320392 42 683 321023 458
My questions are:
1. Does the LOCKS need to be increased?
2. CHKPOINT Interval, do i need to put in on 120 seconds?
What are other things can you see? except for the fact that DB needs an
upgrade?
Thank you.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Here's the SQL statement. It's just very simple..
SELECT a.CUSTTYPE FROM customers a
INNER JOIN
table2 b
ON a.CUSTOMERID = b.CUSTOMERID
WHERE b.CARDID = '0000001'
table1=1.1M Records, table2=900K records
custid have index and primary keys
---------------------------------------------
SELECT col1,col2, col3 FROM table1
WHERE col2 = 5
Col1 and Col2 has index in it.
-----------------------------------
I tried to use "set explain on" but it didn't give me full details
explanation...like n seconds. Is there a way to show what is being done by SQL
statement in IDS 9.4?
thanks,
Hi,
set explain on writes a sqexplain.out file in the home dir of the user on theserver.
You should check this if the indexes for the query are being used.
If not, watch your update statistics.
For such a number of data rows, whenever a sequential scan occurs, you should
have
to wait for a number of minutes probably.
If the tables are connected via a customerid column, you should check if there
are indexes on these columns (or maybe a foreign key).
Additionally also on the cardid column.
These should be used for the query and you should see that in sqexplain.out
file.
Marcus
----- Ursprüngliche Mail -----
Von: "JACK PAPA" <informix2009@gmail.com>
An: ids@iiug.org
Gesendet: Mittwoch, 20. März 2013 10:14:48
Betreff: Re: DB Tuning question [29789]
Here's the SQL statement. It's just very simple..
SELECT a.CUSTTYPE FROM customers a
INNER JOIN
table2 b
ON a.CUSTOMERID = b.CUSTOMERID
WHERE b.CARDID = '0000001'
table1=1.1M Records, table2=900K records
custid have index and primary keys
---------------------------------------------
SELECT col1,col2, col3 FROM table1
WHERE col2 = 5
Col1 and Col2 has index in it.
-----------------------------------
I tried to use "set explain on" but it didn't give me full details
explanation...like n seconds. Is there a way to show what is being done by SQL
statement in IDS 9.4?
thanks,
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks Marcus, I was able to search on how to use "set explain on".. Here's the output i've got. Estimated Cost: 2 Estimated # of Rows Returned: 1 1) informix.cardrefere1_: INDEX PATH (1) Index Keys: cardid (Serial, fragments: ALL) Lower Index Filter: informix.cardrefere1_.cardid = '41000000001' 2) informix.customer0_: INDEX PATH (1) Index Keys: customerid (Serial, fragments: ALL) Lower Index Filter: informix.customer0_.customerid = informix.cardrefere1_.customerid NESTED LOOP JOIN That simple statement took 2 seconds to execute, give just (1) record selected. Should the index be drop and recreated? MAXPDQPRIORITY=100, may i know why it says Serial instead of Parallel?.
Your bufwaits ratio is 27.6% indicating massive contention for the buffers
or for the LRU queues. Increase the number of LRUS to at leas 128.
Unless you zero'd the stats or started the instance less than an hour ago,
your buffer turnover rate is fine indicating that you have sufficient
numbers of buffers.
Readahead utilization is over 99.97% which is ideal.
Locks seem to be fine.
You show a massive number of sequential scans. Over three scans per query
on average. Look for missing indexes. Complex queries that are processing
scans. Check your data distributions. It looks like you have not run
UPDATE STATISTICS in a while.
That's about all I can tell from the information provided.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
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 Wed, Mar 20, 2013 at 4:44 AM, JACK PAPA <informix2009@gmail.com> wrote:
> Hi All,
>
> I have this configuration in my IDS 9.4. Problem it takes time for the
> select
> statement to completed. Is my configuration correct? please see below:
>
> # Shared Memory Parameters
>
> LOCKS 300000 # Maximum number of locks> #BUFFERS 150000 # Maximum number of shared buffers
> BUFFERS 50000 # Max number of shared buf
> NUMAIOVPS 1 # Number of IO vps
> PHYSBUFF 64 # Physical log buffer size (Kbytes)
> LOGBUFF 32 # Logical log buffer size (Kbytes)
> CLEANERS 16 # Number of buffer cleaner processes> #SHMBASE 0xa000000 # Shared memory base address
> SHMBASE 0x40000000 # Shared memory base address
> SHMVIRTSIZE 20000 # initial virtual shared memory segment size
> SHMADD 20000 # Size of new shared memory segments (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
> CKPTINTVL 300 # Check point interval (in sec)
> LRUS 16 # Number of LRU queues
> LRU_MAX_DIRTY 3.000000 # LRU percent dirty begin
> LRU_MIN_DIRTY 1.000000 # LRU percent dirty end
> 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)>
> onstat -p output>
> Profile
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 500948 502638 116682547 99.57 1762 3162 10296 82.89
>
> isamtot open start read write rewrite delete commit rollbk
> 14577441 130688 2378995 4714570 1332 434 2501 805 0
>
> gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
> 0 0 0 0 0 0 0
>
> ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
> 0 0 0 19117.68 599.85 29 104
>
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> 141579 0 1082752862 0 0 6 111 410141
>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 320392 42 683 321023 458
>
> My questions are:
> 1. Does the LOCKS need to be increased?
> 2. CHKPOINT Interval, do i need to put in on 120 seconds?
>
> What are other things can you see? except for the fact that DB needs an
> upgrade?
>
> Thank you.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8fb20486c13dc304d8589937
First, you should know that the implementation of ANSI style joins in
version 9.40 was rather rudimentary. The optimizer didn't do a good job of
optimizing these until version 11.xx. Similarly, the set explain output in
9.40 did not include actual results only the pre-query estimates that the
optimizer made. That said, post the set explain output and the actual
number of rows returned by the query here and we'll look it over.
You might also try one of these versions of your query which may perform
better:
ANSI join rules require that filters listed in the WHERE clause must be
applied POST join. That means that with your original query Informix must
create a temp table containing the results of joining every row from the
customers table to every matching row in the table table2 then select from
that temp table the rows that have CARDID equal to '0000001'. The version
below moves the filter into the ON clause permitting the optimizer to apply
it PRE join:
SELECT a.CUSTTYPE
FROM customers a
INNER JOIN table2 b
ON a.CUSTOMERID = b.CUSTOMERID
AND b.CARDID = '0000001';
This one uses the older SQL '89 syntax aka 'Informix syntax' where all
filters are applied pre join. Also the optimizer in 9.40 understands this
syntax better:
SELECT a.CUSTTYPE
FROM customers a, table2 b
WHERE a.CUSTOMERID = b.CUSTOMERID
AND b.CARDID = '0000001';
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
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 Wed, Mar 20, 2013 at 5:14 AM, JACK PAPA <informix2009@gmail.com> wrote:
> Here's the SQL statement. It's just very simple..
>
> SELECT a.CUSTTYPE FROM customers a>
> INNER JOIN
>
> table2 b
>
> ON a.CUSTOMERID = b.CUSTOMERID
> WHERE b.CARDID = '0000001'
>
> table1=1.1M Records, table2=900K records
> custid have index and primary keys
> ---------------------------------------------
> SELECT col1,col2, col3 FROM table1
> WHERE col2 = 5>
> Col1 and Col2 has index in it.
>
> -----------------------------------
>
> I tried to use "set explain on" but it didn't give me full details
> explanation...like n seconds. Is there a way to show what is being done by
> SQL
> statement in IDS 9.4?
>
> thanks,
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f46d04182734fa3bcc04d858c48a
The ONCONFIG parameter MAXPDQPRIORITY just acts as a governor adjusting
users effective PDQPRIORITY to prevent users from hogging resources. The
effective PDQPRIORITY for a user is governed by the PDQPRIORITY environment
variable in the users environment at runtime or by the user executing the
SET PDQPRIORITY=<%value>; statement within his session prior to executingqueries. Did you set PDQPRIORITY in one of these ways? The default
PDQPRIORITY is zero which does not scan tables in parallel.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
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 Wed, Mar 20, 2013 at 5:50 AM, JACK PAPA <informix2009@gmail.com> wrote:
> Thanks Marcus,
>
> I was able to search on how to use "set explain on".. Here's the output
> i've
> got.
>
> Estimated Cost: 2
> Estimated # of Rows Returned: 1
>
> 1) informix.cardrefere1_: INDEX PATH
>
> (1) Index Keys: cardid (Serial, fragments: ALL)
>
> Lower Index Filter: informix.cardrefere1_.cardid = '41000000001'
>
> 2) informix.customer0_: INDEX PATH
>
> (1) Index Keys: customerid (Serial, fragments: ALL)
>
> Lower Index Filter: informix.customer0_.customerid =
> informix.cardrefere1_.customerid
> NESTED LOOP JOIN
>
> That simple statement took 2 seconds to execute, give just (1) record
> selected.
>
> Should the index be drop and recreated? MAXPDQPRIORITY=100, may i know why
> it
> says Serial instead of Parallel?.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8fb20546929d8d04d858d9ee
Hi Art, Thank you for your explanation. I did try to embeed the SET PQDPRIORITY=1 and also value=50, but i'm still getting sequential scan of the table. Here;s the output Estimated Cost: 162 Estimated # of Rows Returned: 1 1) informix.tablelist0_: SEQUENTIAL SCAN Filters: informix.tablelist0_.tableid = 2222 Is there any way to avoid the sequential scan? i did try to drop and recreate the indexes (tableid and division) but the estimated cost is still 162. The table have 2000 records only.
Hi Art, Here's the output comparing with the original statement: Estimated Cost: 2 Estimated # of Rows Returned: 1 1) informix.b: INDEX PATH (1) Index Keys: cardid (Serial, fragments: ALL) Lower Index Filter: informix.b.cardid = '47002307535' 2) informix.a: INDEX PATH (1) Index Keys: customerid (Serial, fragments: ALL) Lower Index Filter: informix.a.customerid = informix.b.customerid NESTED LOOP JOIN May i know what does it mean by this "(Serial, fragments: ALL)" and "NESTED LOOP JOIN"? Does this mean "Index is fragmented"? if it's so, can this be corrected via update stats? i've been searching the whole day looking for a solution on how to make this SQL statement faster. Thanks a lot.
Sequential scans are not affected by PDQPRIORITY, only whether the partitions of a partitioned (or fragmented) table are accessed serially -versus- in parallel. Sequential scans are caused by one of more of the following: - Setting OPTCOMPIND to 2 in the ONCONFIG file. For an OLTP instance it should be set to zero (0) - Missing indexes on join or filter columns - Missing filter conditions that would reasonably limit the number of data pages that the engine needs to examine. If the optimizer determines that it will have to read the majority of data pages anyway it will decide to save the additional index page IOs and just perform a sequential scan. - The data distributions produced by running UPDATE STATISTICS MEDIUM or HIGH are either stale (old) or were gathered with insufficient detail levels or too low a sampling rate. If you are not doing so already, get my dostats utility (in the package utils2_ak in the IIUG Software Repository - a free to download and use package) and use it religiously. Dostats maintains the recommended levels of stats for you. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) 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 Wed, Mar 20, 2013 at 8:13 AM, JACK PAPA <informix2009@gmail.com> wrote: > Hi Art, > > Thank you for your explanation. I did try to embeed the SET PQDPRIORITY=1 > and > also value=50, but i'm still getting sequential scan of the table. Here;s > the > output > > Estimated Cost: 162 > Estimated # of Rows Returned: 1 > > 1) informix.tablelist0_: SEQUENTIAL SCAN > > Filters: informix.tablelist0_.tableid = 2222 > > Is there any way to avoid the sequential scan? i did try to drop and > recreate > the indexes (tableid and division) but the estimated cost is still 162. The > table have 2000 records only. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d04016b496fb4a204d85c361c
See my last response. It means that if the tables have multiple partitions (ie were created with a FRAGMENT BY clause) the optimizer will read the partitions one at a time rather than in parallel. Nested loop join is the optimal join type for an OLTP type query. It uses indexes in nested loops. So outer loop is find matching rows in one table. Inner loop is look up the join column(s) in the dependent table via index and fetch them in a loop. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) 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 Wed, Mar 20, 2013 at 8:38 AM, JACK PAPA <informix2009@gmail.com> wrote: > Hi Art, > > Here's the output comparing with the original statement: > > Estimated Cost: 2 > Estimated # of Rows Returned: 1 > > 1) informix.b: INDEX PATH > > (1) Index Keys: cardid (Serial, fragments: ALL) > > Lower Index Filter: informix.b.cardid = '47002307535' > > 2) informix.a: INDEX PATH > > (1) Index Keys: customerid (Serial, fragments: ALL) > > Lower Index Filter: informix.a.customerid = informix.b.customerid > NESTED LOOP JOIN > > May i know what does it mean by this "(Serial, fragments: ALL)" and "NESTED > LOOP JOIN"? Does this mean "Index is fragmented"? if it's so, can this be > corrected via update stats? > > i've been searching the whole day looking for a solution on how to make > this > SQL statement faster. > > Thanks a lot. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d04016b49869f7704d85c48eb
Hi Art,
This is the updated configuration: I have changed SHMADD and SHMVIRTUALSIZE
from 20000 to 100000. LRUS is still 16.
LOCKS 300000 # Maximum number of locks#BUFFERS 150000 # Maximum number of shared buffers
BUFFERS 50000 # Max number of shared buf
NUMAIOVPS 1 # Number of IO vps
PHYSBUFF 64 # Physical log buffer size (Kbytes)
LOGBUFF 32 # Logical log buffer size (Kbytes)
CLEANERS 16 # Number of buffer cleaner processes#SHMBASE 0xa000000 # Shared memory base address
SHMBASE 0x40000000 # Shared memory base address#SHMVIRTSIZE 20000 # initial virtual shared memory segment size
#SHMADD 20000 # Size of new shared memory segments (Kbytes)
SHMVIRTSIZE 100000 # initial virtual shared memory segment size
SHMADD 100000 # Size of new shared memory segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL 300 # Check point interval (in sec)
LRUS 16 # Number of LRU queues
LRU_MAX_DIRTY 3.000000 # LRU percent dirty begin
LRU_MIN_DIRTY 1.000000 # LRU percent dirty end
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)
Do i need to still adjust the LRUS?
This is the current "onstat -p" status.
=>onstat -p
IBM Informix Dynamic Server Version 9.40.UC6 -- On-Line (Prim) -- Up 1 days
06:20:17 -- 349184 Kbytes
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
6061 6080 69757 91.31 266 406 3117 91.47
isamtot open start read write rewrite delete commit rollbk
69305 3618 13036 25347 327 8 75 402 0
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
0 0 0 0 0 0 0
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 18.46 4.45 9 146
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
40 0 47870 0 0 0 196 306
ixda-RA idx-RA da-RA RA-pgsused lchwaits
40 0 154 97 0
Kindly let me know what can i do make the SQL statement run faster.
Thank you,
It's too soon to tell from the stats in the onstat -p output. Looks like
either you recently zero'd the stats or there hasn't been much activity
since the restart yesterday. Honestly, I think I'd have to get hands-on to
diagnose this query and your server in general better. Bufwaits are low so
far, but the number of sequential scans is still high (about 80% of queries
include a scan) but again, it's hard to trust such small absolute numbers
over so short a time.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
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, Mar 21, 2013 at 6:46 AM, JACK PAPA <informix2009@gmail.com> wrote:
> Hi Art,
>
> This is the updated configuration: I have changed SHMADD and SHMVIRTUALSIZE
> from 20000 to 100000. LRUS is still 16.
>
> LOCKS 300000 # Maximum number of locks> #BUFFERS 150000 # Maximum number of shared buffers
> BUFFERS 50000 # Max number of shared buf
> NUMAIOVPS 1 # Number of IO vps
> PHYSBUFF 64 # Physical log buffer size (Kbytes)
> LOGBUFF 32 # Logical log buffer size (Kbytes)
> CLEANERS 16 # Number of buffer cleaner processes> #SHMBASE 0xa000000 # Shared memory base address
> SHMBASE 0x40000000 # Shared memory base address> #SHMVIRTSIZE 20000 # initial virtual shared memory segment size
> #SHMADD 20000 # Size of new shared memory segments (Kbytes)
> SHMVIRTSIZE 100000 # initial virtual shared memory segment size
> SHMADD 100000 # Size of new shared memory segments (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
> CKPTINTVL 300 # Check point interval (in sec)
> LRUS 16 # Number of LRU queues
> LRU_MAX_DIRTY 3.000000 # LRU percent dirty begin
> LRU_MIN_DIRTY 1.000000 # LRU percent dirty end
> 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)>
> Do i need to still adjust the LRUS?
>
> This is the current "onstat -p" status.
>
> =>onstat -p
>
> IBM Informix Dynamic Server Version 9.40.UC6 -- On-Line (Prim) -- Up 1 days
> 06:20:17 -- 349184 Kbytes>
> Profile
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 6061 6080 69757 91.31 266 406 3117 91.47
>
> isamtot open start read write rewrite delete commit rollbk
> 69305 3618 13036 25347 327 8 75 402 0
>
> gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
> 0 0 0 0 0 0 0
>
> ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
> 0 0 0 18.46 4.45 9 146
>
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> 40 0 47870 0 0 0 196 306
>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 40 0 154 97 0
>
> Kindly let me know what can i do make the SQL statement run faster.
>
> Thank you,
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec554dbc8cbc04004d86d36ed
Thank you Art. I really appreciate your response on my questions. Kudos to you!