Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM) - Mailing list pgsql-hackers
| From | Pavan Deolasee |
|---|---|
| Subject | Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM) |
| Date | |
| Msg-id | CABOikdNDDO_jH3XCTy5zKfK6z=YisbqDFQc0Oz6GXKkdCdyyuA@mail.gmail.com Whole thread Raw |
| In response to | Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM) (Mithun Cy <mithun.cy@enterprisedb.com>) |
| Responses |
Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)
(Mithun Cy <mithun.cy@enterprisedb.com>)
Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM) (Amit Kapila <amit.kapila16@gmail.com>) Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM) (Mithun Cy <mithun.cy@enterprisedb.com>) |
| List | pgsql-hackers |
On Wed, Mar 22, 2017 at 4:53 PM, Mithun Cy <mithun.cy@enterprisedb.com> wrote:
On Wed, Mar 22, 2017 at 3:44 PM, Pavan Deolasee
<pavan.deolasee@gmail.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.
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
PostgreSQL Development, 24x7 Support, Training & Services
Attachment
pgsql-hackers by date: