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 1qUGen-00CFkO-VO for pgsql-general@arkaria.postgresql.org; Fri, 11 Aug 2023 01:09:38 +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 1qUGel-002Lv1-Ch for pgsql-general@arkaria.postgresql.org; Fri, 11 Aug 2023 01:09:35 +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 1qUGek-002LuQ-Ui for pgsql-general@lists.postgresql.org; Fri, 11 Aug 2023 01:09:35 +0000 Received: from mail-oi1-x236.google.com ([2607:f8b0:4864:20::236]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qUGei-001i8i-HI for pgsql-general@lists.postgresql.org; Fri, 11 Aug 2023 01:09:34 +0000 Received: by mail-oi1-x236.google.com with SMTP id 5614622812f47-3a7aedc57ffso1217891b6e.2 for ; Thu, 10 Aug 2023 18:09:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1691716171; x=1692320971; h=content-transfer-encoding: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=5oUhHKKPwHTA1+8OQ89pUsW8EPpP9Qnu2VrbxYEK3nM=; b=iaWeQfvayBfrumU6xDgjzOQEgE9rt5I8pBk7d7vviTfsd1tMzkCAAiO7IDeJSdJyGA 8YXp3wsw4zp7a2K2SF7RbygZcgm/smA42R8LkLyz2pUcFXt9YyllocSRWtFlozLPkUcA crO4ae8RKZghZ8SwRJnXaBiCbE1B8GR2nio/VaJjfdxZ+LdNQoLGW03IioixemG3uR21 ILWDWt4/v2UHqdmHAofWKW7hAII1YbXkQKvWQHB4nYva2a8iU6J7yDQaFsN2/oNkaaSc mk0KMhfDpfTQpPa3woQ1p0dA5wi1ZqfI584AXBH+wLG2TFila2m2MMHMN/Bwpu4y5K4u uRZQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1691716171; x=1692320971; h=content-transfer-encoding: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=5oUhHKKPwHTA1+8OQ89pUsW8EPpP9Qnu2VrbxYEK3nM=; b=LJwADJTBETWpHTUFwLCGZR3I4jHcukuUOZcTiVdgHxPW+Ng3ps6cQntfkVXMEpE0EN W5QhOLGYL9Enwl9BbwIAwvHcblRBfSL3nWHVNGDeKa777A8xv26U0Dne0ubDJB6E/6/r B7xDx9hRZI1Sg8hmjs4V+jy2bswpz1evGTLb3qUAD5GQkminZYATUS6o1fwlwtH7+6gU vKkEksygijO4ZezQp6vA/2eoVHqzgAheeL9U6ifr+XVm9Tt/GWSEFWWAJussHtQ5TLus C1iK5S9BtCmVm2LI0tY1/+KPn8oRlhXxUAAvNEMEozBWj1/9dZACRM/lM9fUR573VSjB Xx7A== X-Gm-Message-State: AOJu0YznLF9uAj9BoS/48/yT9nPNcwEsEuGoSFhMXtr7G2GvsGFo4A7+ 5HjaV8wvn80YYn8EGhY64LGDHItLCRs= X-Google-Smtp-Source: AGHT+IF1DCenegsgBRk9YeZpXUrOWHaTa9LC+K45piPxLQnyG2ADKRLQzeSkebFnO09cvkoyN66/zw== X-Received: by 2002:a05:6358:3396:b0:134:c682:213f with SMTP id i22-20020a056358339600b00134c682213fmr688060rwd.31.1691716170907; Thu, 10 Aug 2023 18:09:30 -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 i6-20020a25d106000000b00d0b0bbe574asm652847ybg.44.2023.08.10.18.09.30 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 10 Aug 2023 18:09:30 -0700 (PDT) Message-ID: <0551ec91-ef1b-b796-6179-f191e3987d93@gmail.com> Date: Thu, 10 Aug 2023 20:09:29 -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: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 8/10/23 16:36, Marc Millas wrote: > Hi, > > I have a 15 TB db on postgres 14 (soon 15). > shared buffers is 32 GB. Does the system have 128GB AM? > > 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. What is work_mem set to? When were the tables last vacuumed and analyzed? Good index support?  (But that might not matter if every row in the table is in the GROUP BY.) Clustering the tables, and using BRIN indices might help. > > 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... -- Born in Arizona, moved to Babylonia.