On 2013-10-14 15:51:14 +0200, Andres Freund wrote: > > > It'd probably not hurt to redo those benchmarks to make sure... > > > > Yes, I think it would be good to characterize it more precisely than > > "a bit", so people know what to expect. > > A "bit" was below the 3% range for loops of adding columns. > > So, any tests you'd like to see? > * loop around CREATE TABLE/DROP TABLE > * loop around ALTER TABLE ... ADD COLUMN > * loop around CREATE FUNCTION/DROP FUNCTION
So, see the attatched benchmark skript. I've always done using a disk bound and a memory bound (using eatmydata, preventing fsyncs) run. * unpatched run, wal_level = hot_standby, eatmydata * unpatched run, wal_level = hot_standby * patched run, wal_level = hot_standby, eatmydata * patched run, wal_level = hot_standby * patched run, wal_level = logical, eatmydata * patched run, wal_level = logical Based on those results, there's no difference above noise for wal_level=hot_standby, with or without the patch. With wal_level=logical there's a measurable increase in wal traffic (~12-17%), but no performance decrease above noise. >From my POV that's ok, those are really crazy catalog workloads. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
$ eatmydata /tmp/bench.sh createtable single tps = 320.293146 (including connections establishing) tps = 320.334981 (excluding connections establishing) 262152 /tmp/perf/pg_xlog createtable parallel tps = 1516.095491 (including connections establishing) tps = 1516.488664 (excluding connections establishing) 1163272 /tmp/perf/pg_xlog altertable single tps = 139.474141 (including connections establishing) tps = 139.491721 (excluding connections establishing) 163848 /tmp/perf/pg_xlog altertable parallel tps = 774.868029 (including connections establishing) tps = 775.136842 (excluding connections establishing) 851976 /tmp/perf/pg_xlog createfunction single tps = 2938.380801 (including connections establishing) tps = 2938.711692 (excluding connections establishing) 81928 /tmp/perf/pg_xlog createfunction parallel tps = 20603.023567 (including connections establishing) tps = 20608.799987 (excluding connections establishing) 458760 /tmp/perf/pg_xlog $ /tmp/bench.sh createtable single tps = 51.014096 (including connections establishing) tps = 51.020329 (excluding connections establishing) 49160 /tmp/perf/pg_xlog createtable parallel tps = 171.012045 (including connections establishing) tps = 171.054406 (excluding connections establishing) 147464 /tmp/perf/pg_xlog altertable single tps = 9.138758 (including connections establishing) tps = 9.139863 (excluding connections establishing) 32776 /tmp/perf/pg_xlog altertable parallel tps = 45.109269 (including connections establishing) tps = 45.122066 (excluding connections establishing) 65544 /tmp/perf/pg_xlog createfunction single tps = 131.192719 (including connections establishing) tps = 131.209112 (excluding connections establishing) 16392 /tmp/perf/pg_xlog createfunction parallel tps = 624.830173 (including connections establishing) tps = 625.017525 (excluding connections establishing) 32776 /tmp/perf/pg_xlog -- patch applied -- $ eatmydata /tmp/bench.sh createtable single tps = 329.063474 (including connections establishing) tps = 329.104147 (excluding connections establishing) 262152 /tmp/perf/pg_xlog createtable parallel tps = 1462.524932 (including connections establishing) tps = 1462.872552 (excluding connections establishing) 1130504 /tmp/perf/pg_xlog altertable single tps = 141.091905 (including connections establishing) tps = 141.108352 (excluding connections establishing) 163848 /tmp/perf/pg_xlog altertable parallel tps = 812.810544 (including connections establishing) tps = 813.026734 (excluding connections establishing) 901128 /tmp/perf/pg_xlog createfunction single tps = 3384.068190 (including connections establishing) tps = 3384.460953 (excluding connections establishing) 81928 /tmp/perf/pg_xlog createfunction parallel tps = 20744.363972 (including connections establishing) tps = 20750.135978 (excluding connections establishing) 458760 /tmp/perf/pg_xlog $ /tmp/bench.sh createtable single tps = 42.522505 (including connections establishing) tps = 42.527233 (excluding connections establishing) 49160 /tmp/perf/pg_xlog createtable parallel tps = 148.753762 (including connections establishing) tps = 148.794361 (excluding connections establishing) 131080 /tmp/perf/pg_xlog altertable single tps = 9.524616 (including connections establishing) tps = 9.525757 (excluding connections establishing) 32776 /tmp/perf/pg_xlog altertable parallel tps = 49.209278 (including connections establishing) tps = 49.223312 (excluding connections establishing) 65544 /tmp/perf/pg_xlog createfunction single tps = 132.325526 (including connections establishing) tps = 132.340677 (excluding connections establishing) 16392 /tmp/perf/pg_xlog createfunction parallel tps = 654.730078 (including connections establishing) tps = 654.951506 (excluding connections establishing) 32776 /tmp/perf/pg_xlog -- patch applied wal_level = logical -- $ eatmydata /tmp/bench.sh createtable single tps = 323.503496 (including connections establishing) tps = 323.542429 (excluding connections establishing) 311304 /tmp/perf/pg_xlog createtable parallel tps = 1537.166484 (including connections establishing) tps = 1537.599205 (excluding connections establishing) 1441800 /tmp/perf/pg_xlog altertable single tps = 137.785443 (including connections establishing) tps = 137.802088 (excluding connections establishing) 196616 /tmp/perf/pg_xlog altertable parallel tps = 759.320764 (including connections establishing) tps = 759.616314 (excluding connections establishing) 1064968 /tmp/perf/pg_xlog createfunction single tps = 3206.263449 (including connections establishing) tps = 3206.635093 (excluding connections establishing) 98312 /tmp/perf/pg_xlog createfunction parallel tps = 20454.430246 (including connections establishing) tps = 20460.920317 (excluding connections establishing) 557064 /tmp/perf/pg_xlog $ /tmp/bench.sh createtable single tps = 44.728192 (including connections establishing) tps = 44.733640 (excluding connections establishing) 65544 /tmp/perf/pg_xlog createtable parallel tps = 180.100543 (including connections establishing) tps = 180.158944 (excluding connections establishing) 196616 /tmp/perf/pg_xlog altertable single tps = 10.651403 (including connections establishing) tps = 10.652567 (excluding connections establishing) 32776 /tmp/perf/pg_xlog altertable parallel tps = 46.401373 (including connections establishing) tps = 46.414657 (excluding connections establishing) 81928 /tmp/perf/pg_xlog createfunction single tps = 136.372357 (including connections establishing) tps = 136.388755 (excluding connections establishing) 16392 /tmp/perf/pg_xlog createfunction parallel tps = 685.702432 (including connections establishing) tps = 685.883334 (excluding connections establishing) 32776 /tmp/perf/pg_xlog
bench.sh
Description: Bourne shell script
CREATE TABLE foo:client_id(id serial, data text, anotherdata int); ALTER TABLE foo:client_id ADD COLUMN col_01 int; ALTER TABLE foo:client_id ADD COLUMN col_02 int; ALTER TABLE foo:client_id ADD COLUMN col_03 int; ALTER TABLE foo:client_id ADD COLUMN col_04 int; ALTER TABLE foo:client_id ADD COLUMN col_05 int; ALTER TABLE foo:client_id ADD COLUMN col_06 int; ALTER TABLE foo:client_id ADD COLUMN col_07 int; ALTER TABLE foo:client_id ADD COLUMN col_08 int; ALTER TABLE foo:client_id ADD COLUMN col_09 int; ALTER TABLE foo:client_id ADD COLUMN col_10 int; ALTER TABLE foo:client_id ADD COLUMN col_11 int; ALTER TABLE foo:client_id ADD COLUMN col_12 int; ALTER TABLE foo:client_id ADD COLUMN col_13 int; ALTER TABLE foo:client_id ADD COLUMN col_14 int; ALTER TABLE foo:client_id ADD COLUMN col_15 int; ALTER TABLE foo:client_id ADD COLUMN col_16 int; ALTER TABLE foo:client_id ADD COLUMN col_17 int; ALTER TABLE foo:client_id ADD COLUMN col_18 int; ALTER TABLE foo:client_id ADD COLUMN col_19 int; DROP TABLE foo:client_id;
CREATE FUNCTION foo:client_id(param int) RETURNS int LANGUAGE sql AS $b$SELECT 1 + $1;$b$; DROP FUNCTION foo:client_id(int);
CREATE TABLE foo:client_id(id serial, data text, anotherdata int); DROP TABLE foo:client_id;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
