Re: [WIP] Performance Improvement by reducing WAL for Update Operation - Mailing list pgsql-hackers

Hi Amit,

On Tue, Oct 16, 2012 at 09:22:39AM +0000, Amit kapila wrote:
> On Saturday, October 06, 2012 7:34 PM Amit Kapila wrote:
> > Please find the readings of LZ patch along with Xlog-Scale patch.
> > The comparison is between for Update operations
> > base code + Xlog Scale Patch
> > base code + Xlog Scale Patch + Update WAL Optimization (LZ compression)
> 
> This contains all the consolidated data and comparison for both the approaches:
> 
> The difference of this testcase as compare to previous one is that it has default value of wal_page_size ( 8K ) as
compareto previous one where configuration used for wal_page_size was 1K
 

What is "wal_page_size"?  Is that ./configure --with-wal-blocksize?

> Observations From Performance Data
> ----------------------------------------------
> 1. With both the approaches Performance data is good.
>     LZ compression - upto 100% performance improvement.
>     Offset Approach - upto 160% performance improvement.
> 2. The performance data is better for LZ compression approach when the changed value of tuple is large. (Refer 500
lengthchanged value).
 
> 3. The performance data is better for Offset Approach for 1 thread for any size of Data (it dips for LZ compression
Approach).

Stepping back a moment, I would expect this patch to change performance in at
least four ways (Heikki largely covered this upthread):

a) High-concurrency workloads will improve thanks to reduced WAL insert
contention.
b) All workloads will degrade due to the CPU cost of identifying and
implementing the optimization.
c) Workloads starved for bulk WAL I/O will improve due to reduced WAL volume.
d) Workloads composed primarily of long transactions with high WAL volume will
improve due to having fewer end-of-WAL-segment fsync requests.

Your benchmark numbers show small gains and losses for single-client
workloads, moving to moderate gains for 2-client workloads.  This suggests
strong influence from (a), some influence from (b), and little influence from
(c) and (d).  Actually, the response to scale evident in your numbers seems
too good to be true; why would (a) have such a large effect over the
transition from one client to two clients?  Also, for whatever reason, all
your numbers show fairly bad scaling.  With the XLOG scale and LZ patches,
synchronous_commit=off, -F 80, and rec length 250, 8-client average
performance is only 2x that of 1-client average performance.

I attempted to reproduce this effect on an EC2 m2.4xlarge instance (8 cores,
70 GiB) with the data directory under a tmpfs mount.  This should thoroughly
isolate effects (a) and (b) from (c) and (d).  I used your pgbench_250.c[1] in
30s runs.  Configuration:
autovacuum          | offcheckpoint_segments | 500checkpoint_timeout  | 1hclient_encoding     | UTF8lc_collate
|Clc_ctype            | Cmax_connections     | 100server_encoding     | SQL_ASCIIshared_buffers      | 4GBwal_buffers
     | 16MB
 

Benchmark results:

-Patch-             -tps@-c1-   -tps@-c2-   -tps@-c8-   -WAL@-c8-
HEAD,-F80           816         1644        6528        1821 MiB
xlogscale,-F80      824         1643        6551        1826 MiB
xlogscale+lz,-F80   717         1466        5924        1137 MiB
xlogscale+lz,-F100  753         1508        5948        1548 MiB

Those are short runs with no averaging of multiple iterations; don't put too
much faith in the absolute numbers.  Still, I consistently get linear scaling
from 1 client to 8 clients.  Why might your results have been so different in
this regard?

It's also odd that your -F100 numbers tend to follow your -F80 numbers despite
the optimization kicking in far more frequently for the latter.

nm

[1] http://archives.postgresql.org/message-id/001d01cda180$9f1e47a0$dd5ad6e0$@kapila@huawei.com