pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: pb with big volumes
Date: Thu, 10 Aug 2023 20:53:54 -0500
Message-ID: <72a34a05-47cb-1635-e56a-79d1f7082094@gmail.com> (raw)
In-Reply-To: <CA+s62-P=_NCCm1c=D8bp3vBk4+y+OXYQcvXhUjYkzY_SGiDtQQ@mail.gmail.com>
References: <CADX_1aYU=XF1rzmq-YA244pBQtCsDYcYXs0KNzbkZ7M0A8YTCw@mail.gmail.com>
	<CA+s62-P=_NCCm1c=D8bp3vBk4+y+OXYQcvXhUjYkzY_SGiDtQQ@mail.gmail.com>

Wouldn't IO contention make for additive timings instead of exponential?

On 8/10/23 20:41, Adam Scott wrote:
> I think your concern is that 20 min + 30 min does not equal 3 hours.
>
> It might be natural to think the contention would, at max, be 50 min x 2 
> (1 hr 40 min).
>
> So what's going on?
>
> It seems disk I/O  is a primary suspect since you hint for an iostat 
> replacement inside of Postgres.
>
> If it is due to disk I/O the resolution will be to add RAID 0 SSDs at best.
>
> Consider looking at io stats on the container's persistent volumes.
>
> What is the pipe connecting the database server to the disks?  If it's 
> NAS, well that would explain it.
>
> HTH,
> Adam
>
>
>
> On Thu, Aug 10, 2023 at 2:37 PM Marc Millas <marc.millas@mokadb.com> wrote:
>
>     Hi,
>
>     I have a 15 TB db on postgres 14 (soon 15).
>     shared buffers is 32 GB.
>
>     It's a db with max 15 users and often less, and currently 1 or 2.
>     the biggest table have 133 partitions of 150M to 200M+ rows each.
>     lots of request access explicitly one of those.
>
>     When I, alone, run a query "reading" 15M buffers, it takes 20 minutes
>     (+-5minutes). inside the query there are 2 group by on a 200M rows
>     partition, with all the rows in each group by.
>     When a colleague run the same kind of request (not the same request,
>     but something reading roughly the same volume ) , on a different set
>     of data,   his request is completed in less than half an hour.
>     If we run our requests simultaneously... my request take hours. around
>     3 hours.
>
>     I am making a supposition that its some kind of "pumping" effect in
>     the cache.
>
>     I cannot have access to the underlying OS. I can, for sure, do some
>     copy xx from program 'some command',  but its a container with very
>     limited possibilities, not even 'ps'.
>     So I would like to monitor from inside the db (so without iostat and
>     the same)  the volumes of read that postgres do to the OS.
>     I did activate track_io_timing, but the volumes I get in the explain
>     analyze buffer are roughly the same alone or not alone. (the 15M
>     buffers told )
>     to my understanding, the volumes that are shown in pg_stat_database
>     are the useful ones ie. even if the db as to read it from disk more
>     than once. true ? or false ?
>
>     So.. either my supposition is not correct, and I will read with a lot
>     of interest other ideas
>     either its correct and I would like to know how to monitor this (in
>     the current context, installing a dedicated extension is not
>     impossible, but is a very boring process)
>
>     Thanks for your help :-)
>
>     regards,
>
>     PS: I know that providing the complete data model and the exact
>     requests can be considered mandatory, but when I change the request I
>     get the very same behaviour...
>
>
>
>     Marc MILLAS
>     Senior Architect
>     +33607850334
>     www.mokadb.com <http://www.mokadb.com;
>

-- 
Born in Arizona, moved to Babylonia.

view thread (9+ messages)  latest in thread

Message-ID: <72a34a05-47cb-1635-e56a-79d1f7082094@gmail.com>
Permalink:  ../72a34a05-47cb-1635-e56a-79d1f7082094@gmail.com/
Also on:    postgresql.org/message-id/72a34a05-47cb-1635-e56a-79d1f7082094@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: pb with big volumes
  In-Reply-To: <72a34a05-47cb-1635-e56a-79d1f7082094@gmail.com>
  References: <CADX_1aYU=XF1rzmq-YA244pBQtCsDYcYXs0KNzbkZ7M0A8YTCw@mail.gmail.com> <CA+s62-P=_NCCm1c=D8bp3vBk4+y+OXYQcvXhUjYkzY_SGiDtQQ@mail.gmail.com> <72a34a05-47cb-1635-e56a-79d1f7082094@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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