[GENERAL] PostgreSQL 8.4.8 bringing my website down every evening
Alexander Farber
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
--
Sent via pgsql-general mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thom Brown
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
t...@fuzzy.cz
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
Steve Crawford
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
Alexander Farber
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
Alexander Farber
kernel.shmmax = 68719476736
# sysctl kernel.shmall
kernel.shmall = 4294967296
On Wed, May 25, 2011 at 9:54 PM, Alexander Farber
<alexande...@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?
--
Tomas Vondra
> 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
Craig Ringer
> 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
Merlin Moncure
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
Alexander Farber
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
Tomas Vondra
> 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
Merlin Moncure
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
Tomas Vondra
> On Thu, May 26, 2011 at 9:01 AM, Tomas Vondra <t...@fuzzy.cz> wrote:
>> Dne 26.5.2011 11:41, Alexander Farber napsal(a):
>>> 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
Alexander Farber
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
Alexander Farber
# 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?
Scott Marlowe
<alexande...@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.
Alexander Farber
(through PHP scripts and 1 game daemon in Perl)....
Scott Marlowe
<alexande...@gmail.com> wrote:
>
> On Thu, May 26, 2011 at 6:23 PM, Scott Marlowe <scott....@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.
Albe Laurenz
> 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
Alexander Farber
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
Cédric Villemain
> 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
Alexander Farber
> 2011/6/16 Alexander Farber <alexande...@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:
>>
>> 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)
Scott Marlowe
<alexande...@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
Alexander Farber
> 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
> 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
Scott Marlowe
<alexande...@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.
Alban Hertroys
> On 6/16/11, C�dric Villemain <cedric.vill...@gmail.com> wrote:
>> 2011/6/16 Alexander Farber <alexande...@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!
Alexander Farber
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)
Cédric Villemain
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-...@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
--
Alexander Farber
On Sun, Jun 19, 2011 at 9:56 PM, C�dric Villemain
<cedric.vill...@gmail.com> wrote:
> 2011/6/19 Alexander Farber <alexande...@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
Tomas Vondra
> 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
John R Pierce
> 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
Tomas Vondra
> 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
Tomas Vondra
> As another possible improvement, I'd probably not create a new connection in every function call, but use a global $db object instead. 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.
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