Обсуждение: 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 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
On 25 May 2011 18:58, Alexander Farber <alexander.farber@gmail.com> wrote:
--
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
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: > shared_buffers = 512MB > > Do you think I need to reconfigure CentOS 5.6 > for the bigger shared memory too or > will it adapt by itself?
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 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. >
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
2011/6/16 Alexander Farber <alexander.farber@gmail.com>: > 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 > you probably want to use transaction mode here, instead of session. -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
On 6/16/11, Cédric Villemain <cedric.villemain.debian@gmail.com> wrote: > 2011/6/16 Alexander Farber <alexander.farber@gmail.com>: >> 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: >> >> [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 >> > > you probably want to use transaction mode here, instead of session. > Is transaction mode better for PHP scripts with persistent connections? I'll try that, thank you (rebooting right now... GPRS sucks)
On Thu, Jun 16, 2011 at 1:27 PM, Alexander Farber <alexander.farber@gmail.com> wrote: > 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% What do vmstat 1 and iostat -xd 1 (or equivalent for your OS) say? So here's a healthy not working too hard machine: procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 4 0 0 552768 480820 121441792 0 0 1 24 0 0 8 1 91 0 5 0 0 556168 480820 121441664 0 0 0 468 4005 4763 11 1 88 0 2 0 0 561660 480820 121441680 0 0 0 164 4032 4555 10 1 88 0 1 0 0 555076 480820 121441680 0 0 12 300 4171 4710 5 0 94 0 Note that the in and cs values are pretty reasonable (interrupts and context switches / sec) and idle CPU is ok. 0 wait. If those cs and in start climbing and the wait starts climbing your IO is getting saturated. In iostat keep an eye on %util
On 6/16/11, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> What do vmstat 1 and iostat -xd 1 (or equivalent for your OS) say? So
> here's a healthy not working too hard machine:
>
> procs -----------memory---------- ---swap-- -----io---- -system--
> ----cpu----
> r b swpd free buff cache si so bi bo in cs us sy id
> wa
> 4 0 0 552768 480820 121441792 0 0 1 24 0 0 8 1
> 91 0
> Note that the in and cs values are pretty reasonable (interrupts and
> context switches / sec) and idle CPU is ok. 0 wait.
>
> If those cs and in start climbing and the wait starts climbing your IO
> is getting saturated.
>
> In iostat keep an eye on %util
It shows (but I have switched pgbouncer from session
to transaction mode now and it seems to have helped -
Drupal+my scripts are running again and pg_top shows
2-3 SELECTs at the top and the rest is "idle"):
# vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
3 1 0 2896644 30748 617744 0 0 70 359 350 260 61
1 37 1 0
2 0 0 2875252 30760 619516 0 0 8 140 1333 1231 46
1 53 0 0
2 0 0 2873888 30768 618160 0 0 0 332 1293 1294 59
1 40 1 0
2 0 0 2871904 30768 619920 0 0 0 16 1257 453 38
0 61 0 0
4 0 0 2872896 30768 618136 0 0 0 24 1204 574 51
1 48 0 0
2 1 0 2868308 30768 622096 0 0 0 5608 1304 603 66
1 33 0 0
2 0 0 2870628 30780 620688 0 0 0 236 1329 999 53
1 46 2 0
0 0 0 2873356 30792 616816 0 0 0 88 1247 696 13
1 85 0 0
1 0 0 2873736 30792 616708 0 0 0 80 1270 745 20
0 80 0 0
0 0 0 2873736 30792 616708 0 0 0 44 1234 535 10
1 89 0 0
0 0 0 2873800 30804 616708 0 0 0 252 1249 576 13
0 86 0 0
2 0 0 2900708 30804 616932 0 0 0 72 1340 646 21
1 79 0 0
2 0 0 2898360 30812 618908 0 0 0 160 1549 799 45
0 54 0 0
3 0 0 2895004 30812 621448 0 0 0 0 1622 829 46
2 52 0 0
2 0 0 2896068 30812 619320 0 0 0 72 1501 1115 50
0 50 0 0
3 0 0 2897804 30820 617196 0 0 0 48 1445 1044 50
1 49 0 0
1 0 0 2895820 30832 619024 0 0 0 1128 1431 826 44
0 55 0 0
3 0 0 2893292 30844 621212 0 0 0 88 1353 1379 52
1 47 0 0
2 0 0 2894424 30844 620380 0 0 0 56 1298 668 63
1 36 0 0
3 0 0 2890580 30852 624332 0 0 0 8 1251 480 60
1 39 0 0
5 0 0 2895696 30860 618688 0 0 0 80 1304 876 75
1 24 0 0
6 0 0 2890612 30876 624612 0 0 0 3636 1437 610 87
1 12 0 0
4 0 0 2891908 30900 622388 0 0 0 112 1316 960 99
1 0 0 0
3 0 0 2890544 30908 624360 0 0 0 0 1279 1174 97
0 3 0 0
2 0 0 2891908 30912 623860 0 0 0 112 1289 1319 80
1 19 0 0
2 0 0 2896868 30924 617672 0 0 0 88 1242 764 53
0 47 0 0
1 0 0 2894264 30936 619504 0 0 0 1492 1433 733 44
1 54 0 0
1 0 0 2894884 30944 616824 0 0 0 84 1556 846 39
1 60 0 0
2 0 0 2894544 30944 616784 0 0 0 24 1436 710 14
1 85 0 0
3 0 0 2871412 30944 618220 0 0 16 96 1335 1511 48
1 51 0 0
2 0 0 2867444 30944 622332 0 0 0 332 1429 546 61
1 38 0 0
3 0 0 2867196 30956 621984 0 0 0 6684 1522 1033 75
1 24 0 0
And I have RAID1, but it seems to be ok right now:
# cat /proc/mdstat
Personalities : [raid1]
md0 : active raid1 sdb1[1] sda1[0]
1023936 blocks [2/2] [UU]
md2 : active raid1 sdb5[1] sda5[0]
277728192 blocks [2/2] [UU]
md3 : active raid1 sdb6[1] sda6[0]
185151360 blocks [2/2] [UU]
md1 : active raid1 sdb3[1] sda3[0]
20479936 blocks [2/2] [UU]
unused devices: <none>
Regards
Alex
On Thu, Jun 16, 2011 at 2:08 PM, Alexander Farber <alexander.farber@gmail.com> wrote: > It shows (but I have switched pgbouncer from session > to transaction mode now and it seems to have helped - > Drupal+my scripts are running again and pg_top shows > 2-3 SELECTs at the top and the rest is "idle"): > > # vmstat 1 > procs -----------memory---------- ---swap-- -----io---- --system-- > -----cpu------ > r b swpd free buff cache si so bi bo in cs us sy id wa st > 3 1 0 2896644 30748 617744 0 0 70 359 350 260 61 > 1 37 1 0 > 2 0 0 2875252 30760 619516 0 0 8 140 1333 1231 46 > 1 53 0 0 > 2 0 0 2873888 30768 618160 0 0 0 332 1293 1294 59 > 1 40 1 0 > 2 0 0 2871904 30768 619920 0 0 0 16 1257 453 38 > 0 61 0 0 > 4 0 0 2872896 30768 618136 0 0 0 24 1204 574 51 > 1 48 0 0 > 2 1 0 2868308 30768 622096 0 0 0 5608 1304 603 66 > 1 33 0 0 > 2 0 0 2870628 30780 620688 0 0 0 236 1329 999 53 > 1 46 2 0 I assume that this is with things working properly. Keep an eye on it when the server is running slow to see what's changing. So far it looks like that one change may have made a difference but I wouldn't assume you're completely out of the woods just yet.
On 16 Jun 2011, at 21:42, Alexander Farber wrote: > On 6/16/11, Cédric Villemain <cedric.villemain.debian@gmail.com> wrote: >> 2011/6/16 Alexander Farber <alexander.farber@gmail.com>: >>> 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: >>> >>> [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 >>> >> >> you probably want to use transaction mode here, instead of session. >> > > Is transaction mode better for PHP scripts with > persistent connections? I'll try that, thank you > (rebooting right now... GPRS sucks) I think the intention was to use a pooler INSTEAD OF php persistent connections. Having both doesn't make sense to me. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4dfaf87012091396613445!
Hello everyone,
after the suggestion from this mailing list,
I have installed pgbouncer at my
CentOS 5.6 / 64 bit server and
activated its transaction mode:
[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 = transaction
server_check_delay = 10
max_client_conn = 200
default_pool_size = 20
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1
Now the server stopped crashing even
at peak times and "pg_top -I" only shows
few simultaneous commands active:
last pid: 13476; load avg: 4.03, 4.02, 4.29; up 2+22:57:32
19:37:05
16 processes: 3 running, 13 sleeping
CPU states: 67.8% user, 0.0% nice, 0.7% system, 27.0% idle, 4.5% iowait
Memory: 3363M used, 561M free, 374M buffers, 2377M cached
Swap: 7812M free
PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
13018 postgres 20 0 1173M 179M run 4:08 11.39% 99.99%
postgres: pref pref [local] SELECT
13144 postgres 18 0 1172M 179M run 3:38 6.11% 84.15%
postgres: pref pref [local] SELECT
1636 postgres 16 0 1170M 152M run 186:34 4.67% 20.79%
postgres: pref pref [local] SELECT
12761 postgres 16 0 1173M 180M sleep 3:16 20.22% 5.94%
postgres: pref pref [local] idle
And in the /var/log/pgbouncer.log shows:
2011-06-19 19:28:05.772 3031 LOG Stats: 29 req/s, in 4087 b/s, out
116615 b/s,query 106024 us
2011-06-19 19:29:05.772 3031 LOG Stats: 29 req/s, in 3665 b/s, out
39510 b/s,query 71303 us
2011-06-19 19:30:05.772 3031 LOG Stats: 32 req/s, in 4318 b/s, out
90909 b/s,query 115946 us
2011-06-19 19:31:05.773 3031 LOG Stats: 33 req/s, in 4686 b/s, out
79397 b/s,query 84436 us
2011-06-19 19:32:05.774 3031 LOG Stats: 31 req/s, in 4478 b/s, out
108103 b/s,query 104060 us
But I have a new (not so bad problem) -
My php script displaying player stats:
http://preferans.de/user.php?id=OK493430777441
will sometimes exit with the PDO error:
SQLSTATE[26000]: Invalid sql statement name:
7 ERROR: prepared statement
"pdo_stmt_00000016" does not exist
When I reload it, it works ok.
The SQL statements called by the script are:
try {
# enable persistent connections and throw exception on any errors
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_PERSISTENT => true);
$db = new PDO('pgsql:host=' . DBHOST . ' port=6432; dbname=' .
DBNAME, DBUSER, DBPASS, $options);
$sth = $db->prepare('
select first_name, city, avatar, login > logout as online
from pref_users where id=?
');
$sth->execute(array($id));
and so on - a dozen SELECT statements.
I wonder, if this "prepared statement not found"
problem is caused by transaction mode of pgbouncer
and if there is a way to workaround that?
And I can't switch to pgbouncer session mode,
because it will hang at peak time - tried that already.
Thank you
Alex
P.S. Here again my specs:
pgbouncer-1.3.4-1.rhel5
postgresql-libs-8.4.8-1PGDG.rhel5
compat-postgresql-libs-4-1PGDG.rhel5
postgresql-8.4.8-1PGDG.rhel5
postgresql-server-8.4.8-1PGDG.rhel5
postgresql-devel-8.4.8-1PGDG.rhel5
php53-pgsql-5.3.3-1.el5_6.1
php53-pdo-5.3.3-1.el5_6.1
php53-5.3.3-1.el5_6.1
I have 4GB RAM and postgresql.conf contains:
max_connections = 50
shared_buffers = 1024MB
#listen_addresses = 'localhost' (i.e. unix socket only)
2011/6/19 Alexander Farber <alexander.farber@gmail.com>: > Hello everyone, > > after the suggestion from this mailing list, > I have installed pgbouncer at my > CentOS 5.6 / 64 bit server and > activated its transaction mode: > > [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 = transaction > > server_check_delay = 10 > > max_client_conn = 200 > default_pool_size = 20 > > log_connections = 0 > log_disconnections = 0 > log_pooler_errors = 1 > > Now the server stopped crashing even > at peak times and "pg_top -I" only shows > few simultaneous commands active: > > last pid: 13476; load avg: 4.03, 4.02, 4.29; up 2+22:57:32 > 19:37:05 > 16 processes: 3 running, 13 sleeping > CPU states: 67.8% user, 0.0% nice, 0.7% system, 27.0% idle, 4.5% iowait > Memory: 3363M used, 561M free, 374M buffers, 2377M cached > Swap: 7812M free > > PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND > 13018 postgres 20 0 1173M 179M run 4:08 11.39% 99.99% > postgres: pref pref [local] SELECT > 13144 postgres 18 0 1172M 179M run 3:38 6.11% 84.15% > postgres: pref pref [local] SELECT > 1636 postgres 16 0 1170M 152M run 186:34 4.67% 20.79% > postgres: pref pref [local] SELECT > 12761 postgres 16 0 1173M 180M sleep 3:16 20.22% 5.94% > postgres: pref pref [local] idle > > And in the /var/log/pgbouncer.log shows: > > 2011-06-19 19:28:05.772 3031 LOG Stats: 29 req/s, in 4087 b/s, out > 116615 b/s,query 106024 us > 2011-06-19 19:29:05.772 3031 LOG Stats: 29 req/s, in 3665 b/s, out > 39510 b/s,query 71303 us > 2011-06-19 19:30:05.772 3031 LOG Stats: 32 req/s, in 4318 b/s, out > 90909 b/s,query 115946 us > 2011-06-19 19:31:05.773 3031 LOG Stats: 33 req/s, in 4686 b/s, out > 79397 b/s,query 84436 us > 2011-06-19 19:32:05.774 3031 LOG Stats: 31 req/s, in 4478 b/s, out > 108103 b/s,query 104060 us > > But I have a new (not so bad problem) - > > My php script displaying player stats: > http://preferans.de/user.php?id=OK493430777441 > will sometimes exit with the PDO error: > > SQLSTATE[26000]: Invalid sql statement name: > 7 ERROR: prepared statement > "pdo_stmt_00000016" does not exist > > When I reload it, it works ok. > > The SQL statements called by the script are: > > try { > # enable persistent connections and throw exception on any errors > $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, > PDO::ATTR_PERSISTENT => true); > $db = new PDO('pgsql:host=' . DBHOST . ' port=6432; dbname=' . > DBNAME, DBUSER, DBPASS, $options); > > $sth = $db->prepare(' > select first_name, city, avatar, login > logout as online > from pref_users where id=? > '); > $sth->execute(array($id)); > > and so on - a dozen SELECT statements. > > I wonder, if this "prepared statement not found" > problem is caused by transaction mode of pgbouncer > and if there is a way to workaround that? Ooops, I would have been more explicit here! You need to add begin/commit to build a transaction. From PHP::PDO doc: http://www.php.net/manual/en/pdo.begintransaction.php <? /* Begin a transaction, turning off autocommit */ $dbh->beginTransaction(); /* Change the database schema */ $sth = $dbh->exec("DROP TABLE fruit"); /* Commit the changes */ $dbh->commit(); /* Database connection is now back in autocommit mode */ ?> An alternative can be to use pre_prepare: https://github.com/dimitri/preprepare Please read the README carefully for this one if you intend to use it. > > And I can't switch to pgbouncer session mode, > because it will hang at peak time - tried that already. > > Thank you > Alex > > P.S. Here again my specs: > > pgbouncer-1.3.4-1.rhel5 > postgresql-libs-8.4.8-1PGDG.rhel5 > compat-postgresql-libs-4-1PGDG.rhel5 > postgresql-8.4.8-1PGDG.rhel5 > postgresql-server-8.4.8-1PGDG.rhel5 > postgresql-devel-8.4.8-1PGDG.rhel5 > php53-pgsql-5.3.3-1.el5_6.1 > php53-pdo-5.3.3-1.el5_6.1 > php53-5.3.3-1.el5_6.1 > > I have 4GB RAM and postgresql.conf contains: > max_connections = 50 > shared_buffers = 1024MB > #listen_addresses = 'localhost' (i.e. unix socket only) > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
Hello Cedric and others, On Sun, Jun 19, 2011 at 9:56 PM, Cédric Villemain <cedric.villemain.debian@gmail.com> wrote: > 2011/6/19 Alexander Farber <alexander.farber@gmail.com>: >> [pgbouncer] >> logfile = /var/log/pgbouncer.log >> pidfile = /var/run/pgbouncer/pgbouncer.pid >> listen_port = 6432 >> unix_socket_dir = /tmp >> auth_type = md5 >> auth_file = /var/lib/pgsql/data/global/pg_auth >> pool_mode = transaction >> server_check_delay = 10 >> max_client_conn = 200 >> default_pool_size = 20 >> My php script displaying player stats: >> http://preferans.de/user.php?id=OK493430777441 >> will sometimes exit with the PDO error: >> >> SQLSTATE[26000]: Invalid sql statement name: >> 7 ERROR: prepared statement >> "pdo_stmt_00000016" does not exist >> >> try { >> # enable persistent connections and throw exception on any errors >> $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, >> PDO::ATTR_PERSISTENT => true); >> $db = new PDO('pgsql:host=' . DBHOST . ' port=6432; dbname=' . >> DBNAME, DBUSER, DBPASS, $options); >> >> $sth = $db->prepare(' >> select first_name, city, avatar, login > logout as online >> from pref_users where id=? >> '); >> $sth->execute(array($id)); >> >> and so on - a dozen SELECT statements. >> > Ooops, I would have been more explicit here! > You need to add begin/commit to build a transaction. > From PHP::PDO doc: > http://www.php.net/manual/en/pdo.begintransaction.php > <? > /* Begin a transaction, turning off autocommit */ > $dbh->beginTransaction(); > > /* Change the database schema */ > $sth = $dbh->exec("DROP TABLE fruit"); > > /* Commit the changes */ > $dbh->commit(); > > /* Database connection is now back in autocommit mode */ > ?> > > An alternative can be to use pre_prepare: > https://github.com/dimitri/preprepare > > Please read the README carefully for this one if you intend to use it. why add a begin/commit if I only have SELECT statements there (in the default mode) and the data isn't critical to me (just some player statistics and notes by other players - i.e. a statistic or note is ok to be lost occasionally)? Also I've changed my PHP-script to non-persistent connections: $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION); $db = new PDO(sprintf('pgsql:host=%s port=%u; dbname=%s', DBHOST, DBPORT, DBNAME), DBUSER, DBPASS, $options); and restarted Apache 2.2.3, but that error is still there: SQLSTATE[26000]: Invalid sql statement name: 7 ERROR: prepared statement "pdo_stmt_0000000a" does not exist Regards Alex
2011/6/19 Alexander Farber <alexander.farber@gmail.com>: > Hello Cedric and others, > > On Sun, Jun 19, 2011 at 9:56 PM, Cédric Villemain > <cedric.villemain.debian@gmail.com> wrote: >> 2011/6/19 Alexander Farber <alexander.farber@gmail.com>: >>> [pgbouncer] >>> logfile = /var/log/pgbouncer.log >>> pidfile = /var/run/pgbouncer/pgbouncer.pid >>> listen_port = 6432 >>> unix_socket_dir = /tmp >>> auth_type = md5 >>> auth_file = /var/lib/pgsql/data/global/pg_auth >>> pool_mode = transaction >>> server_check_delay = 10 >>> max_client_conn = 200 >>> default_pool_size = 20 > >>> My php script displaying player stats: >>> http://preferans.de/user.php?id=OK493430777441 >>> will sometimes exit with the PDO error: >>> >>> SQLSTATE[26000]: Invalid sql statement name: >>> 7 ERROR: prepared statement >>> "pdo_stmt_00000016" does not exist >>> >>> try { >>> # enable persistent connections and throw exception on any errors >>> $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, >>> PDO::ATTR_PERSISTENT => true); >>> $db = new PDO('pgsql:host=' . DBHOST . ' port=6432; dbname=' . >>> DBNAME, DBUSER, DBPASS, $options); >>> You have your first request here : >>> $sth = $db->prepare(' >>> select first_name, city, avatar, login > logout as online >>> from pref_users where id=? >>> '); then the second: >>> $sth->execute(array($id)); in auto-commit, each request/transaction will probably be affected to a different connection with pgbouncer in mode transaction. So you need to have a begin/commit before/after them to be sure everything is executed together. >>> >>> and so on - a dozen SELECT statements. >>> >> Ooops, I would have been more explicit here! >> You need to add begin/commit to build a transaction. >> From PHP::PDO doc: >> http://www.php.net/manual/en/pdo.begintransaction.php >> <? >> /* Begin a transaction, turning off autocommit */ >> $dbh->beginTransaction(); >> >> /* Change the database schema */ >> $sth = $dbh->exec("DROP TABLE fruit"); >> >> /* Commit the changes */ >> $dbh->commit(); >> >> /* Database connection is now back in autocommit mode */ >> ?> >> >> An alternative can be to use pre_prepare: >> https://github.com/dimitri/preprepare >> >> Please read the README carefully for this one if you intend to use it. > > why add a begin/commit if I only > have SELECT statements > there (in the default mode) and > the data isn't critical to me > (just some player statistics and > notes by other players - i.e. > a statistic or note is ok to be lost > occasionally)? > > Also I've changed my PHP-script > to non-persistent connections: > > $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION); > $db = new PDO(sprintf('pgsql:host=%s port=%u; dbname=%s', > DBHOST, DBPORT, DBNAME), DBUSER, DBPASS, $options); > > and restarted Apache 2.2.3, > but that error is still there: > > SQLSTATE[26000]: Invalid sql > statement name: 7 ERROR: prepared > statement "pdo_stmt_0000000a" does not exist > > Regards > Alex > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
On Mon, Jun 20, 2011 at 1:43 AM, Alexander Farber <alexander.farber@gmail.com> wrote:
Hello Cedric and others,
On Sun, Jun 19, 2011 at 9:56 PM, Cédric Villemain<cedric.villemain.debian@gmail.com> wrote:> 2011/6/19 Alexander Farber <alexander.farber@gmail.com>:>> [pgbouncer]
>> logfile = /var/log/pgbouncer.log
>> pidfile = /var/run/pgbouncer/pgbouncer.pid>> listen_port = 6432
>> unix_socket_dir = /tmp
>> auth_type = md5
>> auth_file = /var/lib/pgsql/data/global/pg_auth
>> pool_mode = transaction
>> server_check_delay = 10
>> max_client_conn = 200
>> default_pool_size = 20>> My php script displaying player stats:
>> http://preferans.de/user.php?id=OK493430777441
>> will sometimes exit with the PDO error:
>>
>> SQLSTATE[26000]: Invalid sql statement name:
>> 7 ERROR: prepared statement
>> "pdo_stmt_00000016" does not exist
>>
<snip>
why add a begin/commit if I only
have SELECT statements
there (in the default mode) and
the data isn't critical to me
(just some player statistics and
notes by other players - i.e.
a statistic or note is ok to be lost
occasionally)?
Also I've changed my PHP-script
to non-persistent connections:
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
$db = new PDO(sprintf('pgsql:host=%s port=%u; dbname=%s',
DBHOST, DBPORT, DBNAME), DBUSER, DBPASS, $options);
and restarted Apache 2.2.3,
but that error is still there:statement "pdo_stmt_0000000a" does not exist
SQLSTATE[26000]: Invalid sql
statement name: 7 ERROR: prepared
Regards
Alex
Try setting "set server_reset_query = DISCARD ALL;" in your pgbouncer configuration file.
Amitabh
On Mon, Jun 20, 2011 at 5:08 AM, Amitabh Kant <amitabhkant@gmail.com> wrote: > On Mon, Jun 20, 2011 at 1:43 AM, Alexander Farber > <alexander.farber@gmail.com> wrote: >> >> Hello Cedric and others, >> >> On Sun, Jun 19, 2011 at 9:56 PM, Cédric Villemain >> <cedric.villemain.debian@gmail.com> wrote: >> > 2011/6/19 Alexander Farber <alexander.farber@gmail.com>: >> >> [pgbouncer] >> >> logfile = /var/log/pgbouncer.log >> >> pidfile = /var/run/pgbouncer/pgbouncer.pid >> >> listen_port = 6432 >> >> unix_socket_dir = /tmp >> >> auth_type = md5 >> >> auth_file = /var/lib/pgsql/data/global/pg_auth >> >> pool_mode = transaction >> >> server_check_delay = 10 >> >> max_client_conn = 200 >> >> default_pool_size = 20 >> >> >> My php script displaying player stats: >> >> http://preferans.de/user.php?id=OK493430777441 >> >> will sometimes exit with the PDO error: >> >> >> >> SQLSTATE[26000]: Invalid sql statement name: >> >> 7 ERROR: prepared statement >> >> "pdo_stmt_00000016" does not exist >> >> > > <snip> >> >> why add a begin/commit if I only >> have SELECT statements >> there (in the default mode) and >> the data isn't critical to me >> (just some player statistics and >> notes by other players - i.e. >> a statistic or note is ok to be lost >> occasionally)? >> >> Also I've changed my PHP-script >> to non-persistent connections: >> >> $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION); >> $db = new PDO(sprintf('pgsql:host=%s port=%u; dbname=%s', >> DBHOST, DBPORT, DBNAME), DBUSER, DBPASS, $options); >> >> and restarted Apache 2.2.3, >> but that error is still there: >> >> SQLSTATE[26000]: Invalid sql >> statement name: 7 ERROR: prepared >> statement "pdo_stmt_0000000a" does not exist >> >> Regards >> Alex >> > > Try setting "set server_reset_query = DISCARD ALL;" in your pgbouncer > configuration file. No, in transaction mode the reset_query should actually be empty. Instead you need to disable use of db-side prepared statements by adding option PDO::ATTR_EMULATE_PREPARES => true -- marko
I've added $db->beginTransaction(); .... $db->commit(); around _all_ statements, but now get: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block quite often. I don't understand why would transaction get aborted here... Maybe I should try session mode of pgbouncer again, now that I've got rid of the persistent PHP connections? Regards Alex
On Mon, Jun 20, 2011 at 9:36 AM, Alexander Farber <alexander.farber@gmail.com> wrote: > I've added > > $db->beginTransaction(); > .... > $db->commit(); > > around _all_ statements, but now get: I don't think that was a good idea. > SQLSTATE[25P02]: In failed sql transaction: > 7 ERROR: current transaction is aborted, > commands ignored until end of transaction block > > quite often. I don't understand why > would transaction get aborted here... Probably some commands were meant to be run in same transaction, or were transaction control commands. Don't do it without synchronizing with business logic... > Maybe I should try session mode of pgbouncer > again, now that I've got rid of the persistent > PHP connections? You could, but try to turn off prepared statements in PDO first. -- marko
Hell Marko and others, On Mon, Jun 20, 2011 at 4:08 PM, Marko Kreen <markokr@gmail.com> wrote: >> Maybe I should try session mode of pgbouncer >> again, now that I've got rid of the persistent >> PHP connections? > > You could, but try to turn off prepared > statements in PDO first. isn't having prepared statements good for overall performance? I've decided to try another way first - I've set "pgsql.allow_persistent = Off" in /etc/php.ini and have changed pgbouncer back to session mode (sorry, here's my config again - it unfortunately was eaten by Gmail in the previous mail): [databases] pref = host=/tmp user=pref password=XXX dbname=pref [pgbouncer] logfile = /var/log/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid listen_port = 6432 unix_socket_dir = /tmp auth_type = md5 auth_file = /var/lib/pgsql/data/global/pg_auth pool_mode = session server_check_delay = 10 max_client_conn = 200 default_pool_size = 20 log_connections = 0 log_disconnections = 0 log_pooler_errors = 1 I'll see, if my server survives the next few evenings. I must add, that PostgreSQL doesn't make it easy to use it - at least for me as an amateur user :-( Wonder, if MySQL would put less hassle on me (just want to run Drupal 7.2 + my custom PHP/Perl scripts on what I think is a good enough hardware...) Still I will try to stick with PostgreSQL, I somehow have a good feeling using it :-) Regards Alex
Nope, pool_mode = session kills my site...
PDO::ATTR_EMULATE_PREPARES => true kills my server too... On Mon, Jun 20, 2011 at 7:34 PM, Alexander Farber <alexander.farber@gmail.com> wrote: > Nope, pool_mode = session kills my site... >
At 04:13 AM 6/20/2011, Alexander Farber wrote: >why add a begin/commit if I only >have SELECT statements >there (in the default mode) and >the data isn't critical to me >(just some player statistics and >notes by other players - i.e. >a statistic or note is ok to be lost >occasionally)? If you're not intending to write anything to the database you could try do a rollback instead of commit. I'm not familiar with your system, but if you are getting > SQLSTATE[25P02]: In failed sql transaction: > 7 ERROR: current transaction is aborted, > commands ignored until end of transaction block Maybe you can do a rollback, begin, followed by your sql statements then rollback again. You would get warnings for a rollback followed by rollback, but they might not be fatal (not sure about your config). There might be a problem with one of the SQL queries and that's why it cannot be committed. Alternatively the program logic might be bypassing a COMMIT. A BEGIN followed by a BEGIN would cause an "already in transaction" error which would cause the "transaction is aborted" problem. Regards, Link.
2011/6/21 Lincoln Yeoh <lyeoh@pop.jaring.my>:
> At 04:13 AM 6/20/2011, Alexander Farber wrote:
>>
>> why add a begin/commit if I only
>> have SELECT statements
>> there (in the default mode) and
>> the data isn't critical to me
>> (just some player statistics and
>> notes by other players - i.e.
>> a statistic or note is ok to be lost
>> occasionally)?
>
> If you're not intending to write anything to the database you could try do a
> rollback instead of commit.
>
> I'm not familiar with your system, but if you are getting
>
>> SQLSTATE[25P02]: In failed sql transaction:
>> 7 ERROR: current transaction is aborted,
>> commands ignored until end of transaction block
I understood that the OP put a begin/commit around *each* request (so
one around prepare, and one around exec)
Php documentation contains useful information with examples.
Alexander, I suggest you to just add a begin before the 'prepare' and
a commit after the 'exec', not between each.
>
> Maybe you can do a rollback, begin, followed by your sql statements then
> rollback again.
>
> You would get warnings for a rollback followed by rollback, but they might
> not be fatal (not sure about your config).
>
> There might be a problem with one of the SQL queries and that's why it
> cannot be committed.
>
> Alternatively the program logic might be bypassing a COMMIT. A BEGIN
> followed by a BEGIN would cause an "already in transaction" error which
> would cause the "transaction is aborted" problem.
this is not correct
cedric=# begin ;
BEGIN
cedric=# begin ;
ATTENTION: une transaction est déjà en cours
BEGIN
cedric=# select 1;
?column?
----------
1
(1 ligne)
cedric=# commit;
COMMIT
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
Sorry for the late reply - but I still haven't found a solution, for example I have a PHP script with 5 consecutive SELECT statements (source code + problem described again under: http://stackoverflow.com/questions/6458246/php-and-pgbouncer-in-transaction-mode-current-transaction-is-aborted ) and if I add $db->beginTransaction(); $db->commit(); around each $db->prepare("select ..."); execute(); then my script will fail very often with SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block But if I omit $db->beginTransaction(); $db->commit(); completely, then it will fail less often, but still fail with: SQLSTATE[26000]: Invalid sql statement name: 7 ERROR: prepared statement "pdo_stmt_00000016" does not exist My guess is that the 2nd behaviour is less frequent because it occurs only when pgbouncer switches connections exactly between prepare()/execute(). If I try PDO::ATTR_EMULATE_PREPARES => true or pool_mode = session - my web site will hang. Regards Alex
> Sorry for the late reply - but I still haven't found a solution, > for example I have a PHP script with 5 consecutive SELECT > statements (source code + problem described again under: > > http://stackoverflow.com/questions/6458246/php-and-pgbouncer-in-transaction-mode-current-transaction-is-aborted > > ) and if I add $db->beginTransaction(); $db->commit(); > around each $db->prepare("select ..."); execute(); > then my script will fail very often with > > SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current > transaction is aborted, commands ignored until end of transaction > block In that case there's something wrong - probably an error or something, that aborts a transaction. You have to investigate this first, see the postgresql log and try to call errorInfo/errorCode of the PDO. BTW why are you using prepared statements? That's great for running a query multiple times with various parameter values, but according to the PHP you're executing each statement exactly once. Even if the function is called repeatedly, the statements will be prepared over and over because you're calling a 'prepare' right before the execute. So what you actually get is this PREPARE EXECUTE PREPARE EXECUTE but you'd like to get this PREPARE EXECUTE EXECUTE If you can't do this, just use a plain PDO::query and those prepared statement errors will be gone. Plus it might actually improve the performance (with prepared statements the optimizer does not know the actual values when planning the query, which sometimes prevents him from choosing a good plan). regards Tomas
Dne 20.6.2011 18:47, Alexander Farber napsal(a): > isn't having prepared statements good for overall performance? I've already mentioned that in my previous post, but let's make this clear. Prepared statements are good for performance, but only if you're going to execute the statement multiple times in the same session. When running a SQL statement, the database has to parse it and plan it first, which may be a lot of work (depending on how complex the statement is etc.). Prepared statements allow you to do this (parsing and planning) only once, which may significantly improve the performance. Let's say you have a statement that takes 10ms to parse/plan and 50ms to actually execute, and you want to execute it 100x. If you're going to do this without prepared statements, then you'll spend 100x 10ms for planning and 100x 50ms for execution. That's 6 seconds in total. With prepared statements, this takes only 5 seconds. Yes, it all depends on how much time you spend in planning vs. executing the query. And there's a downside too - with prepared statements the the planner can't use the actual parameter values to choose the plan (it does not know them), so it may choose a plan that's good on average but sucks for some parameter values. If my assumption that your code executes each SQL exactly once per session is right, then get right of the prepared statements and use plain PDO::query instead. That should fix the problems you currently have - you can keep the transaction mode in pgpool, you won't get those annoying prepared statement exceptions and you don't need to put the transactions there. Plus it's very likely the optimizer will be able to come up with a better plan. Tomas
On 06/24/11 4:51 PM, Tomas Vondra wrote: > And there's a downside too - with prepared statements the the planner > can't use the actual parameter values to choose the plan (it does not > know them), so it may choose a plan that's good on average but sucks for > some parameter values. indeed, this can really bite you on partitioned tables. My lead Oracle programmer would like to see support for prepared statements that are parsed but not preplanned... our standard coding model has all the queries prepared up front as part of process initialization, and queries executed against these prepared statements. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
Dne 25.6.2011 02:15, John R Pierce napsal(a): > indeed, this can really bite you on partitioned tables. My lead > Oracle programmer would like to see support for prepared statements that > are parsed but not preplanned... our standard coding model has all the > queries prepared up front as part of process initialization, and queries > executed against these prepared statements. AFAIK Oracle does a plan recheck, i.e. verifies that the prepared plan is reasonable. The most serious issue with inheritance and plans (prepared statement did not reflect partitions created afterwards) has already been fixed. Tomas
On 23 Jun 2011, at 19:48, Alexander Farber wrote: > Sorry for the late reply - but I still haven't found a solution, > for example I have a PHP script with 5 consecutive SELECT > statements (source code + problem described again under: > > http://stackoverflow.com/questions/6458246/php-and-pgbouncer-in-transaction-mode-current-transaction-is-aborted > > ) and if I add $db->beginTransaction(); $db->commit(); > around each $db->prepare("select ..."); execute(); > then my script will fail very often with > > SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current > transaction is aborted, commands ignored until end of transaction > block You almost got it right, but you added too many beginTransaction/commit pairs. You only need a beginTransaction after you set up your database connection in your function, and a commit at the very end.You're supposed to put related queries in one transaction so that they either all commit, or none of them do if there'san error in one of the statements. As another possible improvement, I'd probably not create a new connection in every function call, but use a global $db objectinstead. Creating DB-connections is relatively expensive, so you don't want to do that more often than strictly necessary.In that case you could probably use beginTransaction at the start of your script and commit at the end. But that all depends on your requirements, of course. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4e05c52212095034320330!
Dne 25.6.2011 13:22, Alban Hertroys napsal(a): > As another possible improvement, I'd probably not create a new connection in every function call, but use a global $dbobject instead. Creating DB-connections is relatively expensive, so you don't want to do that more often than strictlynecessary. In that case you could probably use beginTransaction at the start of your script and commit at the end. Not really - he already uses a pgbouncer. Sure, obtaining a connection from pgbouncer still an overhead, but much smaller than creating a true pg connection. But you're right it's a bad programming habbit, and the connection should be closed at the end. regards Tomas