From: Laurenz Albe
Sent: Tuesday, February 20, 2024 8:29 AM
>Re: "not related" code blocks for removal of dead rows when using vacuum and
>this kills the performance
>Laurenz Albe
>Lars Aksel Opsahl;
>pgsql-performance@lists.postgresql.org
>On Tue, 2024-02-2
From: Lars Aksel Opsahl
>From: Laurenz Albe
>>
>>It is not entirely clear what you are doing, but it seems like you are holding
>>a database transaction open, and yes, then it is expected behavior that
>>VACUUM cannot clean up dead rows in the table.
>&g
From: Laurenz Albe
>
>It is not entirely clear what you are doing, but it seems like you are holding
>a database transaction open, and yes, then it is expected behavior that
>VACUUM cannot clean up dead rows in the table.
>
>Make sure that your database transactions are short.
>Don't use table
Hi
We have a master code block which starts small, tiny operations that create a
table and inserts data into that table in many threads.
Nothing is done the master code, we follow an Orchestration pattern , where
master just sends a message about what to do and that is done in other database
>From: James Pang (chaolpan) Sent: Friday, February 2, 2024
>7:47 AMTo: Laurenz Albe ;
>pgsql-performance@lists.postgresql.org
>Subject: RE: huge SubtransSLRU and
>SubtransBuffer wait_event
>
> Our case is 1) we use PL/PGSQL procedure1-->procedure2 (update table
> ;commit); 2)
From: Julien Rouhaud
Sent: Friday, October 21, 2022 12:41 PM
To: Lars Aksel Opsahl
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: ogr2ogr slow sql when checking system tables for column info and
so on.
>From: Julien Rouhaud Sent: Friday, October
From: Julien Rouhaud
Sent: Friday, October 21, 2022 11:48 AM
To: Lars Aksel Opsahl
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: ogr2ogr slow sql when checking system tables for column info and
so on.
>From: Julien Rouhaud
>Sent: Friday, O
Hei
The main problem is that for instance ogr2ogr is using more time to get system
info about tables than doing the actual job.
The time pick up postgresql meta info takes between 30 and 60 seconds and
sometimes hours if we have not done vacuum analyze recenlty.
Then actual spatial jobs takes
From: Justin Pryzby
Sent: Wednesday, March 23, 2022 2:19 PM
>On Wed, Mar 23, 2022 at 09:44:09AM +0000, Lars Aksel Opsahl wrote:
>> Why is temp tables with no indexes much faster system tables with indexes ?
>
>I think the "temp table" w
>From: Justin Pryzby Sent: Wednesday, March 23, 2022 2:19
>PMTo: Lars Aksel Opsahl Cc:
>pgsql-performance@lists.postgresql.org
>Subject: Re: Using system tables
>directly takes many hours, using temp tables with no indexes takes
>Hi
>
>We are running
>postgres server 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
>POSTGIS="3.1.1 aaf4c79" [EXTENSION] PGSQL="120" GEOS="3.9.0-CAPI-1.16.2"
>SFCGAL="1.3.7" PROJ="7.2.1" GDAL="GDAL 3.2.1, released 2020/12/29"
>LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"
Hi
We are running
postgres server 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
POSTGIS="3.1.1 aaf4c79" [EXTENSION] PGSQL="120" GEOS="3.9.0-CAPI-1.16.2"
SFCGAL="1.3.7" PROJ="7.2.1" GDAL="GDAL 3.2.1, released 2020/12/29"
LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"
>From: Tom Lane
>Sent: Monday, February 7, 2022 8:02 PM
>To: Lars Aksel Opsahl
>Cc: Vijaykumar Jain ; Pgsql Performance
>
>Subject: Re: slow "select count(*) from information_schema.tables;" in some
>cases
>
>Lars Aksel Opsahl writes
>Vijaykumar Jain
>Mon 2/7/2022 6:49 PM
>
>On Mon, Feb 7, 2022, 10:26 PM Lars Aksel Opsahl wrote:
>Hi
>
Hi
>Can you share the output of the below query?
>
>From the past threads I have learnt that too many templates objects may add to
>bloat of sy
>>
>> Here is a slow one:
>> https://explain.depesz.com/s/tUt5
>>
>> and here is fast one :
>> https://explain.depesz.com/s/yYG4
>
>The only difference is that this is sometimes many times slower.
>
> Finalize Aggregate (cost=42021.15..42021.16 rows=1 width=8) (actual
>
Hi
Sometimes simple sql's like this takes a very long time "select count(*) from
information_schema.tables;"
Other sql's not including system tables may work ok but login also takes a very
long time.
The CPU load on the server is around 25%. There is no iowait.
This happens typically when
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
Hi
>From: Laurenz Albe
>Sent: Tuesday, February 18, 2020 6:27 PM
>ATo: Pavel Stehule ; Tom Lane
>Cc: Lars Aksel Opsahl ;
>pgsql-performance@lists.postgresql.org
>Subject: Re: SubtransControlLock and performance problems
>
>From: Laurenz Albe
>Sent: Monday, February 17, 2020 10:53 AM
>To: Lars Aksel Opsahl ;
>pgsql-performance@lists.postgresql.org
>Subject: Re: SubtransControlLock and performance problems
>
>Lars Aksel Opsahl wrote:
>> What happens is that after some minutes t
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
> From: Joe Conway
> Sent: Sunday, December 8, 2019 9:04 PM
> To: Lars Aksel Opsahl; Laurenz Albe; pgsql-performance@lists.postgresql.org
> Subject: Re: How to run in parallel in Postgres, EXECUTE_PARALLEL
>
> On 12/8/19 1:14 PM, Lars Aksel Opsahl wrote:
> >
>You cannot run several queries in parallel in a PostgreSQL function.
>
>You may want to have a look at PL/Proxy which might be used for things like
>that.
>
>Yours,
>Laurenz Albe
>--
>Cybertec | https://www.cybertec-postgresql.com
Hi Laurenz
The code below takes 3, seconds
DO
$body$
DECLARE
> From: Justin Pryzby
> Sent: Saturday, December 7, 2019 2:25 AM
> To: Lars Aksel Opsahl
> Cc: pgsql-performance@lists.postgresql.org
>
> Subject: Re: How to run in parallel in Postgres
>
> On Thu, Dec 05, 2019 at 12:10:42PM +, Lars Aksel Opsahl wrote
>From: Ondrej Ivanič
>Sent: Saturday, December 7, 2019 2:23 AM
>Cc: pgsql-performance@lists.postgresql.org
>
>Subject: Re: How to run in parallel in Postgres
>
>Hi Lars,
>
>I have two suggestions:
>
>- `xargs` almost always present and it can run in parallel (-P) but script
>From: Laurenz Albe
>Sent: Thursday, December 5, 2019 5:42 PM
>To: Lars Aksel Opsahl ;
>pgsql-performance@lists.postgresql.org
>Subject: Re: How to run in parallel in Postgres
>
>On Thu, 2019-12-05 at 12:10 +0000, Lars Aksel Opsahl wrote:
>> have a function tha
Hi
I have a function that prepares data, so the big job can be run it in parallel.
Today I have solved this by using "Gnu parallel" like this.
psql testdb -c"\! psql -t -q -o /tmp/run_cmd.sql testdb -c\"SELECT
Hei
Sorry it's was a zero to much, it should 30-40 million weather observations pr
second.
Lars
Fra: Lars Aksel Opsahl <lars.ops...@nibio.no>
Sendt: 28. januar 2018 12:57
Til: Mariel Cherkassky; PostgreSQL mailing lists
Emne: SV: copy csv into parti
Hi
We had the same problems with performance when testing with more than 100
billion weather observations. We now have a solution where we can push between
300 and 400 million weather observations pr. Second into the database.
We download date from NetCDF files. The date are joined based on
28 matches
Mail list logo
