On 05/25/2011 10:58 AM, Alexander Farber wrote:
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.
Thank you
Alex
Start by reading
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and
http://www.postgresql.org/docs/current/static/kernel-resources.html.
It's impossible to give specific advice given the information provided.
With persistent connections, you will likely see lots of PostgreSQL
processes since there will be one per established connection. But are
they idle or doing something? And if they are doing something, is the
bottleneck disk, memory or CPU?
As to general advice, if you are limiting Apache connections to 300, I'm
not sure why you need 512 max connections to the DB unless there are a
lot of simultaneous non-web processes hitting the DB.
I doubt that most of those connections are simultaneously in use. A
connection pooler like pgbouncer may be in your future. Pgbouncer is
pretty easy to set up and mah
If most of the queries are simple reads that can be cached, something
like memcached can provide huge benefits.
Your shared_mem looks way too low. Read the Tuning Guide noted above.
You will probably want something closer to a 1G (though probably a bit
less due to the memory use of Apache, OS, etc.). The kernel-resources
article has info on adjusting the kernel settings.
Bad query design or need for indexes can be non-issues at low-load but
damaging under high-use. Enable more query logging - especially log
queries that exceed some threshold. You might start at a couple seconds
and adjust from there. See log_min_duration_statement.
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
