Thread: Notes about Pl/PgSQL assignment performance

Notes about Pl/PgSQL assignment performance

From

Андрей Жиденков

Date:

19 December 2017, 14:28:00

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'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.

Re: Notes about Pl/PgSQL assignment performance

From

Pavel Stehule

Date:

19 December 2017, 14:36:25

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.
--

Re: Notes about Pl/PgSQL assignment performance

From

Hannu Krosing

Date:

19 December 2017, 14:40:31

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 ?

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.
--

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

Re: Notes about Pl/PgSQL assignment performance

From

Andrey Zhidenkov

Date:

19 December 2017, 14:45:55

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.
--

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

Re: Notes about Pl/PgSQL assignment performance

From

Pavel Stehule

Date:

19 December 2017, 14:46:31

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.

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.
--

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

Re: Notes about Pl/PgSQL assignment performance

From

Pavel Stehule

Date:

19 December 2017, 14:49:06

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

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.
--

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

Re: Notes about Pl/PgSQL assignment performance

From

Pavel Stehule

Date:

19 December 2017, 14:54:24

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

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.
--

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

Re: Notes about Pl/PgSQL assignment performance

From

Alvaro Herrera

Date:

19 December 2017, 16:34:02

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

Re: Notes about Pl/PgSQL assignment performance

From

Andrey Zhidenkov

Date:

19 December 2017, 16:48:00

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

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

Re: Notes about Pl/PgSQL assignment performance

From

David Rowley

Date:

19 December 2017, 17:06:23

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

Re: Notes about Pl/PgSQL assignment performance

From

Alvaro Herrera

Date:

19 December 2017, 17:08:26

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