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
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. |
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) |