On 09/17/2016 05:23 AM, Amit Kapila wrote:
On Sat, Sep 17, 2016 at 6:54 AM, Tomas Vondra
<tomas.von...@2ndquadrant.com> wrote:
On 09/14/2016 06:04 PM, Dilip Kumar wrote:
...
(I've also ran it with 100M rows, called "large" in the results), and
pgbench is running this transaction:
\set id random(1, 100000)
BEGIN;
UPDATE t SET val = val + 1 WHERE id = :id;
SAVEPOINT s1;
UPDATE t SET val = val + 1 WHERE id = :id;
SAVEPOINT s2;
UPDATE t SET val = val + 1 WHERE id = :id;
SAVEPOINT s3;
UPDATE t SET val = val + 1 WHERE id = :id;
SAVEPOINT s4;
UPDATE t SET val = val + 1 WHERE id = :id;
SAVEPOINT s5;
UPDATE t SET val = val + 1 WHERE id = :id;
SAVEPOINT s6;
UPDATE t SET val = val + 1 WHERE id = :id;
SAVEPOINT s7;
UPDATE t SET val = val + 1 WHERE id = :id;
SAVEPOINT s8;
COMMIT;
So 8 simple UPDATEs interleaved by savepoints.
The difference between these and tests performed by Dilip is that he
has lesser savepoints. I think if you want to try it again, then can
you once do it with either no savepoint or 1~2 savepoints. The other
thing you could try out is the same test as Dilip has done (with and
without 2 savepoints).
I don't follow. My understanding is the patches should make savepoints
cheaper - so why would using fewer savepoints increase the effect of the
patches?
FWIW I've already done a quick test with 2 savepoints, no difference. I
can do a full test of course.
The benchmark was running on
a machine with 256GB of RAM, 32 cores (4x E5-4620) and a fairly large SSD
array. I'd done some basic tuning on the system, most importantly:
effective_io_concurrency = 32
work_mem = 512MB
maintenance_work_mem = 512MB
max_connections = 300
checkpoint_completion_target = 0.9
checkpoint_timeout = 3600
max_wal_size = 128GB
min_wal_size = 16GB
shared_buffers = 16GB
Although most of the changes probably does not matter much for unlogged
tables (I planned to see how this affects regular tables, but as I see no
difference for unlogged ones, I haven't done that yet).
You are right. Unless, we don't see the benefit with unlogged tables,
there is no point in doing it for regular tables.
So the question is why Dilip sees +30% improvement, while my results are
almost exactly the same. Looking at Dilip's benchmark, I see he only ran the
test for 10 seconds, and I'm not sure how many runs he did, warmup etc.
Dilip, can you provide additional info?
I'll ask someone else to redo the benchmark after the weekend to make sure
it's not actually some stupid mistake of mine.
I think there is not much point in repeating the tests you have
done, rather it is better if we can try again the tests done by Dilip
in your environment to see the results.
I'm OK with running Dilip's tests, but I'm not sure why there's not much
point in running the tests I've done. Or perhaps I'd like to understand
why "my tests" show no improvement whatsoever first - after all, they're
not that different from Dilip's.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
