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