jf <jfmeteo@gmail.com> wrote:
> I tried to tune posgresql.conf to use more memory.
Right before the problems started? As an attempt to deal with the
problems? Years ago?
> In two years on production everything was fine, but today
> something goes wrong: postgresql processes eat my server
> ressources, my load, which was between 0 and 2.5 on my 4 cpus
> server, grows to 20.
The output from `vmstat 1` during such an episode might be
enlightening.
> When the trouble appends there was a lot of waiting queries on my
> ltree table.
Waiting where? Are you talking about executing queries which are
blocked trying to acqure locks on the ltree table?
> I tried to restart postgresql server and to reboot the server, but
> 5 minutes later, the trouble was here again.
Any idea what changed during those 5 minutes?
> I did a backup/restore of my database (pg_dump -Fc / pg_restore),
> and it was fine for 2 hours but after the touble reappears.
Did you notice anything about the usage during those two hours? Did
you take a close look at what queries were being run when things are
OK versus when they are not?
> I notice that the "base" directory grows a lot when the trouble
> begins. I made a 'vacuum', 'vacuum analyze', 'vacuum full' but the
> disk usage isn't lower.
Hopefully you are running autovacuum. VACUUM FULL is normally not a
good idea in 8.3. What you might want to look for is a lingering
transaction which is preventing normal autovacuum from working.
Does anything show if you select from pg_prepared_xacts? Are there
any rows in pg_stat_activity with a xact_start value older than it
should be? One connection sitting in status '<IDLE> in transaction'
for a long time can cause all sorts of problems.
If these hints don't point you in the right direction, please review
this page for ideas on more information you can supply to help
people understand the problem and its cause:
http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
In particular, knowing your exact PostgreSQL version, the contents
of your postgresql.conf file (minus all comments), the number of
connections you have active during the problem periods, and (if
possible) listings of pg_stat_activity and pg_locks during a problem
episode.
-Kevin