On Wed, May 25, 2011 at 2:40 PM, Thom Brown <thom@xxxxxxxxx> wrote: > On 25 May 2011 18:58, Alexander Farber <alexander.farber@xxxxxxxxx> 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. > > Well your shared_buffers are likely to be far too low. How much memory do > you have available in your system? I doubt this will help. For many systems, most even, especially those not doing a lot of writing, the number of shared buffers is irrelevant. The first step to solving the problem is determining what the problem is. during high load: 1. cpu bound? check top cpu usage during 2. i/o bound? check top wait% 3. scaling issues? # active connections over 20 or so can be dangerous. consider installing a pooler (my favorite is pgbouncer). also monitor vmstat for context switches 4. lousy queries? enable min_duration_statement in logs and take note of queries running over 20-50ms 5. something else? when are your backups running? what else is happening at that time? merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
