public inbox for [email protected]  

help / color / mirror / Atom feed

From: Purav Chovatia <[email protected]>
To: [email protected]
Subject: postgresql tuning with perf
Date: Tue, 24 Oct 2017 00:49:20 +0530
Message-ID: <CADrzpjHuvgoTNOQosjHDaJ8YW=jDiwCn8u2h8A1HWRTaPzVc5w@mail.gmail.com> (raw)
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: postgresql tuning with perf
  In-Reply-To: <CADrzpjHuvgoTNOQosjHDaJ8YW=jDiwCn8u2h8A1HWRTaPzVc5w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox