From: Steve Crawford <scrawford@pinpointresearch.com>
To: Mark Mikulec <mark@anticentertainment.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: pg_dumpall affecting performance
Date: Tue, 15 Feb 2011 10:56:44 -0800
Message-ID: <4D5ACC6C.50803@pinpointresearch.com> (raw)
In-Reply-To: <a483a34e4c34f31e44ee156f79c0fc71@anticentertainment.com>
References: <a483a34e4c34f31e44ee156f79c0fc71@anticentertainment.com>
On 02/15/2011 10:41 AM, Mark Mikulec wrote:
> Hello,
>
> I was under the impression that pg_dumpall didn't affect database
> performance when dumping while the db is live. However I have evidence to
> the contrary now - queries that are run during the pg_dumpall time take 10
> to a 100 times longer to execute than normal while pg_dumpall is running.
> The strange thing is that this started after my database grew by about 25%
> after a large influx of data due to user load. I'm wonder if there is a
> tipping
> point or a config setting I need to change now that the db is larger that
> is
> causing all this to happen.
>
Don't know where that impression came from. It is true that you can
continue to *use* your database normally while running a dump but you
are reading the entire database and either transmitting it over the
network or writing it to a local drive so it shouldn't be surprising
that performance is impacted.
There are tipping points - one big one is when you move from having all
your data in RAM to needing to read disk. And it can be a whopper. If
all your information, through PG or OS caching is in RAM then your dumps
may run very quickly. The moment you cross the point that things don't
quite fit you can see a sharp decline.
Consider a least-recently-used algorithm and a very simplistic scenario.
You read the "start" data. It isn't cached so you go to disk *and* you
put those blocks into cache pushing others than you would need later out
of cache. This continues and you potentially end up having to read
everything from disk plus incur the overhead of checking and updating
the cache. Meanwhile, the data you needed for your query may have been
pushed out of cache so there is more contention for disk.
Admittedly an over-simplified example but you see the problem.
Cheers,
Steve
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scrawford@pinpointresearch.com, mark@anticentertainment.com
Subject: Re: pg_dumpall affecting performance
In-Reply-To: <4D5ACC6C.50803@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox