Re: Patch: Write Amplification Reduction Method (WARM)

On Mon, Mar 27, 2017 at 10:25 PM, Pavan Deolasee
<pavan(dot)deolasee(at)gmail(dot)com> wrote:
> On Tue, Mar 28, 2017 at 1:59 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Thu, Mar 23, 2017 at 2:47 PM, Pavan Deolasee
>> <pavan(dot)deolasee(at)gmail(dot)com> wrote:
>> > It's quite hard to say that until we see many more benchmarks. As author
>> > of
>> > the patch, I might have got repetitive with my benchmarks. But I've seen
>> > over 50% improvement in TPS even without chain conversion (6 indexes on
>> > a 12
>> > column table test).
>>
>> This seems quite mystifying. What can account for such a large
>> performance difference in such a pessimal scenario? It seems to me
>> that without chain conversion, WARM can only apply to each row once
>> and therefore no sustained performance improvement should be possible
>> -- unless rows are regularly being moved to new blocks, in which case
>> those updates would "reset" the ability to again perform an update.
>> However, one would hope that most updates get done within a single
>> block, so that the row-moves-to-new-block case wouldn't happen very
>> often.
>
> I think you're confusing between update chains that stay within a block vs
> HOT/WARM chains. Even when the entire update chain stays within a block, it
> can be made up of multiple HOT/WARM chains and each of these chains offer
> ability to do one WARM update. So even without chain conversion, every
> alternate update will be a WARM update. So the gains are perpetual.

You're right, I had overlooked that. But then I'm confused: how does
the chain conversion stuff help as much as it does? You said that you
got a 50% improvement from WARM, because we got to skip half the index
updates. But then you said with chain conversion you got an
improvement of more like 100%. However, I would think that on this
workload, chain conversion shouldn't save much. If you're sweeping
through the database constantly performing updates, the updates ought
to be a lot more frequent than the vacuums.

No?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company