Re: Comparing Top 5 Timed Events and Cache Advisory
Date: Fri, 17 Jun 2011 09:08:16 -0700 (PDT)
Message-ID: <4d8ea049-84d4-457d-a5b3-b36072c3cd65_at_e17g2000prj.googlegroups.com>
On Jun 16, 5:18 pm, Noons <wizofo..._at_gmail.com> wrote:
> On Jun 16, 11:38 am, vsevolod afanassiev
>
> <vsevolod.afanass..._at_gmail.com> wrote:
> > This database is running batch processing. We are trying to justify
> > purchasing more memory.
> > The question is: how much improvement in batch run time we'll get if
> > we double memory size? 25%? 50%?
>
> I won't repeat the good advice already given.
> Just one addition: note that the total batch run time is the sum of
> *all* I/O wait time *plus* all CPU used to process the data that has
> been brought into memory.
> I cannot see an indication of how much CPU versus how much I/O time is
> being spent in the batch run.
>
> But I can guarantee you that if CPU is, say, 70% and I/O wait 30%,
> then the most you can gain by only adding memory is a portion of the I/
> O wait, ie, a portion of 30%. It will never exceed that.
I don't see how you can guarantee that. What if the 70% is due to cpu run queues from i/o asking for cpu to perform i/o, and you wipe out the need for most of the i/o? It could happen your cpu usage could go down and it could all run twice as fast. (I suspect with no evidence certain OS patches have done something like that). As with every complex system, it depends, even a batch process is complex. Sometimes the answer is as simple as turning on async i/o 8-)
>
> However, if you look at the SQL with the intention of reducing how
> much I/O you have to do to process all data - say, by reducing use of
> intermediate tables - then you will have an immediate gain that
> reflects across the board: CPU + I/O.
>
> Worth a shot? In my book: yes.
I think we're all agreed that one needs to at least look at whether the work is optimal before throwing memory at it. In olden days, memory could help just about any system, but now even low end commodity machines may have enough.
jg
-- _at_home.com is bogus. Happy 00th birthday, IBM! http://www.pcmag.com/article2/0,2817,2387107,00.aspReceived on Fri Jun 17 2011 - 11:08:16 CDT