MIME-Version: 1.0
From: Purav Chovatia <puravc@gmail.com>
Date: Tue, 24 Oct 2017 00:49:20 +0530
Message-ID: 
 <CADrzpjHuvgoTNOQosjHDaJ8YW=jDiwCn8u2h8A1HWRTaPzVc5w@mail.gmail.com>
Subject: postgresql tuning with perf
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary="001a114b83f40fbf77055c3bb3d9"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a114b83f40fbf77055c3bb3d9
Content-Type: text/plain; charset="UTF-8"

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

--001a114b83f40fbf77055c3bb3d9
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello Experts,<div><br></div><div>We are trying to tune ou=
r postgresql DB using perf. We are running a C program that connects to pos=
tgres DB and calls very simple StoredProcs, one each for SELECT, INSERT &am=
p; UPDATE.=C2=A0</div><div><br></div><div>The SPs are very simple.=C2=A0</d=
iv><div><b>SELECT_SP</b>:</div><div>CREATE OR REPLACE PROCEDURE query_dept_=
new(p1 IN numeric, p2 OUT numeric,p3 OUT numeric,.......,p205 OUT numeric) =
AS</div><div>BEGIN</div><div>=C2=A0 =C2=A0 SELECT c2,c3,......,c205</div><d=
iv>=C2=A0 =C2=A0 =C2=A0 =C2=A0 INTO p2,p3,.......,p205</div><div>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 FROM dept_new</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE=
 c1 =3D p1;</div><div>END;</div><div><br></div><div><b>UPDATE_SP</b>:</div>=
<div><div>CREATE OR REPLACE PROCEDURE query_dept_update(p1 IN numeric, p2 I=
N numeric,........,p205 IN numeric) AS</div><div>BEGIN</div><div>=C2=A0 =C2=
=A0 update dept_new set=C2=A0 c2 =3D p2,c3 =3D p3,.....,c205 =3D p205=C2=A0=
</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE c1 =3D p1;</div><div><span sty=
le=3D"white-space:pre">	</span>commit;</div><div>END;</div></div><div><br><=
/div><div><b>INSERT_SP</b>:<br></div><div><div>CREATE OR REPLACE PROCEDURE =
query_dept_insert(p1 IN numeric, p2 IN numeric,.....,p205 IN numeric) AS</d=
iv><div>BEGIN</div><div><span style=3D"white-space:pre">	</span>insert into=
 dept_new values(p1,p2,.....,p205);</div><div><span style=3D"white-space:pr=
e">	</span>commit;</div><div>END;</div></div><div><br></div><div>As shown a=
bove, its all on a single table. Before every test, the table is truncated =
and loaded with 1m rows. WAL is on a separate disk.</div><div><br></div><di=
v>Its about 3x slower as compared to Oracle and major events are WAL relate=
d. With fsync=3Doff or sync_commit=3Doff it gets 10% better but still far f=
rom Oracle. Vacuuming the table does not help. Checkpoint too is not an iss=
ue.=C2=A0</div><div><br></div><div>Since we dont see any other way to find =
out what is slowing it down, we gathered data using the perf tool. Can some=
body pls help on how do we go about reading the perf report.=C2=A0</div><di=
v><br></div><div>Thanks &amp; Regards</div></div>

--001a114b83f40fbf77055c3bb3d9--