Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.

От: Andres Freund
Тема: Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.
Дата: ,
Msg-id: 20170427155906.uudovtd62x56brmx@alap3.anarazel.de
(см: обсуждение, исходный текст)
Ответ на: [GENERAL] Questionaire: Common WAL write rates on busy servers.  (Andres Freund)
Ответы: Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.  ("Joshua D. Drake")
Список: pgsql-general

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

[GENERAL] Questionaire: Common WAL write rates on busy servers.  (Andres Freund, )
 Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.  (Vladimir Borodin, )
 Re: [GENERAL] Questionaire: Common WAL write rates on busy servers.  (bricklen, )
 Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.  (Claudio Freire, )
 Re: [GENERAL] Questionaire: Common WAL write rates on busy servers.  (Jerry Sievers, )
 Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.  (Andres Freund, )
  Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.  ("Joshua D. Drake", )
   Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.  (Andres Freund, )
    Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.  ("Joshua D. Drake", )
     Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.  (Andres Freund, )
      Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.  (Tomas Vondra, )
       Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.  (Andres Freund, )
        Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.  (Tomas Vondra, )
    Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.  (Tomas Vondra, )

Hi,

On 2017-04-24 21:17:43 -0700, Andres Freund wrote:
> I've lately seen more and more installations where the generation of
> write-ahead-log (WAL) is one of the primary bottlenecks.  I'm curious
> whether that's primarily a "sampling error" of mine, or whether that's
> indeed more common.
>
> The primary reason I'm curious is that I'm pondering a few potential
> optimizations, and would like to have some guidance which are more and
> which are less important.
>
> Questions (answer as many you can comfortably answer):
> - How many MB/s, segments/s do you see on busier servers?
> - What generates the bulk of WAL on your servers (9.5+ can use
>   pg_xlogdump --stats to compute that)?
> - Are you seeing WAL writes being a bottleneck?OA
> - What kind of backup methods are you using and is the WAL volume a
>   problem?
> - What kind of replication are you using and is the WAL volume a
>   problem?
> - What are your settings for wal_compression, max_wal_size (9.5+) /
>   checkpoint_segments (< 9.5), checkpoint_timeout and wal_buffers?
> - Could you quickly describe your workload?

Ok, based on the, few, answers I've got so far, my experience is indeed
skewed.  A number of the PG users I interacted with over the last couple
years had WAL write ranges somewhere in the range of 500MB/s to 2.2GB/s
(max I'veseen).  At that point WAL insertion became a major bottleneck,
even if storage was more than fast enough to keep up.  To address these
we'd need some changes, but the feedback so far suggest that it's not
yet a widespread issue...

- Andres



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

От: ""
Дата:
Сообщение: [GENERAL] Perl Dev 2017 survey results
От: Samuel Williams
Дата:
Сообщение: Re: [GENERAL] Large data and slow queries