pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSubtransControlLock and performance problems
13+ messages / 5 participants
[nested] [flat]
* SubtransControlLock and performance problems
@ 2020-02-16 17:15 Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
0 siblings, 2 replies; 13+ messages in thread
From: Lars Aksel Opsahl @ 2020-02-16 17:15 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi
On a server with 32 cores and 250 GB memory, with CentOS 7 and kernel 4.4.214-1.el7.elrepo.x86_64, I try to run 30 parallel threads using dblink. (https://github.com/larsop/postgres_execute_parallel) . I have tried to disconnect and reconnect in the dblink code and that did not help.
If I reduce the number of threads I get less CPU usage and much less SubtransControlLock.
Each thread are inserting many lines into a Postgis Topology layer. I have a lot of try catch in this code to avoid missing lines (https://github.com/larsop/resolve-overlap-and-gap/tree/add_postgis_topology_using_proc) .
What happens is that after some minutes the CPU can fall to maybe 20% usage and most of the threads are blocked by SubtransControlLock, and when the number SubtransControlLock goes down the CPU load increases again. The jobs usually goes through without any errors, but it takes to long time because of the SubtransControlLock blocks.
There is no iowait on the server and there is plenty of free memory on the server. There seems to be no locks on the common tables.
“SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;” is always empty.
I am using a lot temp tables and unlogged tables.
To reduce the number locks I have a simple check before I kick off new jobs like the one below, but that did not help very much either. Yes it does a lot waiting, but SubtransControlLock kick in when all threads are up running again.
LOOP
EXECUTE Format('SELECT count(*) from pg_stat_activity where wait_event = %L and query like %L',
'SubtransControlLock',
'CALL resolve_overlap_gap_save_single_cells%') into subtransControlLock;
EXIT WHEN subtransControlLock = 0;
subtransControlLock_count := subtransControlLock_count + 1;
PERFORM pg_sleep(subtransControlLock*subtransControlLock_count*0.1);
END LOOP;
I have tested with postgres 11, postgres 12, postgis 2.5 , postgis 3.0 and it seems to behave save.
I have also tried to recompile postgres with the setting below and that did not solve the problem either.
/* Number of SLRU buffers to use for subtrans */
#define NUM_SUBTRANS_BUFFERS 2048
I have tested different values for memory and other settings nothing seems to matter. Here are the settings right now.
maintenance_work_mem = 8GB
max_connections = 600
work_mem = 500MB
temp_buffers = 100MB
shared_buffers = 64GB
effective_cache_size = 124GB
wal_buffers = 640MB
seq_page_cost = 2.0
random_page_cost = 2.0
checkpoint_flush_after = 2MB
checkpoint_completion_target = 0.9
default_statistics_target = 1000
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
effective_io_concurrency = 500 # 1-1000; 0 disables prefetching
# test to avoid SubtransControlLock
#bgwriter_lru_maxpages = 100000
#bgwriter_lru_maxpages=0
#bgwriter_delay = 20ms
synchronous_commit = off
Any idea about how to solve this ?
Lars
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SubtransControlLock and performance problems
@ 2020-02-17 09:53 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
1 sibling, 1 reply; 13+ messages in thread
From: Laurenz Albe @ 2020-02-17 09:53 UTC (permalink / raw)
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Lars Aksel Opsahl wrote:
> What happens is that after some minutes the CPU can fall to maybe 20% usage and most of
> the threads are blocked by SubtransControlLock, and when the number SubtransControlLock
> goes down the CPU load increases again. The jobs usually goes through without any errors,
> but it takes to long time because of the SubtransControlLock blocks.
That's typically a sign that you are using more than 64 subtransactions per transaction.
That could either be SAVEPOINT SQL statements or PL/pgSQL code with blocks
containing the EXCEPTION clause.
The data structure in shared memory that holds information for each session
can cache 64 subtransactions, beyond that it has to access "pg_subtrans" to get
the required information, which leads to contention.
Often the problem is caused by a misguided attempt to wrape every single
statement in a subtransaction to emulate the behavior of other database
systems, for example with the "autosave = always" option of the JDBC driver.
The solution is to use fewer subtransactions per transaction.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SubtransControlLock and performance problems
@ 2020-02-17 15:03 Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 13+ messages in thread
From: Lars Aksel Opsahl @ 2020-02-17 15:03 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
>From: Laurenz Albe <laurenz.albe@cybertec.at>
>Sent: Monday, February 17, 2020 10:53 AM
>To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
>Subject: Re: SubtransControlLock and performance problems
>
>Lars Aksel Opsahl wrote:
>> What happens is that after some minutes the CPU can fall to maybe 20% usage and most of
>> the threads are blocked by SubtransControlLock, and when the number SubtransControlLock
>> goes down the CPU load increases again. The jobs usually goes through without any errors,
>> but it takes to long time because of the SubtransControlLock blocks.
>
>That's typically a sign that you are using more than 64 subtransactions per transaction.
>
>That could either be SAVEPOINT SQL statements or PL/pgSQL code with blocks
>containing the EXCEPTION clause.
>
>The data structure in shared memory that holds information for each session
>can cache 64 subtransactions, beyond that it has to access "pg_subtrans" to get
> the required information, which leads to contention.
>
> Often the problem is caused by a misguided attempt to wrape every single
> statement in a subtransaction to emulate the behavior of other database
> systems, for example with the "autosave = always" option of the JDBC driver.
>
> The solution is to use fewer subtransactions per transaction.
>
Hi
I have tested in branch ( https://github.com/larsop/resolve-overlap-and-gap/tree/add_postgis_topology_using_func) where I use only have functions and no procedures and I still have the same problem with subtransaction locks.
Can I based on this assume that the problem is only related to exceptions ?
Does this mean that if have 32 threads running in parallel and I get 2 exceptions in each thread I have reached a state where I will get contention ?
Is it any way increase from 64 to a much higher level, when compiling the code ?
Basically what I do here is that I catch exceptions when get them and tries to solve the problem in a alternative way.
Thanks a lot.
Lars
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SubtransControlLock and performance problems
@ 2020-02-17 16:35 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
0 siblings, 1 reply; 13+ messages in thread
From: Laurenz Albe @ 2020-02-17 16:35 UTC (permalink / raw)
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Mon, 2020-02-17 at 15:03 +0000, Lars Aksel Opsahl wrote:
> I have tested in branch ( https://github.com/larsop/resolve-overlap-and-gap/tree/add_postgis_topology_using_func)
> where I use only have functions and no procedures and I still have the same problem with subtransaction locks.
>
> Can I based on this assume that the problem is only related to exceptions ?
No, it is related to BEGIN ... EXCEPTION ... END blocks, no matter if
an exception is thrown or not.
As soon as execution enters such a block, a subtransaction is started.
> Does this mean that if have 32 threads running in parallel and I get 2 exceptions in each thread I have reached a state where I will get contention ?
No, it means that if you enter a block with an EXCEPTION clause more
than 64 times in a single transaction, performance will drop.
> Is it any way increase from 64 to a much higher level, when compiling the code ?
Yes, you can increase PGPROC_MAX_CACHED_SUBXIDS in src/include/storage/proc.h
> Basically what I do here is that I catch exceptions when get them and tries to solve the problem in a alternative way.
Either use shorter transactions, or start fewer subtransactions.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SubtransControlLock and performance problems
@ 2020-02-17 18:01 Pavel Stehule <pavel.stehule@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 13+ messages in thread
From: Pavel Stehule @ 2020-02-17 18:01 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi
po 17. 2. 2020 v 17:36 odesílatel Laurenz Albe <laurenz.albe@cybertec.at>
napsal:
> On Mon, 2020-02-17 at 15:03 +0000, Lars Aksel Opsahl wrote:
> > I have tested in branch (
> https://github.com/larsop/resolve-overlap-and-gap/tree/add_postgis_topology_using_func
> )
> > where I use only have functions and no procedures and I still have the
> same problem with subtransaction locks.
> >
> > Can I based on this assume that the problem is only related to
> exceptions ?
>
> No, it is related to BEGIN ... EXCEPTION ... END blocks, no matter if
> an exception is thrown or not.
>
> As soon as execution enters such a block, a subtransaction is started.
>
> > Does this mean that if have 32 threads running in parallel and I get 2
> exceptions in each thread I have reached a state where I will get
> contention ?
>
> No, it means that if you enter a block with an EXCEPTION clause more
> than 64 times in a single transaction, performance will drop.
>
> > Is it any way increase from 64 to a much higher level, when compiling
> the code ?
>
> Yes, you can increase PGPROC_MAX_CACHED_SUBXIDS in
> src/include/storage/proc.h
>
> > Basically what I do here is that I catch exceptions when get them and
> tries to solve the problem in a alternative way.
>
> Either use shorter transactions, or start fewer subtransactions.
>
> Yours,
> Laurenz Albe
>
it is interesting topic, but I don't see it in my example
CREATE OR REPLACE FUNCTION public.fx(integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
begin
for i in 1..$1 loop
begin
--raise notice 'xx';
exception when others then
raise notice 'yyy';
end;
end loop;
end;
$function$
the execution time is without performance drops.
Is there some prerequisite to see performance problems?
Pavel
--
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SubtransControlLock and performance problems
@ 2020-02-17 18:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2020-02-17 18:23 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Pavel Stehule <pavel.stehule@gmail.com> writes:
> po 17. 2. 2020 v 17:36 odesílatel Laurenz Albe <laurenz.albe@cybertec.at>
> napsal:
>> Either use shorter transactions, or start fewer subtransactions.
> it is interesting topic, but I don't see it in my example
> CREATE OR REPLACE FUNCTION public.fx(integer)
> RETURNS void
> LANGUAGE plpgsql
> AS $function$
> begin
> for i in 1..$1 loop
> begin
> --raise notice 'xx';
> exception when others then
> raise notice 'yyy';
> end;
> end loop;
> end;
> $function$
This example doesn't create or modify any table rows within the
subtransactions, so (I think) we won't assign XIDs to them.
It's consumption of subtransaction XIDs that causes the issue.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SubtransControlLock and performance problems
@ 2020-02-17 18:41 Pavel Stehule <pavel.stehule@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Pavel Stehule @ 2020-02-17 18:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
po 17. 2. 2020 v 19:23 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
> > po 17. 2. 2020 v 17:36 odesílatel Laurenz Albe <laurenz.albe@cybertec.at
> >
> > napsal:
> >> Either use shorter transactions, or start fewer subtransactions.
>
> > it is interesting topic, but I don't see it in my example
>
> > CREATE OR REPLACE FUNCTION public.fx(integer)
> > RETURNS void
> > LANGUAGE plpgsql
> > AS $function$
> > begin
> > for i in 1..$1 loop
> > begin
> > --raise notice 'xx';
> > exception when others then
> > raise notice 'yyy';
> > end;
> > end loop;
> > end;
> > $function$
>
> This example doesn't create or modify any table rows within the
> subtransactions, so (I think) we won't assign XIDs to them.
> It's consumption of subtransaction XIDs that causes the issue.
>
I tested
CREATE OR REPLACE FUNCTION public.fx(integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
begin
for i in 1..$1 loop
begin
insert into foo values(i);
exception when others then
raise notice 'yyy';
end;
end loop;
end;
$function$
and I don't see any significant difference between numbers less than 64 and
higher
> regards, tom lane
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SubtransControlLock and performance problems
@ 2020-02-17 19:10 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
1 sibling, 0 replies; 13+ messages in thread
From: Alvaro Herrera @ 2020-02-17 19:10 UTC (permalink / raw)
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On 2020-Feb-16, Lars Aksel Opsahl wrote:
> On a server with 32 cores and 250 GB memory, with CentOS 7 and kernel
> 4.4.214-1.el7.elrepo.x86_64, I try to run 30 parallel threads using
> dblink. (https://github.com/larsop/postgres_execute_parallel) . I have
> tried to disconnect and reconnect in the dblink code and that did not
> help.
I think one issue is that pg_clog has 128 buffers (per commit
5364b357fb1) while subtrans only has 32. It might be productive to
raise the number of subtrans buffers (see #define NUM_SUBTRANS_BUFFERS
in src/include/access/subtrans.h; requires a recompile.) Considering
that each subtrans entry is 16 times larger than clog (2 bits vs. 4
bytes), you'd require 2048 subtrans buffers to cover the same XID range
without I/O if my math is right. That's only 16 MB ... though slru.c
code might not be prepared to deal with that many buffers. Worth some
experimentation, I guess.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SubtransControlLock and performance problems
@ 2020-02-18 17:27 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Laurenz Albe @ 2020-02-18 17:27 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Mon, 2020-02-17 at 19:41 +0100, Pavel Stehule wrote:
> I tested
>
> CREATE OR REPLACE FUNCTION public.fx(integer)
> RETURNS void
> LANGUAGE plpgsql
> AS $function$
> begin
> for i in 1..$1 loop
> begin
> insert into foo values(i);
> exception when others then
> raise notice 'yyy';
> end;
> end loop;
> end;
> $function$
>
> and I don't see any significant difference between numbers less than 64 and higher
Did you have several concurrent sessions accessing the rows that others created?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SubtransControlLock and performance problems
@ 2020-02-18 23:46 Pavel Stehule <pavel.stehule@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 13+ messages in thread
From: Pavel Stehule @ 2020-02-18 23:46 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
út 18. 2. 2020 v 18:27 odesílatel Laurenz Albe <laurenz.albe@cybertec.at>
napsal:
> On Mon, 2020-02-17 at 19:41 +0100, Pavel Stehule wrote:
> > I tested
> >
> > CREATE OR REPLACE FUNCTION public.fx(integer)
> > RETURNS void
> > LANGUAGE plpgsql
> > AS $function$
> > begin
> > for i in 1..$1 loop
> > begin
> > insert into foo values(i);
> > exception when others then
> > raise notice 'yyy';
> > end;
> > end loop;
> > end;
> > $function$
> >
> > and I don't see any significant difference between numbers less than 64
> and higher
>
> Did you have several concurrent sessions accessing the rows that others
> created?
>
no, I didn't
Pavel
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SubtransControlLock and performance problems
@ 2020-02-19 10:49 Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 13+ messages in thread
From: Lars Aksel Opsahl @ 2020-02-19 10:49 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; Pavel Stehule <pavel.stehule@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi
________________________________
>From: Laurenz Albe <laurenz.albe@cybertec.at>
>Sent: Tuesday, February 18, 2020 6:27 PM
>ATo: Pavel Stehule <pavel.stehule@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>
>Cc: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
>Subject: Re: SubtransControlLock and performance problems
>
>Did you have several concurrent sessions accessing the rows that others created?
Hi
Thanks every body, I have done more testing here..
- I was not able fix this problem by increasing this values
src/include/access/subtrans.h, define NUM_SUBTRANS_BUFFERS 8196
src/include/storage/proc.h , PGPROC_MAX_CACHED_SUBXIDS 128
If tried to increase PGPROC_MAX_CACHED_SUBXIDS more than 128 Postgres core dumped. I tried to increase shared memory and other settings but I was not able to get it statble.
With the values above I did see same performance problems and we ended with a lot of subtransControlLock.
So I started to change the code based on your feedbacks.
- What seems to work very good in combination with a catch exception and retry pattern is to insert the data in to separate table for each job. (I the current testcase we reduced the number of subtransControlLock from many hundreds to almost none.)
Then I later can pick up these results from different the tables with another job that inserts data in to common data structure and in this job I don’t have any catch retry pattern. Then I was able to handle 534 of 592 jobs/cells with out any subtransControlLock at all.
But 58 jobs did not finish so for these I had to use a catch retry pattern and then then I got the subtransControlLock problems, but thats for a limited sets of the data.
Between each job I also close open the connections I dblink.
In this test I used dataset with data set 619230 surface with total of 25909671 and it did finish in 24:42.363, with NUM_SUBTRANS_BUFFERS 8196 and PGPROC_MAX_CACHED_SUBXIDS 128. When I changed this back to the original values the same test took 23:54.973.
For me it’s seems like in Postgres it’s better to have functions that returns an error state together with the result and not throws an exceptions, because exceptions leads performance degeneration when working with big datasets.
Thanks
Lars
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SubtransControlLock and performance problems
@ 2020-02-19 15:23 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
0 siblings, 1 reply; 13+ messages in thread
From: Alvaro Herrera @ 2020-02-19 15:23 UTC (permalink / raw)
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pavel Stehule <pavel.stehule@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On 2020-Feb-19, Lars Aksel Opsahl wrote:
> With the values above I did see same performance problems and we ended
> with a lot of subtransControlLock.
>
> So I started to change the code based on your feedbacks.
>
> - What seems to work very good in combination with a catch exception
> and retry pattern is to insert the data in to separate table for each
> job. (I the current testcase we reduced the number of
> subtransControlLock from many hundreds to almost none.)
I think at this point your only recourse is to start taking profiles to
see where the time is going. Without that, you're just flying blind and
whatever you do will not necessarily move your needle at all.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: SubtransControlLock and performance problems
@ 2020-02-20 09:20 Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 0 replies; 13+ messages in thread
From: Lars Aksel Opsahl @ 2020-02-20 09:20 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pavel Stehule <pavel.stehule@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi
________________________________
>From: Alvaro Herrera <alvherre@2ndquadrant.com>
>Sent: Wednesday, February 19, 2020 4:23 PM
>To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
>Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pavel Stehule <pavel.stehule@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
>Subject: Re: SubtransControlLock and performance problems
>
>On 2020-Feb-19, Lars Aksel Opsahl wrote:
>
>> With the values above I did see same performance problems and we ended
>> with a lot of subtransControlLock.
>>
>> So I started to change the code based on your feedbacks.
>>
>> - What seems to work very good in combination with a catch exception
>> and retry pattern is to insert the data in to separate table for each
>> job. (I the current testcase we reduced the number of
>> subtransControlLock from many hundreds to almost none.)
>
>I think at this point your only recourse is to start taking profiles to
>see where the time is going. Without that, you're just flying blind and
>whatever you do will not necessarily move your needle at all.
Hi
Yes I totally agree with you and yes I have tried to do some profiling and testing while developing.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2020-02-20 09:20 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-16 17:15 SubtransControlLock and performance problems Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
2020-02-17 09:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-02-17 15:03 ` Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
2020-02-17 16:35 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-02-17 18:01 ` Pavel Stehule <pavel.stehule@gmail.com>
2020-02-17 18:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-02-17 18:41 ` Pavel Stehule <pavel.stehule@gmail.com>
2020-02-18 17:27 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-02-18 23:46 ` Pavel Stehule <pavel.stehule@gmail.com>
2020-02-19 10:49 ` Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
2020-02-19 15:23 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-02-20 09:20 ` Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
2020-02-17 19:10 ` Alvaro Herrera <alvherre@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox