Re: Auto-tuning work_mem and maintenance_work_mem

On Tue, Oct 15, 2013 at 7:26 PM, Andres Freund <andres(at)2ndquadrant(dot)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?)

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...

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