Hello Experts,
We are trying to tune our postgresql DB using perf. 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.
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.
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.
Thanks & Regards
