Re: increased load on server

Hi,


>> I tried to tune posgresql.conf to use more memory.
> Right before the problems started?

Yes two years ago.


>>  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.

I saw those things during the problem:
 - a lot of blocks read per seconds (max to 46k) on the disk which
only contains /var/lib (PostgreSQL datas but not pg_xlog)
 - lot of blocks write on the same disk (max 20k) and on the system +
pg_xlog disk (max 25k)
 - a peak to 64k of PostgreSQL buffers allocated per second


>> 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?

pg_stat_activity shows only select queries on ltree table and "IDLE"
connections.
All of those queries had xact_start value older than it should be.

>> 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?
5 minutes: just the time for my users to reconnect to my frontend.


>> 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 think queries are always quite the same.


> In particular, knowing your exact PostgreSQL version,
It was PostgresSQL 8.3.9, after this I upgrade to 8.3.14.

> your postgresql.conf file (minus all comments),
data_directory = '/var/lib/postgresql/8.3/main'
hba_file = '/etc/postgresql/8.3/main/pg_hba.conf'
ident_file = '/etc/postgresql/8.3/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/8.3-main.pid'
port = 5432
max_connections = 100
shared_buffers = 64MB
work_mem       = 16MB
maintenance_work_mem  = 128MB
max_fsm_pages = 153600
log_min_duration_statement = 100
autovacuum = on
datestyle = 'iso, dmy'
lc_messages = 'fr_FR.UTF-8'
lc_monetary = 'fr_FR.UTF-8'
lc_numeric = 'fr_FR.UTF-8'
lc_time = 'fr_FR.UTF-8'
default_text_search_config = 'pg_catalog.french'

>  the number of connections you have active during the problem periods,
Connections during problem: peak to 63.
During normal use my average number is 6.5

My system:
 - RAM: 4Go
 - 2 disks:
     - sdb: contains only /var/lib,
/var/lib/postgresql/8.3/main/pg_xlog is a symlink to an other
partition on an other disk
     - sda contains partitions for /, /var, /tmp, /boot, /home
 - CPU: Intel Xeon 1.6G with 4 cores
 - No error on Linux system logs: dmesg, /var/log/syslog


Thanks.