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 1q5Fir-0006hJ-HM for pgsql-general@arkaria.postgresql.org; Sat, 03 Jun 2023 01:06:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q5Fiq-0004Zt-B3 for pgsql-general@arkaria.postgresql.org; Sat, 03 Jun 2023 01:06:24 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q5Fiq-0004Z1-07 for pgsql-general@lists.postgresql.org; Sat, 03 Jun 2023 01:06:24 +0000 Received: from mail-yw1-x1134.google.com ([2607:f8b0:4864:20::1134]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q5Fij-003LAs-Bx for pgsql-general@postgresql.org; Sat, 03 Jun 2023 01:06:22 +0000 Received: by mail-yw1-x1134.google.com with SMTP id 00721157ae682-565a63087e9so27948647b3.2 for ; Fri, 02 Jun 2023 18:06:17 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1685754376; x=1688346376; 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=1VJW05JbxHhdJDVsgRTCoKPmqHOtrCOLwLzM45pTiIY=; b=oS4GuDfg7dmG0Qlb+Lc1PknIKn/nVe2SLl/wyE6+G2IrAHgCS0BdQfgd7NzVZYWWD9 YHL+vO9ku6zB5myRKRgl8v7tZ8UR1wRvdXsdDLyJUgzxcz7P4pDtU++iD1zCDwLwiWss UTQT/8yG8inWZfmSamUJ5fe73EUAv1exTezfHktdhW8TeKzx9e9zq+VL83laItdodByX 0Z7mwRMkUNDHR4n0/CNSzeQ0EwGoQ/IE1exLBA2PACWIdWiNJ7q2CXCDgWE4mH4MyoY6 2BfhJal/uIuo4sEcbMa7KlQowI93HvreP7VY/15a1E+LVMtZMyIpEuYo1rbBkQwu2+XU ZvLg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1685754376; x=1688346376; 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=1VJW05JbxHhdJDVsgRTCoKPmqHOtrCOLwLzM45pTiIY=; b=YejkRZ4B91wh+NdVz0n2PUJQB8iQHqQJaDbfn4a8qa6D9xGGL0rxwD5lWLVAWkVr4X eNdmCvUCCfE/hxAPQhj2yMitLWVBvwr5a8QFLmZ8DbpvNJpFG0oQCpwRlNgup1WIao7C yYek65GxXpL9fVvxRnjIkafXVFln2VWe+9VpPXnaDSb2QsTkeVuK/OxlAyTFMcCHorrZ BYjnR2xmjJXb9B4D7UJJvp3OVlnrk+pPpCNPsmGh/H3c10oDjHEZ3Lz1LWb2mEVFNT6z qDE9v7arlJPCvX6/CfJG0EGHYjml+u+qO5noGVz/0bWY+WbVENupCvR/57GwKPHCJV52 Rg2Q== X-Gm-Message-State: AC+VfDydc0Qqf9C92M/QcmJ15rXGNJc4YRTDbhF85r4POSHByfZ9c+8t 0N9T8dJG/vascpqP552WYEgi1MJRrtI= X-Google-Smtp-Source: ACHHUZ4vNffF/qCvnNRHb0DZ/pWsgncW/tVHtB4HTZnnDSxazaFI5pvzOhQfPboFZalV/kBenRy3dQ== X-Received: by 2002:a0d:d3c2:0:b0:561:4dc7:ff41 with SMTP id v185-20020a0dd3c2000000b005614dc7ff41mr1893857ywd.23.1685754376204; Fri, 02 Jun 2023 18:06:16 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:d816:fcbf:df7e:7054? ([2600:1700:bd2c:b810:d816:fcbf:df7e:7054]) by smtp.googlemail.com with ESMTPSA id j198-20020a8192cf000000b00568cbb028c6sm999373ywg.85.2023.06.02.18.06.15 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 02 Jun 2023 18:06:15 -0700 (PDT) Message-ID: <605d689c-7f3c-6ce6-1cfd-8a1511206dec@gmail.com> Date: Fri, 2 Jun 2023 20:06:15 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.10.0 Subject: Re: pg_upgrade and schema complexity... Content-Language: en-US To: pgsql-general References: <6185dd27-b3d3-b594-33fc-58345df79a13@gmail.com> <9648047c-1895-2480-c1af-164672a3188e@aklaver.com> From: Ron In-Reply-To: <9648047c-1895-2480-c1af-164672a3188e@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 6/2/23 19:58, Adrian Klaver wrote: > On 6/2/23 17:44, Ron wrote: >> Ran into this when upgrading from 13.11 to 15.3... >> >> The pg_restore phase failed with "ERROR: out of shared memory", and >> recommended that I increase max_locks_per_transaction. Doing so let the >> process run to completion. >> >> It took 12.5 minutes to upgrade a 13GB instance.  Soon after, I upgraded >> a 78GB cluster, and it only took 3.1 minutes. > > Where/how did you measure  those sizes? Does it really matter? START_SECS=$(date +"%s") pg_upgrade ... FINISH_SECS=$(date +"%s") ET=`echo "scale=2;(${FINISH_SECS} - ${START_SECS})/60" | bc` date +"%F %T pg_upgrade finished.  Elapsed time: ${ET} minutes." (Text copied between air-gapped computers, so there might be errors.) >> >> (Both are VMs (same number of CPUs and RAM) connected to the same SAN.) >> >> A "pg_dump --schema-only" of the two systems shows that the >> small-but-slow schema is 5.9M lines. > > Anything special you are doing in this cluster to create all those lines? I do nothing; the schema is provided by the vendor. > > What is the line count for the other instance? 227K rows. >> >> Is this to be expected of such a huge schema? >> -- Born in Arizona, moved to Babylonia.