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 > > > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
