Re: Performance Improvement by reducing WAL for Update Operation - Mailing list pgsql-hackers

On Wed, Feb 5, 2014 at 5:13 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> On 02/05/2014 07:54 AM, Amit Kapila wrote:
>>
>> On Tue, Feb 4, 2014 at 11:58 PM, Robert Haas <robertmhaas@gmail.com>
>> wrote:
>>>
>>> On Tue, Feb 4, 2014 at 12:39 PM, Amit Kapila <amit.kapila16@gmail.com>
>>> wrote:
>>>>
>>>> Now there is approximately 1.4~5% CPU gain for
>>>> "hundred tiny fields, half nulled" case
>>
>>
>>> Assuming that the logic isn't buggy, a point in need of further study,
>>> I'm starting to feel like we want to have this.  And I might even be
>>> tempted to remove the table-level off switch.
>>
>>
>> I have tried to stress on worst case more, as you are thinking to
>> remove table-level switch and found that even if we increase the
>> data by approx. 8 times ("ten long fields, all changed", each field
>> contains
>> 80 byte data), the CPU overhead is still < 5% which clearly shows that
>> the overhead doesn't increase much even if the length of unmatched data
>> is increased by much larger factor.
>> So the data for worst case adds more weight to your statement
>> ("remove table-level switch"), however there is no harm in keeping
>> table-level option with default as 'true' and if some users are really
>> sure
>> the updates in their system will have nothing in common, then they can
>> make this new option as 'false'.
>>
>> Below is data for the new case " ten long fields, all changed" added
>> in attached script file:
>
>
> That's not the worst case, by far.
>
> First, note that the skipping while scanning new tuple is only performed in
> the first loop. That means that as soon as you have a single match, you fall
> back to hashing every byte. So for the worst case, put one 4-byte field as
> the first column, and don't update it.
>
> Also, I suspect the runtimes in your test were dominated by I/O. When I
> scale down the number of rows involved so that the whole test fits in RAM, I
> get much bigger differences with and without the patch. You might also want
> to turn off full_page_writes, to make the effect clear with less data.
>
> So with this test, the overhead is very significant.
>
> With the skipping logic, another kind of "worst case" case is that you have
> a lot of similarity between the old and new tuple, but you miss it because
> you skip.

This is exactly the reason why I have not kept skipping logic in second
pass(loop), but I think may be it would have been better to keep it not
as aggressive as in first pass. The basic idea I had in mind is that if we
get match, then there is high chance that we get match in consecutive
positions.

I think we should see this patch as I/O reduction feature rather than CPU
gain/overhead because the I/O reduction in WAL has some other has
some other benefits like transfer for replication, in archiving, recovery,
basically where-ever there is disk read operation, as I/O reduction will
amount to less data read and which can be beneficial in many ways.

Sometime back, I was reading article on benefits of compression
in database where the benefits are shown something like what
I said above (atleast that is what I understood from it). Link to that
article is:
http://db2guys.wordpress.com/2013/08/23/compression/

Another thing is that I think it might be difficult to get negligible
overhead for data which is very less or non-compressible, that's
why it is preferred to have compression for table enabled with
switch.

Is it viable to see here, what is the best way to get I/O reduction
for most cases and provide a switch so that for worst cases
user can make it off?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com