Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hTU5l-0001qW-GN for pgsql-admin@arkaria.postgresql.org; Wed, 22 May 2019 16:27:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hTU5j-0006eZ-Vv for pgsql-admin@arkaria.postgresql.org; Wed, 22 May 2019 16:27:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hTU5j-0006e5-Kp for pgsql-admin@lists.postgresql.org; Wed, 22 May 2019 16:27:47 +0000 Received: from mail-ot1-x333.google.com ([2607:f8b0:4864:20::333]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hTU5g-00073t-PL for pgsql-admin@lists.postgresql.org; Wed, 22 May 2019 16:27:47 +0000 Received: by mail-ot1-x333.google.com with SMTP id i8so2620450oth.10 for ; Wed, 22 May 2019 09:27:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=hTn7REXGnlHC/BZyAaQaq/PR6ntmumX8uglx2P9jNg8=; b=PW0jJBvZ69JfZhHm7ZuWzojHPjXu/bdaoxKVbYFJqkPzShzVOAxEbRBhkJ7cg1ndue QIgHX4bsw4RxRDUrRKHDupA3xhpT56HhFqQ4CQzzMkU6LecDV/6GtPYtiTB+cMPsOhY3 NkE28t5EbVUr38M015sSL9Z5U928P8B7nQFXitKOOHa1J59VWfB25qCgjW752Z+o3iLc zhloHKJ7ihXf8b9Yrb3LVSWRtGZaHz9GRlVuDrt/VX7K9FFLnn7Z2NIYJ9TCkZ8gfLtI /HvBv/Dj3l0tyHC108c4zqDu0s+WmHMmveIZhD6EbsJdJNhd6YHYGjQtyrAAOdylnw/P Vurw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=hTn7REXGnlHC/BZyAaQaq/PR6ntmumX8uglx2P9jNg8=; b=pZGo//JWlm6TvQd4ERCWrpBn0sFZ6bfnA36ytOT2xQqfPyhPUefR9LiYPYrVSvg82G qmibQjoRSG9ZeISGoK0uw1ygEUCzWihozLLQMOHX7JZnq22hxxgnqsrfe5E4i2vivt+s jvfEvdh8YOprWk3qmlF+phFgpX9QS+TN/6g503HzpeaqP3Lrl+ZTB6JOsjRON9lnRsdC Mn62fWI5lACVoC6tNrEsBYOGcueIid6PVBB8FNMcSPoahClPTRGFQhMKTWvqRWXdEf70 M3xsSCYiLxaoTS066rFvEXty83Hma/iFkUEl2qEU2J1Fbh9iv/P7CfBN95wlaDEv4yCA Q4xA== X-Gm-Message-State: APjAAAXy19qCkmpbDOermu5vhFL8LxaXCHSRj4+oaoygRXjts2vR9E7W U6OSaLBxk8SQAvXfEf0CiJViGS/jLtA= X-Google-Smtp-Source: APXvYqyiRj8y1LKZYRkA3Tsi4sEaCRTLSZ8yVeDsHvGx/mNTrt363JalpauAbc3JqIoCvZllv5ROyw== X-Received: by 2002:a9d:71de:: with SMTP id z30mr32341369otj.323.1558542462228; Wed, 22 May 2019 09:27:42 -0700 (PDT) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id 19sm7409868otf.72.2019.05.22.09.27.40 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 22 May 2019 09:27:41 -0700 (PDT) Subject: Re: pg_restore takes more time on creation of rules To: pgsql-admin@lists.postgresql.org References: <20190522154102.zpvieebklnvis5u3@development> <3a350cb9-aeca-e858-2c8b-3481080d1dcf@gmail.com> From: Ron Message-ID: <7b2dfc1b-bd47-d800-4465-2614a07ffd12@gmail.com> Date: Wed, 22 May 2019 11:27:40 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.6.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------98D749102258AC7BD4D23451" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------98D749102258AC7BD4D23451 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Not looking for errors, but redirecting it to a file for posterity. Were/are the rules being created, but just slowly? On 5/22/19 11:02 AM, Mariel Cherkassky wrote: > Basically if I didnt redirect stderr i should be redirected to the screen > ? I didnt see any errors, I used -v (verbose) when I run the restore, and > I see that it restores the rules (but a lot of them..) > > ‫בתאריך יום ד׳, 22 במאי 2019 ב-19:01 מאת ‪Ron‬‏ <‪ronljohnsonjr@gmail.com > ‬‏>:‬ > > I'd redirect stderr to a file and tail it to monitor progress. > > On 5/22/19 10:44 AM, Mariel Cherkassky wrote: >> By rules I mean DB rules (simillar to triggers but different) >> >> ‫בתאריך יום ד׳, 22 במאי 2019 ב-18:41 מאת ‪Tomas Vondra‬‏ >> <‪tomas.vondra@2ndquadrant.com ‬‏>:‬ >> >> On Wed, May 22, 2019 at 06:26:49PM +0300, Mariel Cherkassky wrote: >> >Hey, >> >I'm trying to restore a cluster (9.2) from 3 binary dumps >> (pg_dump -Fc). >> >Each dump contains only one database. >> >The sizes : >> >A-10GB >> >B-20GB >> >C-5GB. >> > >> >For unclear reason the restore of the third database is taking >> alot of >> >time. It isnt stuck but it continues creating db rules. This >> database has >> >more then 400K rules. >> > >> >> What do you mean by "rules"? >> >> >I changed a few postgresql.conf parameters : >> >shared_buffers = 2GB >> >effective_cache_size = 65GB >> >checkpoint_segments =20 >> >checkpoint_completion_target = 0.9 >> >maintenance_work_mem = 10GB >> >checkpoint_timeout=30min >> >work_mem=64MB >> >autovacuum = off >> >full_page_writes=off >> >wal_buffers=50MB >> > >> >my machine has 31 cpu and 130GB of ram. >> > >> >Any idea why the restore of the two dbs takes about 15 minutes >> while the >> >third db which is the smallest takes more than 1 hour ?  I >> restore the >> >dump with pg_restore with 5 jobs (-j). >> > >> >> Well, presumably the third database has complexity in other places, >> possibly spending a lot of time on CPU, while the other databases >> don't >> have such issue. >> >> What would help is a CPU profile, e.g. from perf. >> >> >> regards >> >> -- >> Tomas Vondra http://www.2ndQuadrant.com >> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >> > > -- > Angular momentum makes the world go 'round. > -- Angular momentum makes the world go 'round. --------------98D749102258AC7BD4D23451 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
Not looking for errors, but redirecting it to a file for posterity.

Were/are the rules being created, but just slowly?

On 5/22/19 11:02 AM, Mariel Cherkassky wrote:
Basically if I didnt redirect stderr i should be redirected to the screen ? I didnt see any errors, I used -v (verbose) when I run the restore, and I see that it restores the rules (but a lot of them..)

‫בתאריך יום ד׳, 22 במאי 2019 ב-19:01 מאת ‪Ron‬‏ <‪ronljohnsonjr@gmail.com‬‏>:‬
I'd redirect stderr to a file and tail it to monitor progress.

On 5/22/19 10:44 AM, Mariel Cherkassky wrote:
By rules I mean DB rules (simillar to triggers but different)

‫בתאריך יום ד׳, 22 במאי 2019 ב-18:41 מאת ‪Tomas Vondra‬‏ <‪tomas.vondra@2ndquadrant.com‬‏>:‬
On Wed, May 22, 2019 at 06:26:49PM +0300, Mariel Cherkassky wrote:
>Hey,
>I'm trying to restore a cluster (9.2) from 3 binary dumps (pg_dump -Fc).
>Each dump contains only one database.
>The sizes :
>A-10GB
>B-20GB
>C-5GB.
>
>For unclear reason the restore of the third database is taking alot of
>time. It isnt stuck but it continues creating db rules. This database has
>more then 400K rules.
>

What do you mean by "rules"?

>I changed a few postgresql.conf parameters :
>shared_buffers = 2GB
>effective_cache_size = 65GB
>checkpoint_segments =20
>checkpoint_completion_target = 0.9
>maintenance_work_mem = 10GB
>checkpoint_timeout=30min
>work_mem=64MB
>autovacuum = off
>full_page_writes=off
>wal_buffers=50MB
>
>my machine has 31 cpu and 130GB of ram.
>
>Any idea why the restore of the two dbs takes about 15 minutes while the
>third db which is the smallest takes more than 1 hour ?  I restore the
>dump with pg_restore with 5 jobs (-j).
>

Well, presumably the third database has complexity in other places,
possibly spending a lot of time on CPU, while the other databases don't
have such issue.

What would help is a CPU profile, e.g. from perf.


regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Angular momentum makes the world go 'round.

--
Angular momentum makes the world go 'round.
--------------98D749102258AC7BD4D23451--