Re: Questions on query planner, join types, and work_mem

От: Kevin Grittner
Тема: Re: Questions on query planner, join types, and work_mem
Дата: ,
Msg-id: 4C59800002000025000341F7@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: Questions on query planner, join types, and work_mem  (Greg Smith)
Ответы: Re: Questions on query planner, join types, and work_mem  (Greg Smith)
Список: pgsql-performance

Скрыть дерево обсуждения

Questions on query planner, join types, and work_mem  (Peter Hussey, )
 Re: Questions on query planner, join types, and work_mem  (Andres Freund, )
 Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
  Re: Questions on query planner, join types, and work_mem  (Alvaro Herrera, )
   Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
 Re: Questions on query planner, join types, and work_mem  (Jayadevan M, )
 Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
  Re: Questions on query planner, join types, and work_mem  (Peter Hussey, )
   Re: Questions on query planner, join types, and work_mem  (Robert Haas, )
   Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
   Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
    Re: Questions on query planner, join types, and work_mem  (Robert Haas, )
     Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
      Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
       Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
        Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
         Re: Questions on query planner, join types, and work_mem  (Greg Smith, )
          Re: Questions on query planner, join types, and work_mem  (Greg Smith, )
          Re: Questions on query planner, join types, and work_mem  ("Kevin Grittner", )
           Re: Questions on query planner, join types, and work_mem  (Greg Smith, )
            Re: Questions on query planner, join types, and work_mem  ("Kevin Grittner", )
          Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
         Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
         Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
          Re: Questions on query planner, join types, and work_mem  ("Kevin Grittner", )
         Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
       Re: Questions on query planner, join types, and work_mem  (Bruce Momjian, )
        Re: Questions on query planner, join types, and work_mem  (Robert Haas, )
         Re: Questions on query planner, join types, and work_mem  (Bruce Momjian, )
          Re: Questions on query planner, join types, and work_mem  (Bruce Momjian, )
   Re: Questions on query planner, join types, and work_mem  ("Kevin Grittner", )
 Re: Questions on query planner, join types, and work_mem  (Greg Smith, )

Greg Smith <> wrote:

> What I do to quantify that is...well, the attached shows it better
> than I can describe; only works on 9.0 or later as it depends on a
> feature I added for this purpose there. It measures exactly how
> much buffer cache churn happened during a test, in this case
> creating a pgbench database.

I'm not entirely sure I understand what I'm supposed to get from
that.  On a 3GB workstation, a compile from a recent HEAD checkout,
with a default postgresql.conf file, I get this:

-[ RECORD 1 ]------+------------------------------
now                | 2010-08-04 14:25:46.683766-05
checkpoints_timed  | 0
checkpoints_req    | 0
buffers_checkpoint | 0
buffers_clean      | 0
maxwritten_clean   | 0
buffers_backend    | 0
buffers_alloc      | 73

Initializing pgbench
-[ RECORD 1 ]------+------------------------------
now                | 2010-08-04 14:27:49.062551-05
checkpoints_timed  | 0
checkpoints_req    | 0
buffers_checkpoint | 0
buffers_clean      | 0
maxwritten_clean   | 0
buffers_backend    | 633866
buffers_alloc      | 832

I boost shared_buffers from 32MB to 320MB, restart, and get this:

-[ RECORD 1 ]------+------------------------------
now                | 2010-08-04 14:30:42.816719-05
checkpoints_timed  | 0
checkpoints_req    | 0
buffers_checkpoint | 0
buffers_clean      | 0
maxwritten_clean   | 0
buffers_backend    | 0
buffers_alloc      | 0

Initializing pgbench
-[ RECORD 1 ]------+------------------------------
now                | 2010-08-04 14:32:40.750098-05
checkpoints_timed  | 0
checkpoints_req    | 0
buffers_checkpoint | 0
buffers_clean      | 0
maxwritten_clean   | 0
buffers_backend    | 630794
buffers_alloc      | 2523

So run time dropped from 123 seconds to 118 seconds, buffers_backend
dropped by less than 0.5%, and buffers_alloc went up.  Assuming this
is real, and not just "in the noise" -- what conclusions would you
draw from this?  Dedicating an additional 10% of my free memory got
me a 4% speed improvement?  Was I supposed to try with other scales?
Which ones?

-Kevin


В списке pgsql-performance по дате сообщения:

От: Hannu Krosing
Дата:
Сообщение: Re: Questions on query planner, join types, and work_mem
От: Chris Browne
Дата:
Сообщение: Re: Testing Sandforce SSD