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.
Well your shared_buffers are likely to be far too low. How much memory do you have available in your system?
And the instructions I think you are looking for are here: http://www.postgresql.org/docs/current/static/kernel-resources.html
What have you got checkpoint_segments set to? Are there any warnings in your log about checkpoints occurring too frequently?
And a way to reduce the I/O impact of checkpoint spikes is to smooth them out by increasing checkpoint_completion_target to 0.8.
And do you know how many connections are in use during the times where it's locked up? If you're reaching your connection limit, it will start rejecting connections. A way to solve this problem is either to increase your max_connections setting further, or introduce connection pooling, either with something like pgBouncer (http://pgfoundry.org/projects/pgbouncer/) or using Apache's connection pooling if you know how to set it up.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
