postgresql tuning with perf

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux