On Thu, Aug 25, 2016 at 10:29 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Thu, Aug 25, 2016 at 11:21 AM, Simon Riggs <si...@2ndquadrant.com> wrote: >> On 25 August 2016 at 02:31, Robert Haas <robertmh...@gmail.com> wrote: >>> Furthermore, there is an enforced, synchronous fsync at the end of >>> every segment, which actually does hurt performance on write-heavy >>> workloads.[2] Of course, if that were the only reason to consider >>> increasing the segment size, it would probably make more sense to just >>> try to push that extra fsync into the background, but that's not >>> really the case. From what I hear, the gigantic number of files is a >>> bigger pain point. >> >> I think we should fully describe the problem before finding a solution. > > Sure, that's usually a good idea. I attempted to outline all of the > possible issues of which I am aware in my original email, but of > course you may know of considerations which I overlooked. > >> This is too big a change to just tweak a value without discussing the >> actual issue. > > Again, I tried to make sure I was discussing the actual issues in my > original email. In brief: having to run archive_command multiple > times per second imposes very tight latency requirements on it; > directories with hundreds of thousands or millions of files are hard > to manage; enforced synchronous fsyncs at the end of each segment hurt > performance. > >> And if the problem is as described, how can a change of x4 be enough >> to make it worth the pain of change? I think you're already admitting >> it can't be worth it by discussing initdb configuration. > > I guess it depends on how much pain of change you think there will be. > I would expect a change from 16MB -> 64MB to be fairly painless, but > (1) it might break tools that aren't designed to cope with differing > segment sizes and (2) it will increase disk utilization for people who > have such low velocity systems that they never end up with more than 2 > WAL segments, and now those segments are bigger. If you know of other > impacts or have reason to believe those problems will be serious, > please fill in the details. > > Despite the fact that initdb configuration has dominated this thread, > I mentioned it only in the very last sentence of my email and only as > a possibility. I believe that a 4x change will be good enough for the > majority of people for whom this is currently a pain point. However, > yes, I do believe that there are some people for whom it won't be > sufficient. And I believe that as we continue to enhance PostgreSQL > to support higher and higher transaction rates, the number of people > who need an extra-large WAL segment size will increase. As I see it, > there are three options here: > > 1. Do nothing. So far, I don't see anybody arguing for that. > > 2. Change the default to 64MB and call it good. This idea seems to > have considerable support. > > 3. Allow initdb-time configurability but keep the default at 16MB. I > don't see any support for this. There is clearly support for > configurability, but I don't see anyone arguing that the current > default is preferable, unless that is what you are arguing. > > 4. Change the default to 64MB and also allow initdb-time > configurability. This option also appears to enjoy substantial > support, perhaps more than #2. Magnus seemed to be arguing that this > is preferable to #2, because then it's easier for people to change the > setting back if someone discovers a case where the higher default is a > problem; Tom, on the other hand, seems to think this is overkill. >
If we change the default to 64MB, then I think it won't allow to use old databases as-is because we store it in pg_control (I think one will get below error [1] for old databases, if we just change default and don't do anything else). Do you have way to address it or you think it is okay? [1] - FATAL: database files are incompatible with server DETAIL: The database cluster was initialized with XLOG_SEG_SIZE 16777216, but the server was compiled with XLOG_SEG_SIZE 67108864. HINT: It looks like you need to recompile or initdb. LOG: database system is shut down -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
