jf <jfmeteo@xxxxxxxxx> 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
