Re: [Testperf-general] Re: [PERFORM] First set of OSDL Shared Mem
On Thu, 2004-10-14 at 16:57 -0700, Josh Berkus wrote: > Simon, > > > > > If you draw a graph of speedup (y) against cache size as a > > % of total database size, the graph looks like an upside-down "L" - i.e. > > the graph rises steeply as you give it more memory, then turns sharply at a > > particular point, after which it flattens out. The "turning point" is the > > "sweet spot" we all seek - the optimum amount of cache memory to allocate - > > but this spot depends upon the worklaod and database size, not on available > > RAM on the system under test. > > Hmmm ... how do you explain, then the "camel hump" nature of the real > performance?That is, when we allocated even a few MB more than the > "optimum" ~190MB, overall performance stated to drop quickly. The result is > that allocating 2x optimum RAM is nearly as bad as allocating too little > (e.g. 8MB). > > The only explanation I've heard of this so far is that there is a significant > loss of efficiency with larger caches. Or do you see the loss of 200MB out > of 3500MB would actually affect the Kernel cache that much? > In a past life there seemed to be a sweet spot around the applications working set. Performance went up until you got just a little larger than the cache needed to hold the working set and then went down. Most of the time a nice looking hump.It seems to have to do with the additional pages not increasing your hit ratio but increasing the amount of work to get a hit in cache.This seemed to be independent of the actual database software being used. (I observed this running Oracle, Informix, Sybase and Ingres.) > Anyway, one test of your theory that I can run immediately is to run the exact > same workload on a bigger, faster server and see if the desired quantity of > shared_buffers is roughly the same. I'm hoping that you're wrong -- not > because I don't find your argument persuasive, but because if you're right it > leaves us without any reasonable ability to recommend shared_buffer settings. > -- Timothy D. Witham - Chief Technology Officer - [EMAIL PROTECTED] Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton OR, 97005 (503)-626-2455 x11 (office)(503)-702-2871 (cell) (503)-626-2436 (fax) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] TPC-R benchmarks
On Thu, 2003-09-25 at 14:32, Jenny Zhang wrote:
> I am running TPC-H with scale factor of 1 on RedHat7.2 with the kernel
> 2.5.74. Q17 can always finish in about 7 seconds on my system. The
> execution plan is:
I just want to point out that we are the OSDL are not running
a TPC-X anything. We have fair use implementations of these
benchmarks but because of differences our performance tests can
not be compared with the TPCC's benchmark results.
>
> Aggregate (cost=780402.43..780402.43 rows=1 width=48)
>-> Nested Loop (cost=0.00..780397.50 rows=1973 width=48)
> Join Filter: ("inner".l_quantity < (subplan))
> -> Seq Scan on part (cost=0.00..8548.00 rows=197 width=12)
>Filter: ((p_brand = 'Brand#31'::bpchar) AND (p_container
> = 'LG CASE'::bpchar))
> -> Index Scan using i_l_partkey on lineitem
> (cost=0.00..124.32 rows=30 width=36)
>Index Cond: ("outer".p_partkey = lineitem.l_partkey)
> SubPlan
>-> Aggregate (cost=124.40..124.40 rows=1 width=11)
> -> Index Scan using i_l_partkey on lineitem
> (cost=0.00..124.32 rows=30 width=11)
>Index Cond: (l_partkey = $0)
> (11 rows)
>
> Hope this helps,
> Jenny
> On Thu, 2003-09-25 at 12:40, Oleg Lebedev wrote:
> > I am running TPC-R benchmarks with a scale factor of 1, which correspond
> > to approximately 1 GB database size on PostgreSQL 7.3.4 installed on
> > CygWin on Windows XP. I dedicated 128 MB of shared memory to my postrges
> > installation.
> > Most of the queries were able to complete in a matter of minutes, but
> > query 17 was taking hours and hours. The query is show below. Is there
> > any way to optimize it ?
> >
> > select
> > sum(l_extendedprice) / 7.0 as avg_yearly
> > from
> > lineitem,
> > part
> > where
> > p_partkey = l_partkey
> > and p_brand = 'Brand#11'
> > and p_container = 'SM PKG'
> > and l_quantity < (
> > select
> >0.2 * avg(l_quantity)
> > from
> >lineitem
> > where
> >l_partkey = p_partkey
> > );
> >
> > Thanks.
> >
> > Oleg
> >
> > *
> >
> > This e-mail may contain privileged or confidential material intended for the named
> > recipient only.
> > If you are not the named recipient, delete this message and all attachments.
> > Unauthorized reviewing, copying, printing, disclosing, or otherwise using
> > information in this e-mail is prohibited.
> > We reserve the right to monitor e-mail sent through our network.
> >
> > *
>
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
--
Timothy D. Witham - Lab Director - [EMAIL PROTECTED]
Open Source Development Lab Inc - A non-profit corporation
12725 SW Millikan Way - Suite 400 - Beaverton OR, 97005
(503)-626-2455 x11 (office)(503)-702-2871 (cell)
(503)-626-2436 (fax)
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
