Notes about Pl/PgSQL assignment performance

Hi

2017-12-19 12:28 GMT+01:00 Андрей Жиденков <pensnarik@gmail.com>:

Few day ago a faced a problem: Pl/PgSQL procedure works slower when
running in parallel threads. I found the correlation between number of
assignments in procedure code and performance. I decided to write the
simple benchmark procedures and perform some test on PostgreSQL 9.6.5
database installed on the server with 20 CPU cores (2 Xeon E5-2690V2 CPUs).

This benchmark showed me that a simple Pl/PgSQL procedure with a simple
loop inside works slower when running even in 2 threads. There is a
procedure:

CREATE OR REPLACE FUNCTION benchmark_test() RETURNS VOID AS $$
DECLARE
v INTEGER; i INTEGER;
BEGIN
for i in 1..1000 loop
v := 1;
end loop;
END;
$$ LANGUAGE plpgsql;

What is the point? I know, that Pl/PgSQL performs a SELECT query to
calculate each value for assignment but I didn't expect that it produce
side effects like this. If there is some buffer lock or anything else?

I am little bit lost when you are speaking about threads. Postgres doesn't
use it.

your test is not correct - benchmark_test should be marked as immutable.
What will be result?

Regards

Pavel

On 19.12.2017 11:36, Pavel Stehule wrote:

Hi

2017-12-19 12:28 GMT+01:00 Андрей Жиденков <pensnarik@gmail.com
<mailto:pensnarik@gmail.com>>:

Few day ago a faced a problem: Pl/PgSQL procedure works slower
when running in parallel threads. I found the correlation between
number of assignments in procedure code and performance. I decided
to write the simple benchmark procedures and perform some test on
PostgreSQL 9.6.5 database installed on the server with 20 CPU
cores (2 Xeon E5-2690V2 CPUs).

This benchmark showed me that a simple Pl/PgSQL procedure with a
simple loop inside works slower when running even in 2 threads.
There is a procedure:

CREATE OR REPLACE FUNCTION benchmark_test() RETURNS VOID AS $$
DECLARE
  v INTEGER; i INTEGER;
BEGIN
  for i in 1..1000 loop
    v := 1;
  end loop;
END;
$$ LANGUAGE plpgsql;

What is the point? I know, that Pl/PgSQL performs a SELECT query
to calculate each value for assignment but I didn't expect that it
produce side effects like this. If there is some buffer lock or
anything else?

I am little bit lost when you are speaking about threads. Postgres
doesn't use it.

your test is not correct - benchmark_test should be marked as immutable.

Would marking it IMMUTABLE not cache the result and thus bypass the
actual testing ?

What will be result?

Regards

Pavel

 

I've been written a post with charts and detailed explanation to
display these side
effects: http://telegra.ph/Notes-about-PlPgSQL-assignment-performance-12-19
<http://telegra.ph/Notes-about-PlPgSQL-assignment-performance-12-19&gt;

Any help would be greatly appreciated.
--

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
https://2ndquadrant.com/

When I run this test in 2 threads I expect that running time will be the
same, because PostgreSQL will fork process for the second connection and
this process will be served by a separate CPU core because I have more than
2 cores.
Yes, IMMUTABLE flag helps, but I think It's just because Postgres actually
executes procedure only once.

On Tue, Dec 19, 2017 at 2:36 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

2017-12-19 12:28 GMT+01:00 Андрей Жиденков <pensnarik@gmail.com>:

Few day ago a faced a problem: Pl/PgSQL procedure works slower when
running in parallel threads. I found the correlation between number of
assignments in procedure code and performance. I decided to write the
simple benchmark procedures and perform some test on PostgreSQL 9.6.5
database installed on the server with 20 CPU cores (2 Xeon E5-2690V2 CPUs).

This benchmark showed me that a simple Pl/PgSQL procedure with a simple
loop inside works slower when running even in 2 threads. There is a
procedure:

CREATE OR REPLACE FUNCTION benchmark_test() RETURNS VOID AS $$
DECLARE
v INTEGER; i INTEGER;
BEGIN
for i in 1..1000 loop
v := 1;
end loop;
END;
$$ LANGUAGE plpgsql;

What is the point? I know, that Pl/PgSQL performs a SELECT query to
calculate each value for assignment but I didn't expect that it produce
side effects like this. If there is some buffer lock or anything else?

I am little bit lost when you are speaking about threads. Postgres doesn't
use it.

your test is not correct - benchmark_test should be marked as immutable.
What will be result?

Regards

Pavel

I've been written a post with charts and detailed explanation to display
these side effects: http://telegra.ph/Notes-about-PlPgSQL-assignment-
performance-12-19

Any help would be greatly appreciated.
--

--
С уважением, Андрей Жиденков.

2017-12-19 12:40 GMT+01:00 Hannu Krosing <hkrosing@gmail.com>:

On 19.12.2017 11:36, Pavel Stehule wrote:

Hi

2017-12-19 12:28 GMT+01:00 Андрей Жиденков <pensnarik@gmail.com>:

Few day ago a faced a problem: Pl/PgSQL procedure works slower when
running in parallel threads. I found the correlation between number of
assignments in procedure code and performance. I decided to write the
simple benchmark procedures and perform some test on PostgreSQL 9.6.5
database installed on the server with 20 CPU cores (2 Xeon E5-2690V2 CPUs).

This benchmark showed me that a simple Pl/PgSQL procedure with a simple
loop inside works slower when running even in 2 threads. There is a
procedure:

CREATE OR REPLACE FUNCTION benchmark_test() RETURNS VOID AS $$
DECLARE
v INTEGER; i INTEGER;
BEGIN
for i in 1..1000 loop
v := 1;
end loop;
END;
$$ LANGUAGE plpgsql;

What is the point? I know, that Pl/PgSQL performs a SELECT query to
calculate each value for assignment but I didn't expect that it produce
side effects like this. If there is some buffer lock or anything else?

I am little bit lost when you are speaking about threads. Postgres doesn't
use it.

your test is not correct - benchmark_test should be marked as immutable.

Would marking it IMMUTABLE not cache the result and thus bypass the actual
testing ?

CREATE OR REPLACE FUNCTION public.fx1()
RETURNS void
LANGUAGE plpgsql
AS $function$
begin
for i in 1..10
loop
raise notice '%', i;
end loop;
end;
$function$

postgres=# do $$
postgres$# begin
postgres$# for i in 1..2
postgres$# loop
postgres$# perform fx1();
postgres$# end loop;
postgres$# end;
postgres$# $$;
NOTICE: 1
NOTICE: 2
NOTICE: 3
NOTICE: 4
NOTICE: 5
NOTICE: 6
NOTICE: 7
NOTICE: 8
NOTICE: 9
NOTICE: 10
NOTICE: 1
NOTICE: 2
NOTICE: 3
NOTICE: 4
NOTICE: 5
NOTICE: 6
NOTICE: 7
NOTICE: 8
NOTICE: 9
NOTICE: 10
DO

test it.

Personally - this test is little bit bad. What is goal? PLpgSQL is glue for
SQL queries - nothing less, nothing more.

2017-12-19 12:45 GMT+01:00 Andrey Zhidenkov <pensnarik@gmail.com>:

When I run this test in 2 threads I expect that running time will be the
same, because PostgreSQL will fork process for the second connection and
this process will be served by a separate CPU core because I have more than
2 cores.
Yes, IMMUTABLE flag helps, but I think It's just because Postgres actually
executes procedure only once.

surely not - test it.

I am lazy think about it - but probably real reason is +/- execution of
read only transactions or possibly write transactions.

PostgreSQL is primary ACID database. You cannot to think about it like
scripting environment only.

Regards

Pavel

2017-12-19 12:46 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2017-12-19 12:40 GMT+01:00 Hannu Krosing <hkrosing@gmail.com>:

On 19.12.2017 11:36, Pavel Stehule wrote:

Hi

2017-12-19 12:28 GMT+01:00 Андрей Жиденков <pensnarik@gmail.com>:

Few day ago a faced a problem: Pl/PgSQL procedure works slower when
running in parallel threads. I found the correlation between number of
assignments in procedure code and performance. I decided to write the
simple benchmark procedures and perform some test on PostgreSQL 9.6.5
database installed on the server with 20 CPU cores (2 Xeon E5-2690V2 CPUs).

This benchmark showed me that a simple Pl/PgSQL procedure with a simple
loop inside works slower when running even in 2 threads. There is a
procedure:

CREATE OR REPLACE FUNCTION benchmark_test() RETURNS VOID AS $$
DECLARE
v INTEGER; i INTEGER;
BEGIN
for i in 1..1000 loop
v := 1;
end loop;
END;
$$ LANGUAGE plpgsql;

What is the point? I know, that Pl/PgSQL performs a SELECT query to
calculate each value for assignment but I didn't expect that it produce
side effects like this. If there is some buffer lock or anything else?

I am little bit lost when you are speaking about threads. Postgres
doesn't use it.

your test is not correct - benchmark_test should be marked as immutable.

Would marking it IMMUTABLE not cache the result and thus bypass the
actual testing ?

CREATE OR REPLACE FUNCTION public.fx1()
RETURNS void
LANGUAGE plpgsql
AS $function$
begin
for i in 1..10
loop
raise notice '%', i;
end loop;
end;
$function$

postgres=# do $$
postgres$# begin
postgres$# for i in 1..2
postgres$# loop
postgres$# perform fx1();
postgres$# end loop;
postgres$# end;
postgres$# $$;
NOTICE: 1
NOTICE: 2
NOTICE: 3
NOTICE: 4
NOTICE: 5
NOTICE: 6
NOTICE: 7
NOTICE: 8
NOTICE: 9
NOTICE: 10
NOTICE: 1
NOTICE: 2
NOTICE: 3
NOTICE: 4
NOTICE: 5
NOTICE: 6
NOTICE: 7
NOTICE: 8
NOTICE: 9
NOTICE: 10
DO

test it.

Personally - this test is little bit bad. What is goal? PLpgSQL is glue
for SQL queries - nothing less, nothing more.

I am wrong - sorry

It needs a fake parameter

postgres=# create or replace function fx1(int)
returns void as $$
begin
for i in 1..10
loop
raise notice '%', i;
end loop;
end;
$$ language plpgsql immutable;

postgres=# do $$
begin
for i in 1..2
loop
perform fx1(i);
end loop;
end;
$$;
NOTICE: 1
NOTICE: 2
NOTICE: 3
NOTICE: 4
NOTICE: 5
NOTICE: 6
NOTICE: 7
NOTICE: 8
NOTICE: 9
NOTICE: 10
NOTICE: 1
NOTICE: 2
NOTICE: 3
NOTICE: 4
NOTICE: 5
NOTICE: 6
NOTICE: 7
NOTICE: 8
NOTICE: 9
NOTICE: 10
DO

Andrey Zhidenkov wrote:

When I run this test in 2 threads I expect that running time will be the
same, because PostgreSQL will fork process for the second connection and
this process will be served by a separate CPU core because I have more than
2 cores.
Yes, IMMUTABLE flag helps, but I think It's just because Postgres actually
executes procedure only once.

Just a guess without actually looking at the WaitEvents (which you
should do) is that this is blocking on snapshot acquisition or something
like that.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

I've digged into the source code a little bit and found that chain:

PLPGSQL_STMT_ASSIGN -> exec_stmt_assign() -> exec_assign_expr()
-> exec_eval_expr() -> exec_run_select()
-> SPI_execute_plan_with_paramlist() -> _SPI_execute_plan() which finnaly
calls PushActiveSnapshot() and PopActiveSnapshot() wich just do memory
context allocations and use malloc() to copy snaphot.

Maybe I have missed something?

On Tue, Dec 19, 2017 at 4:34 PM, Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:

Andrey Zhidenkov wrote:

When I run this test in 2 threads I expect that running time will be the
same, because PostgreSQL will fork process for the second connection and
this process will be served by a separate CPU core because I have more

than

2 cores.
Yes, IMMUTABLE flag helps, but I think It's just because Postgres

actually

executes procedure only once.

Just a guess without actually looking at the WaitEvents (which you
should do) is that this is blocking on snapshot acquisition or something
like that.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
С уважением, Андрей Жиденков.

On 20 December 2017 at 02:48, Andrey Zhidenkov <pensnarik@gmail.com> wrote:

PLPGSQL_STMT_ASSIGN -> exec_stmt_assign() -> exec_assign_expr() ->
exec_eval_expr() -> exec_run_select() -> SPI_execute_plan_with_paramlist()
-> _SPI_execute_plan() which finnaly calls PushActiveSnapshot() and
PopActiveSnapshot() wich just do memory context allocations and use malloc()
to copy snaphot.

Probably the best thing to do is to look at which functions are taking
the most time by doing a perf record for a single running instance,
then the same again with multiple instances running. Perhaps something
in there might appear in the samples more often with the multiple
instances than it does with a single instance.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Andrey Zhidenkov wrote:

I've digged into the source code a little bit and found that chain:

PLPGSQL_STMT_ASSIGN -> exec_stmt_assign() -> exec_assign_expr()
-> exec_eval_expr() -> exec_run_select()
-> SPI_execute_plan_with_paramlist() -> _SPI_execute_plan() which finnaly
calls PushActiveSnapshot() and PopActiveSnapshot() wich just do memory
context allocations and use malloc() to copy snaphot.

Maybe I have missed something?

Yes.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services