PostgreSQL 8.4.8 bringing my website down every evening

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

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

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

Thank you for your replies,

I've reverted httpd.conf to

StartServers 8
MinSpareServers 5
MaxSpareServers 20
ServerLimit 256
MaxClients 256

and have changed postgresql.conf to:

shared_buffers = 512MB
# for Apache + my game daemon + cron jobs
max_connections = 260

Do you think I need to reconfigure CentOS 5.6
for the bigger shared memory too or
will it adapt by itself?

I'm still studying the docs.

Also I've installed the pgbouncer package and
will read on it too, but I already wonder what is
its behaviour if configured for 100 connections
and a 101st comes in?

; total number of clients that can connect
max_client_conn = 100
default_pool_size = 20

Regards
Alex

# rpm -qa|grep -i pg
perl-DBD-Pg-1.49-2.el5_3.1
pgbouncer-1.3.4-1.rhel5
pgdg-centos-8.4-2
php53-pgsql-5.3.3-1.el5_6.1
postgresql-8.4.8-1PGDG.rhel5
postgresql-devel-8.4.8-1PGDG.rhel5
postgresql-libs-8.4.8-1PGDG.rhel5
postgresql-server-8.4.8-1PGDG.rhel5

# uname -rm
2.6.18-238.9.1.el5 x86_64

# sysctl kernel.shmmax
kernel.shmmax = 68719476736
# sysctl kernel.shmall
kernel.shmall = 4294967296

On Wed, May 25, 2011 at 9:54 PM, Alexander Farber
<alexander.farber@gmail.com> wrote:

Dne 25.5.2011 21:54, Alexander Farber napsal(a):

Thank you for your replies,

I've reverted httpd.conf to

StartServers 8
MinSpareServers 5
MaxSpareServers 20
ServerLimit 256
MaxClients 256

and have changed postgresql.conf to:

shared_buffers = 512MB
# for Apache + my game daemon + cron jobs
max_connections = 260

Do you think I need to reconfigure CentOS 5.6
for the bigger shared memory too or
will it adapt by itself?

If the database started up fine, then you probably don't need to mess
with the kernel parameters. There are two limits here

SHMMAX - max size of a single memory segment
SHMALL - max size of all the memory segments (sum)

So if you've increased the shared buffers and the database starts fine,
there's a slight change that something else will fail because it needs
it's own segment and the sum exceeds SHMALL.

But if everything works fine, it's probably fine. When something fails
you'll know where to look.

You can see the list of segments using "ipcs -m"

I'm still studying the docs.

Also I've installed the pgbouncer package and
will read on it too, but I already wonder what is
its behaviour if configured for 100 connections
and a 101st comes in?

Say you have "max_client_conn = 2" and "pool_size = 1", and then three
clients come.

client 1: connects to pgbouncer, gets a db connection from the pool,
starts a transaction and works

client 2: connects to pgbouncer, asks for a db connection but has to
wait until client 1 finishes (because there's only 1
connection in the pool)

client 3: can't connect to the pgbouncer, get's "ERROR: no more
connections allowed" (there's max_client_conn = 2)

So in your case the 101st client is rejected. But those connections are
much cheaper (compared to the real db connections), so you may create
more of them. So if you have ServerLimit=256, you may do set
max_client_conn to 256 to handle the peak.

; total number of clients that can connect
max_client_conn = 100
default_pool_size = 20

BTW as Steve Crawford already pointed out, it's impossible to give
reliable advices without more information. So it may happen that this
won't fix the actual cause.

You need to find out whether the number of connections really is the
problem. Maybe there's some poorly performing SQL that causes all this,
and fixing that one problem might solve all this.

Or maybe there's some concurrency issue (e.g. all the sessions updating
the same row). In that case the number of connections is rather a
symptom than a cause.

Are those connections idle? What does a vmstat / iostat show? Have you
enabled logging of slow queries?

Tomas

On 05/26/2011 02:53 AM, tv@fuzzy.cz wrote:

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.

... and this is why it'd be great to see pooling-by-default in Pg, be it
integrated PgPool or something else. For every person making the effort
to ask on the mailing list, how many give up and go away?

Yes, I know it's not exactly easy to integrate pooling, and that there
are real disagreements about pooling vs admission control.

--
Craig Ringer

On Wed, May 25, 2011 at 2:40 PM, Thom Brown <thom@linux.com> wrote:

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

I doubt this will help. For many systems, most even, especially those
not doing a lot of writing, the number of shared buffers is
irrelevant. The first step to solving the problem is determining
what the problem is.

during high load:
1. cpu bound? check top cpu usage during
2. i/o bound? check top wait%
3. scaling issues? # active connections over 20 or so can be
dangerous. consider installing a pooler (my favorite is pgbouncer).
also monitor vmstat for context switches
4. lousy queries? enable min_duration_statement in logs and take note
of queries running over 20-50ms
5. something else? when are your backups running? what else is
happening at that time?

merlin

Thank you, I'll try your suggestions.

I'm just slow in doing so, because it's just a
(sometimes pretty time consuming) hobby-project.

I'm missing knowledge on how to monitor my DB status,
i.e. how to check some of the things you've asked.

Also I wonder, how's shared memory used by PostgreSQL.
I'm irritated - how it could work with 32MB,
but now I've got suggestion to increase it
to 512MB (and it seems to work too...)

Regards
Alex

Dne 26.5.2011 11:41, Alexander Farber napsal(a):

Thank you, I'll try your suggestions.

I'm just slow in doing so, because it's just a
(sometimes pretty time consuming) hobby-project.

I'm missing knowledge on how to monitor my DB status,
i.e. how to check some of the things you've asked.

OK, let me explain in a bit more detail. Merlin recommended those 5
things to find out where the real bottleneck is (CPU, I/O, ...), because
that's the necessary first step to fix it.

1. cpu bound? check top cpu usage during

Just run "top" and see what's going on when there are problems. If
the is 100% busy then the DB is CPU bound and you have to optimize
it so that it uses less CPU (or add faster/more CPUs).

It might be that most of the CPU is consumed by other processes
(e.g. Java doing GC) but still you need to find out if it's the case.

2. i/o bound? check top wait%

Run "top" and see what is the wait time. If you have more drives,
you can run "dstat" or "iostat -x" to see "per disk" stats. If the
wait/util values grow too much (beyond 50%), you're probably I/O
bound and you need to fix this.

3. scaling issues? # active connections over 20 or so can be
dangerous. consider installing a pooler (my favorite is pgbouncer).
also monitor vmstat for context switches

Run "vmstat 1" and see the "cs" (context switch) column. The more
context switches happen, the more overhead that makes and the less
actual work can be done. So if you have too many active processes
(and each connection is a separate postgres backend process), this
may be a serious problem (unless the connections are idle).

The state of the connection can be seen from "ps ax" output - there
will be something like this:

5257 ? Ss 0:00 postgres: pguser pgdb [local] idle

which means the connection is idle, or this

5257 ? Rs 0:02 postgres: vampire pgmap [local] SELECT

when there's a query running.

Or you can use pg_stat_activity system view - the idle connections
will have "<IDLE>" in the "current_query" column.

4. lousy queries? enable min_duration_statement in logs and take note of
queries running over 20-50ms

Poor SQL queries are often the real cause - you have to find out
which queries are slow (and then you can analyze why). The queries
can be obtained in two ways.

First you can set "log_min_duration_statement" in the config file,
and queries exceeding this number of miliseconds will be written
to the postgresql log. For example this

log_min_duration_statement = 250

will log all queries that take more than 250ms. Be careful not to
set it too low (I really wouldn't set it to 20ms right now), because
it means more I/O and it might make the problem even worse. Queries
start to slow down, more and more of them exceed this threshold and
need to be written, that means more I/O and that makes more queries
to run slow - you get the idea.

Or you could use the pg_stat_activity view again. Once the problems
happen log into psql and run this

select * from pg_stat_activity where current_query != '<IDLE>'
order by (now() - query_start) desc;

and you'll get list of currently running queries sorted by time.

5. something else? when are your backups running? what else is
happening at that time?

This just means the actual problem might be outside postgresql, e.g.
an intensive batch process / backup / ... consuming a lot of CPU,
I/O or other resources. That's it.

Also I wonder, how's shared memory used by PostgreSQL.
I'm irritated - how it could work with 32MB,
but now I've got suggestion to increase it
to 512MB (and it seems to work too...)

Shared buffers are a 'database cache'. When the DB needs a block from a
file (because that's where the data are stored), it reads the data into
the cache. When the same block is needed again, it may be read from the
cache (which is much faster). Unless there's not enough space to hold
all the blocks - in that case the block may be removed from the cache
and will be read from the disk again.

So it's about size of this cache - when you increase the cache more
reads will be resolved without actual I/O. But as Merlin noted, this may
not help when there's a lot of writes in your workload. It may actually
make the thing worse during checkpoint. I'd recommend to enable
"log_checkpoints" to see if this is a problem.

regards
Tomas

On Thu, May 26, 2011 at 9:01 AM, Tomas Vondra <tv@fuzzy.cz> wrote:

Dne 26.5.2011 11:41, Alexander Farber napsal(a):

Thank you, I'll try your suggestions.

I'm just slow in doing so, because it's just a
(sometimes pretty time consuming) hobby-project.

I'm missing knowledge on how to monitor my DB status,
i.e. how to check some of the things you've asked.

OK, let me explain in a bit more detail. Merlin recommended those 5
things to find out where the real bottleneck is (CPU, I/O, ...), because
that's the necessary first step to fix it.

1. cpu bound? check top cpu usage during

  Just run "top" and see what's going on when there are problems. If
  the is 100% busy then the DB is CPU bound and you have to optimize
  it so that it uses less CPU (or add faster/more CPUs).

  It might be that most of the CPU is consumed by other processes
  (e.g. Java doing GC) but still you need to find out if it's the case.

2. i/o bound? check top wait%

  Run "top" and see what is the wait time. If you have more drives,
  you can run "dstat" or "iostat -x" to see "per disk" stats. If the
  wait/util values grow too much (beyond 50%), you're probably I/O
  bound and you need to fix this.

3. scaling issues? # active connections over 20 or so can be
dangerous.  consider installing a pooler (my favorite is pgbouncer).
also monitor vmstat for context switches

  Run "vmstat 1" and see the "cs" (context switch) column. The more
  context switches happen, the more overhead that makes and the less
  actual work can be done. So if you have too many active processes
  (and each connection is a separate postgres backend process), this
  may be a serious problem (unless the connections are idle).

  The state of the connection can be seen from "ps ax" output - there
  will be something like this:

     5257 ?        Ss     0:00 postgres: pguser pgdb [local] idle

  which means the connection is idle, or this

     5257 ?        Rs     0:02 postgres: vampire pgmap [local] SELECT

  when there's a query running.

  Or you can use pg_stat_activity system view - the idle connections
  will have "<IDLE>" in the "current_query" column.

4. lousy queries? enable min_duration_statement in logs and take note of
queries running over 20-50ms

  Poor SQL queries are often the real cause - you have to find out
  which queries are slow (and then you can analyze why). The queries
  can be obtained in two ways.

  First you can set "log_min_duration_statement" in the config file,
  and queries exceeding this number of miliseconds will be written
  to the postgresql log. For example this

     log_min_duration_statement = 250

  will log all queries that take more than 250ms. Be careful not to
  set it too low (I really wouldn't set it to 20ms right now), because
  it means more I/O and it might make the problem even worse. Queries
  start to slow down, more and more of them exceed this threshold and
  need to be written, that means more I/O and that makes more queries
  to run slow - you get the idea.

  Or you could use the pg_stat_activity view again. Once the problems
  happen log into psql and run this

     select * from pg_stat_activity where current_query != '<IDLE>'
                                 order by (now() - query_start) desc;

  and you'll get list of currently running queries sorted by time.

5. something else? when are your backups running?  what else is
happening at that time?

  This just means the actual problem might be outside postgresql, e.g.
  an intensive batch process / backup / ... consuming a lot of CPU,
  I/O or other resources. That's it.

Great stuff.

Also I wonder, how's shared memory used by PostgreSQL.
I'm irritated - how it could work with 32MB,
but now I've got suggestion to increase it
to 512MB (and it seems to work too...)

Shared buffers are a 'database cache'. When the DB needs a block from a
file (because that's where the data are stored), it reads the data into
the cache. When the same block is needed again, it may be read from the
cache (which is much faster). Unless there's not enough space to hold
all the blocks - in that case the block may be removed from the cache
and will be read from the disk again.

*or the disk cache*. lowering shared buffers does not lower the
amount of ram in the system and thus does not lower the availability
of cache. If I may nitpick this point on your otherwise very
excellent email, this is exactly the type of thing that drives me
crazy about advice to raise shared buffers. It suggests you will get
less disk i/o which may or may not be the case (in fact, it can make
the i/o problem worse). If it does help i/o, it will probably not be
for the reasons you suspect. See my thread in -performance on this
topic.

merlin

Dne 26.5.2011 16:39, Merlin Moncure napsal(a):

On Thu, May 26, 2011 at 9:01 AM, Tomas Vondra <tv@fuzzy.cz> wrote:

Dne 26.5.2011 11:41, Alexander Farber napsal(a):

Also I wonder, how's shared memory used by PostgreSQL.
I'm irritated - how it could work with 32MB,
but now I've got suggestion to increase it
to 512MB (and it seems to work too...)

Shared buffers are a 'database cache'. When the DB needs a block from a
file (because that's where the data are stored), it reads the data into
the cache. When the same block is needed again, it may be read from the
cache (which is much faster). Unless there's not enough space to hold
all the blocks - in that case the block may be removed from the cache
and will be read from the disk again.

*or the disk cache*. lowering shared buffers does not lower the
amount of ram in the system and thus does not lower the availability
of cache. If I may nitpick this point on your otherwise very
excellent email, this is exactly the type of thing that drives me
crazy about advice to raise shared buffers. It suggests you will get
less disk i/o which may or may not be the case (in fact, it can make
the i/o problem worse). If it does help i/o, it will probably not be
for the reasons you suspect. See my thread in -performance on this
topic.

Yes, you're right. I didn't want to complicate the things further so
I've skipped the part about page cache.

Tomas

I've switched duration and SQL 'all' logging on,
but I have hard time to identify which SQL statement
has had which duration.

For example which SQL statement please has
the duration of 13 seconds (13025.016 ms) below?

LOG: statement: SELECT 1 AS expression
FROM
drupal_sessions sessions
WHERE ( (sid = 'uM2YMQ9c5Z6y93Zaots9jMzF7wS_rjlbDPjSxKffr1I')
AND (ssid = '') ) FOR UPDATE
LOG: statement: SELECT column_name, data_type, column_default FROM
information_schema.columns WHERE table_schema = 'public' AND
table_name = 'drupal_sessions' AND (data_type = 'bytea' OR
(numeric_precision IS NOT NULL AND column_default LIKE '%nextval%'))
LOG: statement: UPDATE drupal_sessions SET uid='8467', cache='0',
hostname='13.106.153.82', session='', timestamp='1306423187'
WHERE ( (sid = 'uM2YMQ9c5Z6y93Zaots9jMzF7wS_rjlbDPjS7Kffr1I')
AND (ssid = '') )
LOG: statement: COMMIT
LOG: statement: SELECT column_name, data_type, column_default FROM
information_schema.columns WHERE table_schema = 'public' AND
table_name = 'drupal_users' AND (data_type = 'bytea' OR
(numeric_precision IS NOT NULL AND column_default LIKE '%nextval%'))
LOG: statement: UPDATE drupal_users SET access='1306423187'
WHERE (uid = '8467')
LOG: duration: 57.913 ms
LOG: statement: SELECT 'DBD::Pg ping test'
LOG: execute dbdpg_1: select pref_update_money($1, $2)
DETAIL: parameters: $1 = 'DE9107', $2 = '13'
LOG: execute dbdpg_2: select pref_update_hand($1, $2, $3)
DETAIL: parameters: $1 = 'DE9107', $2 = '159556649', $3 = '13'
LOG: duration: 54.081 ms
LOG: statement: SELECT 'DBD::Pg ping test'
LOG: execute dbdpg_1: select pref_update_money($1, $2)
DETAIL: parameters: $1 = 'OK397412944345', $2 = '9'
LOG: execute dbdpg_2: select pref_update_hand($1, $2, $3)
DETAIL: parameters: $1 = 'OK397412944345', $2 = '270751304', $3 = '9'
LOG: duration: 56.573 ms
LOG: statement: SELECT 'DBD::Pg ping test'
LOG: execute dbdpg_1: select pref_update_money($1, $2)
DETAIL: parameters: $1 = 'OK368420510411', $2 = '19'
LOG: execute pdo_stmt_0000006b: SELECT u.id, u.first_name, u.avatar,
u.female, r.nice
FROM pref_users u LEFT OUTER JOIN pref_rep r
ON u.id = r.id AND r.author = $1 WHERE u.id in
( $2, $3 )
DETAIL: parameters: $1 = 'OK524015351816', $2 = 'OK491946648759', $3
= 'OK135178454570'
LOG: execute dbdpg_2: select pref_update_hand($1, $2, $3)
DETAIL: parameters: $1 = 'OK368420510411', $2 = '244318614', $3 = '19'
LOG: statement: DEALLOCATE pdo_stmt_0000006b
LOG: execute pdo_stmt_000000aa: SELECT u.id, u.first_name, u.avatar,
u.female, r.nice
FROM pref_users u LEFT OUTER JOIN pref_rep r
ON u.id = r.id AND r.author = $1 WHERE u.id in
( $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14 )
DETAIL: parameters: $1 = 'OK250619934309', $2 = 'OK301001052424', $3
= 'OK353189811941', $4 = 'OK358143063475', $5 = 'DE8890', $6 =
'OK343020320504', $7 = 'MR11145992487713570697', $8 =
'OK488913512462', $9 = 'MR18364595699142101947', $10 =
'OK508907787570', $11 = 'OK345960562675', $12 = 'OK341680565482', $13
= 'OK266334509747', $14 = 'DE10140'
LOG: statement: DEALLOCATE pdo_stmt_000000aa
LOG: duration: 57.492 ms
LOG: statement: SELECT 'DBD::Pg ping test'
LOG: execute dbdpg_1: select pref_update_money($1, $2)
DETAIL: parameters: $1 = 'MR8956887007365082416', $2 = '-27'
LOG: duration: 13025.016 ms
LOG: execute pdo_stmt_00000002: SELECT u.id, u.first_name, u.avatar,
u.female, r.nice
FROM pref_users u LEFT OUTER JOIN pref_rep r
ON u.id = r.id AND r.author = $1 WHERE u.id in
( $2, $3, $4 )
DETAIL: parameters: $1 = 'OK491817224261', $2 = 'OK496106546037', $3
= 'OK491946648759', $4 = 'OK332008971867'
LOG: statement: DEALLOCATE pdo_stmt_00000002
LOG: duration: 7681.654 ms

And below are my stored routnies and few tables just in case:

create or replace function pref_update_hand(_id varchar,
_hand bigint, _money int) returns void as $BODY$
begin

delete from pref_hand where id=_id and stamp in
(select stamp from pref_hand where id=_id order by
stamp desc offset 9);

insert into pref_hand (id, hand, money) values (_id,
_hand, _money);

end;
$BODY$ language plpgsql;

create or replace function pref_update_money(_id varchar,
_money integer) returns void as $BODY$
begin

update pref_money set
money = money + _money
where id = _id and yw = to_char(current_timestamp, 'IYYY-IW');

if not found then
insert into pref_money(id, money)
values (_id, _money);
end if;
end;
$BODY$ language plpgsql;

create table pref_money (
id varchar(32) references pref_users,
money integer not null,
yw char(7) default to_char(current_timestamp, 'IYYY-IW')
);
create index pref_money_yw_index on pref_money(yw);

create table pref_hand (
id varchar(32) references pref_users,
hand bigint not NULL check (hand > 0),
money integer not null,
stamp timestamp default current_timestamp
);

Also I've noticed I had a wrong index:

create table pref_match (
id varchar(32) references pref_users,
started integer default 0 check (started >= 0),
completed integer default 0 check (completed >= 0),
win integer default 0 check (completed >= win and win >= 0),
quit integer default 0 check (quit >= 0),
yw char(7) default to_char(current_timestamp, 'IYYY-IW')
);
create index pref_match_yw_index on pref_money(yw);

Regards
Alex

But when I try to look at that wrong index it seems to be ok?

# psql
psql (8.4.8)
Type "help" for help.

pref=> \d pref_match
Table "public.pref_match"
Column | Type | Modifiers
-----------+-----------------------+-----------------------------------------
id | character varying(32) |
started | integer | default 0
completed | integer | default 0
quit | integer | default 0
yw | character(7) | default to_char(now(), 'IYYY-IW'::text)
win | integer | default 0
Indexes:
"pref_match_yw_index" btree (yw)
Check constraints:
"pref_match_check" CHECK (completed >= win AND win >= 0)
"pref_match_completed_check" CHECK (completed >= 0)
"pref_match_quit_check" CHECK (quit >= 0)
"pref_match_started_check" CHECK (started >= 0)
Foreign-key constraints:
"pref_match_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)

pref=> \d pref_money
Table "public.pref_money"
Column | Type | Modifiers
--------+-----------------------+-----------------------------------------
id | character varying(32) |
money | integer | not null
yw | character(7) | default to_char(now(), 'IYYY-IW'::text)
Indexes:
"pref_money_yw_index" btree (yw)
Foreign-key constraints:
"pref_money_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)

Thank you for the explanations.

I have 4 GB RAM in my Quad-Core AMD Opteron(tm) Processor 1381
CentOS 5.6 / 64 bit machine.

# select * from pg_stat_activity where current_query != '<IDLE>'
order by (now() - query_start) desc;

shows 0 to 3 commands at any time, so it's probably not much?

Regards
Alex

On Thu, May 26, 2011 at 10:02 AM, Alexander Farber
<alexander.farber@gmail.com> wrote:

I've switched duration and SQL 'all' logging on,
but I have hard time to identify which SQL statement
has had which duration.

You need to log more stuff. Look at the log_line_prefix setting, and
add things like pid, username, database name, etc.

Actually I have 1 db user accessing 1 db name
(through PHP scripts and 1 game daemon in Perl)....

On Thu, May 26, 2011 at 10:27 AM, Alexander Farber
<alexander.farber@gmail.com> wrote:

On Thu, May 26, 2011 at 6:23 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

You need to log more stuff.  Look at the log_line_prefix setting, and
add things like pid, username, database name, etc.

Actually I have 1 db user accessing 1 db name
(through PHP scripts and 1 game daemon in Perl)....

Then just use pid or something that can uniquely identify the queries
when they're running.

Scott Marlowe wrote:

Then just use pid or something that can uniquely identify the queries
when they're running.

I recommend %c in log_line_prefix.

Yours,
Laurenz Albe

Hello,

I'm still suffering with my Drupal 7.2 site and
PostgreSQL 8.4.8 every evening, for example
right now. I have tried different combinations
for /etc/pgbouncer.ini - for example now I have:

[databases]
pref = host=/tmp user=pref password=XXX dbname=pref

[pgbouncer]
logfile = /var/log/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
;listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir = /tmp

auth_type = md5
auth_file = /var/lib/pgsql/data/global/pg_auth

pool_mode = session

; If server was used more recently that this many seconds ago,
; skip the check query. Value 0 may or may not run in immidiately.
server_check_delay = 10

max_client_conn = 200
default_pool_size = 20

log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

I have moved Drupal 7.2 and all my PHP scripts
(mostly displaying stats for the players of my card game)
to use the /tmp and port 6432 (instead of 5432).

During off-peak hours the site works ok.

But on evenings everything stops.

For example pg_top shows (why is everything idle?):

last pid: 5215; load avg: 0.65, 1.64, 2.13; up 0+00:46:48

20:16:37
22 processes: 22 sleeping
CPU states: 12.4% user, 0.0% nice, 0.3% system, 87.4% idle, 0.1% iowait
Memory: 1187M used, 2737M free, 34M buffers, 611M cached
Swap: 7812M free

PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
3303 postgres 16 0 1170M 137M sleep 3:29 10.92% 20.16%
postgres: pref pref [local] idle
5045 postgres 18 0 1169M 168M sleep 0:49 0.00% 0.00%
postgres: pref pref [local] idle
5057 postgres 16 0 1169M 168M sleep 0:37 0.00% 0.00%
postgres: pref pref [local] idle
5000 postgres 16 0 1169M 168M sleep 0:34 0.00% 0.00%
postgres: pref pref [local] idle
5025 postgres 18 0 1169M 150M sleep 0:31 0.00% 0.00%
postgres: pref pref [local] idle
5030 postgres 16 0 1171M 152M sleep 0:29 0.00% 0.00%
postgres: pref pref [local] idle
5046 postgres 18 0 1169M 168M sleep 0:28 0.00% 0.00%
postgres: pref pref [local] idle
5001 postgres 18 0 1169M 168M sleep 0:24 0.00% 0.00%
postgres: pref pref [local] idle
5050 postgres 16 0 1169M 168M sleep 0:18 0.00% 0.00%
postgres: pref pref [local] idle
5047 postgres 16 0 1169M 168M sleep 0:18 0.00% 0.00%
postgres: pref pref [local] idle
5014 postgres 16 0 1169M 168M sleep 0:15 0.00% 0.00%
postgres: pref pref [local] idle
5038 postgres 16 0 1171M 73M sleep 0:13 0.00% 0.00%
postgres: pref pref [local] idle
5024 postgres 19 0 1169M 168M sleep 0:10 0.00% 0.00%
postgres: pref pref [local] idle
5004 postgres 25 0 1169M 52M sleep 0:10 0.00% 0.00%
postgres: pref pref [local] idle
5039 postgres 16 0 1169M 70M sleep 0:10 0.00% 0.00%
postgres: pref pref [local] idle
5059 postgres 23 0 1169M 150M sleep 0:10 0.00% 0.00%
postgres: pref pref [local] idle
5015 postgres 16 0 1167M 163M sleep 0:08 0.00% 0.00%
postgres: pref pref [local] idle
5003 postgres 23 0 1169M 167M sleep 0:08 0.00% 0.00%
postgres: pref pref [local] idle
5013 postgres 25 0 1169M 150M sleep 0:07 0.00% 0.00%
postgres: pref pref [local] idle
5051 postgres 23 0 1169M 150M sleep 0:07 0.00% 0.00%
postgres: pref pref [local] idle
4999 postgres 25 0 1169M 30M sleep 0:05 0.00% 0.00%
postgres: pref pref [local] idle
5216 postgres 16 0 1166M 4752K sleep 0:00 0.00% 0.00%
postgres: pref pref [local] idle

In my postgresql.conf I have:

max_connections = 50
shared_buffers = 1024MB

(the rest unchanged, besides log settings.
I can't access logs well enough now,
because I'm on bad GPRS connection from Mallorca...)

In my httpd.conf I have:

<IfModule prefork.c>
StartServers 8
MinSpareServers 5
MaxSpareServers 20
ServerLimit 120
MaxClients 120
MaxRequestsPerChild 4000
</IfModule>

In error_log I see no alarming errors, but I don't
understand why does pg_top show "idle"?

In pgbouncer.log I have:

2011-06-16 20:10:54.315 4997 LOG C-0xbab2f40: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:10:56.472 4997 LOG C-0xbab30a0: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:10:58.488 4997 LOG C-0xbab3200: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:10:58.957 4997 LOG C-0xbab3360: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:02.264 4997 LOG C-0xbab34c0: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:05.782 4997 LOG C-0xbab3620: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:09.348 4997 LOG C-0xbab3780: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:12.349 4997 LOG C-0xbab38e0: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:12.701 4997 LOG C-0xbab3a40: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:14.452 4997 LOG C-0xbab3ba0: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:20.091 4997 LOG C-0xbab3d00: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:21.301 4997 LOG C-0xbab3e60: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:27.123 4997 LOG C-0xbab3fc0: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:32.739 4997 LOG Stats: 20 req/s, in 2366 b/s, out
53821 b/s,query 114176 us
2011-06-16 20:11:34.074 4997 LOG C-0xbab4120: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:35.395 4997 LOG C-0xbab4280: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:37.558 4997 LOG C-0xbab43e0: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:47.783 4997 LOG C-0xbab4540: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:48.624 4997 LOG C-0xbab46a0: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:50.553 4997 LOG C-0xbab4800: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:51.802 4997 LOG C-0xbab4960: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:53.019 4997 LOG C-0xbab4ac0: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:11:54.021 4997 LOG C-0xbab4c20: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:12:03.959 4997 LOG C-0xbab4d80: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:12:11.356 4997 LOG C-0xbab4ee0: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:12:11.794 4997 LOG C-0xbab5040: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:12:28.356 4997 LOG C-0xbab51a0: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:12:29.506 4997 LOG C-0xbab5300: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:12:30.033 4997 LOG C-0xbab5460: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:12:32.740 4997 LOG Stats: 13 req/s, in 1601 b/s, out
20196 b/s,query 107777 us
2011-06-16 20:12:33.978 4997 LOG C-0xbab5460: pref/pref@unix:6432
closing because: client close request (age=3)
2011-06-16 20:12:41.241 4997 LOG C-0xbab5460: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:13:00.454 4997 LOG C-0xbab55c0: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:13:15.025 4997 LOG C-0xbab5720: pref/pref@unix:6432
login attempt: db=pref user=pref
2011-06-16 20:13:32.740 4997 LOG Stats: 3 req/s, in 415 b/s, out 31253
b/s,query 108335 us
2011-06-16 20:14:32.740 4997 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us
2011-06-16 20:15:32.740 4997 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us
2011-06-16 20:16:32.740 4997 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us
2011-06-16 20:17:32.740 4997 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us
2011-06-16 20:18:32.741 4997 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us
2011-06-16 20:19:32.741 4997 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us
2011-06-16 20:20:32.742 4997 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0 us

(why 0 req/s here?)

My machine is a Quad-core with 4GB RAM.
I'm running CentOS 5.6 / 64 bit with PGDG:

pgbouncer-1.3.4-1.rhel5
pgdg-centos-8.4-2
pg_top-3.6.2-1.rhel5
php53-pgsql-5.3.3-1.el5_6.1
compat-postgresql-libs-4-1PGDG.rhel5
postgresql-8.4.8-1PGDG.rhel5
postgresql-devel-8.4.8-1PGDG.rhel5
postgresql-libs-8.4.8-1PGDG.rhel5
postgresql-server-8.4.8-1PGDG.rhel5

It is not loaded since I have pgbouncer active:

top - 20:26:42 up 56 min, 2 users, load average: 0.54, 0.70, 1.38
Tasks: 251 total, 2 running, 249 sleeping, 0 stopped, 0 zombie
Cpu0 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 0.0%us, 0.3%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 13.2%us, 0.0%sy, 0.0%ni, 85.5%id, 1.3%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 20.5%us, 0.3%sy, 0.0%ni, 78.2%id, 0.3%wa, 0.0%hi, 0.7%si, 0.0%st
Mem: 4018280k total, 1229012k used, 2789268k free, 36832k buffers
Swap: 7999472k total, 0k used, 7999472k free, 636948k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2847 afarber 16 0 98808 29m 2704 S 21.2 0.7 11:28.30 pref.pl
3303 postgres 16 0 1169m 137m 134m R 12.9 3.5 4:43.59 postmaster
5239 afarber 15 0 12896 1228 828 R 0.7 0.0 0:00.08 top
1 root 15 0 10372 696 580 S 0.0 0.0 0:01.05 init
2 root RT -5 0 0 0 S 0.0 0.0 0:00.00 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:00.01 ksoftirqd/0
4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
5 root RT -5 0 0 0 S 0.0 0.0 0:00.00 migration/1
6 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/1
7 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/1
9 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/2
10 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/2
11 root RT -5 0 0 0 S 0.0 0.0 0:00.00 migration/3
12 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/3
13 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/3
14 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/0
15 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/1
16 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/2
17 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/3
18 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khelper
55 root 14 -5 0 0 0 S 0.0 0.0 0:00.00 kthread
62 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kblockd/0
63 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kblockd/1
64 root 10 -5 0 0 0 S 0.0 0.0 0:00.02 kblockd/2
....

I have probably misconfigured pgbouncer?

My (poor) site is: preferans.de (currently almost at halt)

Any help please?
Alex