Disk wait problem...

From: pf(at)pfortin(dot)com
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Disk wait problem...
Date: 2023-10-23 18:54:59
Message-ID: 20231023145459.143faba7.pfortin@pfortin.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Attachment Content-Type Size
image/png 47.9 KB
pg_slow application/octet-stream 20.4 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2023-10-23 19:44:56 Re: Disk wait problem...
Previous Message Tom Lane 2023-10-23 18:50:13 Re: psql \r and \e -- what does clearing the query buffer actually do?