Re: PostgreSQL 8.4.8 bringing my website down every evening

On Wed, May 25, 2011 at 2:40 PM, Thom Brown <thom(at)linux(dot)com> wrote:
> On 25 May 2011 18:58, Alexander Farber <alexander(dot)farber(at)gmail(dot)com> 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