On Tue, Mar 21, 2017 at 8:10 PM, Robert Haas <robertmh...@gmail.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.cy@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.cy@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
WARM_test.ods
Description: application/vnd.oasis.opendocument.spreadsheet
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
