From: Hannu Krosing <hannu@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Peter Hussey <peter@labkey.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Questions on query planner, join types, and work_mem
Date: Wed, 04 Aug 2010 22:03:54 +0300
Message-ID: <1280948634.3735.69.camel@hvost> (raw)
In-Reply-To: <1280947287.3735.56.camel@hvost>
References: <AANLkTikMwVZKDYLitnsK31R=VNOuTH8NtmDHDGkBR7k8@mail.gmail.com>
<18551.1280275502@sss.pgh.pa.us>
<AANLkTimXMR0jMdw+66E4Xx=SqNV37Ne9WfkFjPFZVjbV@mail.gmail.com>
<25534.1280498632@sss.pgh.pa.us>
<AANLkTik3oY1=F_uC843c84LxBLNdHxZaWdfjxrAtyBeu@mail.gmail.com>
<1280819031.14579.38.camel@hvost>
<AANLkTik6UBt2ezVFzdCM8iaUPkra_7f-Hs_AmGZgvFhh@mail.gmail.com>
<1280944268.3735.12.camel@hvost>
<22961.1280944836@sss.pgh.pa.us>
<1280947287.3735.56.camel@hvost>
On Wed, 2010-08-04 at 21:41 +0300, Hannu Krosing wrote:
> On Wed, 2010-08-04 at 14:00 -0400, Tom Lane wrote:
> > regression=# select name, setting from pg_settings where name like '%cost';
> > name | setting
> > ----------------------+---------
> > cpu_index_tuple_cost | 0.005
> > cpu_operator_cost | 0.0025
> > cpu_tuple_cost | 0.01
> > random_page_cost | 4
> > seq_page_cost | 1
> > (5 rows)
> >
> > To model an all-in-RAM database, you can either dial down both
> > random_page_cost and seq_page_cost to 0.1 or so, or set random_page_cost
> > to 1 and increase all the CPU costs. The former is less effort ;-)
> >
> > It should be noted also that there's not all that much evidence backing
> > up the default values of the cpu_xxx_cost variables. In the past those
> > didn't matter much because I/O costs always swamped CPU costs anyway.
> > But I can foresee us having to twiddle those defaults and maybe refine
> > the CPU cost model more, as all-in-RAM cases get more common.
>
> Especially the context switch + copy between shared buffers and system
> disk cache will become noticeable at these speeds.
>
> An easy way to test it is loading a table with a few indexes, once with
> a shared_buffers value, which is senough for only the main table and
> once with one that fits both table and indexes,
ok, just to back this up I ran the following test with 28MB and 128MB
shared buffers.
create table sbuf_test(f1 float, f2 float, f3 float);
create index sbuf_test1 on sbuf_test(f1);
create index sbuf_test2 on sbuf_test(f2);
create index sbuf_test3 on sbuf_test(f3);
and then did 3 times the following for each shared_buffers setting
truncate sbuf_test;
insert into sbuf_test
select random(), random(), random() from generate_series(1,600000);
the main table size was 31MB, indexes were 18MB each for total size of
85MB
in case of 128MB shared buffers, the insert run in 14sec (+/- 1 sec)
in case of 28MB shared buffers, the insert run between 346 and 431 sec,
that is 20-30 _times_ slower.
There was ample space for keeping the indexes in linux cache (it has 1GB
cached currently) though the system may have decided to start writing it
to disk, so I suspect that most of the time was spent copying random
index pages back and forth between shared buffers and disk cache.
I did not verify this, so there may be some other factors involved, but
this seems like the most obvious suspect.
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hannu@2ndquadrant.com, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, peter@labkey.com
Subject: Re: Questions on query planner, join types, and work_mem
In-Reply-To: <1280948634.3735.69.camel@hvost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox