Performance tuning: cannot get %write cache above 85%
Eben
The environment: IDS 9.21UC7 Solaris 8 - supposed to be OLTP, but
reads over writes are about 99 % reads to 1% writes.
I am struggling to tune write cache above 85%:
Informix Dynamic Server 2000 Version 9.21.UC7 -- On-Line -- Up 16
days 02:15
:21 -- 301056 Kbytes
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
242754974 742675918 2393666100 89.86 4857542 43038645 23354840
79.20
isamtot open start read write rewrite delete commit
rollbk
3362325381 193679474 377821399 2030202937 4993613 795962 1203891
792874 93
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 1746 438116.99 37041.94 777 2500
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress
seqscans
7333045 9 2772340802 0 0 528 514311
8084838
ixda-RA idx-RA da-RA RA-pgsused lchwaits
16903319 51351542 58589526 126441210 901836
My bufwaits ratio is low, RA usage is good:
Read Utilization (UR): 99.6821 %
Bufwaits Ratio (BR): 0.957329 %
Checkpoints take between 0 and 2 seconds:
08:01:48 Checkpoint duration was 0 seconds,
08:31:49 Checkpoint duration was 2 seconds,
09:01:51 Checkpoint duration was 2 seconds,
09:31:53 Checkpoint duration was 1 seconds,
Yet the write cache remains low!
The only problem I can see is that all writes occur as chunk writes:
Fg Writes LRU Writes Chunk Writes
1746 0 603944
i.e. LRUMAX and LRUMIN is too high:
LRUS 32 # Number of LRU queues
LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning
limit
LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit
CLEANERS 32
The site previously hit the bug of Btree pages taking over the buffer
pool.
We have set LRUAGE env variable and it appears to have solved the
problem, however it now indicates FG writes, which according to Tech
Support is a bug - there are no Fg writes happening.
onstat -P|tail:
Totals: 54000 4872 39099 10029 0 826
Percentages:
Data 72.41
Btree 9.02
Other 18.57
I cannot see how decreasing LRUMAX and LRUMIN would solve my problem
of low write cache, but?
RA page settings seem to be very high:
RA_PAGES 128
RA_THRESHOLD 80
Could this be the cause of a low write cache %?
Advice would be appreciated.
Thanks.
Eben
Obnoxio The Clown
Is it a problem, i.e., are your users bitching? Probably not. Good write
cache is a nice to have, IMHO.
These are probably not used to that extent. Try 16/12.
The Big Potatoe
"Obnoxio The Clown" <obn...@hotmail.com> wrote in message
news:3D9C1EFD...@hotmail.com...
> Eben wrote:
> > Hi all,
> >
> > The environment: IDS 9.21UC7 Solaris 8 - supposed to be OLTP, but
> > reads over writes are about 99 % reads to 1% writes.
> >
> > I am struggling to tune write cache above 85%:
> > Informix Dynamic Server 2000 Version 9.21.UC7 -- On-Line -- Up 16
> > days 02:15
> > :21 -- 301056 Kbytes
> > Profile
> > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> > 242754974 742675918 2393666100 89.86 4857542 43038645 23354840
> > 79.20
> > isamtot open start read write rewrite delete commit
> > rollbk
> > 3362325381 193679474 377821399 2030202937 4993613 795962 1203891
> > 792874 93
> > 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 1746 438116.99 37041.94 777 2500
ovbuff => Run out of immediately available buffers, this will probably cause
a fg write.
Reduce LRU_MAX_DIRTY and LRU_MIN_DIRTY to represent < 16Mb (8Mb on crap
systems).
> > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress
> > seqscans
bufwaits => What a lot. Reduce LRU_MAX_DIRTY .....
Try 128 / 32 ;).
How many BUFFERS?
Where's my bangers?
Eben
The users used to complain like mad about performance until we set LRUAGE.
Initially they said it was MUCH better, but now they are getting spoilt again.
Obnoxio The Clown <obn...@hotmail.com> wrote in message news:<3D9C1EFD...@hotmail.com>...
Obnoxio The Clown
> Thanks for the advice so far Obnoxio.
> The users used to complain like mad about performance until we set LRUAGE.
> Initially they said it was MUCH better, but now they are getting spoilt again.
What kind of box is it? How much RAM, how many CPUs, etc?
Art S. Kagel
high but I can't know for sure since you did not post the BUFFERS value.
You should be turning over buffers very seldom say 2-6 times an hour
on a read-mostly system. As for the chunk writes get the
LRU_MAX/MIN_DIRTY down from 60/50 to say 10/5 or even 2/1 and you'll see
that shift to LRU Writes.
Art S. Kagel
Eben
> What kind of box is it? How much RAM, how many CPUs, etc?
Sorry, I should have posted this too:
Sun enterprise 450. 2 CPUs:
System Configuration: Sun Microsystems sun4u
Memory size: 512 Megabytes
SUNW,Ultra-4
SUNW,UltraSPARC-II (driver not attached)
RAM too little, I know. I cannot allocate more BUFFERS, else I have to
allocate less Virtual:
BUFFERS 54000 # Maximum number of shared buffers
SHMVIRTSIZE 120000 #
SHMADD 32000 #
SHMTOTAL 512000 #
RESIDENT 1 # Forced residency flag (Yes = 1, No =
0)
onstat -g seg
Informix Dynamic Server 2000 Version 9.21.UC7 -- On-Line -- Up 17
days 00:55
:19 -- 301056 Kbytes
Segment Summary:
id key addr size ovhd class blkused
blkfree
100 1381713921 a000000 150994944 219888 R* 33118 3746
101 1381713922 13000000 123731968 4376 V 23037 7171
102 1381713923 1a600000 1048576 632 M 156 100
105 1381713924 1a700000 33554432 1624 V 345 7847
Total: - - 309329920 - - 56656 18864
Bangers? Like in Bangers & mash?
Sorry I'm only British Colonial - not pure!
Thanks again.
Obnoxio The Clown
> Obnoxio The Clown <obn...@hotmail.com> wrote in message news:<3D9C47CF...@hotmail.com>...
>
>
>>What kind of box is it? How much RAM, how many CPUs, etc?
>
>
> Sorry, I should have posted this too:
>
> Sun enterprise 450. 2 CPUs:
> System Configuration: Sun Microsystems sun4u
> Memory size: 512 Megabytes
> SUNW,Ultra-4
> SUNW,UltraSPARC-II (driver not attached)
>
> RAM too little, I know. I cannot allocate more BUFFERS, else I have to
> allocate less Virtual:
> BUFFERS 54000 # Maximum number of shared buffers
> SHMVIRTSIZE 120000 #
> SHMADD 32000 #
> SHMTOTAL 512000 #
> RESIDENT 1 # Forced residency flag (Yes = 1, No =
> 0)
Try RESIDENT -1
I just re-read your original post, you have a read cache rate of below
90%. That is dire -- you *have* to get more buffers.
Andy Lennard
<obn...@hotmail.com> writes>>CF.50...@hotmail.com>...
>>
>>>What kind of box is it? How much RAM, how many CPUs, etc?
>> Sorry, I should have posted this too:
>> Sun enterprise 450. 2 CPUs:
>> System Configuration: Sun Microsystems sun4u
>> Memory size: 512 Megabytes
>> SUNW,Ultra-4
>> SUNW,UltraSPARC-II (driver not attached)
>> RAM too little, I know. I cannot allocate more BUFFERS, else I have
>>to
>> allocate less Virtual:
>> BUFFERS 54000 # Maximum number of shared buffers
>> SHMVIRTSIZE 120000 #
>> SHMADD 32000 # SHMTOTAL 512000 #
>> RESIDENT 1 # Forced residency flag (Yes = 1, No =
>> 0)
>
>Try RESIDENT -1
Maybe not? Forcing everything to be resident on a machine with too
little RAM may cause it to stop completely. I'd suggest sticking with 1.
>
>I just re-read your original post, you have a read cache rate of below
>90%. That is dire -- you *have* to get more buffers.
>
>> onstat -g seg
>> Informix Dynamic Server 2000 Version 9.21.UC7 -- On-Line -- Up 17
>> days 00:55
>> :19 -- 301056 Kbytes
>> Segment Summary:
>> id key addr size ovhd class blkused
>>blkfree
>> 100 1381713921 a000000 150994944 219888 R* 33118 3746
>> 101 1381713922 13000000 123731968 4376 V 23037 7171
>> 102 1381713923 1a600000 1048576 632 M 156 100
>> 105 1381713924 1a700000 33554432 1624 V 345 7847
>> Total: - - 309329920 - - 56656 18864
>> Bangers? Like in Bangers & mash?
>> Sorry I'm only British Colonial - not pure!
>> Thanks again.
>
>
I'd go along with the 'get more RAM' guys here.
And I'd like to put in a good word for the engine here. If it is
delivering real performance, even though it clearly isn't 'over-
resourced', then it is doing really well in my book! Here's another
reason why I like Informix. Not only does it perform well when given
loads of CPU/RAM/disk, but it can carry on in tough times too, and from
what Eben says, the users are only complaining because they've got used
to the level of performance, not that they are unable to meet response,
or whatever, targets.
Respect to the guys responsible. And to Eben for squeezing every last
drop out of the configuration.
--
Andrew Lennard an...@kontron.demon.co.uk
Madison Pruet
If you can not get more memory, then you must reduce your sequential scans. Since your read
buffer activity outweighs your write buffer activity, you should be far more concerned with the
read cache rate, not the write cache rate. This means that you will need to keep the buffer pool
fairly clean. Otherwise, you will end up with a larger percentage of you buffer pool filled up
with dirty pages that need to be flushed and thus have fewer pages for read data. Generally the
read cache rate is far more important than the write cache rate. It's best to try to keep the read
cache rate above 97%.
The best thing is to get more memory and increase the buffer pool size. Short of that...
Check the dictionary size (onstat -g dic). If your statements are not prepared, but tend to be
dynamic in nature, then you will be re-reading the dictionary information constantly. If the
dictionary cache pool size is too small for your work, you may want to consider increasing the
size.
Decrease lru min/max. You need to decrease your chunk writes. --- Often a write will update a
page and the dirty page will hang arround until the checkpoint. There is nothing gained by that if
the page is never written to again. Instead the page needs to be flushed so that it can be easily
reused as a read buffer. Do not set lur min/max to 0/1. Set it to 1/2...
You need to turn on table statistics and check onstat -g ppf. (This may require setting
TABLSPACE_STATS 1 in your onconfig.) You need to check for the partitions that are having
sequential scans (the seqsc column). The partition can be converted into a table name by matching
with the partnum in systables or sysfragments. There is nothing wrong with doing sequential scans
against index partitions or if the data partition is very small. But if you are doing scans
against larger data partitions, then you need to do some further work to see if some indexes would
help. Also, you can get an idea as to which partitions your system activity is going against.
Examine syssqexplan from sysmaster. It might help identifying the queries that are going against
those tables.
Eben wrote:
--
---------------------------------------
Madison Pruet
Enterprise Replication Product Development
IBM Informix Dynamic Server
Dallas, Texas
lalaker
I saw you post a while ago and upon reading it I gathered that you
are having performance issues with your informix setup on SUN E-450.
Can you answer a few simple questions for me.
1. Are you using cooked filesystems or raw devices ??
2. The disks that your are writing to /reading from are they internal
or external( connected via a D1000 or similar storage array )
I might be able to solve your problem.
Shailesh
vanren...@dwaf.gov.za (Eben) wrote in message news:<22000b9f.02100...@posting.google.com>...