pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedDisk wait problem...
8+ messages / 4 participants
[nested] [flat]
* Disk wait problem...
@ 2023-10-23 18:54 pf@pfortin.com
0 siblings, 1 reply; 8+ messages in thread
From: pf@pfortin.com @ 2023-10-23 18:54 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
I use SQL-workbench/J (WB) and starting at any table, if I use up/down
arrow to switch to another table, all that happens is WB queries the
table to get the row count and a LIMIT of rows to display.
If I set LIMIT to 10, I see one process in disk wait:
$ top -b -n 1 | grep postgre
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1913118 postgres 20 0 172620 22184 21064 S 0.0 0.0 0:04.26 postgres
1913119 postgres 20 0 172744 11428 10240 S 0.0 0.0 0:00.02 postgres
1913120 postgres 20 0 172756 8868 7680 S 0.0 0.0 0:00.42 postgres
1913122 postgres 20 0 172620 9988 8800 S 0.0 0.0 0:00.36 postgres
1913123 postgres 20 0 178300 9508 8160 S 0.0 0.0 0:02.29 postgres
1913124 postgres 20 0 176232 7908 6560 S 0.0 0.0 0:00.02 postgres
1931492 postgres 20 0 178044 21668 18720 S 0.0 0.0 0:00.22 postgres
1977002 postgres 20 0 182984 36992 31360 D 0.0 0.0 0:01.61 postgres
3851279 postgres 20 0 13544 6560 2880 S 0.0 0.0 0:00.01 bash
3854720 postgres 20 0 10716 5416 4456 S 0.0 0.0 0:00.02 psql
With LIMIT set to 500, this table has 2,148,035 rows (disk-wait process
only):
$ top -b -n 1 | grep postgre
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1977002 postgres 20 0 184708 37900 32000 D 0.0 0.0 0:01.83 postgres
2,152,372 rows -- took nearly 30 seconds
$ top -b -n 1 | grep postgre
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1977002 postgres 20 0 184708 37900 32000 D 0.0 0.0 0:01.83 postgres
Earlier, with LIMIT 500, I was seeing 3 processes in disk-wait.
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
Attachments:
htop.png (image/png, 47.9K) download
view image
pg_slow (application/octet-stream, 20.4K) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Disk wait problem...
@ 2023-10-23 19:44 Adrian Klaver <adrian.klaver@aklaver.com>
parent: pf@pfortin.com
0 siblings, 2 replies; 8+ messages in thread
From: Adrian Klaver @ 2023-10-23 19:44 UTC (permalink / raw)
To: pf@pfortin.com; pgsql-general@lists.postgresql.org
On 10/23/23 11:54, pf@pfortin.com 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...
What happens if you select the tables in the command line client psql?
>
> 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
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Disk wait problem...
@ 2023-10-23 20:09 Ken Marshall <ktm@rice.edu>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 1 reply; 8+ messages in thread
From: Ken Marshall @ 2023-10-23 20:09 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
> 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.
Regards,
Ken
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Disk wait problem...
@ 2023-10-23 21:30 pf@pfortin.com
parent: Ken Marshall <ktm@rice.edu>
0 siblings, 0 replies; 8+ messages in thread
From: pf@pfortin.com @ 2023-10-23 21:30 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Disk wait problem...
@ 2023-10-23 23:31 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 2 replies; 8+ messages in thread
From: Adrian Klaver @ 2023-10-23 23:31 UTC (permalink / raw)
To: pf@pfortin.com; +Cc: pgsql-general
On 10/23/23 14:55, pf@pfortin.com wrote:
Please reply to list also.
Ccing the list for this post.
> On Mon, 23 Oct 2023 12:44:56 -0700 Adrian Klaver wrote:
>
>> On 10/23/23 11:54, pf@pfortin.com 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...
>>
>> What happens if you select the tables in the command line client psql?
>
> Hi Adrian,
>
> Sorry about everything inline; I think it saves time vs dealing with
> attachments...
>
> Summary:
> * single large partition dedicated to PG
> * only PG seems affected
> * buffered reads are 6x slower when PG queries are in progress
> * psql queries of 30 33M tables shows random results up to 4m51s
> * successful psql queries below 500ms
> * ls -l loop on another directory had no delays.
>
> HTH...
>
> The WB query was in disk-wait, so psql gave:
> ncsbe=# select count(*) from ncvhis_2022_10_29;
> FATAL: terminating connection due to administrator command
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Succeeded.
>
> The above query finished a second or two before the WB results
> appeared; then this next query succeeded right after:
> ncsbe=# select count(*) from
> ncvhis_2022_10_29; count
> ----------
> 33861176
> (1 row)
>
> Another table went into disk-wait long enough that I was able to query
> the tmp directory on the same disk just fine.
>
> Querying the same table; no error, canceled it:
> ncsbe=# select count(*) from ncvhis_2022_07_23;
> ^CCancel request sent
> ERROR: canceling statement due to user request
>
> While this table was still in disk-wait, tried another table which
> returned quickly:
> ncsbe=# select count(*) from ncvhis_2023_10_21;
> count
> ----------
> 32983343
> (1 row)
>
> Eventually, the WB query did complete...
>
> Next, I made a query list for 30 tables, turned on timing and pasted the
> queries. Pasting results inline:
> ncsbe=# select count(*) from ncvhis_2012_12_06;
> select count(*) from ncvhis_2016_12_03;
> select count(*) from ncvhis_2020_08_01;
> select count(*) from ncvhis_2020_10_31;
> select count(*) from ncvhis_2020_12_05;
> select count(*) from ncvhis_2020_12_26;
> select count(*) from ncvhis_2021_03_06;
> select count(*) from ncvhis_2021_06_12;
> select count(*) from ncvhis_2022_07_23;
> select count(*) from ncvhis_2022_10_15;
> select count(*) from ncvhis_2022_10_22;
> select count(*) from ncvhis_2022_10_29;
> select count(*) from ncvhis_2022_11_05;
> select count(*) from ncvhis_2022_11_12;
> select count(*) from ncvhis_2022_11_19;
> select count(*) from ncvhis_2022_11_26;
> select count(*) from ncvhis_2022_12_03;
> select count(*) from ncvhis_2022_12_10;
> select count(*) from ncvhis_2022_12_17;
> select count(*) from ncvhis_2022_12_24;
> select count(*) from ncvhis_2022_12_31;
> select count(*) from ncvhis_2023_01_08;
> select count(*) from ncvhis_2023_01_14;
> select count(*) from ncvhis_2023_01_21;
> select count(*) from ncvhis_2023_01_28;
> select count(*) from ncvhis_2023_02_04;
> select count(*) from ncvhis_2023_02_11;
> select count(*) from ncvhis_2023_02_18;
> select count(*) from ncvhis_2023_02_27;
> select count(*) from ncvhis_2023_03_04;
> count
> ----------
> 31923950
> (1 row)
>
> Time: 72404.786 ms (01:12.405)
> count
> ----------
> 29701168
> (1 row)
>
> Time: 301.246 ms
> count
> ----------
> 32172845
> (1 row)
>
> Time: 409.974 ms
> count
> ----------
> 32162748
> (1 row)
>
> Time: 363.836 ms
> count
> ----------
> 34679651
> (1 row)
>
> Time: 351.167 ms
> count
> ----------
> 35006711
> (1 row)
>
> Time: 348.378 ms
> count
> ----------
> 35003995
> (1 row)
>
> Time: 348.712 ms
> count
> ----------
> 34994502
> (1 row)
>
> Time: 351.901 ms
> count
> ----------
> 33686292
> (1 row)
>
> Time: 487.837 ms
> count
> ----------
> 33861658
> (1 row)
>
> Time: 40987.826 ms (00:40.988)
> count
> ----------
> 33861381
> (1 row)
>
> Time: 76964.281 ms (01:16.964)
> count
> ----------
> 33861176
> (1 row)
>
> Time: 483.329 ms
> count
> ----------
> 33861071
> (1 row)
>
> Time: 18919.267 ms (00:18.919)
> count
> ----------
> 29344354
> (1 row)
>
> Time: 50896.978 ms (00:50.897)
> count
> ----------
> 30709227
> (1 row)
>
> Time: 25784.000 ms (00:25.784)
> count
> ----------
> 32368001
> (1 row)
>
> Time: 45407.599 ms (00:45.408)
> count
> ----------
> 33126826
> (1 row)
>
> Time: 75171.609 ms (01:15.172)
> count
> ----------
> 33126808
> (1 row)
>
> Time: 88871.004 ms (01:28.871)
> count
> ----------
> 33127317
> (1 row)
>
> Time: 128565.127 ms (02:08.565)
> count
> ----------
> 33127192
> (1 row)
>
> Time: 210248.222 ms (03:30.248)
> count
> ----------
> 33127117
> (1 row)
>
> Time: 246609.561 ms (04:06.610) 4 minutes! ;p
> count
> ----------
> 33124457
> (1 row)
>
> Time: 166406.283 ms (02:46.406)
> count
> ----------
> 33123714
> (1 row)
>
> Time: 291483.538 ms (04:51.484) nearly 5 minutes ;p ;p
> count
> ----------
> 33123313
> (1 row)
>
> Time: 2269.961 ms (00:02.270)
> count
> ----------
> 33123197
> (1 row)
>
> Time: 413.219 ms
> count
> ----------
> 33123091
> (1 row)
>
> Time: 433.817 ms
> count
> ----------
> 33122953
> (1 row)
>
> Time: 395.844 ms
> count
> ----------
> 33122752
> (1 row)
>
> Time: 7251.637 ms (00:07.252)
> count
> ----------
> 33122665
> (1 row)
>
> Time: 384.328 ms
> count
> ----------
> 33122573
> (1 row)
>
> Time: 384.887 ms
> ncsbe=#
>
> I don't see a pattern in the above; matches the randomness I saw using
> WB...
>
> Before the above finished, I issued this command on another konsole...
>
> $ while true; do ls -l > /tmp/ll; date; done
> Mon Oct 23 05:12:54 PM EDT 2023
> Mon Oct 23 05:12:54 PM EDT 2023
> Mon Oct 23 05:12:54 PM EDT 2023
> Mon Oct 23 05:12:54 PM EDT 2023
> Mon Oct 23 05:12:54 PM EDT 2023
> Mon Oct 23 05:12:54 PM EDT 2023
> Mon Oct 23 05:12:54 PM EDT 2023
> Mon Oct 23 05:12:54 PM EDT 2023
> Mon Oct 23 05:12:54 PM EDT 2023
> Mon Oct 23 05:12:54 PM EDT 2023
> Mon Oct 23 05:12:54 PM EDT 2023
> Mon Oct 23 05:12:54 PM EDT 2023
> ...
> Lots of high speed output with no pauses, at 64 loops per second.
>
> So that means only PG is getting disk waits.
>
> This disk is dedicated to PG and a rarely used semi-permanent tmp
> directory on a single partition.
>
> This is from fdisk -l:
> Disk /dev/nvme1n1: 3.64 TiB, 4000787030016 bytes, 7814037168 sectors
> Disk model: Seagate FireCuda 530 ZP4000GM30023
> Units: sectors of 1 * 512 = 512 bytes
> Sector size (logical/physical): 512 bytes / 512 bytes
> I/O size (minimum/optimal): 512 bytes / 512 bytes
> Disklabel type: gpt
> Disk identifier: 9B3C5D73-6FFD-475D-9E1E-26F43B41C690
>
> Device Start End Sectors Size Type
> /dev/nvme1n1p1 2048 7814035455 7814033408 3.6T Linux filesystem
>
> $ hdparm -Tt /dev/nvme1n1
> /dev/nvme1n1:
> Timing cached reads: 36820 MB in 2.00 seconds = 18434.72 MB/sec
> Timing buffered disk reads: 774 MB in 3.00 seconds = 257.74 MB/sec
>
> The 30 queries still hadn't finished at this point...
>
> Interesting: the hdparm buffered read is very slow; once the queries
> finished, the disk performance is back:
> $ hdparm -Tt /dev/nvme1n1
>
> /dev/nvme1n1:
> Timing cached reads: 37382 MB in 2.00 seconds = 18715.85 MB/sec
> Timing buffered disk reads: 5002 MB in 3.17 seconds = 1575.89 MB/sec
>
> Over 6x faster...
>
> So far, the only disk waits I see are PG related. Do have any opinion on
> Ken's comment about parallel queries?
>
> Thanks,
> Pierre
>
>
>>>
>>> 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
>>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Disk wait problem...
@ 2023-10-24 00:03 pf@pfortin.com
parent: Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 0 replies; 8+ messages in thread
From: pf@pfortin.com @ 2023-10-24 00:03 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On Mon, 23 Oct 2023 16:31:30 -0700 Adrian Klaver wrote:
>Please reply to list also.
>Ccing the list for this post.
Sorry, got the list's message and one directly from you; looks like I
picked the wrong one to reply to...
I just heard from a remote team member who wrote this:
!! Eventually found that 3 PG processes are sitting in disk-wait
(attached
!! screenshot)... I still can't imagine what would hold onto a
disk for that
!! long,
I wonder if these are abandoned queries that did not properly close
out.
He forgot to attach his screenshot; trying to get more info from him...
Thanks,
Pierre
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Disk wait problem...
@ 2023-10-26 09:50 Peter J. Holzer <hjp-pgsql@hjp.at>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 1 reply; 8+ messages in thread
From: Peter J. Holzer @ 2023-10-26 09:50 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2023-10-23 16:31:30 -0700, Adrian Klaver wrote:
> On 10/23/23 14:55, pf@pfortin.com wrote:
> > Next, I made a query list for 30 tables, turned on timing and pasted the
> > queries. Pasting results inline:
> > ncsbe=# select count(*) from ncvhis_2012_12_06;
> > select count(*) from ncvhis_2016_12_03;
> > select count(*) from ncvhis_2020_08_01;
> > select count(*) from ncvhis_2020_10_31;
[...]
> > Time: 72404.786 ms (01:12.405)
> > Time: 301.246 ms
> > Time: 409.974 ms
> > Time: 363.836 ms
> > Time: 351.167 ms
> > Time: 348.378 ms
> > Time: 348.712 ms
> > Time: 351.901 ms
> > Time: 487.837 ms
> > Time: 40987.826 ms (00:40.988)
> > Time: 76964.281 ms (01:16.964)
> > Time: 483.329 ms
> > Time: 18919.267 ms (00:18.919)
> > Time: 50896.978 ms (00:50.897)
> > Time: 25784.000 ms (00:25.784)
> > Time: 45407.599 ms (00:45.408)
> > Time: 75171.609 ms (01:15.172)
> > Time: 88871.004 ms (01:28.871)
> > Time: 128565.127 ms (02:08.565)
> > Time: 210248.222 ms (03:30.248)
> > Time: 246609.561 ms (04:06.610) 4 minutes! ;p
> > Time: 166406.283 ms (02:46.406)
> > Time: 291483.538 ms (04:51.484) nearly 5 minutes ;p ;p
> > Time: 2269.961 ms (00:02.270)
> > Time: 413.219 ms
> > Time: 433.817 ms
> > Time: 395.844 ms
> > Time: 7251.637 ms (00:07.252)
> > Time: 384.328 ms
> > Time: 384.887 ms
> >
> > I don't see a pattern in the above; matches the randomness I saw using
> > WB...
It would be interesting to see whether there's a difference in plan
between the slow and the fast queries.
If your experiment above is repeatable, you can just prefix every
query with
explain (analyze, buffers)
explain (analyze, buffers) select count(*) from ncvhis_2016_12_03;
explain (analyze, buffers) select count(*) from ncvhis_2020_08_01;
explain (analyze, buffers) select count(*) from ncvhis_2020_10_31;
...
If it is hard to reproduce (happens only sometimes), the best way to
catch is probably to enable auto_explain
<https://www.postgresql.org/docs/current/auto-explain.html;, possibly
with the auto_explain.log_analyze option and check the logs after it
happened. (auto_explain is generally a good idea, IMHO).
> > Before the above finished, I issued this command on another konsole...
> >
> > $ while true; do ls -l > /tmp/ll; date; done
This is unlikely to generate noticeable disk waits. The current
directory will be in the cache after the first ls and the writes happen
asynchroneously.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Disk wait problem...
@ 2023-10-26 19:50 pf@pfortin.com
parent: Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 0 replies; 8+ messages in thread
From: pf@pfortin.com @ 2023-10-26 19:50 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hi Peter,
All of the following is based on using SQL_workbench/J (WB) (Mageia Linux
has not provided a viable pgAdmin4 package); WB is setup to autoload the
table row count and 10 rows. I'm sticking to one set of files where they
are all roughly 33.6M rows.
I've been doing a lot of digging and found more interesting info...
At first, I thought the disk was in disk wait the entire time of the
delay; this _appeared_ to be the case since the system journal logged
everything about each query, with every query looking the same; no errors.
I was going to provide some logs; but suffice it to say that the logs,
combined with running iostat every second has been enlightening...
Every time I switch from one table to another, sometimes the results
display in:
* less than a half-second
* one or more seconds
* up to 5+ minutes.
So far, none has failed to eventually return; just l..o..n..g delays...
This morning, I've been using iostat:
$ while true; do echo -n "$(date +%H:%M:%S) "; iostat /dev/nvme1n1 |
grep nvme1n1; sleep 1; done
When the response is fast; I see iostat report ~225000 bytes read. On the
other hand when the query takes a long time to respond, I see the read
bytes spread over time... a little every second... So while htop
appears to report a steady D (disk wait) status, I now suspect the short
transfers are fast and most of the time, htop is going to catch the D
state.
It definitely appears to be a problem with the 4TB NVMe SSD drive which
utilities say it still has 95% life left[1]. The drive is reporting:
Error Information Log Entries: 1,177
which is growing, so I'll be digging into that. It also reports:
Error Information (NVMe Log 0x01, 16 of 63 entries)
Num ErrCount SQId CmdId Status PELoc LBA NSID VS Message
0 1177 0 0xc002 0x4004 - 0 1 - Invalid Field in Command
1 1176 0 0xb001 0x4004 - 0 1 - Invalid Field in Command
2 1175 0 0x801a 0x4004 - 0 1 - Invalid Field in Command
...
This list is not growing, so I'll look into it later than sooner.
A few moments ago, I decided to switch to the backup which resides on a
17TB mechanical platter drive which is running great.
[1] SSD drives wear out with usage; think of it as a vehicle tire which
slowly wears out.
Also found that there is a firmware update available from:
https://www.seagate.com/support/downloads/
No idea if this will solve the problem; but...
Thanks for your suggestions.
Thought I saw there may be instrumentation within PG which I can access;
suggestions welcome if so...
Cheers,
Pierre
On Thu, 26 Oct 2023 11:50:00 +0200 Peter J. Holzer wrote:
>On 2023-10-23 16:31:30 -0700, Adrian Klaver wrote:
>> On 10/23/23 14:55, pf@pfortin.com wrote:
>> > Next, I made a query list for 30 tables, turned on timing and pasted the
>> > queries. Pasting results inline:
>> > ncsbe=# select count(*) from ncvhis_2012_12_06;
>> > select count(*) from ncvhis_2016_12_03;
>> > select count(*) from ncvhis_2020_08_01;
>> > select count(*) from ncvhis_2020_10_31;
>[...]
>> > Time: 72404.786 ms (01:12.405)
>> > Time: 301.246 ms
>> > Time: 409.974 ms
>> > Time: 363.836 ms
>> > Time: 351.167 ms
>> > Time: 348.378 ms
>> > Time: 348.712 ms
>> > Time: 351.901 ms
>> > Time: 487.837 ms
>> > Time: 40987.826 ms (00:40.988)
>> > Time: 76964.281 ms (01:16.964)
>> > Time: 483.329 ms
>> > Time: 18919.267 ms (00:18.919)
>> > Time: 50896.978 ms (00:50.897)
>> > Time: 25784.000 ms (00:25.784)
>> > Time: 45407.599 ms (00:45.408)
>> > Time: 75171.609 ms (01:15.172)
>> > Time: 88871.004 ms (01:28.871)
>> > Time: 128565.127 ms (02:08.565)
>> > Time: 210248.222 ms (03:30.248)
>> > Time: 246609.561 ms (04:06.610) 4 minutes! ;p
>> > Time: 166406.283 ms (02:46.406)
>> > Time: 291483.538 ms (04:51.484) nearly 5 minutes ;p ;p
>> > Time: 2269.961 ms (00:02.270)
>> > Time: 413.219 ms
>> > Time: 433.817 ms
>> > Time: 395.844 ms
>> > Time: 7251.637 ms (00:07.252)
>> > Time: 384.328 ms
>> > Time: 384.887 ms
>> >
>> > I don't see a pattern in the above; matches the randomness I saw using
>> > WB...
>
>It would be interesting to see whether there's a difference in plan
>between the slow and the fast queries.
>
>If your experiment above is repeatable, you can just prefix every
>query with
> explain (analyze, buffers)
>
>
>explain (analyze, buffers) select count(*) from ncvhis_2016_12_03;
>explain (analyze, buffers) select count(*) from ncvhis_2020_08_01;
>explain (analyze, buffers) select count(*) from ncvhis_2020_10_31;
>...
>
>If it is hard to reproduce (happens only sometimes), the best way to
>catch is probably to enable auto_explain
><https://www.postgresql.org/docs/current/auto-explain.html;, possibly
>with the auto_explain.log_analyze option and check the logs after it
>happened. (auto_explain is generally a good idea, IMHO).
>
>
>> > Before the above finished, I issued this command on another konsole...
>> >
>> > $ while true; do ls -l > /tmp/ll; date; done
>
>This is unlikely to generate noticeable disk waits. The current
>directory will be in the cache after the first ls and the writes happen
>asynchroneously.
>
> hp
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-10-26 19:50 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-23 18:54 Disk wait problem... pf@pfortin.com
2023-10-23 19:44 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-10-23 20:09 ` Ken Marshall <ktm@rice.edu>
2023-10-23 21:30 ` pf@pfortin.com
2023-10-23 23:31 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-10-24 00:03 ` pf@pfortin.com
2023-10-26 09:50 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-10-26 19:50 ` pf@pfortin.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox