Re: Having trouble with backups (was: Re: Crash Recovery) - Mailing list 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
--