On Thu, Mar 30, 2017 at 5:27 PM, Amit Kapila <amit.kapil...@gmail.com> wrote: > I am not sure if we can consider it as completely synthetic because we > might see some similar cases for json datatypes. Can we once try to > see the impact when the same test runs from multiple clients? For > your information, I am also trying to setup some tests along with one > of my colleague and we will report the results once the tests are > complete. We have done some testing and below is the test details and results.
Test: I have derived this test from above test given by pavan[1] except below difference. - I have reduced the fill factor to 40 to ensure that multiple there is scope in the page to store multiple WARM chains. - WARM updated all the tuples. - Executed a large select to enforce lot of recheck tuple within single query. - Smaller tuple size (aid field is around ~100 bytes) just to ensure tuple have sufficient space on a page to get WARM updated. Results: ----------- * I can see more than 15% of regression in this case. This regression is repeatable. * If I increase the fill factor to 90 than regression reduced to 7%, may be only fewer tuples are getting WARM updated and others are not because of no space left on page after few WARM update. Test Setup: ---------------- Machine Information: Intel(R) Xeon(R) CPU E5-2695 v3 @ 2.30GHz RAM: 64GB Config Change: synchronous_commit=off ——Setup.sql— DROP TABLE IF EXISTS pgbench_accounts; CREATE TABLE pgbench_accounts ( aid text, bid bigint, abalance bigint, filler1 text DEFAULT md5(random()::text), filler2 text DEFAULT md5(random()::text), filler3 text DEFAULT md5(random()::text), filler4 text DEFAULT md5(random()::text), filler5 text DEFAULT md5(random()::text), filler6 text DEFAULT md5(random()::text), filler7 text DEFAULT md5(random()::text), filler8 text DEFAULT md5(random()::text), filler9 text DEFAULT md5(random()::text), filler10 text DEFAULT md5(random()::text), filler11 text DEFAULT md5(random()::text), filler12 text DEFAULT md5(random()::text) ) WITH (fillfactor=40); \set scale 10 \set end 0 \set start (:end + 1) \set end (:start + (:scale * 100)) INSERT INTO pgbench_accounts SELECT generate_series(:start, :end )::text || repeat('a', 100), (random()::bigint) % :scale, 0; CREATE UNIQUE INDEX pgb_a_aid ON pgbench_accounts(aid); CREATE INDEX pgb_a_filler1 ON pgbench_accounts(filler1); CREATE INDEX pgb_a_filler2 ON pgbench_accounts(filler2); CREATE INDEX pgb_a_filler3 ON pgbench_accounts(filler3); CREATE INDEX pgb_a_filler4 ON pgbench_accounts(filler4); UPDATE pgbench_accounts SET filler1 = 'X'; --WARM update all the tuples —Test.sql— set enable_seqscan=off; set enable_bitmapscan=off; explain analyze select * FROM pgbench_accounts WHERE aid < '400' || repeat('a', 100) ORDER BY aid —Script.sh— ./psql -d postgres -f setup.sql ./pgbench -c1 -j1 -T300 -M prepared -f test.sql postgres Patch: tps = 3554.345313 (including connections establishing) tps = 3554.880776 (excluding connections establishing) Head: tps = 4208.876829 (including connections establishing) tps = 4209.440321 (excluding connections establishing) *** After changing fill factor to 90 — Patch: tps = 3794.414770 (including connections establishing) tps = 3794.919592 (excluding connections establishing) Head: tps = 4206.445608 (including connections establishing) tps = 4207.033559 (excluding connections establishing) [1] https://www.postgresql.org/message-id/CABOikdMduu9wOhfvNzqVuNW4YdBgbgwv-A%3DHNFCL7R5Tmbx7JA%40mail.gmail.com I have done some perfing for the patch and I have noticed that time is increased in heap_check_warm_chain function. Top 10 functions in perf results (with patch): + 8.98% 1.04% postgres postgres [.] varstr_cmp + 7.24% 0.00% postgres [unknown] [.] 0000000000000000 + 6.34% 0.36% postgres libc-2.17.so [.] clock_gettime + 6.34% 0.00% postgres [unknown] [.] 0x0000000000030000 + 6.18% 6.15% postgres [vdso] [.] __vdso_clock_gettime + 5.72% 0.02% postgres [kernel.kallsyms] [k] system_call_fastpath + 4.08% 4.06% postgres libc-2.17.so [.] __memcpy_ssse3_back + 4.08% 4.06% postgres libc-2.17.so [.] get_next_seq + 3.92% 0.00% postgres [unknown] [.] 0x6161616161616161 + 3.07% 3.05% postgres postgres [.] heap_check_warm_chain Thanks to Amit for helping in discussing the test ideas. -- Regards, Dilip Kumar EnterpriseDB: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers