pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Disk 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