We have a multi purpuse database of about 500 GB on a SAN storing system
with about 100 concurrent users. Although we are using many indexes it
helped very much to have 20GB of RAM for the database. The users directly
can see by the response time of the standard input windows if data is
loaded
from the disks or if it is already in the main memory: if it is in memory
the problematic queries take 0.5 to 3 seconds, if it must be loaded it can
take also more than 60 seconds if there are other users requiring data
from
disk. Unfortunately the Windows 64bit Operating System does seem not to
make
always the best usage of the additional memory: We see many page faults in
the processes. But nevertheless I would say: after having a multiprocessor
CPU the most im****tant part is the quantity of main memory.
Arne Ortlinghaus
ACS Data Systems
"Pat" <pat.casey@[EMAIL PROTECTED]
> schrieb im Newsbeitrag
news:e71181dd-9753-4709-a063-ef2fc5254d26@[EMAIL PROTECTED]
> On May 8, 9:00 pm, "Ana C. Dent" <anaced...@[EMAIL PROTECTED]
> wrote:
>> Pat <pat.ca...@[EMAIL PROTECTED]
> wrote in news:12a7f1d9-6dce-4ba5-9d41-
>> 73c18ab0d...@[EMAIL PROTECTED]
>>
>> When your only tool is a hammer, all problems are viewed as nails.
>>
>>
>>
>> > The classic solution to this is:
>> > add more memory
>>
>> What you are attempting to do is covert Physical I/O to Logical I/O.
>>
>> A smarter solution is to add an index to reduce I/O by orders of
>> magnitude.
>
> The problem here isn't excessive table scans or an absence of indexes.
> The working set of indexes simply don't fit in cache all that well.
> I've got mutltiple indexes > 1 G in size and a half dozen or so >
> 500M.
>
> So, while I appreciate the tutorial on the im****tance of indexes as a
> component to an efficient data retreival strategy, I find it a bit odd
> that you're acting as though cache memory isn't an analagous
> component.
>
> This is the database back end for an enterprise application, it's not
> a data warehouse application. It tends to aggressively chew over the
> same working set (the aforementioned 10-12G of memory) querying it in
> all sorts of unpredictable, end-user defined, ways. If I knew a set of
> additional indexes I could add that would reduce my working set, I'd
> have already added them. At this point, the only solution I can see
> here is to bump up the SGA so that my (existing) index and data blocks
> fit in memory.
|
