pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHaving trouble with backups (was: Re: Crash Recovery)
13+ messages / 7 participants
[nested] [flat]
* Having trouble with backups (was: Re: Crash Recovery)
@ 2003-01-24 15:16 Carlos Moreno <moreno@mochima.com>
2003-01-24 16:08 ` Re: Having trouble with backups (was: Re: Crash Recovery) Rod Taylor <rbt@rbt.ca>
2003-01-24 16:09 ` Re: Having trouble with backups (was: Re: Crash Recovery) Justin Clift <justin@postgresql.org>
2003-01-24 16:13 ` Re: Having trouble with backups (was: Re: Crash Recovery) Andrew Sullivan <andrew@libertyrms.info>
2003-01-24 21:01 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 4 replies; 13+ messages in thread
From: Carlos Moreno @ 2003-01-24 15:16 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
Speaking about daily backups... We are running into some serious
trouble with our backup policy.
First (and less important), the size of our backups is increasing
a lot; yet information is not changing, only being added; so, the
obvious question: is there a way to make incremental backup?
And the second (and intriguing) problem: whenever I run pg_dump,
my system *freezes* until pg_dump finishes. When I say "system",
I mean the software that is running and sending data to the PG
database. It just freezes, users are unable to connect during
several minutes, and the ones already connected think the server
died, so they end up disconnecting after one or two minutes
seeing that the server does not respond.
Is this normal? Is there any way to avoid it? (I guess if I
have a solution to the first problem -- i.e., doing incremental
backups -- then that would solve this one, since it would only
"freeze" the system for a few seconds, which wouldn't be that
bad...)
Thanks for any comments!
Carlos
--
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Having trouble with backups (was: Re: Crash Recovery)
2003-01-24 15:16 Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
@ 2003-01-24 16:08 ` Rod Taylor <rbt@rbt.ca>
3 siblings, 0 replies; 13+ messages in thread
From: Rod Taylor @ 2003-01-24 16:08 UTC (permalink / raw)
To: Carlos Moreno <moreno@mochima.com>; +Cc: pgsql-performance
On Fri, 2003-01-24 at 10:16, Carlos Moreno wrote:
> Speaking about daily backups... We are running into some serious
> trouble with our backup policy.
>
> First (and less important), the size of our backups is increasing
> a lot; yet information is not changing, only being added; so, the
> obvious question: is there a way to make incremental backup?
Incremental backups are coming. Some folks at RedHat are working on
finishing a PIT implementation, with with any luck 7.4 will do what you
want.
For the time being you might be able to cheat. If you're not touching
the old data, it should come out in roughly the same order every time.
You might be able to get away with doing a diff between the new backup
and an older one, and simply store that. When restoring, you'll need to
patch together the proper restore file.
> And the second (and intriguing) problem: whenever I run pg_dump,
> my system *freezes* until pg_dump finishes. When I say "system",
No, this isn't normal -- nor do I believe it. The only explanation would
be a hardware or operating system limitation. I.e. with heavy disk usage
it used to be possible to peg the CPU -- making everything else CPU
starved, but the advent of DMA drives put an end to that.
A pg_dump is not resource friendly, simply due to the quantity of
information its dealing with. Are you dumping across a network? Perhaps
the NIC is maxed out.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Having trouble with backups (was: Re: Crash Recovery)
2003-01-24 15:16 Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
@ 2003-01-24 16:09 ` Justin Clift <justin@postgresql.org>
3 siblings, 0 replies; 13+ messages in thread
From: Justin Clift @ 2003-01-24 16:09 UTC (permalink / raw)
To: Carlos Moreno <moreno@mochima.com>; +Cc: pgsql-performance
Carlos Moreno wrote:
<snip>
> And the second (and intriguing) problem: whenever I run pg_dump,
> my system *freezes* until pg_dump finishes. When I say "system",
> I mean the software that is running and sending data to the PG
> database. It just freezes, users are unable to connect during
> several minutes, and the ones already connected think the server
> died, so they end up disconnecting after one or two minutes
> seeing that the server does not respond.
Is there any chance that you have hardware problems? For example a
couple of disk areas that are defective and the system is not happy
about, or maybe hard drive controller problems?
With PC's, this sort of thing generally seems to mean hardware problems
of some sort that are being triggered by PostgreSQL having to run
through the entire dataset. Could be caused by I/O load, could be
caused by hard drive errors, etc.
?
> Is this normal?
No.
Out of curiosity, which operating system are you using?
:-(
Regards and best wishes,
Justin Clift
> Is there any way to avoid it? (I guess if I
> have a solution to the first problem -- i.e., doing incremental
> backups -- then that would solve this one, since it would only
> "freeze" the system for a few seconds, which wouldn't be that
> bad...)
>
> Thanks for any comments!
>
> Carlos
> --
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Having trouble with backups (was: Re: Crash Recovery)
2003-01-24 15:16 Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
@ 2003-01-24 16:13 ` Andrew Sullivan <andrew@libertyrms.info>
3 siblings, 0 replies; 13+ messages in thread
From: Andrew Sullivan @ 2003-01-24 16:13 UTC (permalink / raw)
To: pgsql-performance
On Fri, Jan 24, 2003 at 10:16:42AM -0500, Carlos Moreno wrote:
> obvious question: is there a way to make incremental backup?
Not really, at the moment. Sorry. It's supposed to be coming soon
(see my other message about PITR).
> my system *freezes* until pg_dump finishes. When I say "system",
> Is this normal? Is there any way to avoid it? (I guess if I
No, it's not normal. I think some additional digging is needed.
One thing that is important is to make sure your pg_dump doesn't
cause swapping on the machine. Causing swapping is easy if you have
been too aggressive in shared-memory allocation for the postmaster,
and your OS is careless about who gets to be a candidate for paging.
(Solaris 7.1 without priority paging was subject to this problem, for
instance).
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Having trouble with backups (was: Re: Crash Recovery)
2003-01-24 15:16 Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
@ 2003-01-24 21:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 00:29 ` Re: Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
3 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2003-01-24 21:01 UTC (permalink / raw)
To: Carlos Moreno <moreno@mochima.com>; +Cc: pgsql-performance
Carlos Moreno <moreno@mochima.com> writes:
> And the second (and intriguing) problem: whenever I run pg_dump,
> my system *freezes* until pg_dump finishes. When I say "system",
> I mean the software that is running and sending data to the PG
> database.
Other people have responded on the assumption that this is a performance
problem, but you should also consider the possibility that it's bad
coding of your application software. Does your app try to grab
exclusive table locks? If so, it'll sit there waiting for the pg_dump
to complete. pg_dump only takes ACCESS SHARE lock on the tables it's
working on, which is the weakest type of lock and does not conflict with
most database operations ... but it does conflict with ACCESS EXCLUSIVE
lock requests.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Having trouble with backups (was: Re: Crash Recovery)
2003-01-24 15:16 Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
2003-01-24 21:01 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-01-25 00:29 ` Carlos Moreno <moreno@mochima.com>
2003-01-25 01:04 ` Re: Having trouble with backups (was: Re: Crash Recovery) Kevin Brown <kevin@sysexperts.com>
2003-01-25 01:19 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 04:20 ` Re: Having trouble with backups (was: Re: Crash Recovery) Curt Sampson <cjs@cynic.net>
0 siblings, 3 replies; 13+ messages in thread
From: Carlos Moreno @ 2003-01-25 00:29 UTC (permalink / raw)
To: pgsql-performance
Tom Lane wrote:
>Carlos Moreno <moreno@mochima.com> writes:
>
>>And the second (and intriguing) problem: whenever I run pg_dump,
>>my system *freezes* until pg_dump finishes. When I say "system",
>>I mean the software that is running and sending data to the PG
>>database.
>>
>
>Other people have responded on the assumption that this is a performance
>problem, but you should also consider the possibility that it's bad
>coding of your application software. Does your app try to grab
>exclusive table locks? If so, it'll sit there waiting for the pg_dump
>to complete.
>
Thanks Tom and the others that have replied.
One quick question, Tom, before some general comments and
reply to the other messages... Where would I specify any
locks the software wants to do? Is it something you do
when connecting to the database, or when executing the
query?? (I ask this because, that I know, we're not doing
any locks; but this may just be lack of knowledge on my
part; I may be doing that without being aware of it)
(I guess I'll check the docs, instead of asking you guys
to do my homework! :-))
Assuming that I indeed am not locking any tables, I tend to
suspect that it is a problem of excessive workload; I'd
like to doubt the possibility of defective hardware --
it's a dedicated server hired from a company that I'd like
to believe are serious guys :-) (Rackforce.com, in case
someone wants to break some bad news to me :-O )
The server is a Dual Athlon 1.8GHz, with 1GB of RAM,
running Linux 7.3, and approx. 250MB for shared buffers.
I installed PostgreSQL from the sources (7.2.3). It's
running nothing else (I mean, no apache, no public ftp
or downloads), other than our application, that is.
"vmstat -n 1" reports ZERO swaps (si and so columns)
during normal operation at peak times, and also during
pg_dump (CPU idle time typically is around 95%, maybe
going down to70 or 80 at peak times, and drops to approx.
40-60% during the time pg_dump is running -- would that
be high enough load to make the software slow down to
a crawl?).
And no, as I said above, I don't think the software locks
any tables -- in fact, if you ask me, I would say *there
is* bad coding in the application, but precisely because
there are no locks, no transactions (I know, shame on me!
That's near the top in the list of most important things
to do...), so that's why I was so reluctant to believe
my colleague when he insisted that the pg_dump's were
"freezing" the application... I had to see it with my
own eyes, and on two different occasions, to be convinced
:-(
In case this tells you something... The size of the
backup files (in plain ASCII) are around 300MB (the
command is "nice pg_dump -c -f file.sql dbname").
Any further comments will be welcome and highly
appreciated. But thank you all for the replies so
far! It gives me a good starting point to do some
digging.
Thanks,
Carlos
--
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Having trouble with backups (was: Re: Crash Recovery)
2003-01-24 15:16 Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
2003-01-24 21:01 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 00:29 ` Re: Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
@ 2003-01-25 01:04 ` Kevin Brown <kevin@sysexperts.com>
2 siblings, 0 replies; 13+ messages in thread
From: Kevin Brown @ 2003-01-25 01:04 UTC (permalink / raw)
To: pgsql-performance
Carlos Moreno wrote:
> And no, as I said above, I don't think the software locks
> any tables -- in fact, if you ask me, I would say *there
> is* bad coding in the application, but precisely because
> there are no locks, no transactions (I know, shame on me!
> That's near the top in the list of most important things
> to do...), so that's why I was so reluctant to believe
> my colleague when he insisted that the pg_dump's were
> "freezing" the application... I had to see it with my
> own eyes, and on two different occasions, to be convinced
> :-(
>
> In case this tells you something... The size of the
> backup files (in plain ASCII) are around 300MB (the
> command is "nice pg_dump -c -f file.sql dbname").
One thing you can do to help track this down is to place
stats_command_string = on
in your postgresql.conf and restart the database (it may be sufficient
to tell the database to reread the config file via "pg_ctl reload").
Then, when the backup is going, run the application.
When it "freezes", connect to the database via psql as the user
postgres and do a "select * from pg_stat_activity". You'll see the
list of connected processes and the current query being executed by
each, if any.
Do that multiple times and you should see the progress, if any, the
application is making in terms of database queries.
Hope this helps...
--
Kevin Brown kevin@sysexperts.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Having trouble with backups (was: Re: Crash Recovery)
2003-01-24 15:16 Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
2003-01-24 21:01 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 00:29 ` Re: Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
@ 2003-01-25 01:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 01:29 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2003-01-25 01:19 UTC (permalink / raw)
To: Carlos Moreno <moreno@mochima.com>; +Cc: pgsql-performance
Carlos Moreno <moreno@mochima.com> writes:
> Tom Lane wrote:
>> Other people have responded on the assumption that this is a performance
>> problem, but you should also consider the possibility that it's bad
>> coding of your application software. Does your app try to grab
>> exclusive table locks?
> One quick question, Tom, before some general comments and
> reply to the other messages... Where would I specify any
> locks the software wants to do?
If you are not issuing any explicit "LOCK" SQL commands, then you can
disregard my theory.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Having trouble with backups (was: Re: Crash Recovery)
2003-01-24 15:16 Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
2003-01-24 21:01 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 00:29 ` Re: Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
2003-01-25 01:19 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-01-25 01:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 15:20 ` Re: Having trouble with backups (was: Re: Crash Recovery) Andrew Sullivan <andrew@libertyrms.info>
2003-01-25 15:22 ` Re: Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
0 siblings, 2 replies; 13+ messages in thread
From: Tom Lane @ 2003-01-25 01:29 UTC (permalink / raw)
To: Carlos Moreno <moreno@mochima.com>; pgsql-performance
I said:
> Carlos Moreno <moreno@mochima.com> writes:
>> One quick question, Tom, before some general comments and
>> reply to the other messages... Where would I specify any
>> locks the software wants to do?
> If you are not issuing any explicit "LOCK" SQL commands, then you can
> disregard my theory.
Actually, that's too simple. Are you creating and dropping tables,
or issuing schema-change commands (such as ADD COLUMN or RENAME)?
All of those things take exclusive locks on the tables they modify.
Ordinary SELECT/INSERT/UPDATE/DELETE operations can run in parallel with
pg_dump, but messing with the database structure is another story.
I guess the real question here is whether your app is actually stopped
dead (as it would be if waiting for a lock), or just slowed to a crawl
(as a performance problem could do). I cannot tell if your "frozen"
description is hyperbole or literal truth.
One thing that might help diagnose it is to look at the output of ps
auxww (or ps -ef on SysV-ish platforms) to see what all the backends are
currently doing while the problem exists.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Having trouble with backups (was: Re: Crash Recovery)
2003-01-24 15:16 Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
2003-01-24 21:01 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 00:29 ` Re: Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
2003-01-25 01:19 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 01:29 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-01-25 15:20 ` Andrew Sullivan <andrew@libertyrms.info>
1 sibling, 0 replies; 13+ messages in thread
From: Andrew Sullivan @ 2003-01-25 15:20 UTC (permalink / raw)
To: pgsql-performance
On Fri, Jan 24, 2003 at 08:29:41PM -0500, Tom Lane wrote:
> auxww (or ps -ef on SysV-ish platforms) to see what all the backends are
Except Solaris, where ps -ef gives you no information at all. Use
/usr/ucb/ps -auxww.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Having trouble with backups (was: Re: Crash Recovery)
2003-01-24 15:16 Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
2003-01-24 21:01 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 00:29 ` Re: Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
2003-01-25 01:19 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 01:29 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-01-25 15:22 ` Carlos Moreno <moreno@mochima.com>
2003-01-25 17:17 ` Re: Having trouble with backups (was: Re: Crash Recovery) Andrew Sullivan <andrew@libertyrms.info>
1 sibling, 1 reply; 13+ messages in thread
From: Carlos Moreno @ 2003-01-25 15:22 UTC (permalink / raw)
To: pgsql-performance
Tom Lane wrote:
>I said:
>
>>Carlos Moreno <moreno@mochima.com> writes:
>>
>>>One quick question, Tom, before some general comments and
>>>reply to the other messages... Where would I specify any
>>>locks the software wants to do?
>>>
>
>>If you are not issuing any explicit "LOCK" SQL commands, then you can
>>disregard my theory.
>>
Well, it was a good thing that you brought it to my attention.
Yes, two minutes after I wrote the message I found the docs
that told me it is an SQL command -- which means that I'm
positively sure that I'm not doing any of those :-) I guess
a well-developed software could use some locks here and there,
and the risk of making a mistake and "over-blocking" things is
there...
>>Actually, that's too simple. Are you creating and dropping tables,
>>or issuing schema-change commands (such as ADD COLUMN or RENAME)?
>>All of those things take exclusive locks on the tables they modify.
>>Ordinary SELECT/INSERT/UPDATE/DELETE operations can run in parallel with
>>pg_dump, but messing with the database structure is another story.
>>
I do that (changing the database schema while the system is
running) once in a while -- but not on a regular basis, and
definitely never during the time a pg_dump is in progress
(*that* would have scared me to death ;-))
>
>I guess the real question here is whether your app is actually stopped
>dead (as it would be if waiting for a lock), or just slowed to a crawl
>(as a performance problem could do). I cannot tell if your "frozen"
>description is hyperbole or literal truth.
>
Actually, you got me on that one... From the "practical" point of
view, you could say it's literal truth (i.e., the system responsiveness
falls to ZERO). The system is an online multi-player game, where the
main things the database is doing is holding the users information
to process the login authentications, and logging results and the
progress of games (to later -- offline -- compute statistics,
rankings, etc.). Logging is done on a separate worker thread, so
it shouldn't matter if that stops for a few minutes (the lists of
SQL's pending to be executed would just grow during that time)...
But the thing is, when I run pg_dump, the games freeze, you are
absolutely unable to connect (the server does not respond, period),
and the players that are in a game, playing, massively abandon
games, and you then see comments in the chat window that the
server went down, etc. (i.e., I take it the server stopped
responding to them and they abandoned thinking that the connection
had dropped, or that the server had died).
Now, I guess a more specific answer to your question is important
(i.e., is the above behaviour the result of the system slowing to
a crawl, or is it that the software just hung on a single db.Exec
statement in the main loop and no single line of code is being
executed until the pg_dump finishes? -- according to the comments
so far, I would say this last option is not possible), and I think
I'll get such an answer when running some tests as suggested by you
and others that replied.
>One thing that might help diagnose it is to look at the output of ps
>auxww (or ps -ef on SysV-ish platforms) to see what all the backends are
>currently doing while the problem exists.
>
We have done (IIRC) top (the command "top", that is), and yes, the
postmaster process takes a lot of CPU... (not sure of the exact
numbers, but it was at the top).
Anyway, thanks again guys for the valuable comments and ideas!!
Carlos
--
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Having trouble with backups (was: Re: Crash Recovery)
2003-01-24 15:16 Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
2003-01-24 21:01 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 00:29 ` Re: Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
2003-01-25 01:19 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 01:29 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 15:22 ` Re: Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
@ 2003-01-25 17:17 ` Andrew Sullivan <andrew@libertyrms.info>
0 siblings, 0 replies; 13+ messages in thread
From: Andrew Sullivan @ 2003-01-25 17:17 UTC (permalink / raw)
To: pgsql-performance
On Sat, Jan 25, 2003 at 10:22:51AM -0500, Carlos Moreno wrote:
> Well, it was a good thing that you brought it to my attention.
> Yes, two minutes after I wrote the message I found the docs
> that told me it is an SQL command -- which means that I'm
> positively sure that I'm not doing any of those :-) I guess
If you have a lot of foreign keys and are doing long-running UPDATES
(or other related things), I think you might also see the same thing.
You could spot this with ps -auxww (or friends) by looking for [some
db operation] waiting.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Having trouble with backups (was: Re: Crash Recovery)
2003-01-24 15:16 Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
2003-01-24 21:01 ` Re: Having trouble with backups (was: Re: Crash Recovery) Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 00:29 ` Re: Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
@ 2003-01-25 04:20 ` Curt Sampson <cjs@cynic.net>
2 siblings, 0 replies; 13+ messages in thread
From: Curt Sampson @ 2003-01-25 04:20 UTC (permalink / raw)
To: Carlos Moreno <moreno@mochima.com>; +Cc: pgsql-performance
On Fri, 24 Jan 2003, Carlos Moreno wrote:
> The server is a Dual Athlon 1.8GHz, with 1GB of RAM,
> running Linux 7.3, and approx. 250MB for shared buffers.
> ...
> In case this tells you something... The size of the
> backup files (in plain ASCII) are around 300MB (the
> command is "nice pg_dump -c -f file.sql dbname").
I was going to ask you to check your disk I/O statistics, but that tells
me that disk I/O is probably not the problem. If the ASCII dump file
(I assume by "plain ASCII" you mean uncompressed as well) is only 300
MB, your database size is likely well under 100 MB. In which case the
entire database ought to be residing in the buffer cache, and you should
see maximum CPU utilisation during the dump, and not too much disk
I/O. (This is, however, assuming that that's the only database on your
machine. You don't have another 250 GB database that gets lots of random
access hiding there, do you? :-))
On a big machine like that, with such a small database, you should be
able to do a dump in a couple of minutes with little noticable impact on
the performance of clients.
I would probably start with carefully tracing what your clients are doing
during backup, and where they're blocking.
cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2003-01-25 17:17 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-24 15:16 Having trouble with backups (was: Re: Crash Recovery) Carlos Moreno <moreno@mochima.com>
2003-01-24 16:08 ` Rod Taylor <rbt@rbt.ca>
2003-01-24 16:09 ` Justin Clift <justin@postgresql.org>
2003-01-24 16:13 ` Andrew Sullivan <andrew@libertyrms.info>
2003-01-24 21:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 00:29 ` Carlos Moreno <moreno@mochima.com>
2003-01-25 01:04 ` Kevin Brown <kevin@sysexperts.com>
2003-01-25 01:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 01:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 15:20 ` Andrew Sullivan <andrew@libertyrms.info>
2003-01-25 15:22 ` Carlos Moreno <moreno@mochima.com>
2003-01-25 17:17 ` Andrew Sullivan <andrew@libertyrms.info>
2003-01-25 04:20 ` Curt Sampson <cjs@cynic.net>
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