Re: huge shared_blocks_hit one select but manually run very fast

values ? How big is the table > index and shared buffers ? An example would really help > > On Sat, 21 Dec 2024, 11:51 James Pang, wrote: > >> Hi, >>we have a simple select from table where ... (that mache the >> index) , table has 80million rows. when

huge shared_blocks_hit one select but manually run very fast

Hi, we have a simple select from table where ... (that mache the index) , table has 80million rows. when many application sessions run the query and at the same time some other sessions doing insert into ... this table. from pg_stat_statements, shared_blks_hit show 31652 / per call. we s

could not send data to client: Connection reset by peer

experts, Postgresql v14.8, client app use Postgresql JDBC, it start a single query that return 470k rows totally in one time, but sometimes, database server side got error "could not send data to client: Connection reset by peer". any idea why it faced this error? Thanks, James

Re: proposal: schema variables

sorry, I sent to wrong email. please ignore. James Pang 於 2024年10月25日週五 下午3:58寫道： > Yes, a lot new coming sessions running some "select" and sql > parsing/planning there, including some partition tables in the query. but > there were other sessions DML on these tables a

Re: proposal: schema variables

Yes, a lot new coming sessions running some "select" and sql parsing/planning there, including some partition tables in the query. but there were other sessions DML on these tables at the same time too Laurenz Albe 於 2024年7月19日週五 下午7:41寫道： > On Sat, 2021-04-10 at 08:58 +0200, Pavel Stehule wrote

lwlock:LockManager wait_events

experts, we faced into a lot of lwlock:LockManager wait-events , all of these queries are "select ..." , there are other several session are doing DML, insert/update/delete on same table. Did these DML transactions holding "transactionid" and "tuple" lock blocking "select" on lwlock:LockMan

Re: many backends hang on MultiXactOffsetSLRU

tiXact may contain an update or delete Xid. ? in this server, we see thousands of session hang on ‘MultixactOffsetSLRU" but they are in " bind " stage instead of "execute", why a backend in "bind" need to access Multixact? Thanks, James Alvaro Herrera 於 2024年9月

Re: many backends hang on MultiXactOffsetSLRU

y one session doing DML with savepoints , and all other queries sessions can see this kind of "MultiXact" waiting ,right? James Pang 於 2024年9月10日週二 下午4:26寫道： > There is no foreign keys, but there are several sessions who did > transactions to tables with savepoints, one savep

many backends hang on MultiXactOffsetSLRU

Hi experts, we have a Postgresql v14.8 database, almost thousands of backends hang on MultiXactOffsetSLRU at the same time, all of these sessions running same query "SELECT ", from OS and postgresql slow log, we found all of these query on "BIND" stage. LOG: duration: 36631.688 ms bind S

Re: logical replication out of memory

From:* khan Affan > *Sent:* Wednesday, July 31, 2024 1:42 PM > *To:* James Pang > *Cc:* pgsql-performance@lists.postgresql.org > *Subject:* Re: logical replication out of memory > > > > Hi Pang > > The text column is exceptionally large, Your server must be out of memory,

Re: logical replication out of memory

lback, associated LSN 3D/318438E0 2024-07-31 00:01:02.795 UTC:xxx..xxx.xxx(33068):repl13801@pgpodb:[3603770]:[13-1]:pgreps_13801STATEMENT: START_REPLICATION SLOT "pgreps_13801" LOGICAL 3C/F24C74D0 (proto_version '1', publication_names 'pgreps_13801') James Pang (

logical replication out of memory

2024-07-31 00:01:02.795 UTC:10.240.6.139(33068):repl13801@pgpodb:[3603770]:[10-1]:pgreps_13801ERROR: out of memory 2024-07-31 00:01:02.795 UTC:10.240.6.139(33068):repl13801@pgpodb:[3603770]:[11-1]:pgreps_13801DETAIL: Cannot enlarge string buffer containing 378355896 bytes by 756711422 more bytes.

Re: Hash Right join and seq scan

75*physical memory size. set random_page_cost=0.9 make optimizer to choose index scan instead of seq scan. Thanks, James David Rowley 於 2024年7月6日週六 上午8:33寫道： > On Sat, 6 Jul 2024 at 02:43, James Pang wrote: > >for nest loop path, since the first one estimated only "8" &g

Re: Hash Right join and seq scan

David Rowley 於 2024年7月5日週五 上午10:15寫道： > On Fri, 5 Jul 2024 at 12:50, James Pang wrote: > >we have a daily vacuumdb and analyze job, generally speaking it's > done in seconds, sometimes it suddenly running more than tens of minutes > with same bind variable values and

Re: Hash Right join and seq scan

we have a daily vacuumdb and analyze job, generally speaking it's done in seconds, sometimes it suddenly running more than tens of minutes with same bind variable values and huge temp space got used and at that time, explain show "Hash Anti join, Hash Right join" with seq scan two tables. Tom

Re: Hash Right join and seq scan

the join is "left out join" James Pang 於 2024年7月3日週三 下午2:51寫道： > > the query is >select >from table b join table aa >on b.partitionkeyid=aa.partitionkeyid >where b.id1= $1 and b.id2=$2 and b.rtime between $3 and $4; > >

Re: Hash Right join and seq scan

partition pruning? since the join key is hash partition key . Thanks, James James Pang 於 2024年7月3日週三 下午12:57寫道： > Both tables are hash partition tables , and we have a left out join , > optimizer convert to Hash Right Join, but it always try to seq scan on > tablexxx 32 paritions. th

Hash Right join and seq scan

Both tables are hash partition tables , and we have a left out join , optimizer convert to Hash Right Join, but it always try to seq scan on tablexxx 32 paritions. there are almost 250k rows per parition for table , so it's slow. As a workaround, I disable hashjoin the it run much fast with

Re: a lot of shared buffers hit when planning for a simple query with primary access path

we have a daily job to do vacuumdb including catalog tables, and in same database , I did similar query with where=pk on another table and shared buffer access is very small, if catalog table bloat, should see similar shared buffer access when planning for other tables ,right? How to get mor

a lot of shared buffers hit when planning for a simple query with primary access path

Hi, a simple SQL "select ... from tablex where id1=34215670 and id2=59403938282; id1 and i2 are bigint and primary key. Index Cond: ((tablex.id2 = ' 5940393828299'::bigint) AND (tablex.id1 = ' 34215670 '::bigint)) Buffers: shared hit=2 Query Identifier: -1350604566224020319 Planning:

Re: LWlock:LockManager waits

you mean too many concurrent sessions trying to acquire lock on same relation , then waiting on "LockManager" LWlock,right? this contention occurred on parsing ,planning, or execute step ? Thanks, James Laurenz Albe 於 2024年4月9日週二 下午12:31寫道： > On Tue, 2024-04-09 at 11:07 +080

LWlock:LockManager waits

we found sometimes , with many sessions running same query "select ..." at the same time, saw many sessions waiting on "LockManager". for example, pg_stat_activity show. It's a production server, so no enable trace_lwlocks flag. could you direct me what's the possible reason and how to reduce

Re: FW: huge SubtransSLRU and SubtransBuffer wait_event

rows) Thanks, James Alvaro Herrera 於 2024年3月1日週五 下午3:35寫道： > On 2024-Mar-01, James Pang wrote: > > > one question: > > we need to increase all SLRU buffers together , MULTIXACT, XACT, > > Subtrans, COMMIT TS , for example, got all of them doubled based on > &g

Re: FW: huge SubtransSLRU and SubtransBuffer wait_event

to increase all SLRU buffers together , MULTIXACT, XACT, Subtrans, COMMIT TS , for example, got all of them doubled based on existing size ? or only increase Subtrans , or Subtrans and multixact ? Thanks, James James Pang (chaolpan) 於 2024年3月1日週五 下午2:45寫道： > > > -Original Message

generic plan generate poor performance

Hi, we create statistics (dependencies,distinct) on (cccid,sssid); with real bind variables , it make good plan of Hash join , but when it try to generic plan, it automatically convert to Nestloop and then very poor sql performance. why generic plan change to to a poor plan "nestloop" ? how

Fwd: extend statistics help reduce index scan a lot of shared buffer hits.

Postgresql 14.8, Redhat8. looks like have to create extend statistics on indexed and joined columns to make join filters pushed down to secondary index scan in nestloop, and the shared buffer hits show big difference. is it expected ? SELECT

extend statistics help reduce index scan a lot of shared buffer hits.

Postgresql 14.8, Redhat8. looks like have to create extend statistics on indexed and joined columns to make join filters pushed down to secondary index scan in nestloop, and the shared buffer hits show big difference. is it expected ? SELECT

Re: FW: huge SubtransSLRU and SubtransBuffer wait_event

> > > From this link, looks like "onfigurable buffer pool and partitioning the > SLRU lock" is one the plan, maybe from v18,19 version, > https://www.postgresql.org/message-id/202402221843.ibzvpndbacbi@alvherre.pgsql > James > *From:* James Pang (chaolpan

Re: sql statement not using all primary key values and poor performance

; On Fri, 2024-02-23 at 15:20 +0800, James Pang wrote: > >we have a SQL from Postgresql JDBC, primary is based on > (bigint,varchar2,bigint), > > but from sql plan, it convert to ::numeric so the plan just use one > "varchar" > > key column and use the other

Re: sql statement not using all primary key values and poor performance

.029 it's from JDBC, we saw this JDBC driver try to set extra_float_digits = 3 before running the SQL ,does that make planner to convert bigint to numeric ? Postgresql 14.10 version. how to avoid this conversion and make planner use all primary keys. Thanks, James James Pang 於 2024年2

sql statement not using all primary key values and poor performance

Hi experts, we have a SQL from Postgresql JDBC, primary is based on (bigint,varchar2,bigint), but from sql plan, it convert to ::numeric so the plan just use one "varchar" key column and use the other 2 bigint keys as filters. what's the cause about that ? Table "t

RE: huge SubtransSLRU and SubtransBuffer wait_event

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 pa

RE: huge SubtransSLRU and SubtransBuffer wait_event

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)

RE: huge SubtransSLRU and SubtransBuffer wait_event

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

huge SubtransSLRU and SubtransBuffer wait_event

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

RE: simple query running long time within a long transaction.

long 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 o

simple query running long time within a long transaction.

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 lo

query pg_stat_ssl hang 100%cpu

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 looks

query pg_stat_ssl hang 100%cpu

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 pid=pg_backend_pid();

RE: Postgresql equal join on function with columns not use index

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

extended statistics n-distinct on multiple columns not used when join two tables

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, Jam

RE: Postgresql equal join on function with columns not use index

ch to pg_catalog.regexp_replace(b.PHONENUMBER,'[^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:

Postgresql equal join on function with columns not use index

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 tablea.column1=tableb.

wrong rows estimation by hash join

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

RE: thousands of CachedPlan entry per backend

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: th

RE: thousands of CachedPlan entry per backend

nks); 1744 used: xxx CachedPlanSource: 2048 total in 2 blocks; 200 free (0 chunks); 1848 used: xxx CachedPlanQuery: 2048 total in 2 blocks; 704 free (0 chunks); 1344 used Thanks, James -Original Message- From: Laurenz Albe Sent: Thursday, June 1, 2023 8:48 PM To:

RE: thousands of CachedPlan entry per backend

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)

RE: DML sql execution time slow down PGv14 compared with PGv13

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

RE: DML sql execution time slow down PGv14 compared with PGv13

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

RE: DML sql execution time slow down PGv14 compared with PGv13

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

RE: DML sql execution time slow down PGv14 compared with PGv13

, 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

DML sql execution time slow down PGv14 compared with PGv13

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

DML sql execution time slow down PGv14 compared with PGv13

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 5%-

wrong rows and cost estimation when generic plan

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

RE: wrong rows and cost estimation when generic plan

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

RE: wrong rows and cost estimation when generic plan

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

RE: wrong rows and cost estimation when generic plan

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

RE: Postgresql JDBC process consumes more memory with partition tables update delete

B 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

RE: Postgresql JDBC process consumes more memory than psql client

ssage- 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 +0000, James Pang (chaolpan) wrote: > W

RE: Postgresql JDBC process consumes more memory than psql client

mory 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

RE: Postgresql JDBC process consumes more memory than psql client

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

Postgresql JDBC process consumes more memory than psql client

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 only

RE: alter table xxx set unlogged take long time

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

RE: alter table xxx set unlogged take long time

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

RE: alter table xxx set unlogged take long time

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

RE: alter table xxx set unlogged take long time

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

alter table xxx set unlogged take long time

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

RE: partition pruning only works for select but update

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

RE: partition pruning only works for select but update

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(); >

RE: partition pruning only works for select but update

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

partition pruning only works for select but update

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) Sub

RE: reindex option for tuning load large data

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 for

RE: reindex option for tuning load large data

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

reindex option for tuning load large data

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 a

RE: postgres backend process hang on " D " state

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

RE: postgres backend process hang on " D " state

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

RE: postgres backend process hang on " D " state

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

RE: postgres backend process hang on " D " state

Original Message- 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 +, James Pang (chaolpan) wrote: >

postgres backend process hang on " D " state

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 fro