Re: moving pg_xlog -- yeah, it's worth it!

От: Kevin Grittner
Тема: Re: moving pg_xlog -- yeah, it's worth it!
Дата: ,
Msg-id: 4B83F30A020000250002F56B@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: moving pg_xlog -- yeah, it's worth it!  (Greg Smith)
Ответы: Re: moving pg_xlog -- yeah, it's worth it!  (Ben Chobot)
Список: pgsql-performance

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

moving pg_xlog -- yeah, it's worth it!  ("Kevin Grittner", )
 Re: moving pg_xlog -- yeah, it's worth it!  (Jesper Krogh, )
  Re: moving pg_xlog -- yeah, it's worth it!  ("Kevin Grittner", )
   Re: moving pg_xlog -- yeah, it's worth it!  (Amitabh Kant, )
    Re: moving pg_xlog -- yeah, it's worth it!  ("Kevin Grittner", )
   Re: moving pg_xlog -- yeah, it's worth it!  (Bruce Momjian, )
    Re: moving pg_xlog -- yeah, it's worth it!  (Scott Marlowe, )
   Re: moving pg_xlog -- yeah, it's worth it!  ("Kevin Grittner", )
    Re: moving pg_xlog -- yeah, it's worth it!  (Alvaro Herrera, )
     Re: moving pg_xlog -- yeah, it's worth it!  (Aidan Van Dyk, )
      Re: moving pg_xlog -- yeah, it's worth it!  ("Kevin Grittner", )
 Re: moving pg_xlog -- yeah, it's worth it!  ("Kevin Grittner", )
  Re: moving pg_xlog -- yeah, it's worth it!  (Alvaro Herrera, )
   Re: moving pg_xlog -- yeah, it's worth it!  (Alvaro Herrera, )
    Re: moving pg_xlog -- yeah, it's worth it!  ("Kevin Grittner", )
     Re: moving pg_xlog -- yeah, it's worth it!  (Alvaro Herrera, )
  Re: moving pg_xlog -- yeah, it's worth it!  (Greg Smith, )
   Re: moving pg_xlog -- yeah, it's worth it!  ("Kevin Grittner", )
    Re: moving pg_xlog -- yeah, it's worth it!  (Ben Chobot, )

Greg Smith <> wrote:

> You can easily quantify if the BGW is aggressive enough.  Buffers
> leave the cache three ways, and they each show up as separate
> counts in pg_stat_bgwriter:  buffers_checkpoint, buffers_clean
> (the BGW), and buffers_backend (the queries).  Cranking it up
> further tends to shift writes out of buffers_backend, which are
> the ones you want to avoid, toward buffers_clean instead.  If
> buffers_backend is already low on a percentage basis compared to
> the other two, there's little benefit in trying to make the BGW do
> more.

Here are the values from our two largest and busiest systems (where
we found the pg_xlog placement to matter so much).  It looks to me
like a more aggressive bgwriter would help, yes?

cir=> select * from pg_stat_bgwriter ;
-[ RECORD 1 ]------+------------
checkpoints_timed  | 125996
checkpoints_req    | 16932
buffers_checkpoint | 342972024
buffers_clean      | 343634920
maxwritten_clean   | 9928
buffers_backend    | 575589056
buffers_alloc      | 52397855471

cir=> select * from pg_stat_bgwriter ;
-[ RECORD 1 ]------+------------
checkpoints_timed  | 125992
checkpoints_req    | 16840
buffers_checkpoint | 260358442
buffers_clean      | 474768152
maxwritten_clean   | 9778
buffers_backend    | 565837397
buffers_alloc      | 71463873477

Current settings:

bgwriter_delay = '200ms'
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4

Any suggestions on how far to push it?

-Kevin


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

От: Scott Carey
Дата:
Сообщение: Re: Planner question - "bit" data types
От: Dave Crooke
Дата:
Сообщение: Re: SSD + RAID