Обсуждение: [PERFORM] postgresql tuning with perf
Thanks Tomas.
And thanks again that you plan to reproduce it.
Would appreciate if somebody can help understand as to how does one go about troubleshooting performance in the postgresql world. In Oracle, I would look at statspack and the wait events and most likely we would get the root cause.
Table has PK on col c1 and the predicate of the SELECT & UPDATE includes c1.
Server is HP DL 380 dual cpu, each cpu with 6 cores with 36GB RAM. Table size including index is 1.7GB. Shared_buffers=8GB, so the table is fully cached. Effective_cache_size=26GB. CPU util is 5-6% while running the workload. EDB is processing ~1800 requests/sec whereas Oracle is processing ~3300 req/sec.
bmdb=# desc dept_new
Table "public.dept_new"
Column | Type | Modifiers
--------+---------------+-----
c1 | numeric(10,0) | not null
c2 | numeric(10,0) |
.
.
.
.
.
c205 | numeric(10,0) |
Indexes:
"dept_new_pkey" PRIMARY KEY, btree (c1)
bmdb=#
We queried pg_stat_activity thrice every sec like this:
bmdb# \o wait_events.lst
bmdb# SELECT wait_event_type, wait_event FROM pg_stat_activity WHERE pid != pg_backend_pid() and wait_event is not null;
bmdb# \watch 0.3
We see WALWriteLock events (and that too very few). However, with either fsync=off or sync_commit=off the time gain is only about 10-15%. So eliminating those waits does not give the expected benefit. Since we dont see any other waits, we believe its actually burning the cpu but we cant figure out why.
Attached herewith is the output of perf report -g -i perf.data redirected to perf_rep.lst. I am not too sure if this is how perf reports are shared, so pls let me know if the correct method. Also, given below is a snapshot of perf report.
Thanks & Regards
On 24 October 2017 at 02:25, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
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
Вложения
Thanks Tomas.
And thanks again that you plan to reproduce it.
Would appreciate if somebody can help understand as to how does one go about troubleshooting performance in the postgresql world. In Oracle, I would look at statspack and the wait events and most likely we would get the root cause.
Table has PK on col c1 and the predicate of the SELECT & UPDATE includes c1.
Server is HP DL 380 dual cpu, each cpu with 6 cores with 36GB RAM. Table size including index is 1.7GB. Shared_buffers=8GB, so the table is fully cached. Effective_cache_size=26GB. CPU util is 5-6% while running the workload. EDB is processing ~1800 requests/sec whereas Oracle is processing ~3300 req/sec.
bmdb=# desc dept_new
Table "public.dept_new"
Column | Type | Modifiers
--------+---------------+-----
c1 | numeric(10,0) | not null
c2 | numeric(10,0) |
.
.
.
.
.
c205 | numeric(10,0) |
Indexes:
"dept_new_pkey" PRIMARY KEY, btree (c1)
bmdb=#
We queried pg_stat_activity thrice every sec like this:
bmdb# \o wait_events.lst
bmdb# SELECT wait_event_type, wait_event FROM pg_stat_activity WHERE pid != pg_backend_pid() and wait_event is not null;
bmdb# \watch 0.3
We see WALWriteLock events (and that too very few). However, with either fsync=off or sync_commit=off the time gain is only about 10-15%. So eliminating those waits does not give the expected benefit. Since we dont see any other waits, we believe its actually burning the cpu but we cant figure out why.
Attached herewith is the output of perf report -g -i perf.data redirected to perf_rep.lst. I am not too sure if this is how perf reports are shared, so pls let me know if the correct method. Also, given below is a snapshot of perf report.
Thanks & Regards
On 24 October 2017 at 02:25, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
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