On 25 May 2011 18:58, Alexander Farber <alexander.far...@gmail.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
