Re: PostgreSQL 8.4.8 bringing my website down every evening

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?

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