Re: PostgreSQL 8.4.8 bringing my website down every evening - Mailing list pgsql-general

Hello fellow PostgreSQL-users,

I run a Drupal 7 (+Facebook app) website
with a multiplayer flash game and use
postgresql-server-8.4.8-1PGDG.rhel5 +
CentOS 5.6 64 bit on a Quad-Core/4GB machine.

I generally like using PostgreSQL eventhough
I'm not an experienced DB-user, but in the recent
weeks it gives me a lot of headache bringing
my website to a halt every evening (when
most players visit the website for a game).

I think this is result of having more users
and having written few more statistics scripts
for them (I use PHP with persistent connections;
I use only local PostgreSQL-connections).

I suspect if I could configure
PostgreSQL accordingly, it would run ok again.

During "crashes" when/if I manage to ssh into
my server it is barely usable and I see lots
of postmaster processes.

I have the following settings in pg_hba.conf:

local   all         all                               md5
host    all         all         127.0.0.1/32          md5

And the following changes in postgresql.conf:

max_connections = 512
shared_buffers = 32MB
log_destination = 'stderr'
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
logging_collector = on
log_rotation_age = 1d
log_rotation_size = 0
log_truncate_on_rotation = on

My Apache httpd.conf:
<IfModule prefork.c>
StartServers       10
MinSpareServers    12
MaxSpareServers   50
ServerLimit      300
MaxClients       300
MaxRequestsPerChild  4000
</IfModule>

I look into
/var/lib/pgsql/data/pg_log/postgresql-Wed.log
but don't see anything alarming there.

WARNING:  nonstandard use of \\ in a string literal at character 220
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING:  nonstandard use of \\ in a string literal at character 142
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING:  nonstandard use of \\ in a string literal at character 204
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
etc.

Does anybody please have any advice?

Do I have to apply any shared memory/etc. settings
to CentOS Linux system? When I used OpenBSD some
years ago, there where specific instructions to apply to
its kernel/sysctl.conf in the postgresql port readme.