Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qHnMv-0006CQ-Bi for pgsql-general@arkaria.postgresql.org; Fri, 07 Jul 2023 15:27:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qHnMt-00012x-Q0 for pgsql-general@arkaria.postgresql.org; Fri, 07 Jul 2023 15:27:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qHnMt-00012o-DO for pgsql-general@lists.postgresql.org; Fri, 07 Jul 2023 15:27:35 +0000 Received: from mail-yb1-xb2e.google.com ([2607:f8b0:4864:20::b2e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qHnMo-002kh5-Rw for pgsql-general@lists.postgresql.org; Fri, 07 Jul 2023 15:27:34 +0000 Received: by mail-yb1-xb2e.google.com with SMTP id 3f1490d57ef6-c01e1c0402cso2060349276.0 for ; Fri, 07 Jul 2023 08:27:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1688743648; x=1691335648; 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=eXFAvCSlq2jxjM4DL27R73vmIob3FXE9dHZZ2Ja8OnI=; b=SMlL+tPolDPDPuKuxqYEIQXClpxx0KQciERb9fREG57im7zEyKPBgIF8Z4KBZfNdxp 7RywGi8E6edr4+NVW6QzjLkHNWbYPEq2VvaDBclBXRHvKfC9kJdF74lam7VGnmPpLfjF tWX/srsVkDzsbO2ehxQ1YuF4O4t+Drn6ul3FinLRyjAiF/o4aqjFO7Y/wQEc2LQeC+6g 41YGNSYVD9GhUr03PEAcODZkVH0frBN8xiY96FT/UpfknRJ7EtD+nAU6LKj2gC/yinrr 9RFiB9mw+d4RhmrA2TqqYlY8jBbj95AITQ1lJSrJR8wkoEdbBOhB92MUr28fPSgGfSS9 hv6A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1688743648; x=1691335648; 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=eXFAvCSlq2jxjM4DL27R73vmIob3FXE9dHZZ2Ja8OnI=; b=VH3TK+kbr8V7qEmZ7wCKBoliQSjMc2IuIJkNYy5JbAUVtkAWF9WbLYhuPrv+5lP7iM WLKm4mFAfJT92vEeNDs0OBifjG5xXYVLJBCFaNvqODgLPfeH+LJLODOXyk1KziWZAQ3r N07pkLyMjzvApVmJqPUhKvAIyIPkWhdFoKS/RLfMxCw3N8Em+TenKxJfTlbwKvNHwkoP z3SxOWbJ2iwLIzg+k+7Nakt7RHobsiEIOSucmiMmGzbWJYkL6JZYir8BwBOfUyuhHHaZ 8dBDbxdW9ysiHKfbHKZ7vurgLSXnG9NS3LAzW34Inn6PkIsRgiEPx44Adc6l3Ku5asZz uSSg== X-Gm-Message-State: ABy/qLZivGhEsXEQ6xboOvHC+ehKrWEdPHYLbQLBCCzJr/2CgGl61iUg k0jCHuBDlBHnVFjok/h6ol5CaWYBcMg= X-Google-Smtp-Source: APBJJlH4O0NUrGT/2bKUlWSG8fnjnO3Q2pLLEBkefmoNTdwcLjVTP2gn+eFHpGDExuVQ6/FdlHjYrA== X-Received: by 2002:a25:86:0:b0:c6e:a2b0:e53c with SMTP id 128-20020a250086000000b00c6ea2b0e53cmr1292518yba.62.1688743648517; Fri, 07 Jul 2023 08:27:28 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:bd3c:e5b9:912:994c? ([2600:1700:bd2c:b810:bd3c:e5b9:912:994c]) by smtp.googlemail.com with ESMTPSA id o201-20020a2541d2000000b00c64533e4e20sm984128yba.33.2023.07.07.08.27.28 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 07 Jul 2023 08:27:28 -0700 (PDT) Message-ID: Date: Fri, 7 Jul 2023 10:27:27 -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: track_activity_query_size max practical size? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <0d08dd4d-3860-e85d-984d-0ed676dbdf1e@gmail.com> <1a7063f7-1d7d-dbf4-a919-a55f733560f2@aklaver.com> <1cf118ea-91da-d9bf-2ed9-c079e2653964@aklaver.com> <922daef0-4093-d07c-4cf1-84c04fda8e5f@gmail.com> <2e3d60d4-24a3-884d-fec8-204de9635c67@aklaver.com> From: Ron In-Reply-To: <2e3d60d4-24a3-884d-fec8-204de9635c67@aklaver.com> 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 7/7/23 10:13, Adrian Klaver wrote: > On 7/7/23 07:58, Ron wrote: >> On 7/7/23 09:55, Adrian Klaver wrote: >>> On 7/7/23 07:51, Adrian Klaver wrote: >>>> On 7/7/23 07:42, Ron wrote: >>>>> We've got some Very Large Queries that take a long time. >>>> >>>> An EXPLAIN(ANALYZE BUFFERS) would go a long way here. >>>> >>>>> Even setting taqs to 10KB isn't adequate, so I want to significantly >>>>> bump it, but am >>>> >>>> It is before coffee here, so you will need to spell out what taqs is? >>> >>> Did I mention lack of coffee? I see  track_activity_query_size in the >>> subject. So the issue is what with the current setting? >> >> 10KB isn't enough to capture the whole query.  Are there any bad side >> effects of increasing it from 10KB to 48KB or 64KB? > > So to be clear the text of the query is large and not all of it is being > being shown in pg_stat_activity.query. I have never had the need to > increase that setting so all I can do is speculate. From the docs > 'Specifies the amount of memory reserved to store the text of the > currently executing command for each active session, ...'. So given a lot > of active sessions with each query being a large text value there could be > a side effect at some point. The bigger issue would seem to be 'This > parameter can only be set at server start'. So you are looking at > starting/stopping the server to find the sweet spot. Restarting is something I can do during a quiet point after the bulk of the day's work is complete, but before the nightly backups. > >> >>> >>>> >>>>> concerned about side effects of setting it to 48KB or even 64KB. >>>>> >>>> >>> >> > -- Born in Arizona, moved to Babylonia.