pg_dump incredibly slow dumping a single schema from a large db

From: Mike Roest <mike(dot)roest(at)replicon(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: pg_dump incredibly slow dumping a single schema from a large db
Date: 2012-03-30 15:51:58
Message-ID: CAE7ByhiroJbXUzAJMFE0mVhOOcyYLGONAj83dkrXFkG5-H-rZg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Hey Everyone,
I've got an interesting issue. We're running postgres 9.1.1 linux x64
centos 5.8

aspdata=# select version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-52), 64-bit

We currently have 1 DB we use for multiple independent tenant schemas. The
database size is current 56227005240 bytes as reported by pg_database_size.

There are 557 schemas each with about 1300 objects (760 tables 520 views).

We are using pg_dump to do backups of a single schema with a total size of
(5480448 bytes calculated with SELECT sum(pg_relation_size(schemaname ||
'.' || tablename))::bigint FROM pg_tables WHERE schemaname ='miketest';)

pg_dump -f /dumps/test.backup -Fc -n miketest aspdata

This dump is currently taking around 8 minutes. While dumping the pg_dump
process is using 100% of one core in the server (24 core machine). Doing a
-v pg_dump I found that the following stages are taking the majority of the
time

reading user_defined tables (2 minutes and 20 seconds)
reading dependency data (5 minutes and 30 seconds)

The size of the schema doesn't really seem to effect theses times are
almost identical for a 700 meg schema as well (obviously the data dump
portion takes longer with the bigger db)

During the reading user_defined tables the following query shows up for a
10-20 seconds then the pg_dump connection sits idle for the rest of the 2
minutes:

SELECT c.tableoid, c.oid, c.relname, c.relacl, c.relkind, c.relnamespace,
(SELECT rolname FROM pg_catalog.pg_roles WHERE oid = c.relowner) AS
rolname, c.relchecks, c.relhastriggers, c.relhasindex, c.relhasrules,
c.relhasoids, c.relfrozenxid, tc.oid AS
toid, tc.relfrozenxid AS tfrozenxid, c.relpersistence, CASE WHEN
c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS
reloftype, d.refobjid AS owning_tab, d.refobjsubid AS owning_col, (SELECT
spcname FROM pg_tablespace
t WHERE t.oid = c.reltablespace) AS reltablespace,
array_to_string(c.reloptions, ', ') AS reloptions,
array_to_string(array(SELECT 'toast.' || x FROM unnest(tc.reloptions) x),
', ') AS toast_reloptions FROM pg_class c LEFT JOIN pg_depend
d ON (c.relkind = 'S' AND d.classid = c.tableoid AND d.objid = c.oid AND
d.objsubid = 0 AND d.refclassid = c.tableoid AND d.deptype = 'a') LEFT JOIN
pg_class tc ON (c.reltoastrelid = tc.oid) WHERE c.relkind in ('r', 'S',
'v', 'c', 'f')
ORDER BY c.oid

During the reading dependency data the following queries show up for a few
seconds then the connection sits idle for the rest of the 5.5 minutes:
SELECT tableoid
, oid, typname, typnamespace, (SELECT rolname FROM pg_catalog.pg_roles
WHERE oid = typowner) AS rolname, typinput::oid AS typinput, typoutput::oid
AS typoutput, typelem, typrelid, CASE WHEN typrelid = 0 THEN ' '::"char"
ELSE (SELECT relk
ind FROM pg_class WHERE oid = typrelid) END AS typrelkind, typtype,
typisdefined, typname[0] = '_' AND typelem != 0 AND (SELECT typarray FROM
pg_type te WHERE oid = pg_type.typelem) = oid AS isarray FROM pg_type
SELECT classid,
objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p'
AND deptype != 'e' ORDER BY 1,2

Non production servers with less schemas don't seem to have any issue and
perform the same dump in under 10 seconds on much lower classed hardware.

Server Specs:

2 x Intel Xeon X5650
32 Gigs of Ram
DELL Perc H700 Controller
Data drive - 6XSAS2 15K in RAID10 FS: xfs
Log Drive - 2XSAS2 15K in RAID1 FS: xfs

There are 2 of these machine one master other slaved via streaming
replication over gigabit network.

Thanks

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2012-03-30 16:18:25 Re: pg_dump incredibly slow dumping a single schema from a large db
Previous Message leaf_yxj 2012-03-30 14:28:26 Re: double check the role has what's kind of the privilege? And the same for the objects. Thanks.

Browse pgsql-hackers by date

  From Date Subject
Next Message Marko Kreen 2012-03-30 15:52:47 Re: Speed dblink using alternate libpq tuple storage
Previous Message Robert Haas 2012-03-30 15:41:03 Re: HTTP Frontend? (and a brief thought on materialized views)