On Wed, Feb 5, 2014 at 5:13 PM, Heikki Linnakangas
> On 02/05/2014 07:54 AM, Amit Kapila wrote:
>> On Tue, Feb 4, 2014 at 11:58 PM, Robert Haas <robertmh...@gmail.com>
>>> On Tue, Feb 4, 2014 at 12:39 PM, Amit Kapila <amit.kapil...@gmail.com>
>>>> 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
>> 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
>> 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
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
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
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?
Sent via pgsql-hackers mailing list (firstname.lastname@example.org)
To make changes to your subscription: