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