Re: PostgreSQL 8.4.8 bringing my website down every evening

> 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

Decrease the max_connections, use connection pooling if possible (e.g.
pgbouncer). Each connection represents a separate postgres process, so you
may get up to 512 processes. And that many active processes kills the
performance.

Set it to something like 25 and use connection pooling to handle the rest.
You may increase the number until the server is 'saturated' - beyond that
point there's no point in adding more connections.

Then increase the shared_buffers. Go with something like 512MB if there's
enough RAM.

> 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?

It has nothing to do with the performance issue, this is related to
incorrectly escaped strings. Modify the app so that strings are properly
escaped (put E in front of the string, so you get something like
E'string').

Or just turn off the warning (escape_string_warning=off). See this

http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE

> 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.

There still are are such instructions. See this

http://www.postgresql.org/docs/9.0/interactive/kernel-resources.html#SYSVIPC

But if the db starts after increasing the shared_buffers, then you
probably don't need to update this.

Tomas