Re: many sessions waiting DataFileRead and extend

we faced this issue 3 times this week, each time last only 2 seconds, so not easy to run perf in peak business time to capture that, anyway, I will try. before that, I want to understand if "os page cache" or "pg buffer cache" can contribute to the wait_event time "extend" and "DataFileRead", or

Re: many sessions waiting DataFileRead and extend

Okay. So it's a situation that is reproducable. And like was mentioned, the system time (percentage) is very high. Is this a physical machine, or a virtual machine? The next thing to do, is use perf to record about 20 seconds or so during a period of time when you see this behavior (perf record -

Re: many sessions waiting DataFileRead and extend

Thanks, I make a summary of the issue, no connection storm(fork) either, just suddenly many session waiting on "extend" and "DataFileRead", it last 2 seconds, this server has 64 vcpu and running there long time without issue, only last weekend, we patch from 14.8 to 14.14. We checked with Infr

Re: many sessions waiting DataFileRead and extend

> On Thu, 2025-06-26 at 10:32 +0800, James Pang wrote: >> thans for you suggestions, we have iowait from sar command too, copy here, >> checking with infra team not found abnormal IO activities either.   >> 02:00:01 PM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal   >> %guest  %gnic

Re: many sessions waiting DataFileRead and extend

On Thu, 2025-06-26 at 10:32 +0800, James Pang wrote: > thans for you suggestions, we have iowait from sar command too, copy here, > checking with infra team not found abnormal IO activities either.   > 02:00:01 PM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal   > %guest  %gnice   %i

Re: many sessions waiting DataFileRead and extend

thans for you suggestions, we have iowait from sar command too, copy here, checking with infra team not found abnormal IO activities either. 02:00:01 PM CPU%usr %nice%sys %iowait%irq %soft %steal %guest %gnice %idle 02:00:03 PM all 15.920.00 43.020.650.7

Re: many deletes and inserts on pg_class, total rows is 14200 rows in "pg_class", any idea why so many inserts/deletes on "pg_class" it's self?

You use temporary tables extensively. > > David J.

many deletes and inserts on pg_class, total rows is 14200 rows in "pg_class", any idea why so many inserts/deletes on "pg_class" it's self?

-[ RECORD 1 ]---+-- relid | 1259 schemaname | pg_catalog relname | pg_class seq_scan| 22173212 seq_tup_read| 153735849586 idx_scan| 12471735164 idx_tup_fetch | 12193538267 n_tup_ins |

Re: many sessions waiting DataFileRead and extend

> On 25 Jun 2025, at 07:59, Laurenz Albe wrote: > > On Wed, 2025-06-25 at 11:15 +0800, James Pang wrote: >> pgv14, RHEL8, xfs , we suddenly see tens of sessions waiting on >> "DataFileRead" and >> "extend", it last about 2 seconds(based on pg_stat_activity query) , during >> the >> waiting t

Guidance Needed: Scaling PostgreSQL for 12 TB Data Growth - New Feature Implementation

Dear PostgreSQL Community, We are implementing a new feature in our application that is expected to generate a significant amount of data, and we are seeking your expert guidance on how to best handle this growth within our existing PostgreSQL setup. Currently, our PostgreSQL instance runs on a

Re: many sessions waiting DataFileRead and extend

transparent_hugepage=never in our prod servers, %iowait is low 0.x-1.x% , read/write iops <2k, and read/write wait 0.x ms. we did not find other abnormal logs from OS logs either. Yes, we are discussing with our application team to reduce concurrency. more questions about DataFileRead and ext