Re: COMMIT NOWAIT Performance Option
От | Richard Huxton |
---|---|
Тема | Re: COMMIT NOWAIT Performance Option |
Дата | |
Msg-id | 45E36C6B.8080605@archonet.com обсуждение исходный текст |
Ответ на | COMMIT NOWAIT Performance Option ("Simon Riggs" <simon@2ndquadrant.com>) |
Ответы |
Re: COMMIT NOWAIT Performance Option
("Simon Riggs" <simon@2ndquadrant.com>)
|
Список | pgsql-hackers |
Simon Riggs wrote: > Proposal: Implement a new option for COMMIT, for enhancing performance, > providing a MySQL-like trade-off between performance and robustness for > *only* those that want it. > > COMMIT NOWAIT > > This form of COMMIT will *not* perform XLogFlush(), but will rely on a > special background process to perform regular WAL fsyncs (see later). > > COMMIT NOWAIT can co-exist with the normal form of COMMIT and does not > threaten the consistency or robustness of other COMMIT modes. Read that > again and think about it, before we go further, please. Normal COMMIT > still guarantees to flush all of WAL up to the point of the commit, > whether or not the previous commits have requested that. > > Mixing COMMIT NOWAIT with other modes does not effect the performance of > other backends - those that specify that mode are faster, those that do > not simply go at the same speed they did before. This is important, > because it allows us to have a fully robust server, yet with certain > critical applications going along much faster. No need for an > all-or-nothing approach at db cluster level. > > Unlike fsync = off, WAL is always consistent and the server can be > recovered easily, though with some potential for data loss for > transactions that chose the COMMIT NOWAIT option. Sounds like a hole > there: normal COMMITs that rely on data written by COMMIT NOWAIT > transactions are still safe, because the normal COMMIT is still bound by > the guarantee to go to disk. The buffer manager/WAL interlock is not > effected by this change and remains in place, as it should. OK, so do I have this right? A is issuing COMMIT NOWAIT once a second B is issuing COMMIT every other second Checkpoints happen every 10 seconds Every 10 seconds we have a checkpoint and all WAL+data are on-disk. Every 2 seconds a standard COMMIT occurs and A+B are synced to WAL In-between COMMIT NOWAIT occurs and no data is written to disk - neither WAL nor data-blocks. So, if I have a load of connections issuing standard COMMITs regularly then I'll not see much performance-gain. However, I can guarantee any data visible to those transactions is committed to disk. If I have only one connection to the database and that uses only COMMIT NOWAIT, it should approach the speed of fsync=off. So this is for: 1. data-loading where I don't want to use fsync=off (for whatever reason) 2. Data-logging type apps Have I got the gist of that? -- Richard Huxton Archonet Ltd
В списке pgsql-hackers по дате отправления: