Subject: Re: pgdump slowness
To: pgsql-admin@lists.postgresql.org
References: 
 <CAAj4MFGc3voQYd850tRH5QeT=3MZ3tmz4QS2Riaz12cHU5RVWg@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <306515fd-e7eb-77ea-d638-411d6ca7a4be@gmail.com>
Date: Thu, 12 Sep 2019 10:52:48 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.8.0
MIME-Version: 1.0
In-Reply-To: 
 <CAAj4MFGc3voQYd850tRH5QeT=3MZ3tmz4QS2Riaz12cHU5RVWg@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------BA903B28C168448A8A21F67E"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------BA903B28C168448A8A21F67E
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

On 9/12/19 10:35 AM, Dhandapani Shanmugam wrote:
> Hi Guys,
>
> We are having wired issue. pg_dump taking more time to backup 320 GB of 
> table,the table has bytea data type with TOAST table associated it. Please 
> let me know, if this is the expected behavior of pg_dump or do i need to 
> tune any of the parameters. I tried to run pg_dump with parallel -j option 
> , still no improvement and there is no lock on the table as well. any 
> inputs are welcome

It took 16 hours to dump a 3TB v8.4 database that's full of bytea fields, 
using the 9.6 pg_dump with 8 threads.  This was from one DC to another 
*across a 10GB WAN link*.

Note that I *disabled compression* because the bytea fields contained 
non-compressible images.  Thus, the dump size was 2.2x larger than the 
actual database.

With compression enabled, the dump took a *lot* longer (though I didn't 
record those numbers).

Ron
-- 
Angular momentum makes the world go 'round.

--------------BA903B28C168448A8A21F67E
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    On 9/12/19 10:35 AM, Dhandapani Shanmugam wrote:<br>
    <blockquote type="cite"
cite="mid:CAAj4MFGc3voQYd850tRH5QeT=3MZ3tmz4QS2Riaz12cHU5RVWg@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">Hi Guys,<br>
        <br>
        We are having wired issue. pg_dump taking more time to backup
        320 GB of table,the table has bytea data type with TOAST table
        associated it. Please let me know, if this is the expected
        behavior of pg_dump or do i need to tune any of the parameters. 
        I tried to run pg_dump with parallel -j option , still no
        improvement and there is no lock on the table as well. any
        inputs are welcome<br>
      </div>
    </blockquote>
    <br>
    It took 16 hours to dump a 3TB v8.4 database that's full of bytea
    fields, using the 9.6 pg_dump with 8 threads.  This was from one DC
    to another <b>across a 10GB WAN link</b>.<br>
    <br>
    Note that I <b>disabled compression</b> because the bytea fields
    contained non-compressible images.  Thus, the dump size was 2.2x
    larger than the actual database.<br>
    <br>
    With compression enabled, the dump took a <b>lot</b> longer (though
    I didn't record those numbers).<br>
    <br>
    Ron<br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------BA903B28C168448A8A21F67E--