On Wed, Aug 24, 2016 at 11:41 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Robert Haas <robertmh...@gmail.com> writes: >> What am I missing? > > Maybe nothing. But I'll point out that of the things that can currently > be configured at initdb time, such as LC_COLLATE, there is not one single > one that matters to walsender/walreceiver. If you think there is zero > risk involved in introducing a parameter that will matter at that level, > you have a different concept of risk than I do. > > If you'd presented some positive reason why we ought to be taking some > risk here, I'd be on board. But you haven't really. The current default > value for this parameter is nearly old enough to vote; how is it that > we suddenly need to make it easily configurable? Let's just change > the value and be happy.
I certainly think that's a good first cut. As I said before, I think that increasing the value from 16MB to 64MB won't really hurt people with mostly-default configurations. max_wal_size=1GB currently means 64 16-MB segments; if it starts meaning 16 64-MB segments, I don't think that will have much impact on on people one way or the other. Meanwhile, we'll significantly help people who are currently generating painfully large but not totally insane numbers of WAL segments. Someone who is currently generating 32,768 WAL segments per day - about one every 2.6 seconds - will have a significantly easier time if they start generating 8,192 WAL segments per day - about one every 10.5 seconds - instead. It's just much easier for a reasonably simple archive command to keep up, "ls" doesn't have as many directory entries to sort, etc. However, for people who have really high velocity systems - say 300,000 WAL segments per day - a fourfold increase in the segment size only gets them down to 75,000 WAL segments per day, which is still pretty nuts. High tens of thousands of segments per day is, surely, easier to manage than low hundreds of thousands, but it still puts really tight requirements on how fast your archive_command has to run. On that kind of system, you really want a segment size of maybe 1GB. In this example that gets you down to ~4700 WAL files per day, or about one every 18 seconds. But 1GB is clearly too large to be the default. I think we're going to run into this issue more and more as people start running PostgreSQL on larger databases. In current releases, the cost of wraparound autovacuums can easily be the limiting factor here: the I/O cost is proportional to the XID burn rate multiplied by the entire size of the database. So mostly read-only databases or databases that only take batch loads can be fine even if they are really big, but it's hard to scale databases that do lots of transaction processing beyond a certain size because you just end up running continuous wraparound vacuums and eventually you can't even do that fast enough. The freeze map changes in 9.6 should help with this problem, though, at least for databases that have hot spots rather than uniform access, which is of course very common. I think the result of that is likely to be that people try to scale up PostgreSQL to larger databases than ever before. New techniques for indexing large amounts of data (like BRIN) and for querying it (like parallel query, especially once we support having the driving scan be a bitmap heap scan) are going to encourage people in that direction, too. You're asking why we suddenly need to make this configurable as if it were a surprising need, but I think it would be more surprising if scaling up didn't create some new needs. I can't think of any reason why a 100TB database and a 100MB database should both want to use the same WAL segment size, and I think we want to support both of those things. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
