All,

I am using 9.30 on HP-UX 11i. The select queries take quite a long tpo
return. The queries are taking index paths and statistics are also updated.
When I tried to look in to the I/O activity, I could find that kio is
having maxlen around 16-17. All other things do not have any thing in
maxlen. I am attaching the first few lines of onstat -g ioq and onstat -g
iov outputs and later the onconfig.
RAM on m/c: 8 GB
CPUs : 4

Could someone please help me nail down the root cause ?

onstat -g ioq
q name/id len maxlen totalops dskread dskwrite dskcopy
kio 0 0 16 413046691 409498127 3548564 0
kio 1 0 17 311094309 307235554 3858755 0
kio 2 0 16 218761675 215065216 3696459 0
adt 0 0 0 0 0 0 0
msc 0 0 1 37339 0 0 0
aio 0 0 29 38152 1090 34347 0

onstat -g iov
class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup
errors
kio 0 s 542.1 266043030 262575149 3467881 0 505359119 0.5
0
kio 1 s 301.4 147943204 144184932 3758272 0 268265145 0.6
0
kio 2 s 317.3 155740487 152138171 3602316 0 287022047 0.5
0
msc 0 i 0.1 37339 0 0 0 37327 1.0 0
aio 0 i 0.1 37966 1089 34192 0 37924 1.0 0
aio 1 i 0.0 160 1 154 0 200 0.8 0
aio 2 i 0.0 3 0 1 0 3 1.0 0

ONCONFIG :
ROOTNAME app3_rootdbs # Root dbspace name
ROOTPATH /dev/infdev/app3_rootdbs.1
# Path for device containing root dbspace
ROOTOFFSET 0 # Offset of root dbspace into device
(Kbytes)
ROOTSIZE 2048000 # Size of root dbspace (Kbytes)

# Disk Mirroring Configuration Parameters

MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
MIRRORPATH # Path for device containing mirrored root
MIRROROFFSET 0 # Offset into mirrored device (Kbytes)

# Physical Log Configuration

PHYSDBS app3_plogdbs # Location (dbspace) of physical log
PHYSFILE 409494 # Physical log file size (Kbytes)

# Logical Log Configuration

LOGFILES 30 # Number of logical log files
LOGSIZE 200000 # Logical log size (Kbytes)

# Diagnostics

MSGPATH /var/opt/informix/1/logs/online.log
# System message log file path
CONSOLE /dev/null # System console message path
ALARMPROGRAM /opt/informix/etc/log_full_1.sh # Alarm program path
SYSALARMPROGRAM /opt/informix/etc/evidence_1.sh
TBLSPACE_STATS 0 # Maintain tblspace statistics

# System Archive Tape Device
#TAPEDEV /dev/null # Tape device path
TAPEDEV /appadm2/informix/tapedev
TAPEBLK 4096 # Tape block size (Kbytes)
TAPESIZE 40000000 # Maximum amount of data to put on tape
(Kbytes)

# Log Archive Tape Device

LTAPEDEV /opt/informix/etc/ltapedev_1 # Log tape device path
LTAPEBLK 64 # Log tape block size (Kbytes)
LTAPESIZE 2000000 # Max amount of data to put on log tape
(Kbytes)

# Optical

STAGEBLOB # Informix Dynamic Server staging area

# System Configuration

SERVERNUM 1 # Unique id corresponding to a OnLine
instance
DBSERVERNAME app3_ea_srv # Name of default database server
DBSERVERALIASES gisops88_1,gisops88_1_soc # List of alternate dbservernames
NETTYPE soctcp,2,350,NET # Configure poll thread(s) for nettype
DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed
env.
RESIDENT 4294967295 # Forced residency flag (Yes = 1, No = 0)

MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-
processor
NUMCPUVPS 3 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to
one

NOAGE 1 # Process aging
AFF_SPROC 0 # Affinity start processor
AFF_NPROCS 0 # Affinity number of processors

# Shared Memory Parameters

LOCKS 500000 # Maximum number of locks
BUFFERS 300000 # Maximum number of shared buffers
NUMAIOVPS 30 # Number of IO vps
PHYSBUFF 320 # Physical log buffer size (Kbytes)
LOGBUFF 256 # Logical log buffer size (Kbytes)
CLEANERS 20 # Number of buffer cleaner processes
SHMBASE 0x0 # Shared memory base address
SHMVIRTSIZE 1024000 # initial virtual shared memory segment size
SHMADD 512000 # Size of new shared memory segments
(Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL 1200 # Check point interval (in sec)
LRUS 256 # Number of LRU queues
LRU_MAX_DIRTY 5 # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY 2 # LRU percent dirty end cleaning limit
TXTIMEOUT 0x12c # Transaction timeout (in sec)
STACKSIZE 64 # Stack size (Kbytes)

# Dynamic Logging
# DYNAMIC_LOGS:
# 2 : server automatically add a new logical log when necessary. (ON)
# 1 : notify DBA to add new logical logs when necessary. (ON)
# 0 : cannot add logical log on the fly. (OFF)
#
# When dynamic logging is on, we can have higher values for LTXHWM/LTXEHWM,
# because the server can add new logical logs during long transaction
rollback.
# However, to limit the number of new logical logs being added,
LTXHWM/LTXEHWM
# can be set to smaller values.
#
# If dynamic logging is off, LTXHWM/LTXEHWM NEED to be set to smaller values
# to avoid long transaction rollback hanging the server due to lack of
logical
# log space, i.e. 50/60 or lower.

DYNAMIC_LOGS 2
LTXHWM 70
LTXEHWM 80

# System Page Size
# BUFFSIZE - OnLine no longer supports this configuration parameter.
# To determine the page size used by OnLine on your platform
# see the last line of output from the command, 'onstat -b'.


# Recovery Variables
# OFF_RECVRY_THREADS:
# Number of parallel worker threads during fast recovery or an offline
restore.
# ON_RECVRY_THREADS:
# Number of parallel worker threads during an online restore.

OFF_RECVRY_THREADS 10 # Default number of offline worker
threads
ON_RECVRY_THREADS 1 # Default number of online worker threads
# Data Replication Variables
DRINTERVAL 30 # DR max time between DR buffer flushes (in
sec)
DRTIMEOUT 30 # DR network timeout (in sec)
DRLOSTFOUND /var/opt/informix/1/dr.lost+found # DR lost+found file path

# CDR Variables
CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional)
CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds)
CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR
queue (Kbytes)
CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none,
9 max)
CDR_SERIAL 0,0 # Serial Column Sequence
CDR_DBSPACE # dbspace for syscdr database
CDR_QHDR_DBSPACE app3_er_dbspace # CDR queue dbspace (default same as
catalog)
CDR_QDATA_SBSPACE app3_er_sbspace # CDR queue smart blob space
CDR_QDATA_SBFLAGS 0 # Log/no-log (default no log)


# Backup/Restore variables
BAR_ACT_LOG /var/opt/informix/1/logs/bar_act.log
# ON-Bar Log file - not in /tmp please
BAR_MAX_BACKUP 0
BAR_RETRY 1
BAR_NB_XPORT_COUNT 10
BAR_XFER_BUF_SIZE 31
RESTARTABLE_RESTORE off
BAR_PROGRESS_FREQ 0

# Informix Storage Manager variables
ISM_DATA_POOL ISMData
ISM_LOG_POOL ISMLogs

# Read Ahead Variables
RA_PAGES 8 # Number of pages to attempt to read ahead
RA_THRESHOLD 4 # Number of pages left before next group

# DBSPACETEMP:
# OnLine equivalent of DBTEMP for SE. This is the list of dbspaces
# that the OnLine SQL Engine will use to create temp tables etc.
# If specified it must be a colon separated list of dbspaces that exist
# when the OnLine system is brought online. If not specified, or if
# all dbspaces specified are invalid, various ad hoc queries will create
# temporary files in /tmp instead.
DBSPACETEMP app3_tmpdbs1,app3_tmpdbs2,app3_tmpdbs3,app3_tmpdbs 4
# Default temp dbspaces

# DUMP*:
# The following parameters control the type of diagnostics information which
# is preserved when an unanticipated error condition (assertion failure)
occurs
# during OnLine operations.
# For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No.

DUMPDIR /var/opt/informix/1/dump #Preserve diagnostics in this
directory
DUMPSHMEM 0 # Dump a copy of shared memory
DUMPGCORE 0 # Dump a core image using 'gcore'
DUMPCORE 0 # Dump a core image (Warning:this aborts
OnLine)
DUMPCNT 1 # Number of shared memory or gcore dumps for
# a single user's session

FILLFACTOR 90 # Fill factor for building indexes

# method for OnLine to use when determining current time
USEOSTIME 0 # 0: use internal time(fast), 1: get time
from OS(slow)

# Parallel Database Queries (pdq)
MAX_PDQPRIORITY 10 # Maximum allowed pdqpriority
DS_MAX_QUERIES 500 # Maximum number of decision support queries
DS_TOTAL_MEMORY 1024000 # Decision support memory (Kbytes)
DS_MAX_SCANS 256 # Maximum number of decision support scans
DATASKIP off # List of dbspaces to skip

# 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 0 # To hint the optimizer

DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default) or
OFF (0)

ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 =
ABORT, 2 = WAIT
OPCACHEMAX 0 # Maximum optical cache size (Kbytes)

# HETERO_COMMIT (Gateway participation in distributed transactions)
# 1 => Heterogeneous Commit is enabled
# 0 (or any other value) => Heterogeneous Commit is disabled
HETERO_COMMIT 0

SBSPACENAME # Default smartblob space name - this is
where blobs
# go if no sbspace is specified when the smartblob is
# created. It is also used by some datablades as
# the location to put their smartblobs.
SYSSBSPACENAME # Default smartblob space for use by the
Informix
# Server. This is used primarily for Informix Server
# system statistics collection.

BLOCKTIMEOUT 3600 # Default timeout for system block
SYSALARMPROGRAM /opt/informix/etc/no_log.sh # System Alarm program path

# Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS
OPT_GOAL -1

ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 or
anything but 1)
BAR_DEBUG_LOG /tmp/bar_dbug.log # ON-Bar Debug Log - not in /tmp please


Regards,
Anirudh

--
Message posted via http://www.dbmonster.com

"Anirudh Chitnis via DBMonster.com" <forum@DBMonster.com> wrote in message
news:4d71b87ec26b42cbba3bfa8ae42593e7@DBMonster.co m...
> All,
>
> I am using 9.30 on HP-UX 11i. The select queries take quite a long tpo
> return. The queries are taking index paths and statistics are also
> updated.
> When I tried to look in to the I/O activity, I could find that kio is
> having maxlen around 16-17. All other things do not have any thing in
> maxlen. I am attaching the first few lines of onstat -g ioq and onstat -g
> iov outputs and later the onconfig.
> RAM on m/c: 8 GB
> CPUs : 4

Can you post the query path from one of the long-running queries (use set
explain on?

You should be able safely to increase BUFFERS to, say 800000.
I wonder what effect this has: RESIDENT 4294967295?!!

PFB the onstat -p output and my thoughts on the same. Please provide your
valuable inputs.

The %cached for buffered read is a low number?it should be well above 95%.
Adding more buffers should solve this problem.

bufwaits can be brought down by reducing the RA_PAGES and RA_THRESHOLD.
params.This can be further checked from the RA stats below?.RA-pgsused is
greater than sum of ixda-RA ids-RA n da-RA

onstat ?p

IBM Informix Dynamic Server Version 9.30.FC6W4 -- On-Line -- Up 8 days
10:22:5

0 -- 1746404 Kbytes

Profile

dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached

1525587639 2242129221 16409885204 90.70 13386474 31272626 96935964 86.19

isamtot open start read write rewrite delete commit
rollbk

16611229372 161354924 870540973 12672021046 19719184 21971728 1518044
10158667

12809



gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs

706929 14664 3254021 14152 0 0 4

ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes

0 0 0 102375.25 31659.65 608 1801


bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans

131086405 5097807 2429049027 3 0 903 1938576 94027


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

38469248 109333718 748405431 894562606 4758183

--
Message posted via http://www.dbmonster.com

Anirudh Chitnis via DBMonster.com wrote:
>
> I am using 9.30 on HP-UX 11i. The select queries take quite a long tpo
> return. The queries are taking index paths and statistics are also updated.
> When I tried to look in to the I/O activity, I could find that kio is
> having maxlen around 16-17. All other things do not have any thing in
> maxlen. I am attaching the first few lines of onstat -g ioq and onstat -g
> iov outputs and later the onconfig.
> RAM on m/c: 8 GB
> CPUs : 4
>
> Could someone please help me nail down the root cause ?

OK, here are the metrics I use to start looking at things:

> calc_ratios
Pagreads: 2242129221
Bufwrits: 96935964
Bufwaits: 96935964
BUFFERS: 300000
Time since reset: 202.33
ixda-RA: 38469248
idx-RA: 109333718
da-RA: 748405431
RA-pgsused: 894562606

BR = (96935964 / (96935964 + 2242129221)) * 100.00 = 4.1400
BTR = (((96935964 + 2242129221) / 300000) / 202.33) = 38.5354/hour
RAU = (894562606/(38469248+109333718+748405431)) * 100.00 = 99.8100

Your Bufwaits Ratio (BR) is fine at 4 so your LRUS settings are OK (however,
you have LRUS set to 256 and the max value is 128! What does onstat -R
report as the number of buffer LRU queue pairs?

Your ReadAhead Utilization rate is 99.81% which is just fine! DO NOT MUCK
WITH THE RA values much. You may try 32 & 4 instead of 8 & 4 but DO NOT go
hog wild as TBP suggests. I think that will be disasterous for throughput.

Your Buffer Turnover Rate of 38.5354/hour is way too high and indicates that
you could likely increase your BUFFERS several times to at least 600000 but
maybe as high as 1500000. The lowish read cache percentage supports the
same conclusion. Start slow by doubling it and see how that goes. Monitor
onstat -P to see if there might be a small number of tables which are
trading off thrashing each other out of the buffer pool.

Your NUMAIOVPS is too high for a server that uses KAIO. Reset to 6 and
monitor onstat -g iov. If there are any aio vps with io/wup that are 0.0
you can drop that many more. Normally a KAIO server uses 4-8 aio vps
depending on how much it writes to the message log, console log, etc.

Your KAIO queue lengths are not terrible at 17 but could be better. Can you
reconfigure the disk farm into a quicker service? Perhaps by trading RAID5
in for RAID10, single spindles in for an array, or a single RAID10 array in
for multiple ones or for a PLAID of several arrays?

Change RESIDENT to -1 that will load all possible current and future shared
segments as locked and will try to compress the resident segment with the
first virtual segment to reduce overhead.

Set CLEANERS >= to LRUS, I would set both to 128. This minimizes the time
spent flushing the LRU queues under peak load.
<Details snipped>
Art S. Kagel

"Art S. Kagel" <kagel@bloomberg.net> wrote in message ...
<snip>
> Time since reset: 202.33
> ixda-RA: 38469248
> idx-RA: 109333718
> da-RA: 748405431
> RA-pgsused: 894562606
>
> BR = (96935964 / (96935964 + 2242129221)) * 100.00 = 4.1400
> BTR = (((96935964 + 2242129221) / 300000) / 202.33) = 38.5354/hour
> RAU = (894562606/(38469248+109333718+748405431)) * 100.00 = 99.8100
>
<snip>
> Your ReadAhead Utilization rate is 99.81% which is just fine! DO NOT MUCK
> WITH THE RA values much. You may try 32 & 4 instead of 8 & 4 but DO NOT go
> hog wild as TBP suggests. I think that will be disasterous for throughput.
>

Humm, well, proof would be in the pudding, but I think that is a
"rash" statement to make without knowing more details about what the
geezer is using this instance for.

99.8100 is a good number, but doesn't that infer that there is scope
for improved usage if there is more read ahead?

I think I stated to "do something stupid once in your life" (I have
acheived that regularly) and set :

RA_PAGES 1024
RA_THRESHOLD 64

which is pretty stupid UNLESS, I am the only person on this system and
I want to fly through a huge table, and then it is pretty good.

Looking at a compromise (and we do not know what this geezer has),
let's say he has 8 big report type users, and 32 little OLTP type
users, then :

RA_PAGES 128
RA_THRESHOLD 32

would be okey dokey.

BUT ... proof is in the pudding, not in the decimal places

So, what did the geezer do?

TheBigPotatoe wrote:
> "Art S. Kagel" <kagel@bloomberg.net> wrote in message ...
> <snip>
>
>>Time since reset: 202.33
>>ixda-RA: 38469248
>>idx-RA: 109333718
>>da-RA: 748405431
>>RA-pgsused: 894562606
>>
>>BR = (96935964 / (96935964 + 2242129221)) * 100.00 = 4.1400
>>BTR = (((96935964 + 2242129221) / 300000) / 202.33) = 38.5354/hour
>>RAU = (894562606/(38469248+109333718+748405431)) * 100.00 = 99.8100
>>
>
> <snip>
>
>>Your ReadAhead Utilization rate is 99.81% which is just fine! DO NOT MUCK
>>WITH THE RA values much. You may try 32 & 4 instead of 8 & 4 but DO NOT go
>>hog wild as TBP suggests. I think that will be disasterous for throughput.
>>
>
>
> Humm, well, proof would be in the pudding, but I think that is a
> "rash" statement to make without knowing more details about what the
> geezer is using this instance for.

> 99.8100 is a good number, but doesn't that infer that there is scope
> for improved usage if there is more read ahead?

Let me say this for everyone and for all time. Excessive readahead is the
single most pervasive cause of poor performance and requiring excessive
caching than any other cause. Perhaps than all other causes combined!

> I think I stated to "do something stupid once in your life" (I have
> acheived that regularly) and set :

Read the manuals. Any single query that's actually performing sequential
reads of 1088 pages in a row will be using light scans which neither use the
RA_ parameters nor the buffer pool! Besides, unless the disk farm serving
this instance is JBOD and those disks and controllers are very old, the
drives are all performing their own readahead to their own cache as is the
SCSI controller (noone's running Informix on an ATA controller right?
<smirk>) itself to it's own cache.

> RA_PAGES 1024
> RA_THRESHOLD 64
>
> which is pretty stupid UNLESS, I am the only person on this system and
> I want to fly through a huge table, and then it is pretty good.

IF you are the only person using the server and you are flying through a
huge table with a hughe sequential scan then A) the scan is likely using
light scan buffers not the buffer pool, B) if the query does not lend itself
to light scanning then the data either is not yet in the buffer pool in
which case it has to be read in anyway. If the larger RA can keep up with a
single user screaming through the data then so can the normal levels of RA.
If it cannot then the disk farm is not fast enough and it does not matter!

A single user is not the one who is helped by RA and not the one who is
hurt. RA helps during multi-user operations when IOs can be prescheduled by
RA while the user's threads are suspended to allow other users to run.

The down side of this is that all that RA will thrash the buffer pool and
force other needed pages out of the pool. With RA set to 1024/64 even a
small sequential read of 4 pages will trigger a 1024 page read which will
flush 1020 pages that noone is interested in and noone will every reuse
while forcing another 1020 pages of perhaps recently accessed data that may
be needed again soon to be overwritten.

> Looking at a compromise (and we do not know what this geezer has),
> let's say he has 8 big report type users, and 32 little OLTP type
> users, then :
>
> RA_PAGES 128
> RA_THRESHOLD 32
>
> would be okey dokey.
>
> BUT ... proof is in the pudding, not in the decimal places
>
> So, what did the geezer do?

I can only speak from my own experience which is with over 50 instances
serving over MANY 10s of thousands of users with many terabytes of data
24x7x365 here on our systems and on the hundreds of sites around the world
that have asked for help over the years. The overwhelming conclusion is
that when it comes to RA and performance - less is more.

Art S. Kagel

Art S. Kagel schrieb:
[ ... snip ... ]

> Read the manuals. Any single query that's actually performing
> sequential reads of 1088 pages in a row will be using light scans which
> neither use the RA_ parameters nor the buffer pool! Besides, unless the
> disk farm serving this instance is JBOD and those disks and controllers
> are very old, the drives are all performing their own readahead to their
> own cache as is the SCSI controller (noone's running Informix on an ATA
> controller right? <smirk>) itself to it's own cache.

Actually - after some testing - we have excellent performance reading
from 3ware Escalde Controllers serving 6 SATA disks each on PCI/X

Excellent means > 75.000 2KB pages/sec when readingfrom 'disk',
that is when diskreads are counting up.
(This nice and cheap machine does 125.000 bufreads per second!)
Needless to mention, that we do NOT configure the arrays to RAID5.

My findings are that motivating mgmt to NOT going the RAID5 way
is much easier when 1 GB costs 0,80 Eurtocent as it is with SATA!
SCSI disks here are still around 8 Euro/GB, which is faktor of 10.

But: What do I know.
I do not dare to compare this SATA setup to whatever has disks inside
and is expensive and slow at the samer time. Maufakturers prolly
would take me to court.
But you can test for yourself if you get similar performance out of
your 'EMitachi700'
Or you go the short way and simply test how much less than this
amount of data rushes thru your supafast 2 Gbit zones on your fabric.
*this was a bit sarcastic, yes I know*

For those who are new here: I am the maniac who
used ONARCHIVE for a long period of time on over 100 machines with
great success. Especially ONDATARTR is what I miss a lot ...

[ ... snip ... ]

dic_k
--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe

Art S. Kagel wrote:
> TheBigPotatoe wrote:
>
>> "Art S. Kagel" <kagel@bloomberg.net> wrote in message ...
>> <snip>
>
> Let me say this for everyone and for all time. Excessive readahead is
> the single most pervasive cause of poor performance and requiring
> excessive caching than any other cause. Perhaps than all other causes
> combined!
>

Yep , or perhaps leaving BUFFERS at 200?

>> I think I stated to "do something stupid once in your life" (I have
>> acheived that regularly) and set :
>

Yep

>
> Read the manuals. Any single query that's actually performing
> sequential reads of 1088 pages in a row will be using light scans which
> neither use the RA_ parameters nor the buffer pool! Besides, unless the
> disk farm serving this instance is JBOD and those disks and controllers
> are very old, the drives are all performing their own readahead to their
> own cache as is the SCSI controller (noone's running Informix on an ATA
> controller right? <smirk>) itself to it's own cache.
>

Humm, read the manuals, and they don't say "Sequential reads of 1088
pages in a row will cause light scans" :P

Rumour has it that the light scan buffer size is based on RA_PAGES.

Loads of intel users use ATA disks.

>> RA_PAGES 1024 RA_THRESHOLD 64
>>
>> which is pretty stupid UNLESS, I am the only person on this system and
>> I want to fly through a huge table, and then it is pretty good.
>

Pretty stupid / hog wild

>
> IF you are the only person using the server and you are flying through a
> huge table with a hughe sequential scan then A) the scan is likely using
> light scan buffers not the buffer pool,

IF I am in dirty read and the table is larger than buffers.

> B) if the query does not lend
> itself to light scanning then the data either is not yet in the buffer
> pool in which case it has to be read in anyway. If the larger RA can
> keep up with a single user screaming through the data then so can the
> normal levels of RA. If it cannot then the disk farm is not fast enough
> and it does not matter!

"Buy faster disks" -> yep

>
> A single user is not the one who is helped by RA and not the one who is
> hurt. RA helps during multi-user operations when IOs can be
> prescheduled by RA while the user's threads are suspended to allow other
> users to run.
>

Humm, okay.

> The down side of this is that all that RA will thrash the buffer pool
> and force other needed pages out of the pool. With RA set to 1024/64
> even a small sequential read of 4 pages will trigger a 1024 page read
> which will flush 1020 pages that noone is interested in and noone will
> every reuse while forcing another 1020 pages of perhaps recently
> accessed data that may be needed again soon to be overwritten.
>

"Do something stupid" and, yep, 1024 / 64 is pretty stupid! But if it
isn't tried then how do you know? (I guess we are not doing light scans
now :O))

>> Looking at a compromise (and we do not know what this geezer has),
>> let's say he has 8 big report type users, and 32 little OLTP type
>> users, then :
>>
>> RA_PAGES 128
>> RA_THRESHOLD 32
>>
>> would be okey dokey.
>>
>> BUT ... proof is in the pudding, not in the decimal places
>>
>> So, what did the geezer do?
>
>
> I can only speak from my own experience which is with over 50 instances
> serving over MANY 10s of thousands of users with many terabytes of data
> 24x7x365 here on our systems and on the hundreds of sites around the
> world that have asked for help over the years. The overwhelming
> conclusion is that when it comes to RA and performance - less is more.
>
> Art S. Kagel

I sort of completely agree with you, and I sort of don't. BUT without
knowing more about what this geezer is doing (and the user base), I
think it a bit "extreme" to state 128 / 32 is completely wrong.

Also, OTC does have a valid point about bad indexes - missing indexes
will cause buffer flooding as well, which affects everyone :O.

Allright, 32 / 4 is nice - depends on 4k or 2k platform, and also
what is read from disk in one go - ooops :O

Can you flame potatoes? Probably
--
"I have enough problems remembering my opinions, let alone justifying them."

"Sorry, I thought you tapped me."

TBP wrote:
> Art S. Kagel wrote:
>
>> TheBigPotatoe wrote:
>>
>>> "Art S. Kagel" <kagel@bloomberg.net> wrote in message ...
>>> <snip>
<more scissor work>
>
> I sort of completely agree with you, and I sort of don't. BUT without
> knowing more about what this geezer is doing (and the user base), I
> think it a bit "extreme" to state 128 / 32 is completely wrong.

OK.

> Also, OTC does have a valid point about bad indexes - missing indexes
> will cause buffer flooding as well, which affects everyone :O.

Definitely. But I normally ignore OTC, why change now?

> Allright, 32 / 4 is nice - depends on 4k or 2k platform, and also
> what is read from disk in one go - ooops :O

Ya.

> Can you flame potatoes? Probably

I LOVE roasted potatoes!

Art S. Kagel