Re: Questions on query planner, join types, and work_mem - Mailing list pgsql-performance

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
>
>
>