Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

#197 Max. db connections exceeded (open db connects not closing)

closed
Reini Urban
Postgresql (18)
5
2012-10-11
2003-05-30
Anonymous
No

Maximum Database Connections Exceeded (open db
connections to a PostgreSQL db not closing)

As listed on the PHPWiki KnownBugs page
(http://phpwiki.sourceforge.net/phpwiki/KnownBugs),
like Thomas Wittich, I am also experiencing an issue
with open db connections. My configuration consists of
the following:

* Windows XP SP1 with all updates thru 30 May 2003
* Apache for Windows v1.3.27 (binary installer)
* PHP v4.3.2 (Apache ISAPI module php4apache.dll),

complete with PEAR installed via go_pear.bat, etc. Some
of the relevant settings of the php.ini file include

safe_mode = on
register_globals = off
magic_quotes_gpc = on
magic_quotes_runtime = on
include_path = ".;C:\Inetpub\PHP\pear"

* PostgreSQL v7.3.2-2 running under Cygwin 1.3.22-1

with CygIPC 1.14-1 (latest and greatest)
* PHPWiki v1.3.4 (have NOT yet resorted to CVS
snapshots, but notice nothing in ?ChangeLog to indicate
fix), configured to use PostgreSQL db.

Once I adjusted PHPWiki index.php by setting
"define('DEFAULT_LANGUAGE', '');"--the default setting
wreaked havoc on building the initial pages for some
reason--all installation went well, and everything
appeared to work just fine.

HOWEVER, rather quickly I found that I was receiving a
"max_connections exceeded" message in my PHPWiki pages.
In test mode, with just myself going through pages, I
would hit this after about 15 minutes of use, and then
I would have to either shutdown/restart Apache or
PostgreSQL to clear things. By default, PostgreSQL's
configuration is to permit 32 connections maximum (with
2 held in reserve for administrative use).

I adjusted the appropriate PostgreSQL configuration
file (/usr/share/postgresql/data/postgresql.conf) and
quadrupled max_connections from 32 to 128 (also making
sure to adjust shared_buffers accordingly). However, I
wanted to make sure this was not simply delaying the
inevitable. So I started monitoring the TCP/IP
connections via the 'netstat' command, and like Thomas
Wittich before me, I found that once opened, database
connections were not being closed...EVER. The list of
connections continued to grow. I even left things idle
for more than an hour (lunch has its purposes), but
none of the connections cleared.

Is there something that can be done to force the
connections to close? I have not found any settings in
PostgreSQL to force closing connections after a certain
amount of idle time, so I am hoping there is such an
ability from the client side (i.e., in PHPWiki).
Whether in PEAR DB or PHPWiki itself, something needs
to be adjusted to make connections close.

I even tried copying over the newest PEAR code (DB
v1.4.0), but to no avail.

I recall reading somewhere in the Wiki code that
persistent connections were the default for now. I'm
not sure if/how that plays into things, as my
understanding of a persistent connection is that
queries would all use the SAME connection, not
continually create new ones.

Anyway, any insight into this would be greatly appreciated.

--FrankSeesink | 30 May 2003
frank@mail.wvnet.edu

Discussion

  • Reini Urban
    Reini Urban
    2004-04-18

    Logged In: YES
    user_id=13755

    worked for me with that latest cyg pg tests, but I will test
    it again.
    also with the new adodb pgsql backend, which supports
    transactions finally, to get rid of table locking.

     
  • Reini Urban
    Reini Urban
    2004-05-03

    Logged In: YES
    user_id=13755

    with postgres we don't use pconnect (persistent connections)
    anymore.

     
  • Reini Urban
    Reini Urban
    2004-05-03

    Logged In: YES
    user_id=13755

    Relevant patch:
    lib/WikiDB/backend/PearDB.php:WikiDB_backend_PearDB()

        $dboptions = array('persistent' => true,
                           'debug' => 2);
        if (preg_match('/^pgsql/',$this->_dsn))
            $dboptions['persistent'] = false;
        $this->_dbh = DB::connect($this->_dsn, $dboptions);