Обсуждение: Why is wal_writer_delay limited to 10s?
Hi, Just to be curious, why is wal_writer_delay limited to 10s? I am using postgresql in an embedded environment where every 10s sensor values are logged and even with "synchronous_commit = off" and wal_writer_delay=10000 this burns quite a lot of nand cycles. For me it wouldn't hurt loosing minutes of data - it is only important that the database is in a consistent state after power loss. Thanks, Clemens PS: It is really impressive how flexible and powerful postgresql is. I am using it on small TP-Link OpenWRT router (32m ram, 400mhz MIPS), on a raspberry pi as well as on larger servers for "real" database stuff with huge Hibernate-generated queries. Whenever/weherever I use postgresql, it does an excellent job and is rock-solid. Thanks a lot for this impressive piece of work :)
On 28/12/13 02:01, Clemens Eisserer wrote: > Hi, > > Just to be curious, why is wal_writer_delay limited to 10s? > I am using postgresql in an embedded environment where every 10s > sensor values are logged and even with "synchronous_commit = off" and > wal_writer_delay=10000 this burns quite a lot of nand cycles. For me > it wouldn't hurt loosing minutes of data - it is only important that > the database is in a consistent state after power loss. > > Thanks, Clemens > > PS: It is really impressive how flexible and powerful postgresql is. > I am using it on small TP-Link OpenWRT router (32m ram, 400mhz MIPS), > on a raspberry pi as well as on larger servers for "real" database > stuff with huge Hibernate-generated queries. Whenever/weherever I use > postgresql, it does an excellent job and is rock-solid. > Thanks a lot for this impressive piece of work :) > > "BECAUSE NO ONE WOULD EVER WANT LESS THAN 10 SECONDS..." was probably an implicit assumption? The truth be told, I don't know! However, I am sure the real experts will weigh in soon. Probably the assumption is that the WAL is on spinning rust, using SSD's might alter the timing equation. So it would be a good idea to tell us what storage you are using, along with anything else you might think could be relevant. It will be interesting to see what the real reasons are. I suspect that are valid technical reasons to suspect problems of using too short an interval, and people played safe by making the minimum time sufficiently greater to avoid such problems. You have the source, so you could reduce the minimum and see what happens. It may be that you need a different way of initiating it. Cheers, Gavin Running it on a raspberry pi is impressive - but way back, I remember IBM once recommending at least one megabyte for running their database on a mainframe. All (not just the smart) phones now have more memory than any of the mainframes I programmed in the early 1980's!
Hi, Unfortunately the question is still open - is there any technical reason why wal_writer_delay is limited to 10s? I am using postgresql in an embedded system and writing every 10s burns flash at an amazing rate (a 32GB SDHC card survived 4 days with only a few GB written). Thank you in advance, Clemens
Hi, Is there any technical reason why wal_writer_delay is limited to 10s? I am using postgresql in an embedded system and writing every 10s burns flash at an amazing rate (a 32GB SDHC card with ext4 survived 4 days with only a few GB written). I've now switched to nilfs2 which is way more friendly to weak controllers (as it only writes sequentially and has no "hot spots" like the ext4' journal), but I am still not confident it will survive long ;) Something like wal_writer_delay=600s would be ideal, I can afford to loose a 10min of data, but can't afford to get a corrupted database after power loss. Thank you in advance, Clemens
On Sat, Feb 1, 2014 at 3:40 PM, Clemens Eisserer <linuxhippy@gmail.com> wrote: > Something like wal_writer_delay=600s would be ideal, I can afford to > loose a 10min of data, but can't afford to get a corrupted database > after power loss. What Postgres version? The WAL Writer will hibernate on Postgres 9.2+. walwriter.c says: /* * Number of do-nothing loops before lengthening the delay time, and the * multiplier to apply to WalWriterDelay when we do decide to hibernate. * (Perhaps these need to be configurable?) */ #define LOOPS_UNTIL_HIBERNATE 50 #define HIBERNATE_FACTOR 25 -- Regards, Peter Geoghegan
On Sat, Feb 1, 2014 at 3:48 PM, Peter Geoghegan <peter.geoghegan86@gmail.com> wrote: > What Postgres version? The WAL Writer will hibernate on Postgres 9.2+. Of course, it will still write things out at a steady rate if they're there to write. However, there is no reason to think you couldn't change things in guc.c to have the upper bound increased. You might even propose a patch to do so. -- Regards, Peter Geoghegan
Hi Peter, Thanks a lot for your reply. > What Postgres version? The WAL Writer will hibernate on Postgres 9.2+. > walwriter.c says: I am using Postgresql-9.1 shipped with Raspbian (debian for raspberry pi). > /* > * Number of do-nothing loops before lengthening the delay time, and the > * multiplier to apply to WalWriterDelay when we do decide to hibernate. > * (Perhaps these need to be configurable?) > */ > #define LOOPS_UNTIL_HIBERNATE 50 > #define HIBERNATE_FACTOR 25 In my case there will always be work at a higher frequency as the delay time - in the hope to write out multiple transactions with a single fsync, so the do-nothing case will most likely not happen. My question on the list was merely to make sure there are no side-effects when increasing this delay above what seems to be considered safe limits. However, I still wonder why this parameter is capped to 10s and whether this restriction could be lifted in future postgresql versions? Thanks & regards, Clemens
On Sat, Feb 1, 2014 at 10:49 PM, Clemens Eisserer <linuxhippy@gmail.com> wrote: > My question on the list was merely to make sure there are no > side-effects when increasing this delay above what seems to be > considered safe limits. However, I still wonder why this parameter is > capped to 10s and whether this restriction could be lifted in future > postgresql versions? I don't think there's any practical reason, other than that it was assumed that increasing it further was not useful. There is perhaps a tendency to set GUC limits as high as seems reasonable without consider niche use-cases such as yours. If you want to hack it to go higher it should be fine, provided that WalWriterDelay * HIBERNATE_FACTOR cannot ever overflow a 32-bit signed integer. But since those are milliseconds and not microseconds, it seems pretty safe. This applies to 9.2+ only. I didn't check what things look like back when the delay was passed to pg_usleep(), which was the case in 9.1. -- Regards, Peter Geoghegan