Re: PostgreSQL 8.4.8 bringing my website down every evening

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