pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feed[SOLVED?] Re: Disk wait problem... not hardware...
6+ messages / 4 participants
[nested] [flat]
* [SOLVED?] Re: Disk wait problem... not hardware...
@ 2023-10-29 13:21 pf@pfortin.com
0 siblings, 1 reply; 6+ messages in thread
From: pf@pfortin.com @ 2023-10-29 13:21 UTC (permalink / raw)
To: ; +Cc: pgsql-general@lists.postgresql.org
On Sat, 28 Oct 2023 18:34:50 -0400 Jim Mlodgenski wrote:
Looking like a GOLD star for Jim...
>On Fri, Oct 27, 2023 at 7:46 PM <pf@pfortin.com> wrote:
>
>> Memory: 125.5 GiB of RAM
>>
>It looks like you have a large amount of memory allocated to the server
>
>But your plans are doing reads instead of pulling things from shared
>buffers
Assuming you are referring to "Buffers: shared hit=248 read=25022" ?
>>explain (analyze, buffers) select count(*) from ncvhis_2016_12_03;
>> Finalize Aggregate (cost=404669.65..404669.66 rows=1 width=8) (actual
>> time=844.158..847.309 rows=1 loops=1) Buffers: shared hit=248 read=25022
>> -> Gather (cost=404669.43..404669.65 rows=2 width=8) (actual
>> time=844.133..847.301 rows=3 loops=1) Workers Planned: 2
>> Workers Launched: 2
>> Buffers: shared hit=248 read=25022
>> -> Partial Aggregate (cost=403669.43..403669.45 rows=1
>> width=8) (actual time=838.772..838.772 rows=1 loops=3) Buffers: shared
>> hit=248 read=25022 -> Parallel Index Only Scan using
>> ncvhis_2016_12_03_voted_party_cd_idx on ncvhis_2016_12_03
>> (cost=0.44..372735.05 rows=12373755 width=0) (actual
>> time=18.277..592.473 rows=9900389 loops=3) Heap Fetches: 0 Buffers:
>> shared hit=248 read=25022 Planning Time: 0.069 ms JIT:
>> Functions: 8
>> Options: Inlining false, Optimization false, Expressions true,
>> Deforming true Timing: Generation 0.284 ms, Inlining 0.000 ms,
>> Optimization 0.268 ms, Emission 3.590 ms, Total 4.143 ms Execution
>> Time: 847.498 ms
>>
>>
>
>
>data/postgresql.conf:
>> max_connections = 100
>> shared_buffers = 128MB
I semi-misquoted; it was running on 128MB when first reported; then 512MB
when I sent this message.
>It looks like you are running with the stock config for shared_buffers.
>With only 128MB dedicated for shared_buffers and such a big database,
>you'll be thrashing the cache pretty hard. With 125GB on the server, try
>upping shared_buffers to something more like 32GB.
WOW! Some tables now have a barely noticeable delay; on the whole,
looking MUCH better. Testing with a just created copy[1] on the NVMe,
it's better; but it's slowly going back to longer delays. Looks like
count(*) may have easily-missed side-effects (more below). My team
members were suggesting a possible motherboard issue since the problem
happened on both the NVMe M.2 SSD, and on a backup SATA-3 platter drive.
Your suggestion arrived just in time as I was about to wipe out the SSD
and re-import everything fresh.
Another thing that bothers me is that when I copied the DB, the:
cp -a ./var ./var2 # same drive, same partition
replicating the DB onto free space on the same SSD, it was experiencing
slow reads 18-20KB/sec; the writes were quick, large blocks; so I was
suspecting something with the SSD... System buffers..?
How autoload row count (or count(*)) can hurt:
The queries which triggered this issue are all the same (WB built-in):
SELECT count(*) FROM table; # WB = SQL-workbench/J
These are all static tables. Does PG maintain a table row count so as to
avoid having to count each time? WB is setup to:
* autoload table row count
* autoload table data (restricted with LIMIT)
Walking through these 8M & 33M row tables (3.8GB & 5.2GB respectively)
must be filling the shared_buffers and thrashing even 32GB...
Ah-ha! Turning off "autoload table row count" has "resolved" this issue. I
can now scroll through all the tables at high speed (hold down the arrow
key to walk through all the tables. Not very useful; but walking through
the tables with only 128MB shared-buffers would have been the ultimate
buffer thrashing... ;p Even walking through the tables to collect their
row counts (whether manually or programmatically. ).
That said, the 32GB shared-buffers may now be masking NVMe drive issues...
Thanks!!!,
Pierre
Time and complexity to find the cause of an issue is inversely
proportional to the simplicity of the issue...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SOLVED?] Re: Disk wait problem... not hardware...
@ 2023-10-29 15:16 Peter J. Holzer <hjp-pgsql@hjp.at>
parent: pf@pfortin.com
0 siblings, 1 reply; 6+ messages in thread
From: Peter J. Holzer @ 2023-10-29 15:16 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2023-10-29 09:21:46 -0400, pf@pfortin.com wrote:
> These are all static tables. Does PG maintain a table row count so as to
> avoid having to count each time?
No. To count the rows in a table, Postgres has to actually read the
whole table (or an index, if a suitable index (e.g. a primary key)
exists).
However, the table statistics contain an estimate for the number of
rows:
hjp=> select schemaname, relname, n_live_tup from pg_stat_user_tables order by 3 desc;
╔════════════╤═══════════════════════════╤════════════╗
║ schemaname │ relname │ n_live_tup ║
╟────────────┼───────────────────────────┼────────────╢
║ public │ ncvhis_2016_12_03 │ 9999977 ║
║ public │ random_test │ 1000000 ║
║ public │ beislindex │ 351 ║
║ public │ delivery │ 6 ║
...
This is an estimate, not the actual count, so it might be wrong
(sometimes very wrong), but accessing it is fast and it might be good
enough for your purposes.
> WB is setup to:
> * autoload table row count
> * autoload table data (restricted with LIMIT)
Maybe WB can be set up to get n_live_tup instead of the real row count?
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] 6+ messages in thread
* Re: [SOLVED?] Re: Disk wait problem... not hardware...
@ 2023-10-29 16:45 pf@pfortin.com
parent: Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 3 replies; 6+ messages in thread
From: pf@pfortin.com @ 2023-10-29 16:45 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On Sun, 29 Oct 2023 16:16:05 +0100 Peter J. Holzer wrote:
>On 2023-10-29 09:21:46 -0400, pf@pfortin.com wrote:
>> These are all static tables. Does PG maintain a table row count so as to
>> avoid having to count each time?
>
>No. To count the rows in a table, Postgres has to actually read the
>whole table (or an index, if a suitable index (e.g. a primary key)
>exists).
Am I correct to assume count(fieldname) would only load that column for
counting? In other words: we should be specific (avoid "*") in general?
>However, the table statistics contain an estimate for the number of
>rows:
>
>hjp=> select schemaname, relname, n_live_tup from pg_stat_user_tables order by 3 desc;
>╔════════════╤═══════════════════════════╤════════════╗
>║ schemaname │ relname │ n_live_tup ║
>╟────────────┼───────────────────────────┼────────────╢
>║ public │ ncvhis_2016_12_03 │ 9999977 ║
>║ public │ random_test │ 1000000 ║
>║ public │ beislindex │ 351 ║
>║ public │ delivery │ 6 ║
>...
Are there prerequisites for this query?
Every (264) n_live_tup returned was zero... though, VERY fast.. :)
>This is an estimate, not the actual count, so it might be wrong
>(sometimes very wrong), but accessing it is fast and it might be good
>enough for your purposes.
Thanks for sharing... We're learning fast... :)
>> WB is setup to:
>> * autoload table row count
>> * autoload table data (restricted with LIMIT)
>
>Maybe WB can be set up to get n_live_tup instead of the real row count?
It appears this is hard-coded [on/off only]; but I thought I saw the WB
author post on this list recently...
> hp
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SOLVED?] Re: Disk wait problem... not hardware...
@ 2023-10-29 16:51 Ron <ronljohnsonjr@gmail.com>
parent: pf@pfortin.com
2 siblings, 0 replies; 6+ messages in thread
From: Ron @ 2023-10-29 16:51 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 10/29/23 11:45, pf@pfortin.com wrote:
> On Sun, 29 Oct 2023 16:16:05 +0100 Peter J. Holzer wrote:
>
>> On 2023-10-29 09:21:46 -0400, pf@pfortin.com wrote:
>>> These are all static tables. Does PG maintain a table row count so as to
>>> avoid having to count each time?
>> No. To count the rows in a table, Postgres has to actually read the
>> whole table (or an index, if a suitable index (e.g. a primary key)
>> exists).
> Am I correct to assume count(fieldname) would only load that column for
> counting?
The OS reads (chunks of) blocks, not individual bytes, and Postgresql is
record oriented, not column-oriented.
> In other words: we should be specific (avoid "*") in general?
No.
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SOLVED?] Re: Disk wait problem... not hardware...
@ 2023-10-29 17:03 Adrian Klaver <adrian.klaver@aklaver.com>
parent: pf@pfortin.com
2 siblings, 0 replies; 6+ messages in thread
From: Adrian Klaver @ 2023-10-29 17:03 UTC (permalink / raw)
To: pf@pfortin.com; pgsql-general@lists.postgresql.org
On 10/29/23 09:45, pf@pfortin.com wrote:
> On Sun, 29 Oct 2023 16:16:05 +0100 Peter J. Holzer wrote:
>
>> On 2023-10-29 09:21:46 -0400, pf@pfortin.com wrote:
>>> These are all static tables. Does PG maintain a table row count so as to
>>> avoid having to count each time?
>>
>> No. To count the rows in a table, Postgres has to actually read the
>> whole table (or an index, if a suitable index (e.g. a primary key)
>> exists).
>
> Am I correct to assume count(fieldname) would only load that column for
> counting? In other words: we should be specific (avoid "*") in general?
Read:
https://wiki.postgresql.org/wiki/Slow_Counting
>>> WB is setup to:
>>> * autoload table row count
>>> * autoload table data (restricted with LIMIT)
>>
>> Maybe WB can be set up to get n_live_tup instead of the real row count?
So basically you are dealing with a client issue.
>
> It appears this is hard-coded [on/off only]; but I thought I saw the WB
> author post on this list recently...
>
>> hp
>>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SOLVED?] Re: Disk wait problem... not hardware...
@ 2023-10-30 09:53 Peter J. Holzer <hjp-pgsql@hjp.at>
parent: pf@pfortin.com
2 siblings, 0 replies; 6+ messages in thread
From: Peter J. Holzer @ 2023-10-30 09:53 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2023-10-29 12:45:08 -0400, pf@pfortin.com wrote:
> On Sun, 29 Oct 2023 16:16:05 +0100 Peter J. Holzer wrote:
> >However, the table statistics contain an estimate for the number of
> >rows:
> >
> >hjp=> select schemaname, relname, n_live_tup from pg_stat_user_tables order by 3 desc;
> >╔════════════╤═══════════════════════════╤════════════╗
> >║ schemaname │ relname │ n_live_tup ║
> >╟────────────┼───────────────────────────┼────────────╢
> >║ public │ ncvhis_2016_12_03 │ 9999977 ║
> >║ public │ random_test │ 1000000 ║
> >║ public │ beislindex │ 351 ║
> >║ public │ delivery │ 6 ║
> >...
>
> Are there prerequisites for this query?
> Every (264) n_live_tup returned was zero... though, VERY fast.. :)
You need to ANALYZE the tables. Autovacuum should do this automatically
when it detects that a table has changed "enough" since the last
analyze, but for slowly growing tables that can sometimes take a long
time (indeed, I did an "analyze beislindex" just before the query above
because it showed only 25 tuples and I knew that wasn't right).
A count of 0 for a large table is weird, though.
Unless ... did you do a major version upgrade recently? That nukes the
statistics and you have to analyze everything again. Also, I'm not sure
if pg_restore triggers an analyze.
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] 6+ messages in thread
end of thread, other threads:[~2023-10-30 09:53 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-29 13:21 [SOLVED?] Re: Disk wait problem... not hardware... pf@pfortin.com
2023-10-29 15:16 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-10-29 16:45 ` pf@pfortin.com
2023-10-29 16:51 ` Re: [SOLVED?] Re: Disk wait problem... not hardware... Ron <ronljohnsonjr@gmail.com>
2023-10-29 17:03 ` Re: [SOLVED?] Re: Disk wait problem... not hardware... Adrian Klaver <adrian.klaver@aklaver.com>
2023-10-30 09:53 ` Peter J. Holzer <hjp-pgsql@hjp.at>
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