Persistent connection for a small site?

[Esopo]

I have done pages that open a new connection for their queries with mysql_connect, and I also have a site that runs entirely on mysql_pconnect.

It seems to be that the persitant connection stablished by mysql_pconnect makes the site run faster, although I heard that this kind of connection is only good for busy pages.

Is it a good idea to make a small site's DB connection a persistant one?

Almost the entire site (if not the entire site) will be ran from a single page, index.php, where the connection is stablished.

[VGR]

use _pconnect() in this case

beware : if on windows, persistent connections used to works quite bad compared to *nix versions of the server (or of PHP, I don't remember exactly)

you could also perhaps make timing measurements to be sure it's faster.

[Anonymous]

Persistent connections come with the following restrictions.
[list=1]

• You cannot have persistent connections for CLI or CGI installations as the php executable gets dropped from the systems memory when the script completes, leaving no connections to the database.
• Persistent connections are NOT database specific. If you have multiple databases on the same database server and you are sharing connections amongst many scripts, they all have to be using the same database UNLESS the SQL statements being issued include the database name. If only the xxxx_pconnect() functions had database as a parameter!
• If you are NOT using persistent connections, they are used internally if available. Imagine a script talking to 2 databases on the same server. You've decided to NOT use pconnect. Makes no difference. If at ANY time a pconnect to the database server was used, then all subsequent connects and pconnects are persistent.
• Using xxxx_select_db (where appropriate) affects the persistent connection which affects ALL connections to that database!!![/list:o]
  
  Poorly written quick proof code!
  Code:
  <?php
  
  $c1 = mssql_pconnect('richardquadling', 'sa', 'sa');
  $c2 = mssql_pconnect('richardquadling', 'sa', 'sa');
  
  mssql_select_db('TripleCDataConversion', $c1);
  mssql_select_db('TripleCDataConversionEmpty', $c2);
  
  $id1 = mssql_query('select * from staticdata', $c1);
  $id2 = mssql_query('select * from staticdata', $c2);
  
  echo "1\n";
  
  while(($row = mssql_fetch_array($id1)) !== False)
  {
  print_r($row);
  }
  
  echo "2\n";
  
  while(($row = mssql_fetch_array($id2)) !== False)
  {
  print_r($row);
  }
  ?>
  
  
  Outputs:
  Code:
  1
  2
  
  
  
  Swap the lines
  Code:
  mssql_select_db('TripleCDataConversion', $c1);
  mssql_select_db('TripleCDataConversionEmpty', $c2);
  for
  Code:
  mssql_select_db('TripleCDataConversionEmpty', $c2);
  mssql_select_db('TripleCDataConversion', $c1);
  
  
  and you get duplicate output ...
  Code:
  1
  Array
  (
      [0] => 3
      [UniqueID] => 3
      [1] => 62020
      [VersionNumber] => 62020
  )
  2
  Array
  (
      [0] => 3
      [UniqueID] => 3
      [1] => 62020
      [VersionNumber] => 62020
  )
  
  
  The proper way to have written this is ...
  Code:
  <?php
  
  $c1 = mssql_pconnect('richardquadling', 'sa', 'sa');
  $id1 = mssql_query('select * from TripleCDataConversion.dbo.staticdata', $c1);
  $id2 = mssql_query('select * from TripleCDataConversionEmpty.dbo.staticdata', $c1);
  
  echo "1\n";
  
  while(($row = mssql_fetch_array($id1)) !== False)
  {
  print_r($row);
  }
  
  echo "2\n";
  
  while(($row = mssql_fetch_array($id2)) !== False)
  {
  print_r($row);
  }
  ?>
  which correctly outputs
  Code:
  1
  Array
  (
      [0] => 3
      [UniqueID] => 3
      [1] => 62020
      [VersionNumber] => 62020
  )
  2
  
  
  
  
  From my examination of the source code, there is no differentiation between unix and windows in this regard.

[VGR]

I hope sa/sa is a joke ;-)

As for platform difference, looking at my code base, Isee this :
//VGR11012002 MODification des mysql_connect() en mysql_pconnect()

then afterwards I kept on using _pconnect() all the time, until
//VGR08102002 MODified pconnect in connect for Windows server
and afterwards I used something like :
if ($sess_osWin==1) $linkID=mysql_connect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect".mysql_error());
 else $linkID=mysql_pconnect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect".mysql_error());



So there ***must be*** a serious reason why when I started using Windows PHP servers, I wrote this code, no ?

[VGR]

I found the reason back. In October 2002, I had many problems on a Windows server with the error "too many connections" at the MySql-PHP level.

I finally solved it by returning to non-persistent connections.

The reason why was that there are (were?) suspicions of a bad threading model on Windows, eventually leading to starvation in available connections.

I had a MySql server configured for 100 concurrent connections, two were in use (webserver's and mine) and each week or so I had to reboot or restart the webserver to "clear the memory"

It was Apache 1.3 - still in production - on Win2K +SP with PHP 4

For me, this problem of a bad deallocation of ressources is linked to the general problem of Windows with the "GDI segment problem" (graphical resources not being deallocated normally, thus leading to starvation and eventually hanging windows). This problem leads to black menus, black bitmaps, etc

It's also linked to a problem I had of starvation in file handles in the webserver, while I was not using explicitly the functions that could have created (fopen) and not closed (fclose) them incorrectly.

[Esopo]

I heard with persitant there could be connection problems (as stablished above) and also denial problems.

The connection problems are not worrying me, I mean, if it works under my enviroment then good, if not I'll just not use persistant.

I will only be using one DB, this is my connection code:

Code:

if (!$db1=@mysql_pconnect ($dbhost,$dbuser,$dbpass)){
//error handling
}
if (!mysql_select_DB ($dbname)){
//error handling
}

My main concern is the possibility of it denying me service. I read somewhere that if there are no available connections persistant connections will get denied while normal connections will not. Is this in any way accurate?

[Anonymous]

If you are using a CLI or a CGI environment, persistent connections are NOT available. No matter what. Even if you use xxx_pconnect().

If you are using ISAPI or PHP compiled into your webserver, persistent connections are used even if you DON'T use xxxx_pconnect().

Oh. The sa/sa was just a simple quick proof test on my MSSQL 2K Developer Edition (full blown SQL Server for £35.00 - Great for development and testing).

Returns: A positive MS SQL persistent link identifier on success, or FALSE on error.

mssql_pconnect() acts very much like mssql_connect() with two major differences.

First, when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.

Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mssql_close() will not close links established by mssql_pconnect()).

This type of links is therefore called 'persistent'.

and

mssql_connect

(PHP 3, PHP 4, PHP 5)
mssql_connect -- Open MS SQL server connection
Description
resource mssql_connect ( [string servername [, string username [, string password]]] )

Returns: A MS SQL link identifier on success, or FALSE on error.

mssql_connect() establishes a connection to a MS SQL server. The servername argument has to be a valid servername that is defined in the 'interfaces' file.

In case a second call is made to mssql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned.

The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mssql_close().

See also mssql_pconnect(), mssql_close().

There is 1 slight difference on mysql_connect(). There is an option to force a new connection to the database. See http://www.php.net/manual/en/function.mysql-connect.php.