Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eMvZn-0008O1-3G for pgsql-performance@arkaria.postgresql.org; Thu, 07 Dec 2017 12:46:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eMvZm-00052d-N7 for pgsql-performance@arkaria.postgresql.org; Thu, 07 Dec 2017 12:46:54 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eMvZl-00052S-Qc for pgsql-performance@lists.postgresql.org; Thu, 07 Dec 2017 12:46:54 +0000 Received: from gproxy6-pub.mail.unifiedlayer.com ([67.222.39.168]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eMvZi-0002rc-Ib for pgsql-performance@lists.postgresql.org; Thu, 07 Dec 2017 12:46:52 +0000 Received: from cmgw3 (unknown [10.0.90.84]) by gproxy6.mail.unifiedlayer.com (Postfix) with ESMTP id 2CEBE1E06E8 for ; Thu, 7 Dec 2017 05:46:47 -0700 (MST) Received: from host214.hostmonster.com ([74.220.215.214]) by cmgw3 with id j0mi1w00i4e7MuJ010mlVN; Thu, 07 Dec 2017 05:46:47 -0700 X-Authority-Analysis: v=2.2 cv=XM9AcUpE c=1 sm=1 tr=0 a=E7lA7DdVRVhKYBNJxJVZOg==:117 a=E7lA7DdVRVhKYBNJxJVZOg==:17 a=ocR9PWop10UA:10 a=r77TgQKjGQsHNAKrUKIA:9 a=UNOIEoGMAAAA:8 a=pWqpWVq2RKLhxegdFrcA:9 a=pILNOxqGKmIA:10 a=eGGdZ-3ClheFOeMZqLIA:9 a=OUchzxHiO1umh9n7:21 a=_W_S_7VecoQA:10 a=m5_vWMB95aWq4UX6iu8c:22 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=gusw.net; s=default; h=Content-Type:In-Reply-To:MIME-Version:Date:Message-ID:From: References:To:Subject:Sender:Reply-To:Cc:Content-Transfer-Encoding:Content-ID :Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To: Resent-Cc:Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe :List-Post:List-Owner:List-Archive; bh=EjfzmfHU5zjt5kMfJ3BDc8T3hJSpYuO8ul+PQxfR2W0=; b=JC2j1mycWZLyaJ7E3ZQc6MOTw9 n0QaDDqbhvXQkq2V1ALW9xBzXNbE+iPjEjcZbMEiSlklDmH6zCCtqdeU6zPRfkeDCDoeAuVra1Z8W cB5gHOi7M9v6ADppwl8wWdamp; Received: from [191.7.145.23] (port=53288 helo=[192.168.9.4]) by host214.hostmonster.com with esmtpsa (TLSv1.2:ECDHE-RSA-AES128-GCM-SHA256:128) (Exim 4.89) (envelope-from ) id 1eMvZZ-004EJC-OQ for pgsql-performance@lists.postgresql.org; Thu, 07 Dec 2017 05:46:42 -0700 Subject: Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade To: pgsql-performance@lists.postgresql.org References: <8F33D0C7-E12E-4996-990C-3CF0C5ED0437@filmlance.se> <0bc6cc72-7d7d-b157-4824-7a6ea2985a60@hipay.com> From: Gunther Message-ID: Date: Thu, 7 Dec 2017 07:46:36 -0500 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 Thunderbird/52.5.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------71B17B8C20C3160D1567873F" Content-Language: en-US X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - host214.hostmonster.com X-AntiAbuse: Original Domain - lists.postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - gusw.net X-BWhitelist: no X-Source-IP: 191.7.145.23 X-Exim-ID: 1eMvZZ-004EJC-OQ X-Source: X-Source-Args: X-Source-Dir: X-Source-Sender: ([192.168.9.4]) [191.7.145.23]:53288 X-Source-Auth: gunther+pragmaticdata.com X-Email-Count: 1 X-Source-Cap: cHJhZ21hdDE7cHJhZ21hdDE7aG9zdDIxNC5ob3N0bW9uc3Rlci5jb20= X-Local-Domain: yes List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------71B17B8C20C3160D1567873F Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit I confess I don't do dump or any backups much other than file system snapshots. But when I do, I don't like how long it takes. I confess my database is big, I have about 200 GB. But still, dumping it should not take 48 hours (and running) while the system is 75% idle and reads are at 4.5 MB/s when the system sustains over 100 MB/s during processing of table scan and hash join queries. Something is wrong with the dump thing. And no, it's not SSL or whatever, I am doing it on a local system with local connections. Version 9.5 something. regards, -Gunther On 11/23/2017 4:26, Henrik Cednert (Filmlance) wrote: > > >> On 22 Nov 2017, at 22:07, Patrick KUI-LI > > wrote: >> >> Hello, >> >> I had this behaviors when the upgraded pg 9.5 was on ssl mode by default. >> >> So i deactivated ssl mode in postgresql.conf. That's all. >> >> Regards, >> >> Patrick >> > > > Hello > > And you just uncommented the  'ssl = off' line in the config for this? > > Is this default behaviour different from 8.4? Is there a 'show running > config' for pgsql? > > I tried that in the test vm and didn't really give me a significant > difference. > > COMPRESSION LEVEL: 6, SSL ON > real82m33.744s > user60m55.069s > sys3m3.375s > > COMPRESSION LEVEL: 6, SSL OFF > real76m31.083s > user61m23.282s > sys1m23.341s --------------71B17B8C20C3160D1567873F Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 8bit

I confess I don't do dump or any backups much other than file system snapshots.

But when I do, I don't like how long it takes.

I confess my database is big, I have about 200 GB. But still, dumping it should not take 48 hours (and running) while the system is 75% idle and reads are at 4.5 MB/s when the system sustains over 100 MB/s during processing of table scan and hash join queries.

Something is wrong with the dump thing. And no, it's not SSL or whatever, I am doing it on a local system with local connections. Version 9.5 something.

regards,
-Gunther


On 11/23/2017 4:26, Henrik Cednert (Filmlance) wrote:


On 22 Nov 2017, at 22:07, Patrick KUI-LI <pkuili@hipay.com> wrote:

Hello,

I had this behaviors when the upgraded pg 9.5 was on ssl mode by default.

So i deactivated ssl mode in postgresql.conf. That's all.

Regards,

Patrick



Hello

And you just uncommented the  'ssl = off' line in the config for this?  

Is this default behaviour different from 8.4? Is there a 'show running config' for pgsql?

I tried that in the test vm and didn't really give me a significant difference. 

COMPRESSION LEVEL: 6, SSL ON
real 82m33.744s
user 60m55.069s
sys 3m3.375s

 
COMPRESSION LEVEL: 6, SSL OFF
real 76m31.083s
user 61m23.282s
sys 1m23.341s

--------------71B17B8C20C3160D1567873F--