On Fri, 27 Oct 2023 21:21:18 -0700 Adrian Klaver wrote:
>On 10/27/23 16:46, p...@pfortin.com wrote:
>> Peter,
>>
>> Thanks for your patience; I've been feeling pressure to get this
>> resolved; so have been lax in providing info here.. Hope the following
>> helps...
>>
>
>
>> Something I hadn't noticed before: SQL-workbench/J (build 129.6) displays
>> an execution timer at the bottom of the window. I see all the queries
>> reporting 0-47ms which it plenty fast. It's the results that often take a
>> long time to display.
>
>Which is not surprising as building GUI elements is an expensive
>operation.
True; but not to this extent (minutes).
>If the time to display is your wait issue then this is not
>really a disk issue.
Guessing you didn't look at attachment "journal2"... it clearly shows
data from the disk coming out VERY slowly...
>What happens if you use psql as the client?
I see my reply on the 23rd only went to you (I'm still forgetting to
check which message I'm replying to -- this is unlike the other lists I
participate in, where the headers are set to force all messages through
the list server), my reply contained the psql info where psql shows long
delays too:
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 [row] 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)
Ti
