Horton

Thread

  1. Notes about Pl/PgSQL assignment performance

    Andrey Zhidenkov <pensnarik@gmail.com> — 2017-12-19T11:28:00Z

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

  2. Re: Notes about Pl/PgSQL assignment performance

    Pavel Stehule <pavel.stehule@gmail.com> — 2017-12-19T11:36:25Z

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

  3. Re: Notes about Pl/PgSQL assignment performance

    Hannu Krosing <hkrosing@gmail.com> — 2017-12-19T11:40:31Z

    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>
>
>     Any help would be greatly appreciated.
>     -- 
>
>

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

  4. Re: Notes about Pl/PgSQL assignment performance

    Andrey Zhidenkov <pensnarik@gmail.com> — 2017-12-19T11:45:55Z

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


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

  5. Re: Notes about Pl/PgSQL assignment performance

    Pavel Stehule <pavel.stehule@gmail.com> — 2017-12-19T11:46:31Z

    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 Availabilityhttps://2ndquadrant.com/
>
>

  6. Re: Notes about Pl/PgSQL assignment performance

    Pavel Stehule <pavel.stehule@gmail.com> — 2017-12-19T11:49:06Z

    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-pe
>>> rformance-12-19
>>>
>>> Any help would be greatly appreciated.
>>> --
>>>
>>>
>>
>
>
> --
> С уважением, Андрей Жиденков.
>

  7. Re: Notes about Pl/PgSQL assignment performance

    Pavel Stehule <pavel.stehule@gmail.com> — 2017-12-19T11:54:24Z

    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-pe
>>> rformance-12-19
>>>
>>> Any help would be greatly appreciated.
>>> --
>>>
>>>
>>
>> --
>> Hannu Krosing
>> PostgreSQL Consultant
>> Performance, Scalability and High Availabilityhttps://2ndquadrant.com/
>>
>>
>

  8. Re: Notes about Pl/PgSQL assignment performance

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2017-12-19T13:34:02Z

    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

  9. Re: Notes about Pl/PgSQL assignment performance

    Andrey Zhidenkov <pensnarik@gmail.com> — 2017-12-19T13:48:00Z

    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
>



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

  10. Re: Notes about Pl/PgSQL assignment performance

    David Rowley <david.rowley@2ndquadrant.com> — 2017-12-19T14:06:23Z

    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

  11. Re: Notes about Pl/PgSQL assignment performance

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2017-12-19T14:08:26Z

    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