Re: Auto-tuning work_mem and maintenance_work_mem - Mailing list pgsql-hackers

On Tue, Oct 15, 2013 at 7:32 PM, Andres Freund <andres@2ndquadrant.com> wrote:
> On 2013-10-15 19:29:50 +0200, Magnus Hagander wrote:
>> On Tue, Oct 15, 2013 at 7:26 PM, Andres Freund <andres@2ndquadrant.com> wrote:
>> > On 2013-10-15 10:19:06 -0700, Josh Berkus wrote:
>> >> On 10/15/2013 05:52 AM, Magnus Hagander wrote:
>> >> > But the argument about being friendly for new users should definitely
>> >> > have us change wal_level and max_wal_senders.
>> >>
>> >> +1 for having replication supported out-of-the-box aside from pg_hba.conf.
>> >>
>> >> To put it another way: users are more likely to care about replication
>> >> than they are about IO overhead on a non-replicated server.  And for the
>> >> users who care about IO overhead, they are more likely to much about in
>> >> pg.conf *anyway* in order to set a slew of performance-tuning settings.
>> >
>> > But it will hurt people restoring backups using pg_restore -j. I think
>> > people might be rather dissapointed if that slows down by a factor of
>> > three.
>> >
>> > I think we really need to get to the point where we increase the wal
>> > level ondemand...
>>
>> Yeha, there are really two things.
>>
>> If we can increase wal_level on demand, that would solve one of them.
>> Turning that into a SIGHUP parameter would be great. I have no idea
>> how hard it would be. In theory, couldn't we let it be sighup and then
>> just have do_pg_start_backup() block until all backends have
>> acknowledged that they are on the new WAL level somehow? (Yes, I
>> realize this might be a big simplification, but I'm allowed to hope,
>> no?)
>
> Depends on what you want to support. For basebackups, that should be
> doable with some pullups.
> It's unfortunately more complex than that for streaming rep - we really
> need persistent standby registration there. Otherwise the wal_level will
> fall back to minimal when the standby disconnects which will obviously
> break the standby.

I was actually thinking the easier step might not be to do it
dynamically as the standby registers - just allow it to be a SIGHUP
parameter. So you'd still change it in postgresql.conf, but it would
be ok with a reload rather than a restart.

Yes, fully dynamic would be better, so if we could combined those two,
that would make us "require nothing for pg_basebackup, and just a
reload for replication slaves". The point being that we wouldn't need
a *restart* at any point - and that alond would be a big improvement.


>> The other problem is max_wal_senders. I think that's a much smaller
>> problem - setting that one to 5 or so by default shouldn't have a big
>> impact. But without the wal_level changes, it would also be mostly
>> pointless...
>
> Well, you currently cannot even set it when the wal_level isn't set
> appropriately, but that that should be easy enough to change.

Yes, it would be a trivial change to allow that parametre to be set
and then just give an error if you try to initiate streaming in that
case.


-- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/