Re: SubtransControlLock and performance problems
Hi
>From: Alvaro Herrera
>Sent: Wednesday, February 19, 2020 4:23 PM
>To: Lars Aksel Opsahl
>Cc: Laurenz Albe ; Pavel Stehule
>; Tom Lane ;
>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.
>From the worst case to best case the time is reduced 15 times (from 300
>minutes to 20 minutes) when testing a small dataset for with 619230 surface
>(25909671 total line points) with the test below
>“resolve_overlap_gap_run('org_jm.jm_ukomm_flate','figurid','geo',4258,false,'test_topo_jm',0.01,31,3000);
> “
The reason for this seems to be related to the problems described by Laurenz
Albe related to how Postgres handles try and catch and sub transactions, which
I did not know about. If we don't have this is mind and we start to get
subtranslocks it seems to kill the performance in some cases.
In this test I ran with 31 parallel threads which is very high on a server with
only 32 cores and maybe not realistic. I just did this now see what happens
when I try to push a server to it’s limits and maximise the performance
increase. If I reduce this to 1 single thread, there should be now difference
and if run on 16 threads the difference would much much smaller.
I will now start to run on datasets which are 10 times bigger to check how
thing scales, but then run with around maybe 28 parallel jobs.
The two branches I have tested on now which should show the main difference are
here.
https://github.com/larsop/resolve-overlap-and-gap/tree/add_postgis_topology_using_TopoGeo_addLinestringwhich
is the faster one.
https://github.com/larsop/resolve-overlap-and-gap/tree/add_postgis_topology
which is slower one, but here I have now added a check on number of
subtranslocks before I kick of new jobs and that reduced time form 9 hours to
3 hours.
Thanks.
Lars
Re: tablespace to benefit from ssd ?
On Tue, Feb 18, 2020, 11:42 PM Nicolas PARIS wrote: > However the server has a large amount of ram > memory and I suspect all of those indexes are already cached in ram. > Then there may be no benefit to be had. > > I have read that tablespaces introduce overhead of maintenance and > introduce complication for replication. Yes, they are a nuisance for the humans who need to document, maintain, configure, etc. And they can induce administrators into making mistakes which can prolong outages or cause data loss. But on the other hand I have > this ssd disk ready for something. > That isn't a good reason. Unless your users are complaining, or you think they will be soon as things scale up, or you think they would be complaining of they weren't too apathetic to, then I would make no change that adds complexity just because the hardware exists. But I would turn on track_io_timing, and load pg_stat_statements, and probably set up auto_explain. That way when problems do arrive, you will be prepared to tackle them with empirical data. Cheers, Jeff
Can we have multiple tablespaces with in a database.
Hi Team, Can we have multiple tablespaces with in a database in postgres? Can we have a table on different tablespace same as Oracle? Thanks,
Re: Can we have multiple tablespaces with in a database.
On Fri, Feb 21, 2020 at 11:04 AM Daulat Ram wrote: > Hi Team, > > > > Can we have multiple tablespaces with in a database in postgres? > > Yes. > Can we have a table on different tablespace same as Oracle? > Yes -- specify TABLESPACE option while creating that table. Regards, Amul
Re: Can we have multiple tablespaces with in a database.
Please pick a single list to post to. Performance seems like the unnecessary one here. On Thu, Feb 20, 2020 at 10:34 PM Daulat Ram wrote: > Can we have multiple tablespaces with in a database in postgres? > I fell as if I'm missing something in your question given the presence of the "CREATE TABLESPACE" SQL command and the related non-command documentation covered here: https://www.postgresql.org/docs/12/manage-ag-tablespaces.html > Can we have a table on different tablespace same as Oracle? > There is no provision to assign two tablespaces to a single physical table. To the benefit of those who don't use the other product you may wish to say exactly what you want to do instead of comparing it to something that many people likely have never used. David J.
RE: Can we have multiple tablespaces with in a database.
Hi Amul , Please share the examples how we can create no. of tablespaces for a single database and how we can use them. As I know we can create database on tablespace 1. CREATE TABLESPACE conn_tbs OWNER enterprisedb LOCATION '/mnt/pgdatatest/test/pgdata/conn_tbs'; 2. Create database test tablespace ‘conn_tbs'; Can we have multiple tablespaces with in a database in postgres? Yes. From: amul sul Sent: Friday, February 21, 2020 11:16 AM To: Daulat Ram Cc: pgsql-gene...@lists.postgresql.org; pgsql-performance@lists.postgresql.org Subject: Re: Can we have multiple tablespaces with in a database. On Fri, Feb 21, 2020 at 11:04 AM Daulat Ram mailto:daulat@exponential.com>> wrote: Hi Team, Can we have multiple tablespaces with in a database in postgres? Yes. Can we have a table on different tablespace same as Oracle? Yes -- specify TABLESPACE option while creating that table. Regards, Amul
Re: Can we have multiple tablespaces with in a database.
On Fri, Feb 21, 2020 at 11:31 AM Daulat Ram wrote: > Hi Amul , > > Please share the examples how we can create no. of tablespaces for a > single database and how we can use them. > > As I know we can create database on tablespace > >1. CREATE TABLESPACE conn_tbs OWNER enterprisedb LOCATION >'/mnt/pgdatatest/test/pgdata/conn_tbs'; >2. Create database test tablespace ‘conn_tbs'; > > Maybe I have misunderstood your question; there is no option to specify more than one tablespace for the database, but you can place the objects of that database to different tablespaces (if options available for that object). E.g. you can place a table in than conn_tbs tablespace. If option is not specified then by default that object will be created in conn_tbs. Regards, Amul
RE: Can we have multiple tablespaces with in a database.
That will be great if you share any doc where it’s mentioned that we can’t use multiple tablespace for a single database. I have to assist my Dev team regarding tablespaces. Also , what are the differences between Oracle and Postgres Tablespacs? Thanks, From: amul sul Sent: Friday, February 21, 2020 11:48 AM To: Daulat Ram Cc: pgsql-gene...@lists.postgresql.org; pgsql-performance@lists.postgresql.org Subject: Re: Can we have multiple tablespaces with in a database. On Fri, Feb 21, 2020 at 11:31 AM Daulat Ram mailto:daulat@exponential.com>> wrote: Hi Amul , Please share the examples how we can create no. of tablespaces for a single database and how we can use them. As I know we can create database on tablespace 1. CREATE TABLESPACE conn_tbs OWNER enterprisedb LOCATION '/mnt/pgdatatest/test/pgdata/conn_tbs'; 2. Create database test tablespace ‘conn_tbs'; Maybe I have misunderstood your question; there is no option to specify more than one tablespace for the database, but you can place the objects of that database to different tablespaces (if options available for that object). E.g. you can place a table in than conn_tbs tablespace. If option is not specified then by default that object will be created in conn_tbs. Regards, Amul
Re: Can we have multiple tablespaces with in a database.
On Fri, Feb 21, 2020 at 11:53 AM Daulat Ram wrote: > That will be great if you share any doc where it’s mentioned that we > can’t use multiple tablespace for a single database. I have to assist my > Dev team regarding tablespaces. > > > > Also , what are the differences between Oracle and Postgres Tablespacs? > > > To be honest I don't know anything about Oracle. Regards, Amul
RE: Can we have multiple tablespaces with in a database.
You mean we can have only single default tablespace for a database but the database objects can be created on different-2 tablespaces? From: amul sul Sent: Friday, February 21, 2020 11:48 AM To: Daulat Ram Cc: pgsql-gene...@lists.postgresql.org; pgsql-performance@lists.postgresql.org Subject: Re: Can we have multiple tablespaces with in a database. On Fri, Feb 21, 2020 at 11:31 AM Daulat Ram mailto:daulat@exponential.com>> wrote: Hi Amul , Please share the examples how we can create no. of tablespaces for a single database and how we can use them. As I know we can create database on tablespace 1. CREATE TABLESPACE conn_tbs OWNER enterprisedb LOCATION '/mnt/pgdatatest/test/pgdata/conn_tbs'; 2. Create database test tablespace ‘conn_tbs'; Maybe I have misunderstood your question; there is no option to specify more than one tablespace for the database, but you can place the objects of that database to different tablespaces (if options available for that object). E.g. you can place a table in than conn_tbs tablespace. If option is not specified then by default that object will be created in conn_tbs. Regards, Amul
