Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-26 Thread Tomas Vondra
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 $db object instead. Creating > DB-connections is relatively expensive, so you don't want to do that more > often than strictly nec

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-26 Thread Alban Hertroys
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-pgboun

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-24 Thread Tomas Vondra
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

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-24 Thread John R Pierce
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 r

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-24 Thread Tomas Vondra
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 tim

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-24 Thread tv
> 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-abort

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-23 Thread Alexander Farber
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

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-21 Thread Cédric Villemain
2011/6/21 Lincoln Yeoh : > 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

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-21 Thread Lincoln Yeoh
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 i

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-20 Thread Alexander Farber
PDO::ATTR_EMULATE_PREPARES => true kills my server too... On Mon, Jun 20, 2011 at 7:34 PM, Alexander Farber wrote: > Nope, pool_mode = session kills my site... > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-20 Thread Alexander Farber
Nope, pool_mode = session kills my site... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-20 Thread Alexander Farber
Hell Marko and others, On Mon, Jun 20, 2011 at 4:08 PM, Marko Kreen 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

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-20 Thread Marko Kreen
On Mon, Jun 20, 2011 at 9:36 AM, Alexander Farber 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,

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-20 Thread Alexander Farber
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

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-20 Thread Marko Kreen
On Mon, Jun 20, 2011 at 5:08 AM, Amitabh Kant wrote: > On Mon, Jun 20, 2011 at 1:43 AM, Alexander Farber > wrote: >> >> Hello Cedric and others, >> >> On Sun, Jun 19, 2011 at 9:56 PM, Cédric Villemain >> wrote: >> > 2011/6/19 Alexander Farber : >> >> [pgbouncer] >> >> logfile = /var/log/pgbounce

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-19 Thread Amitabh Kant
On Mon, Jun 20, 2011 at 1:43 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > Hello Cedric and others, > > On Sun, Jun 19, 2011 at 9:56 PM, Cédric Villemain > wrote: > > 2011/6/19 Alexander Farber : > >> [pgbouncer] > >> logfile = /var/log/pgbouncer.log > >> pidfile = /var/run/pgbounce

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-19 Thread Cédric Villemain
2011/6/19 Alexander Farber : > Hello Cedric and others, > > On Sun, Jun 19, 2011 at 9:56 PM, Cédric Villemain > wrote: >> 2011/6/19 Alexander Farber : >>> [pgbouncer] >>> logfile = /var/log/pgbouncer.log >>> pidfile = /var/run/pgbouncer/pgbouncer.pid >>> listen_port = 6432 >>> unix_socket_dir = /t

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-19 Thread Alexander Farber
Hello Cedric and others, On Sun, Jun 19, 2011 at 9:56 PM, Cédric Villemain wrote: > 2011/6/19 Alexander Farber : >> [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/

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-19 Thread Cédric Villemain
2011/6/19 Alexander Farber : > 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 = /va

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-19 Thread Alexander Farber
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/p

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-16 Thread Alban Hertroys
On 16 Jun 2011, at 21:42, Alexander Farber wrote: > On 6/16/11, Cédric Villemain wrote: >> 2011/6/16 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

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-16 Thread Scott Marlowe
On Thu, Jun 16, 2011 at 2:08 PM, Alexander Farber 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 --

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-16 Thread Alexander Farber
On 6/16/11, Scott Marlowe 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 sobibo

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-16 Thread Scott Marlowe
On Thu, Jun 16, 2011 at 1:27 PM, Alexander Farber 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% iow

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-16 Thread Alexander Farber
On 6/16/11, Cédric Villemain wrote: > 2011/6/16 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: >> >> [pgbouncer] >> logfile =

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-16 Thread Cédric Villemain
2011/6/16 Alexander Farber : > 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 dbnam

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-06-16 Thread Alexander Farber
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/pgboun

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-27 Thread Albe Laurenz
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.post

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-26 Thread Scott Marlowe
On Thu, May 26, 2011 at 10:27 AM, Alexander Farber wrote: > > On Thu, May 26, 2011 at 6:23 PM, Scott Marlowe > 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 >

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-26 Thread Alexander Farber
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 wrote: > You need to log more stuff.  Look at the log_line_prefix setting, and > add things like pid, username, database name, etc. > -- Sent via pgsq

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-26 Thread Scott Marlowe
On Thu, May 26, 2011 at 10:02 AM, Alexander Farber 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, databa

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-26 Thread Alexander Farber
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 ---+---+--

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-26 Thread Alexander Farber
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 s

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-26 Thread Tomas Vondra
Dne 26.5.2011 16:39, Merlin Moncure napsal(a): > On Thu, May 26, 2011 at 9:01 AM, Tomas Vondra 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 i

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-26 Thread Merlin Moncure
On Thu, May 26, 2011 at 9:01 AM, Tomas Vondra 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

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-26 Thread Tomas Vondra
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 ask

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-26 Thread Alexander Farber
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'

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-25 Thread Merlin Moncure
On Wed, May 25, 2011 at 2:40 PM, Thom Brown wrote: > On 25 May 2011 18:58, 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-C

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-25 Thread Craig Ringer
On 05/26/2011 02:53 AM, t...@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

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-25 Thread Tomas Vondra
Dne 25.5.2011 21:54, Alexander Farber napsal(a): > Thank you for your replies, > > I've reverted httpd.conf to > > StartServers 8 > MinSpareServers5 > MaxSpareServers 20 > ServerLimit 256 > MaxClients 256 > > and have changed postgresql.conf to: > > shared_buf

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-25 Thread Alexander Farber
# sysctl kernel.shmmax kernel.shmmax = 68719476736 # sysctl kernel.shmall kernel.shmall = 4294967296 On Wed, May 25, 2011 at 9:54 PM, Alexander Farber 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 itsel

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-25 Thread Alexander Farber
Thank you for your replies, I've reverted httpd.conf to StartServers 8 MinSpareServers5 MaxSpareServers 20 ServerLimit 256 MaxClients 256 and have changed postgresql.conf to: shared_buffers = 512MB # for Apache + my game daemon + cron jobs max_connections

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-25 Thread Steve Crawford
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

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-25 Thread tv
> 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

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-25 Thread Thom Brown
On 25 May 2011 18:58, 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 eventho

[GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

2011-05-25 Thread Alexander Farber
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