jf wrote:
>>> 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.
> max_fsm_pages = 153600
Probably not the main problem, but you should be sure to schedule a
VACUUM ANALYZE of the whole database (run as the database superuser)
and check the last few lines to make sure the free space manager has
enough space to prevent bloat.
> 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)
> max_connections = 100
> shared_buffers = 64MB
> 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
You need to use some sort of connection pool to funnel those requests
through about nine or ten connections. When you have more
connections than that active on the hardware you describe, you're
going to cut both throughput and response time, just when you don't
want those to suffer. You want a pool which has a hard upper limit
on number of connections and will queue requests when at the limit.
Once you've done that you'll be in a better position to determine
whether your hardware is hefty enough to handle the load. In
particular, I'm concerned that you don't have RAID, both from a
performance perspective, and from the ability to survive a drive
failure. Those do happen.
-Kevin