Re: Disk wait problem...

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