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 23:24:40 +0300
Message-ID: <1280953480.3735.94.camel@hvost> (raw)
In-Reply-To: <1280948634.3735.69.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>
<1280948634.3735.69.camel@hvost>
On Wed, 2010-08-04 at 22:03 +0300, Hannu Krosing wrote:
> 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,
I re-ran the test, and checked idx_blks_read for 28MB case
hannu=# select * from pg_statio_user_indexes where relname =
'sbuf_test';
| schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit
+------------+-----------+--------------+---------------+--------------
| hannu | sbuf_test | sbuf_test1 | 71376 | 1620908
| hannu | sbuf_test | sbuf_test2 | 71300 | 1620365
| hannu | sbuf_test | sbuf_test3 | 71436 | 1619619
this means that there were a total of 214112 index blocks read back from
disk cache (obviously at least some of these had to be copied the other
way as well).
This seems to indicate about 1 ms for moving pages over user/system
boundary. (Intel Core2 Duo T7500 @ 2.20GHz, Ubuntu 9.10, 4GB RAM)
for 128MB shared buffers the total idx_blks_read for 3 indexes was about
6300 .
> 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
>
>
>
--
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: <1280953480.3735.94.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