On Tue, Aug 25, 2015 at 5:21 PM, Amit Kapila <amit.kapil...@gmail.com> wrote: > > On Thu, Aug 20, 2015 at 3:49 PM, Andres Freund <and...@anarazel.de> wrote: > > How hard did you try checking whether this causes regressions? This > > increases the number of atomics in the commit path a fair bit. I doubt > > it's really bad, but it seems like a good idea to benchmark something > > like a single full-throttle writer and a large number of readers. > > One way to test this is run pgbench read load (with 100 client count) and > write load (tpc-b - with one client) simultaneously and check the results. > I have tried this and there is lot of variation(more than 50%) in tps in > different runs of write load, so not sure if this is the right way to > benchmark it. > > Another possible way is to hack pgbench code and make one thread run > write transaction and others run read transactions.
I have hacked pgbench to achieve single-writer-multi-reader test and below are results: M/c Configuration ----------------------------- IBM POWER-8 24 cores, 192 hardware threads RAM = 492GB Non-default parameters ------------------------------------ max_connections = 150 shared_buffers=8GB min_wal_size=10GB max_wal_size=15GB checkpoint_timeout =30min maintenance_work_mem = 1GB checkpoint_completion_target = 0.9 wal_buffers = 256MB Data is for 3, 15 minutes pgbench (1-Writer, 127-Readers) test runs Without ProcArrayLock optimization- Commitid – 253de7e1 Client Count/No. Of Runs (tps) 128 Run-1 208011 Run-2 471598 Run-3 218295 With ProcArrayLock optimization - Commitid – 0e141c0f Client Count/No. Of Runs (tps) 128 Run-1 222839 Run-2 469483 Run-3 215791 It seems the test runs get dominated by I/O due to writer client which leads to variation in performance numbers. In general, I don't see any noticeable difference in performance with or without procarraylock optimisation. I have tried even by turning off synchronous_commit and fsync, but the results are quite similar. pgbench modifications ----------------------------------- Introduced a new type of test run with -W option which means single writer and multi-reader, example if user has given 128 clients and 128 threads, it will use 1-Thread for Write (Update) transaction and 127 for Select Only transaction. This works specifically for this use case as I had no intention to make a generic test. Please note, it will work properly if number of clients and threads input by user are same. Attached find the pgbench patch, I have used for this test. Note that, I have used -W option in pgbench run as mentioned in below steps. Test steps for each Run -------------------------------------------------------------------------------------------------------- 1. Start Server 2. dropdb postgres 3. createdb posters 4. pgbench -i -s 300 postgres 5. pgbench -c $threads -j $threads -T 1800 -M prepared -W postgres 6. checkpoint 7. Stop Server With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
pgbench_singlewriter_multireader_v1.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
