memory sort and disk sort

[dusong]

I check the sysprofile table and find there are 700 times disk sort,

I think it is lack of sort memory. I want to turn all the disk sort into
the memory sort ,but how can I malloc more sort memory for informix?

Now my shmvirtsize is 800000(800M)



Another question is:



my server run with 99% read cache hits but just 75% write cache hits,

how can I mprove the write cache hits ?






Thanks


--
Posted via [url]http://dbforums.com[/url]

[Art S. Kagel]

On Thu, 23 Oct 2003 03:15:11 -0400, dusong wrote:

Version and platform information is important! For example 7.31UD4 (and IB
9.30) and later improved the memory sorting algorithms which reduced the number
of sorts that go to disk.

Also using PDQPRIORITY and PSORT_NPROCS will improve sort efficiency and memory
usage by dividing up the sort job into smaller sub-sorts which are merged later.

> I check the sysprofile table and find there are 700 times disk sort,
>
> I think it is lack of sort memory. I want to turn all the disk sort into the
> memory sort ,but how can I malloc more sort memory for informix?
>
> Now my shmvirtsize is 800000(800M)
>
>
>
> Another question is:

Without more information one cannot answer this one. It could be too much read
ahead, too few BUFFERS, LRU_MAX/MIN_DIRTY too low, or just an application load
that is doing massive inserts or updating only one or two rows on a page but
touching most pages. Post your ONCONFIG file, onstat -p, onstat -P (top 15 and
bottom 10 lines only are needed and any partnums with lots of pages), the time
since the system stats were last zero'd (onstat -z) if after startup, and a
description of your disk farm (ie singleton drives, RAIDx, SAN, etc.) and we'll
see what we can see.

Art S. Kagel

> my server run with 99% read cache hits but just 75% write cache hits,
>
> how can I mprove the write cache hits ?
>
>
>
>
>
>
> Thanks
>
>
> --
> Posted via [url]http://dbforums.com[/url]

[dusong]

OS:HP-UX 11i

SERVer:9.4FC2

memory:12G

CPU:12



Buffer 3000000(6G)

LRU_MAX_DIRTY 1.5

LRU_MIN_DIRTY 1

RA_AHEAD 16

checkpoint 15 minutes



LRU writes is very few.


--
Posted via [url]http://dbforums.com[/url]

[Art S. Kagel]

On Fri, 24 Oct 2003 02:03:10 -0400, dusong wrote:

In a vacuum these ONCONFIG values look OK, but we need to see the onstat output
to make a diagnosis. Certainly you have lots of cache and an RA of 16 does not
look outrageous (is that the RA_PAGES or RA_THRESHOLD and what's the other one's
value?) Is KAIO enabled (not on by default on HP)?

Art S. Kagel

> OS:HP-UX 11i
>
> SERVer:9.4FC2
>
> memory:12G
>
> CPU:12
>
>
>
> Buffer 3000000(6G)
>
> LRU_MAX_DIRTY 1.5
>
> LRU_MIN_DIRTY 1
>
> RA_AHEAD 16
>
> checkpoint 15 minutes
>
>
>
> LRU writes is very few.
>
>
> --
> Posted via [url]http://dbforums.com[/url]

[dusong]

Thanks for your answer!

The KAIO is not open!



I try to send the onconfig and onstat_p file but fail,Can you tell me

your E-mail,I would send this two file to you.





my E-mail is :song_du@163.net







IBM Informix Dynamic Server Version 9.40.FC2W2 -- On-Line -- Up 3 days
20:54:43 -- 7974488 Kbytes



Profile

dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached

2513181 18806956 409655914 99.39 1244064 2863990 5121645 75.71



isamtot open start read write rewrite delete
commit rollbk

395390248 40381996 23683373 237457225 1159088 1553081 28937
296172 1728



gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs

0 0 0 0 0 0



ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes

0 0 8063.66 3449.07 57 162



bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans

243109 156 322993464 0 0 972 92377 493880



ixda-RA idx-RA da-RA RA-pgsused lchwaits

1322131 39167 115082 1475322 353609


--
Posted via [url]http://dbforums.com[/url]