Re: Logging WAL when updating hintbit

On Mon, Nov 25, 2013 at 9:02 PM, Sawada Masahiko <sawada(dot)mshk(at)gmail(dot)com> wrote:
> On Sun, Nov 24, 2013 at 6:02 AM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>> On Tue, 2013-11-19 at 11:42 -0500, Robert Haas wrote:
>> My take is that configuration options should be used to serve different
>> use cases. One thing I like about postgres is that it doesn't have
>> options for the sake of options.
>>
>> The trade-off here seems to be: if you want fast failback, you have to
>> write more WAL during normal operation. But it's not clear to me which
>> one I should choose for a given installation. If there's a lot of data,
>> then fast failback is nice, but then again, logging the hint bits on a
>> large amount of data might be painful during normal operation. The only
>> time the choice is easy is when you already have checksums enabled.
>>
>> I think we should work some more in this area first so we can end up
>> with something that works for everyone; or at least a more clear choice
>> to offer users. My hope is that it will go something like:
>>
>> 1. We get more reports from the field about checksum performance
>> 2. pg_rewind gets some more attention
>> 3. we find a way to upgrade a replica set using pg_upgrade and pg_rewind
>> so that the replicas do not all need a new basebackup after an upgrade
>> 4. We further mitigate the performance impact of logging all page
>> modifications
>> 5. We eventually see that the benefits of logging all page modifications
>> outweigh the performance cost for most people, and start recommending to
>> most people
>> 6. The other WAL levels become more specialized for single, unreplicated
>> instances
>>
>> That's just a hope, of course, but we've made some progress and I think
>> it's a plausible outcome. As it stands, the replica re-sync after any
>> failover or upgrade seems to be a design gap.
>>
>> All that being said, I don't object to this option -- I just want it to
>> lead us somewhere. Not be another option that we keep around forever
>> with conflicting recommendations about how to set it.
>>
>
> Thank you for feedback.
>
> I agree with you.
> We need to more report regarding checksum performance first. I will do that.
>

I did performance test on my environment.
Performance test result:

pgbench -T 600

Plane postgres : 460
Plane postgres with checksum : 450
Logging hintbit : 456

There is not huge performance degradation between three cases.
I think that It is better point that user can change the values
without creating database cluster newly.

Regards,

-------
Sawada Masahiko