RE: Slowness
Since nobody else seems to have said anything yet I'll start
the ball rolling with a few suggestions. Lets see how many
things I miss.
First things I noticed are 4 LRUS and 4 CLEANERS for
200 000 Buffers!!
Does onstat -m have some fairly log checkpoints ?
Second thing I noticed was initial virtual memory size was
not exactly large? Yet your database seems non trivial and
I am sure that there may be mileage in bumping it up.
Comments scattered thru below.
> From: Boscia [mailto:rwboscia@worldnet.att.net]
> Sent: 16 June 2000 05:04
> To: informix-list@iiug.org
> Subject: Re: Slowness
> ******* OUTPUT from onstat -c ***************
> PHYSDBS logs # Location (dbspace) of physical log
> PHYSFILE 10000 # Physical log file size (Kbytes)
Do your logical logs go in the same dbspace. If so move them. Try have
diff dbspaces for rootdbs, logdbs and physdbs ( your naming standards
may be slightly different ). Also if possible use diff disks for each.
> TBLSPACE_STATS 1Is 1 on or off? My Boolean type days are way gone. However if you
don't need data on this stuff or have no idea about what it is used
for then you may as well turn it off. ( if 1 is on )
> TAPESIZE 10240 # Maximum amount of data to
> LTAPESIZE 10240 # Max amount of data to putNot the largest tapes I have ever seen !
> DBSERVERNAME pinkfloyd # Name of default database serverWhat line of business are you in :-)
> DBSERVERALIASES floyd_net # List of alternate dbservernames
> NETTYPE tlitcp,1,100,NET # Configure poll thread(s)Personally I would change this to
NETTYPE tlitcp,4,100,NET # Configure poll thread(s)I can't remember why but I have a pin prick in my memory that reminds me
that it shouldn't do any harm and it may have a pro :-)
> NETTYPE ipcshm,4,30,CPU # Configure poll thread(s) for nettype
> BUFFERS 200000 # Maximum number of shared buffersLots. 200 000 * sys page size devoted to buffers and only 8K going to
initial virtual size ???? If you have the mem you can leave it like this.
However you need more LRU's / CLEANERS to service this many buffers.
> NUMAIOVPS 4 # Number of IO vps
> PHYSBUFF 64 # Physical log buffer size (Kbytes)
> LOGBUFF 64 # Logical log buffer size (Kbytes)> LOGSMAX 100 # Maximum number of logical log files
> CLEANERS 4 # Number of buffer cleaner processesBump this up. Depends on a lot of things but I would just go
straight to 127 and try and cut-out 3 days of reading manuals and
understanding it all ( or trying too )
Your LRUS should also go straight to 127 and see if there is
any perf increase.
> SHMBASE 0xa000000 # Shared memory base address
> SHMVIRTSIZE 8192 # initial virtual shared> memory segment size
If your nettype is correct then you generally expect in excess of
5 users and you want this bumped up. I can't find a mention of OS
which may be important. ( didn't look to hard either so it may be
here somewhere.
Do an
onstat -g segHow many entries have a class of V.
Add up all the sizes and get a rough starting point for the setting
of SHMVIRTSIZE
> LRUSMentioned already ( try 127 )
> LRU_MAX_DIRTY 60 # LRU percent dirty begin> cleaning limit
> LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit
Try to lower these. This is open to debate about what they should be but
try set max/min to 1/0 or 5/3 or some such figures. If it improves
performance then stick with it.
> # DBSPACETEMP:
> DBSPACETEMP tempspace # Default temp dbspacesWell I think that this has already been dealt with in other posts
> # OPTCOMPIND
> # 0 => Nested loop joins will be preferred (where
> # possible) over sortmerge joins and hash joins.
> # 1 => If the transaction isolation mode is not
> # "repeatable read", optimizer behaves as in (2)
> # below. Otherwise it behaves as in (0) above.
> # 2 => Use costs regardless of the transaction isolation
> # mode. Nested loop joins are not necessarily
> # preferred. Optimizer bases its decision purely
> # on costs.
> OPTCOMPIND 2 # To hint the optimizerMainly preference and debatable but have you tried to set this to 0
> LBU_PRESERVE 0 # Preserve last log for log backup
Risky. You may want this at one. ( unless I have my Boolean
playing up again. However you do want to preserve your last log
> ********** OUTPUT FROM onstat-p **********
>
> Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up
> 8 days 14:55:04
> -- 491520 Kbytes
>
> Profile
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 682295405 18023000 3083027087 77.87 71759590 15652589
> 1036581591 93.08
> isamtot open start read write rewrite delete commit
> rollbk
> 1492423941 18991866 243150836 3111959883 1137426567 4844731
> 2152933 80738
> 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 855488.77 72041.35 2699 5398
>
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits
> compress seqscans
> 30666291 5573 3559009654 0 0 22997
> 4401153 1335818
>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 250071741 406399 315028979 565468702 11963653
>
>
> *********** OUTPUT from onstat-P| tail -5 ***********
>
> Percentages:
> Data 59.32
> Btree 40.10
> Other 0.59
>
>
>
> *********** OUTPUT from onstat -D ******************
> 250736f0 11 1 11 1 N
> informix logtossy
> 250737b0 12 2001 18 3 N T
> informix tempspace
You have 3 chunks ( all the same size ) in tempspace
Is each chunk on a diff disk.
Could you set each chunk to exist in its own dbspace and then set
DBSPACETEMP newtmpdbs1, newtmpdbs3, newtmpdbs3
or something along those lines.
The last of the 3 chunks never really gets used. It has
written only 1 page.
> 2504b2d0 18 12 0 588700 449535
> /dev/vx/rdsk/informixdg/db14
> 266be018 34 12 0 699406 424696
> /dev/vx/rdsk/informixdg/db31
> 266be0f8 35 12 0 2 1
> /dev/vx/rdsk/informixdg/db32
> The developer runs UPDATE STATISTICS medium on every database each day
> and runs UPDATE STATISTICS high on 4 index fields on the 2
> biggest databases once a week
And do you trust you developer is doing it all correctly :-)
Well. At least you have some thought to start with :-)
Jo
___________________________________________________________________________
This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author and