Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qUHLl-00CIRS-7w for pgsql-general@arkaria.postgresql.org; Fri, 11 Aug 2023 01:54:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qUHLj-002h5o-4w for pgsql-general@arkaria.postgresql.org; Fri, 11 Aug 2023 01:53:59 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qUHLi-002h5g-Md for pgsql-general@lists.postgresql.org; Fri, 11 Aug 2023 01:53:59 +0000 Received: from mail-yw1-x1132.google.com ([2607:f8b0:4864:20::1132]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qUHLg-001iYZ-04 for pgsql-general@lists.postgresql.org; Fri, 11 Aug 2023 01:53:57 +0000 Received: by mail-yw1-x1132.google.com with SMTP id 00721157ae682-583b3939521so18053347b3.0 for ; Thu, 10 Aug 2023 18:53:55 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1691718835; x=1692323635; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=Xc85HIiVafTUw3aOtzA44AylXaTheFzFQWt7lIbyv9U=; b=Wbsf4UA9xi215tmGIHXqvv4KpKO78nR3AfxzJG6b6QBwK8i5D/sFEmUcbgwYI9agwk K4tKHci1j5spth19QuOPA+4/NoHUQGgi3tFm6EfML6RWw0DgQs/StPVZXo5w2WBdJeqo sfRd3bk53gSAWq8SamofSfXG5fiUVkTL89yCT0RNi6cmx0NX7fYdXJD9tYzQqJE+ZNkS d1bqXDdXTlfNuDNMyXaPLXrkoCUpqMT6bJyhVU0kTpHluuV8K2wafR9euXjq+ZNo3WWf FBCLgW1X6ev+ubplvsU7A5VO49imZjNIoYcdNhWIm2fOGn/ZeWejtXql6upq1I/suQzl WBew== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1691718835; x=1692323635; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=Xc85HIiVafTUw3aOtzA44AylXaTheFzFQWt7lIbyv9U=; b=CnAkcYqcrMomFTWXtNkFufcXU75ujH//RGJ2jVI0cgSLQj81YggaH8eG5GvDzgxE7N MGx1i0kQVD/QmEFbHbTSK7A8hBKw0BQh4pGQ1qeEVtN8C8+VQAfPKfNTGsACRvpblRXu mSCT2cmj4I84d/gOg500MC59kSdetyejmaSij8b7QD2Zt9KVn1vphv3krkRrRGOcIagN Vagoy//NDnfWOJg5wAMXzCv2Cihu5lENSrea7ohfLWuO/KXRyLWp9kM1e43Nr00Jc9kb 04/iya+1oF4Vat6Bzd7HzHgm0H8WjLp8jOPKfX92eUK3eTtMZzjOMUO0pW/Wx+kuBkek KkEA== X-Gm-Message-State: AOJu0YwMVvmOoZDtwNuPgBn9GDe38Uz8nL/I5ZCZeZDQ7Fw+CF6G9ngb iW+lBKsuMWqZsNHXAZp4vDuAoTJti6E= X-Google-Smtp-Source: AGHT+IHERrzjzg5rptIRXAptMF9XwFbpEQMk//PLSRR4l4Bgpn/mEsC04BXeUc9fcBUtfSIktA6cZg== X-Received: by 2002:a81:490c:0:b0:561:d1b0:3f7e with SMTP id w12-20020a81490c000000b00561d1b03f7emr632537ywa.44.1691718834926; Thu, 10 Aug 2023 18:53:54 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:6075:3f19:4ef7:e118? ([2600:1700:bd2c:b810:6075:3f19:4ef7:e118]) by smtp.googlemail.com with ESMTPSA id z133-20020a81658b000000b005772e9388cdsm716283ywb.62.2023.08.10.18.53.54 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 10 Aug 2023 18:53:54 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------FKeaN3G2v0tf9a6kGdRbzt5K" Message-ID: <72a34a05-47cb-1635-e56a-79d1f7082094@gmail.com> Date: Thu, 10 Aug 2023 20:53:54 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: pb with big volumes Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------FKeaN3G2v0tf9a6kGdRbzt5K Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 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 > -- Born in Arizona, moved to Babylonia. --------------FKeaN3G2v0tf9a6kGdRbzt5K Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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


--
Born in Arizona, moved to Babylonia.
--------------FKeaN3G2v0tf9a6kGdRbzt5K--