On 10/23/2017 09:19 PM, Purav Chovatia wrote: > Hello Experts, > > We are trying to tune our postgresql DB using perf. Can you share some of the perf reports, then? > We are running a C program that connects to postgres DB and calls > very simple StoredProcs, one each for SELECT, INSERT & UPDATE. > > The SPs are very simple. > *SELECT_SP*: > CREATE OR REPLACE PROCEDURE query_dept_new(p1 IN numeric, p2 OUT > numeric,p3 OUT numeric,.......,p205 OUT numeric) AS > BEGIN > SELECT c2,c3,......,c205 > INTO p2,p3,.......,p205 > FROM dept_new > WHERE c1 = p1; > END; > > *UPDATE_SP*: > CREATE OR REPLACE PROCEDURE query_dept_update(p1 IN numeric, p2 IN > numeric,........,p205 IN numeric) AS > BEGIN > update dept_new set c2 = p2,c3 = p3,.....,c205 = p205 > WHERE c1 = p1; > commit; > END; > > *INSERT_SP*: > CREATE OR REPLACE PROCEDURE query_dept_insert(p1 IN numeric, p2 IN > numeric,.....,p205 IN numeric) AS > BEGIN > insert into dept_new values(p1,p2,.....,p205); > commit; > END; > > As shown above, its all on a single table. Before every test, the table > is truncated and loaded with 1m rows. WAL is on a separate disk. > It'd be nice if you could share more details about the structure of the table, hardware and observed metrics (throughput, ...). Otherwise we can't try reproducing it, for example. > Its about 3x slower as compared to Oracle and major events are WAL > related. With fsync=off or sync_commit=off it gets 10% better but still > far from Oracle. Vacuuming the table does not help. Checkpoint too is > not an issue. So how do you know the major events are WAL related? Can you share how you measure that and the measurements? > > Since we dont see any other way to find out what is slowing it down, we > gathered data using the perf tool. Can somebody pls help on how do we go > about reading the perf report. Well, that's hard to do when you haven't shared the report. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
