Skip site navigation (1) Skip section navigation (2)

Re: Patch: Write Amplification Reduction Method (WARM)

From: Mithun Cy <mithun(dot)cy(at)enterprisedb(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Cc: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Bruce Momjian <bruce(at)momjian(dot)us>, Jaime Casanova <jaime(dot)casanova(at)2ndquadrant(dot)com>, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Patch: Write Amplification Reduction Method (WARM)
Date: 2017-03-21 22:21:10
Message-ID: CAD__Ouips_paapmpuWK47gj7zmcoY8rx7Ynbfu2ywMjdWm7Thg@mail.gmail.com (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-hackers
On Tue, Mar 21, 2017 at 8:10 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> If the WAL writing hides the loss, then I agree that's not a big
> concern.  But if the loss is still visible even when WAL is written,
> then I'm not so sure.

The tests table schema was taken from earlier tests what Pavan has posted
[1], hence it is UNLOGGED all I tried to stress the tests. Instead of
updating 1 row at a time through pgbench (For which I and Pavan both did
not see any regression), I tried to update all the rows in the single
statement. I have changed the settings as recommended and did a quick test
as above in our machine by removing UNLOGGED world in create table
statement.

Patch Tested : Only 0001_interesting_attrs_v18.patch in [2]

Machine: Scylla [ Last time I did same tests on IBM power2 but It is not
immediately available. So trying on another intel based performance
machine.]
============
[mithun(dot)cy(at)scylla bin]$ lscpu
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                56
On-line CPU(s) list:   0-55
Thread(s) per core:    2
Core(s) per socket:    14
Socket(s):             2
NUMA node(s):          2
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 63
Model name:            Intel(R) Xeon(R) CPU E5-2695 v3 @ 2.30GHz
Stepping:              2
CPU MHz:               1235.800
BogoMIPS:              4594.35
Virtualization:        VT-x
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              35840K
NUMA node0 CPU(s):     0-13,28-41
NUMA node1 CPU(s):     14-27,42-55

[mithun(dot)cy(at)scylla bin]$ cat /proc/meminfo
MemTotal:       65687464 kB


Postgresql.conf non default settings
===========================
shared_buffers  = 24 GB
max_wal_size = 10GB
min_wal_size = 5GB
synchronous_commit=off
autovacuum = off  /*manually doing vacumm full before every update. */

This system has 2 storage I have kept datadir on spinning disc and pg_wal
on ssd.

Tests :

DROP TABLE IF EXISTS testtab;

CREATE TABLE testtab (

    col1 integer,

    col2 text,

    col3 float,

    col4 text,

    col5 text,

    col6 char(30),

    col7 text,

    col8 date,

    col9 text,

    col10 text

);

INSERT INTO testtab

    SELECT generate_series(1,10000000),

        md5(random()::text),

        random(),

        md5(random()::text),

        md5(random()::text),

        md5(random()::text)::char(30),

        md5(random()::text),

        now(),

        md5(random()::text),

        md5(random()::text);

CREATE INDEX testindx ON testtab (col1, col2, col3, col4, col5, col6, col7,
col8, col9);
Performance measurement tests: Ran12 times to eliminate run to run
latencies.
==========================
VACUUM FULL;
BEGIN;
UPDATE testtab SET col2 = md5(random()::text);
ROLLBACK;

Response time recorded shows there is a much higher increase in response
time from 10% to 25% after the patch.


[1] Re: rewrite HeapSatisfiesHOTAndKey
<https://www.postgresql.org/message-id/CABOikdMUQQs4BnJ4Ws-ObOEDh8vhNp13Y1caK_i8seSHKPjbhw%40mail.gmail.com>
[2] Re: Patch: Write Amplification Reduction Method (WARM)
<https://www.postgresql.org/message-id/CABOikdP1yeicUPH0NByjrg2Sv3ZtJXWyFPSqwppid8G3kLVKjw%40mail.gmail.com>
-- 
Thanks and Regards
Mithun C Y
EnterpriseDB: http://www.enterprisedb.com

Attachment: WARM_test.ods
Description: application/vnd.oasis.opendocument.spreadsheet (11.2 KB)

In response to

Responses

pgsql-hackers by date

Next:From: David SteeleDate: 2017-03-21 22:26:59
Subject: Re: proposal - psql: possibility to specify sort for describe commands, when size is printed
Previous:From: David SteeleDate: 2017-03-21 22:02:44
Subject: Re: increasing the default WAL segment size

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group