Re: Patch: Write Amplification Reduction Method (WARM)

On Thu, Feb 23, 2017 at 9:21 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Tue, Jan 31, 2017 at 04:52:39PM +0530, Pavan Deolasee wrote:
>> The other critical bug I found, which unfortunately exists in the master too,
>> is the index corruption during CIC. The patch includes the same fix that I've
>> proposed on the other thread. With these changes, WARM stress is running fine
>> for last 24 hours on a decently powerful box. Multiple CREATE/DROP INDEX cycles
>> and updates via different indexed columns, with a mix of FOR SHARE/UPDATE and
>> rollbacks did not produce any consistency issues. A side note: while
>> performance measurement wasn't a goal of stress tests, WARM has done about 67%
>> more transaction than master in 24 hour period (95M in master vs 156M in WARM
>> to be precise on a 30GB table including indexes). I believe the numbers would
>> be far better had the test not dropping and recreating the indexes, thus
>> effectively cleaning up all index bloats. Also the table is small enough to fit
>> in the shared buffers. I'll rerun these tests with much larger scale factor and
>> without dropping indexes.
>
> Thanks for setting up the test harness. I know it is hard but
> in this case it has found an existing bug and given good performance
> numbers. :-)
>
> I have what might be a supid question. As I remember, WARM only allows
> a single index-column change in the chain. Why are you seeing such a
> large performance improvement? I would have thought it would be that
> high if we allowed an unlimited number of index changes in the chain.

I'm not sure how the test case is set up. If the table has multiple
indexes, each on a different column, and only one of the indexes is
updated, then you figure to win because now the other indexes need
less maintenance (and get less bloated). If you have only a single
index, then I don't see how WARM can be any better than HOT, but maybe
I just don't understand the situation.

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