IDS Forum

Re: DB Tuning question

Posted By: Art Kagel
Date: Thursday, 21 March 2013, at 6:58 a.m.

In Response To: Re: DB Tuning question (JACK PAPA)

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

Messages In This Thread

• DB Tuning question
  JACK PAPA -- Wednesday, 20 March 2013, at 4:44 a.m.
  • Re: DB Tuning question
    Marcus Haarmann -- Wednesday, 20 March 2013, at 4:53 a.m.
  • Re: DB Tuning question
    JACK PAPA -- Wednesday, 20 March 2013, at 5:14 a.m.
    • Re: DB Tuning question
      Marcus Haarmann -- Wednesday, 20 March 2013, at 5:43 a.m.
      • Re: DB Tuning question
        JACK PAPA -- Wednesday, 20 March 2013, at 5:50 a.m.
        • Re: DB Tuning question
          Art Kagel -- Wednesday, 20 March 2013, at 6:40 a.m.
          • Re: DB Tuning question
            JACK PAPA -- Wednesday, 20 March 2013, at 8:13 a.m.
            • Re: DB Tuning question
              Art Kagel -- Wednesday, 20 March 2013, at 10:41 a.m.
              • Re: DB Tuning question
                JACK PAPA -- Saturday, 23 March 2013, at 4:44 a.m.
    • Re: DB Tuning question
      Art Kagel -- Wednesday, 20 March 2013, at 6:34 a.m.
      • Re: DB Tuning question
        JACK PAPA -- Wednesday, 20 March 2013, at 8:38 a.m.
        • Re: DB Tuning question
          Art Kagel -- Wednesday, 20 March 2013, at 10:46 a.m.
  • Re: DB Tuning question
    Art Kagel -- Wednesday, 20 March 2013, at 6:22 a.m.
    • Re: DB Tuning question
      JACK PAPA -- Thursday, 21 March 2013, at 6:46 a.m.
      • Re: DB Tuning question
        Art Kagel -- Thursday, 21 March 2013, at 6:58 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.