We finally identified the cause, a pl/pgsql procedure proc1 (for 1…5000
loop call proc2()); proc2 (begin ..exception..end); at the same time, more
than 200 sessions coming in milliseconds and do same query during the “call
proc1 long running transaction”. The code change and cutdown the
e time , then quickly see
MultiXactoffset and SubtransSLRU increased very quickly.
Possible to increase Subtrans SLRU buffer size ?PL/PGSQL proc1-->
procedure2(updates table) it use substransation in procedure2 ,right?
Thanks,
James
-Original Message-----
From: James Pang (chaolpan)
Sent:
a subtransaction like pg_background ? for
JDBC driver option to simulate statement level rollback, could you share more
details ?
Thanks,
James
-Original Message-
From: Laurenz Albe
Sent: Thursday, February 1, 2024 8:42 PM
To: James Pang (chaolpan) ;
pgsql-performance@lists.postgresql.org
Hi,
We have a Postgresqlv14.8 server, client use Postgresql JDBC connections,
today, our server see a lot of "SubtransBuffer" and "SubtransSLRU" wait_event.
Could you help direct me what's the possible cause and how to resolve this
waits ?
Thanks,
James
transaction.
Am 17.11.23 um 09:10 schrieb James Pang (chaolpan):
>
> Hi,
>
> We found one simple query manually run very fast(finished in
> several milliseconds), but there are 2 sessions within long
> transaction to run same sql with same bind variables took tens of
Hi,
We found one simple query manually run very fast(finished in several
milliseconds), but there are 2 sessions within long transaction to run same sql
with same bind variables took tens of seconds.
Manually run this sql only show <100 shared_blks_hit and very small reads, but
for these 2
Hi,
PGv14.8, OS RHEL8, no SSL enabled in this database, we have a lot of client
sessions who check it's ssl state by query, all other sessions got done very
quickly, but only 1 session hang there in 100% cpu tens of hours, even
pg_terminate_backend does not make it stopped either. It
Hi,
PGv14.8, OS RHEL8, no SSL enabled in this database, we have a lot of client
sessions who check it's ssl state by query, all other sessions got done very
quickly, but only 1 session hang there in 100% cpu. It looks like abnormal.
select ssl from pg_stat_ssl where
11:01 PM
To: Tom Lane
Cc: James Pang (chaolpan) ;
pgsql-performance@lists.postgresql.org
Subject: Re: Postgresql equal join on function with columns not use index
út 13. 6. 2023 v 16:17 odesílatel Pavel Stehule
mailto:pavel.steh...@gmail.com>> napsal:
út 13. 6. 2023 v 15:50 odesílat
Hi,
When join two table on multiple columns equaljoin, rows estimation always
use selectivity = multiplied by distinct multiple individual columns, possible
to use extended n-distinct statistics on multiple columns?
PG v14.8-1, attached please check test case with details.
Thanks,
HONENUMBER,'[^0-9]',''),
Index on a.phonenumber got used.
Thanks,
James Pang
-Original Message-
From: Tom Lane
Sent: Monday, June 12, 2023 9:19 PM
To: James Pang (chaolpan)
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgresql equal join on function with columns not
Hi,
We migrate from Oracle to Postgresql14.8, one SQL has regression in
Postgres run in 5800 milliseconds in Postgresql v14.8, but the same SQL got
done in several hundred milliseconds in Oracle database.
With multiple table JOINs, if the join condition is
How does hash join estimation rows ? pg v14, it make wrong rows estimation
then leave nest loop lef join that make poor sql plan. A
-> Nested Loop Left Join
(cost=171112.69..475856.90 rows=1 width=521)
->
n 2 blocks; 200 free (0 chunks); 1848 used:
xxx
CachedPlanQuery: 2048 total in 2 blocks; 704 free (0 chunks); 1344 used
From: Pavel Stehule
Sent: Friday, June 2, 2023 12:57 PM
To: James Pang (chaolpan)
Cc: Laurenz Albe ;
pgsql-performance@lists.postgresql.org
Subject: Re:
James Pang (chaolpan) ; Pavel Stehule
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: thousands of CachedPlan entry per backend
On Thu, 2023-06-01 at 08:50 +0000, James Pang (chaolpan) wrote:
> we found thousands of cached plan , since JDBC driver only allow max
> 256 cached prepared
PM
To: Laurenz Albe
Cc: James Pang (chaolpan) ;
pgsql-performance@lists.postgresql.org
Subject: Re: thousands of CachedPlan entry per backend
Hi
čt 1. 6. 2023 v 8:53 odesílatel Laurenz Albe
mailto:laurenz.a...@cybertec.at>> napsal:
On Thu, 2023-06-01 at 03:36 +, James Pang (chaolpan)
Did you check pg_stat_statements ? looks like select better, but DML decreased,
so average tps looks similar .
-Original Message-
From: David Rowley
Sent: Thursday, December 15, 2022 6:42 PM
To: James Pang (chaolpan)
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: DML sql
Did you check pg_stat_statements ? looks like select some better , but DML
decreased.
-Original Message-
From: David Rowley
Sent: Thursday, December 15, 2022 6:42 PM
To: James Pang (chaolpan)
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: DML sql execution time slow down
Actually, with our application that’s JDBC clients instead of pgbench , we saw
similar DML exec_time increase too.
From: James Pang (chaolpan)
Sent: Thursday, December 15, 2022 4:45 PM
To: Samed YILDIRIM
Cc: pgsql-performance@lists.postgresql.org
Subject: RE: DML sql execution time slow down
, primary keys 640.91 s).
Thanks,
James
From: Samed YILDIRIM
Sent: Thursday, December 15, 2022 4:38 PM
To: James Pang (chaolpan)
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: DML sql execution time slow down PGv14 compared with PGv13
Hello James,
Could you please add configurations
Hi,
We had some load test ( DML inserts/deletes/updates) and found that PGV14
slow down 10-15% compared with PGV13. Same test server, same schema tables and
data. From pg_stat_statements, sql exec_time, we did found similar
mean_exec_time increased from 20%-30% with same SQL statements.
Hi,
We had some load test ( DML inserts/deletes/updates/ on tens of hash
partition tables) and found that PGV14 slow down 10-15% compared with PGV13.
Same test server, same schema tables and data. From pg_stat_statements, sql
exec_time, we did found similar mean_exec_time increased from
al Message-
From: David Rowley
Sent: Tuesday, December 6, 2022 1:59 PM
To: James Pang (chaolpan)
Cc: pgsql-performance@lists.postgresql.org; jamespang...@gmail.com
Subject: Re: wrong rows and cost estimation when generic plan
On Tue, 6 Dec 2022 at 18:28, James Pang (chaolpan) wrote:
>->
Hi,
It's a prepared sql statement on a non-partitioned table , 16millions tuples
and multiple indexes on this table. pk_x primary key (aid,bid,btype) all
3 cols are bigint datatype, there is another index idx_x(starttime,endtime)
, both cols are "timestamp(0) without time zone".
Hi,
It's a prepared sql statement on a non-partitioned table , 16millions tuples
and multiple indexes on this table. pk_x primary key (aid,bid,btype) all
3 cols are bigint datatype, there is another index idx_x(starttime,endtime)
, both cols are "timestamp(0) without time zone".
for
non-partitioned tables with same table attributes and data volume size.
-Original Message-
From: Justin Pryzby
Sent: Wednesday, September 7, 2022 12:15 AM
To: James Pang (chaolpan)
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgresql JDBC process consumes more memory than
-
From: Justin Pryzby
Sent: Wednesday, September 7, 2022 12:15 AM
To: James Pang (chaolpan)
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgresql JDBC process consumes more memory than psql client
On Tue, Sep 06, 2022 at 04:15:03AM +, James Pang (chaolpan) wrote:
> We mak
is about 128GB.
Thanks,
James
-Original Message-
From: James Pang (chaolpan)
Sent: Monday, September 5, 2022 8:52 PM
To: Justin Pryzby
Cc: pgsql-performance@lists.postgresql.org
Subject: RE: Postgresql JDBC process consumes more memory than psql client
PG V13, yes JDBC use prepared state
PG V13, yes JDBC use prepared statements , from psql use pruned ,but even all
partitions it NOT consumes too much memory. Any idea how to print SQL plan
from JDBC driver ?
-Original Message-
From: Justin Pryzby
Sent: Monday, September 5, 2022 8:47 PM
To: James Pang (chaolpan)
Cc
We run same update or delete SQL statement " DELETE FROM ... WHERE ... " the
table is a hash partition table (256 hash partitions). When run the sql from
Postgresql JDBC driver, it soon increased to 150MB memory (RES filed from top
command), but when run the same SQL from psql , it
e-
From: Joe Conway
Sent: Wednesday, July 27, 2022 11:02 PM
To: Tom Lane
Cc: James Pang (chaolpan) ; Jim Mlodgenski
; pgsql-performance@lists.postgresql.org
Subject: Re: alter table xxx set unlogged take long time
On 7/27/22 10:46, Tom Lane wrote:
> Joe Conway writes:
>> Then (c
….” With wal_level=minimal,
is it ok to make copy and create index without logging ?
James
From: Jim Mlodgenski
Sent: Tuesday, July 26, 2022 8:53 PM
To: James Pang (chaolpan)
Cc: Tom Lane ; pgsql-performance@lists.postgresql.org
Subject: Re: alter table xxx set unlogged take long time
On Tue, Jul 26
Without step 3 , copy data take long time. Use wal_level=minimal can help
make COPY load data without logging ?
Thanks,
James
-Original Message-
From: Tom Lane
Sent: Tuesday, July 26, 2022 8:43 PM
To: James Pang (chaolpan)
Cc: Jim Mlodgenski ; pgsql-performance
long time ,especially for large tables.
Thank,
James
From: Jim Mlodgenski
Sent: Tuesday, July 26, 2022 8:21 PM
To: James Pang (chaolpan)
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: alter table xxx set unlogged take long time
On Tue, Jul 26, 2022 at 4:53 AM James Pang (chaolpan
Hi ,
We have PG v13.6 in RHEL8.4, we try to set table unlogged before load data.
There are a lot of existing data in this table, when 'alter table xxx set
unlogged', we found it take long time and spend time on IO datafileread. Is it
expected?
Thanks,
James
ay to improve partition table update for large tables, and as first step
of tuning , we try to reduce partition count for these tables in PGV13.
James
-Original Message-
From: Tom Lane
Sent: Friday, July 1, 2022 9:18 PM
To: Justin Pryzby
Cc: James Pang (chaolpan) ;
pgsq
Pang (chaolpan)
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: partition pruning only works for select but update
"James Pang (chaolpan)" writes:
> But when
> Explain update table set .. where partitionkey between to_timestamp() and
> to_timestamp();
&g
Pang (chaolpan)
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: partition pruning only works for select but update
"James Pang (chaolpan)" writes:
> But when
> Explain update table set .. where partitionkey between to_timestamp() and
> to_timestamp();
> It still
Hi,
We have a table have range partition (about 5K partitions) , when
Explain select count(*) from table where partitionkey between to_timestamp()
and to_timestamp();
It show
Aggregate (cost=15594.72..15594.73 rows=1 width=8)
-> Append (cost=0.15..15582.00 rows=5088 width=0)
constraints.
The questions is instead of drop index and create index, we check update
pg_index set indisready=false and reindex again after load.
From: Jeff Janes
Sent: Sunday, June 19, 2022 4:01 AM
To: James Pang (chaolpan)
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: reindex option
dex the one index done
in serial instead of parallel ?
Compared with “set max_maintain_parallel_workers, and run CREATE INDEX …” ,
which is faster ?
Thanks,
From: Vitalii Tymchyshyn
Sent: Saturday, June 18, 2022 11:49 AM
To: James Pang (chaolpan)
Cc: pgsql-performance@lists.postgresql.org
Subj
Hi ,
We plan to migrate large database from Oracle to Postgres(version 13.6, OS
Redhat8 Enterprise), we are checking options to make data load in Postgres
fast. Data volume is about several TB, thousands of indexes, many large table
with partitions. We want to make data load running fast
to support security
compliance requirements.
systemctl stop fapolicyd , after that, everything go back to be normal soon.
Regards,
James
-Original Message-----
From: James Pang (chaolpan)
Sent: Monday, May 30, 2022 11:00 AM
To: Tom Lane
Cc: Justin Pryzby ; pgsql-performance@lists.post
Maybe any bugs from these extensions ? I can try that removing all
extensions, but we need these extensions.
Thanks,
James
-Original Message-
From: Tom Lane
Sent: Monday, May 30, 2022 10:21 AM
To: James Pang (chaolpan)
Cc: Justin Pryzby ; pgsql-performance@lists.postgresql.org
192.168.205.133(48754) SELECT
1070260 -Z ?00:05:02 [postmaster]
...
Strace / gdb will hang there too for trace a process.
Regards,
James
-Original Message-
From: Justin Pryzby
Sent: Monday, May 30, 2022 10:20 AM
To: James Pang (chaolpan)
Cc: pgsql
-
From: Justin Pryzby
Sent: Sunday, May 29, 2022 11:02 PM
To: James Pang (chaolpan)
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: postgres backend process hang on " D " state
On Sun, May 29, 2022 at 01:20:12PM +0000, James Pang (chaolpan) wrote:
>We have a per
Hi,
We have a performance test on Postgresql 13.4 on RHEL8.4 , just after
connection storm in ( 952 new connections coming in 1 minute), a lot of
backends start on " D " state, and when more sessions got disconnected, they
do not exit successfully, instead became "defunct". No errors
47 matches
Mail list logo
