Search Postgresql Archives

Re: PostgreSQL 8.4.8 bringing my website down every evening

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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?

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux