From: | pf(at)pfortin(dot)com |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Disk wait problem... |
Date: | 2023-10-23 21:30:28 |
Message-ID: | 20231023173028.7a2df1a6.pfortin@pfortin.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, 23 Oct 2023 15:09:16 -0500 Ken Marshall wrote:
>> Hi,
>>
>> I have a 1.6TB database with over 330 tables on a 4TB NVMe SSD. All
>> tables are static (no updates); most in 8M and 33M row sizes. Queries have
>> been great, until recently.
>
>> Also attached is the relevant system journal entries for one query that
>> took 2 seconds and two more that took 62 and 52 seconds... Unlike the
>> above, these had 3 processes in disk-wait -- see attached screenshot...
>>
>> I just stopped PG, updated the full backup which was done overnight (so
>> the update took about a second), restarted PG and the problem persists...
>>
>> Any suggestions where to look next?
>>
>> Thanks,
>> Pierre
>
>Hi Pierre,
>
>I have seen this type of behavior when parallel queries were enabled and
>PostgreSQL decided that a parallel sequential scan query was the fastest
>option. Try disabling them and see if that helps.
Hi Ken, I'm running a bunch of tests for Adrian right now; but I'm
curious: are parallel queries on by default? I have no recollection of
ever seeing that was an option. Since I'm only running simple:
SELECT count(*) FROM table; does this imply that PG could be breaking
the query up in parts to count faster, and why I saw up to 3 processes in
D state?
Thanks for this tip,
Pierre
>Regards,
>Ken
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Torsten Förtsch | 2023-10-23 21:36:24 | partitioning |
Previous Message | Ken Marshall | 2023-10-23 20:09:16 | Re: Disk wait problem... |