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 1q5RgB-00030m-Sp for pgsql-general@arkaria.postgresql.org; Sat, 03 Jun 2023 13:52:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q5RfA-0002qJ-At for pgsql-general@arkaria.postgresql.org; Sat, 03 Jun 2023 13:51:24 +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 1q5Rf9-0002qA-UO for pgsql-general@lists.postgresql.org; Sat, 03 Jun 2023 13:51:24 +0000 Received: from mail-yw1-x1135.google.com ([2607:f8b0:4864:20::1135]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q5Rf0-003tcM-1V for pgsql-general@postgresql.org; Sat, 03 Jun 2023 13:51:21 +0000 Received: by mail-yw1-x1135.google.com with SMTP id 00721157ae682-565c7399afaso32970467b3.1 for ; Sat, 03 Jun 2023 06:51:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1685800273; x=1688392273; 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=WwTg94FWwHIE9Qph595NUJ0XRvo8v04C00myJoiJFgY=; b=Vlgz7eyqf6wYgo8NPFlGD303Ig2Y6/2+UWG47gLz/nUiV3vRPlY7xH0pcPlzSE885V 8ZuEqClNySFujSKNoTCFk0s/bWtkR+l9zOoT3Irl9Xqrc73bIBlc7kzz3l+Oqe9GuzMq rL080YTBVBlEH2SqcZqTmIj/kIs6rBrdIyhdvo4ScoOJLqYkf8hPpqM0Id14lmvcUJM4 SueY+4soeCYaXa9B8sq2qeMKF8UhCxQznlTHPo9Z+h2bhe46gJurJo586L4jxi+YKufD gkxCohfnYzBAto1TxByPj4jVg2Zrbffb5aVpZt6W3Jsc6gfIMFbykLY/c8yjERwd7A4r WbXA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1685800273; x=1688392273; 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=WwTg94FWwHIE9Qph595NUJ0XRvo8v04C00myJoiJFgY=; b=lh1U9UvQy3mnx5sVDc7HOsgKlotgcjU/H++Nfnj/QaoeMRDJ13gCp5VKrzpGGkK2XP j/BwIIrqVCMPEpXG1IUsnvUmeBPrVQYDjaco2MmtzErptOnEIHiLkaXcWyQZDfwyu7Iw T845YulnDYfJTU06Y5Au1a6Ozq1JYGk5SDMhNtOQZ5S9C3zHRiZ5/Ye+9t8RY3gN6N+M tFl/TczMsnhF7Oa8Ckn+fQJrOMVBvTQwfjQkiqdXQVpyiTqWiRZjT/h8elpp6Z/ELgH9 MaY+NtiLA7ElHrEll9z9VMgb7D8S3qDO5C9JNJ207e/fo1qRbyLwNwf3KF38gpK3HyzL NeUA== X-Gm-Message-State: AC+VfDy8ifwKr3QHMp+PQDtZ2MhO3ls6JCHk8+zvHOsWHvYiz6OEFbBP IiXc/mQ1afmkENv4Ivl7rkClSmrji9o= X-Google-Smtp-Source: ACHHUZ6C9dVJjeScAjSfbj6ZadUO7zYgrTKJSs+k3Q3reyUA6dv4SV7FtgOyQ0pKPkcmaSU6u2p3Tw== X-Received: by 2002:a0d:d882:0:b0:55d:aff9:975b with SMTP id a124-20020a0dd882000000b0055daff9975bmr3493887ywe.12.1685800272834; Sat, 03 Jun 2023 06:51:12 -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 o124-20020a0dfe82000000b00565d9a8d976sm1518877ywf.44.2023.06.03.06.51.12 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 03 Jun 2023 06:51:12 -0700 (PDT) Message-ID: Date: Sat, 3 Jun 2023 08:51:12 -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> <605d689c-7f3c-6ce6-1cfd-8a1511206dec@gmail.com> <0f1f8153-c1c9-8116-5500-dbca3bab6b50@aklaver.com> From: Ron In-Reply-To: <0f1f8153-c1c9-8116-5500-dbca3bab6b50@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 21:22, Adrian Klaver wrote: > On 6/2/23 18:06, Ron wrote: >> 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." > > Unless I'm not mistaken the above is how the elapsed time was measured. I > was looking for the procedure for determining the size. du -cm -d1 $DATA13 du -cm -d1 $DATA15 > >> >> (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. > > Alright so it is not your design, but you do have an idea of what is in > the database correct? "In" like the /purpose/ of the database?  Sure. "In" like the /design/ of the database?  No; that's all on the vendor. > > >> >>> >>> What is the line count for the other instance? >> >> 227K rows. > -- Born in Arizona, moved to Babylonia.