Re: incremental-checkopints
От | Hannu Krosing |
---|---|
Тема | Re: incremental-checkopints |
Дата | |
Msg-id | CAMT0RQSADt1S-0k6rfOG3CcD2ZyrsxVFoY-gkU7WkaKjBjU+=A@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: incremental-checkopints (Tomas Vondra <tomas.vondra@enterprisedb.com>) |
Список | pgsql-hackers |
Starting from increments checkpoint is approaching the problem from the wrong end. What you actually want is Atomic Disk Writes which will allow turning off full_page_writes . Without this you really can not do incremental checkpoints efficiently as checkpoints are currently what is used to determine when is "the first write to a page after checkpoint" and thereby when the full page write is needed. On Wed, Jul 26, 2023 at 8:58 PM Tomas Vondra <tomas.vondra@enterprisedb.com> wrote: > > > > On 7/26/23 15:16, Matthias van de Meent wrote: > > On Wed, 26 Jul 2023 at 14:41, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: > >> > >> Hello > >> > >> On 2023-Jul-26, Thomas wen wrote: > >> > >>> Hi Hackes: I found this page : > >>> https://pgsql-hackers.postgresql.narkive.com/cMxBwq65/incremental-checkopints,PostgreSQL > >>> no incremental checkpoints have been implemented so far. When a > >>> checkpoint is triggered, the performance jitter of PostgreSQL is very > >>> noticeable. I think incremental checkpoints should be implemented as > >>> soon as possible > >> > >> I think my first question is why do you think that is necessary; there > >> are probably other tools to achieve better performance. For example, > >> you may want to try making checkpoint_completion_target closer to 1, and > >> the checkpoint interval longer (both checkpoint_timeout and > >> max_wal_size). Also, changing shared_buffers may improve things. You > >> can try adding more RAM to the machine. > > > > Even with all those tuning options, a significant portion of a > > checkpoint's IO (up to 50%) originates from FPIs in the WAL, which (in > > general) will most often appear at the start of each checkpoint due to > > each first update to a page after a checkpoint needing an FPI. > > Yeah, FPIs are certainly expensive and can represent huge part of the > WAL produced. But how would incremental checkpoints make that step > unnecessary? > > > If instead we WAL-logged only the pages we are about to write to disk > > (like MySQL's double-write buffer, but in WAL instead of a separate > > cyclical buffer file), then a checkpoint_completion_target close to 1 > > would probably solve the issue, but with "WAL-logged torn page > > protection at first update after checkpoint" we'll probably always > > have higher-than-average FPI load just after a new checkpoint. > > > > So essentially instead of WAL-logging the FPI on the first change, we'd > only do that later when actually writing-out the page (either during a > checkpoint or because of memory pressure)? How would you make sure > there's enough WAL space until the next checkpoint? I mean, FPIs are a > huge write amplification source ... > > Imagine the system has max_wal_size set to 1GB, and does 1M updates > before writing 512MB of WAL and thus triggering a checkpoint. Now it > needs to write FPIs for 1M updates - easily 8GB of WAL, maybe more with > indexes. What then? > > > regards > > -- > Tomas Vondra > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > >
В списке pgsql-hackers по дате отправления: