Re: Patch: Write Amplification Reduction Method (WARM)

On Wed, Mar 22, 2017 at 4:53 PM, Mithun Cy <mithun(dot)cy(at)enterprisedb(dot)com>
wrote:

> On Wed, Mar 22, 2017 at 3:44 PM, Pavan Deolasee
> <pavan(dot)deolasee(at)gmail(dot)com> wrote:
> >
> > This looks quite weird to me. Obviously these numbers are completely
> > non-comparable. Even the time for VACUUM FULL goes up with every run.
> >
> > May be we can blame it on AWS instance completely, but the pattern in
> your
> > tests looks very similar where the number slowly and steadily keeps going
> > up. If you do complete retest but run v18/v19 first and then run master,
> may
> > be we'll see a complete opposite picture?
> >
>
> For those tests I have done tests in the order --- <Master, patch18,
> patch18, Master> both the time numbers were same.

Hmm, interesting.

> One different thing
> I did was I was deleting the data directory between tests and creating
> the database from scratch. Unfortunately the machine I tested this is
> not available. I will test same with v19 once I get the machine and
> report you back.

Ok, no problem. I did some tests on AWS i2.xlarge instance (4 vCPU, 30GB
RAM, attached SSD) and results are shown below. But I think it is important
to get independent validation from your side too, just to ensure I am not
making any mistake in measurement. I've attached naively put together
scripts which I used to run the benchmark. If you find them useful, please
adjust the paths and run on your machine.

I reverted back to UNLOGGED table because with WAL the results looked very
weird (as posted earlier) even when I was taking a CHECKPOINT before each
set and had set max_wal_size and checkpoint_timeout high enough to avoid
any checkpoint during the run. Anyways, that's a matter of separate
investigation and not related to this patch.

I did two kinds of tests.
a) update last column of the index
b) update second column of the index

v19 does considerably better than even master for the last column update
case and pretty much inline for the second column update test. The reason
is very clear because v19 determines early in the cycle that the buffer is
already full and there is very little chance of doing a HOT update on the
page. In that case, it does not check any columns for modification. The
master on the other hand will scan through all 9 columns (for last column
update case) and incur the same kind of overhead of doing wasteful work.

The first/second/fourth column shows response time in ms and third and
fifth column shows percentage difference over master. (I hope the table
looks fine, trying some text-table generator tool :-). Apologies if it
looks messed up)

+-------------------------------------------------------+
| Second column update |
+-------------------------------------------------------+
| Master | v18 | v19 |
+-----------+---------------------+---------------------+
| 96657.681 | 108122.868 | 11.86% | 96873.642 | 0.22% |
+-----------+------------+--------+------------+--------+
| 98546.35 | 110021.27 | 11.64% | 97569.187 | -0.99% |
+-----------+------------+--------+------------+--------+
| 99297.231 | 110550.054 | 11.33% | 100241.261 | 0.95% |
+-----------+------------+--------+------------+--------+
| 97196.556 | 110235.808 | 13.42% | 97216.207 | 0.02% |
+-----------+------------+--------+------------+--------+
| 99072.432 | 110477.327 | 11.51% | 97950.687 | -1.13% |
+-----------+------------+--------+------------+--------+
| 96730.056 | 109217.939 | 12.91% | 96929.617 | 0.21% |
+-----------+------------+--------+------------+--------+

+-------------------------------------------------------+
| Last column update |
+-------------------------------------------------------+
| Master | v18 | v19 |
+------------+--------------------+---------------------+
| 112545.537 | 116563.608 | 3.57% | 103067.276 | -8.42% |
+------------+------------+-------+------------+--------+
| 110169.224 | 115753.991 | 5.07% | 104411.44 | -5.23% |
+------------+------------+-------+------------+--------+
| 112280.874 | 116437.11 | 3.70% | 104868.98 | -6.60% |
+------------+------------+-------+------------+--------+
| 113171.556 | 116813.262 | 3.22% | 103907.012 | -8.19% |
+------------+------------+-------+------------+--------+
| 110721.881 | 117442.709 | 6.07% | 104124.131 | -5.96% |
+------------+------------+-------+------------+--------+
| 112138.601 | 115834.549 | 3.30% | 104858.624 | -6.49% |
+------------+------------+-------+------------+--------+

Thanks,
Pavan

--
Pavan Deolasee http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services