Thread: pg_dump resulting in excessive memory use by postmaster process
We’ve been using pg_dump and pg_restore for many years now and it has always worked well for us. However, we are currently undertaking a major db architecture to partition our tenant data into separate postgres schemas instead of storing all data in the public schema. When attempting to perform a pg_dump with the --schema-only option using our new db architecture it causes the postmaster process to consume massive amounts of memory until it is killed off (6GB+ used by a single postmaster process). Here are the details:
PG version: 9.2.5
Total number of postgres schemas: 248
Total number of relations across all schemas: 53,154
If I perform a --schema-only dump on a DB where there are only 11 schemas it succeeds with a dump file that is only 7.5mb in size. All of our tenant schemas have the same exact relations so things should scale linearly when more tenant schemas exist.
I should also mention that when performing these dumps there is absolutely no other DB activity occurring. Do you have any ideas why the excessive memory growth on the postmaster service is occurring when doing a pg_dump?
* Ben (ben.fyvie@champsoftware.com) wrote:
> is killed off (6GB+ used by a single postmaster process). Here are the
[...]
> Total number of relations across all schemas: 53,154
[...]
> I should also mention that when performing these dumps there is absolutely
> no other DB activity occurring. Do you have any ideas why the excessive
> memory growth on the postmaster service is occurring when doing a pg_dump?
At first blush, I'm suspicious of the system catalog cache which is
going to want to get every bit of attribute for every relation involved
and it looks like you've got quite a few here.
It seems like something which we could/should improve upon, but I'm not
sure who/what/when/where/how it'll get better.
Still, would it be possible for you to provide a self-contained test
case which shows this, so we can look at what's happening exactly and
verify where the fault lies?
Thanks,
Stephen
Attachment
When you say self-contained test case, what is it exactly that you're
looking for? A script that builds out a DB with hundreds of
schemas/relations, a pg_basebackup or something else?
Thanks!
Ben
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Stephen Frost
Sent: Monday, October 21, 2013 10:37 AM
To: Ben
Cc: 'PG-General Mailing List'
Subject: Re: [GENERAL] pg_dump resulting in excessive memory use by
postmaster process
* Ben (ben.fyvie@champsoftware.com) wrote:
> is killed off (6GB+ used by a single postmaster process). Here are
> the
[...]
> Total number of relations across all schemas: 53,154
[...]
> I should also mention that when performing these dumps there is
> absolutely no other DB activity occurring. Do you have any ideas why
> the excessive memory growth on the postmaster service is occurring when
doing a pg_dump?
At first blush, I'm suspicious of the system catalog cache which is going to
want to get every bit of attribute for every relation involved and it looks
like you've got quite a few here.
It seems like something which we could/should improve upon, but I'm not sure
who/what/when/where/how it'll get better.
Still, would it be possible for you to provide a self-contained test case
which shows this, so we can look at what's happening exactly and verify
where the fault lies?
Thanks,
Stephen
Ben,
* Ben (ben.fyvie@champsoftware.com) wrote:
> When you say self-contained test case, what is it exactly that you're
> looking for? A script that builds out a DB with hundreds of
> schemas/relations, a pg_basebackup or something else?
Ideally, an SQL script that builds the DB and then a pg_dump command
that shows the high memory utilization. Based on the description of the
issue, the SQL script may need to only have the schema and not any of
the data. The point is that it's a self-contained set of scripts which
demonstrate the problem; iow, please put together the SQL script and the
pg_dump command, test that you still see the issue on a clean PG
instance, and then send those our way (if you're willing/able to share
the schema definition or whatever is necessary to replicate what you're
seeing).
Thanks!
Stephen