pgdump slowness

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

pgdump slowness

Dhandapani Shanmugam
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

-D
Reply | Threaded
Open this post in threaded view
|

Re: pgdump slowness

nikhil raj
Hi Dhandapani,

That is the system  configuration and version  and how many parallel job you are running in the command. 

Please let know the details.

On Thu, 12 Sep 2019, 9:05 pm Dhandapani Shanmugam, <[hidden email]> 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

-D
Reply | Threaded
Open this post in threaded view
|

Re: pgdump slowness

Ron-2
In reply to this post by Dhandapani Shanmugam
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.
Reply | Threaded
Open this post in threaded view
|

Re: pgdump slowness

Dhandapani Shanmugam
In reply to this post by nikhil raj
Hi Nikhil,

I don't see any resource issues on server. the cpu shows 90% idle and the load of the server is very normal. I am using 6 parallel process. 

-D

On Thu, Sep 12, 2019 at 9:22 PM nikhil raj <[hidden email]> wrote:
Hi Dhandapani,

That is the system  configuration and version  and how many parallel job you are running in the command. 

Please let know the details.

On Thu, 12 Sep 2019, 9:05 pm Dhandapani Shanmugam, <[hidden email]> 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

-D
Reply | Threaded
Open this post in threaded view
|

Re: pgdump slowness

Ron-2
IO load?  Slow disks, low channel bandwidth, etc?

Is this a VM on an over-subscribed host?

On 9/12/19 10:54 AM, Dhandapani Shanmugam wrote:
Hi Nikhil,

I don't see any resource issues on server. the cpu shows 90% idle and the load of the server is very normal. I am using 6 parallel process. 

-D

On Thu, Sep 12, 2019 at 9:22 PM nikhil raj <[hidden email]> wrote:
Hi Dhandapani,

That is the system  configuration and version  and how many parallel job you are running in the command. 

Please let know the details.

On Thu, 12 Sep 2019, 9:05 pm Dhandapani Shanmugam, <[hidden email]> 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

-D

--
Angular momentum makes the world go 'round.