Edit report at http://bugs.php.net/bug.php?id=29074&edit=1
ID: 29074
Comment by: opc dot three at gmail dot com
Reported by: david dot blair at nsi1 dot com
Summary: mssql_connect fails under heavy load
Status: Bogus
Type: Bug
Package: MSSQL related
Operating System: MS 2003
PHP Version: 4.3.7
New Comment:
There was an error in my reproduce code. Here is the updated version:
<?php
ini_set("display_errors", 1); // ...so we can see all output, good or
bad
ini_set("mssql.max_procs", 50); // default is -1 for library default (25
for DB Lib), but setting this to higher than 25 has no effect on the
results
$new_link = true; // when true each call to mssql_connect opens a new
connection and we see problems after 25 connections
$links = array();
for ($k = 1; $k <= 30; $k++) {
echo "Connection attempt $k...";
$link = mssql_connect("localhost", "sa", "hidden", $new_link);
$links[] = $link;
echo "<br/>";
}
?>
Previous Comments:
------------------------------------------------------------------------
[2010-07-17 18:26:16] opc dot three at gmail dot com
I can recreate the behavior described in this report and it does not
seem to be a PHP or SQL Server problem. Further it does not seem to be
affected by the mssql.max_procs setting. Changing the max_procs setting
up, down or to unlimited has no effect in the reproduce code below.
Rather it appears to be related to the underlying driver used in my
particular setup, namely the Microsoft DB-LIB driver ntwdblib.dll, plus
the value of the $new_link parameter being set to true in the call to
mssql_connect.
I am using ntwdblib.dll version 2000.80.194.0 but have also tried this
with the most current version I could find, 2000.80.2187.0, which is
included in patch set "Microsoft SQL Server 2000 Post-SP4 Rollup".
I am not sure if it is common knowledge but the SQL Server DB-LIB
interface has been officially deprecated since the release of SQL Server
2005. While the server API was left in the product to allow legacy apps
to continue to function, support for new data-types and features are not
being added to the interface and Microsoft discourages new development
from using the interface as it will eventually be dropped from the
product. For example the VARCHAR(MAX), NVARCHAR(MAX) & XML data types
introduced in SQL Server 2005 are not supported by DB-LIB and thus
cannot be reliably communicated to and from the server via the DB-LIB
API.
I think I read somewhere that ntwdblib.dll has a limit of 25 concurrent
connections per process but I cannot seem to find that info now that I
want to reference it. The results from the reproduce code are consistent
with that however as the first 25 connections are made but the 26th
fails. In my environment all connections from all pages in the IIS site
will be opened within the same server process so the limit of open
connections would be 25 per site. I have looked around and I do not see
a way to increase this limit.
For a given web page, setting $new_link to false in the call to
mssql_connect will avoid the issue, however I think the real-world
scenario is when multiple pages within a site are trying to open
connections to the database and the total open connections for the
entire site exceeds 25. Theoretically I think this could be dealt with
by having a dedicated data access layer that emulated a connection pool
to limit the # of open connections from the site to 25 or less.
In my environment I worked arouind the issue by switching to ODBC since
perfromance was not of utmost concern. The odbc libs are slower than the
mssql libs but the SQL Server 2005 Native Client ODBC driver referenced
by my ODBC DSN does not suffer from the concurrent connection limit of
25.
Environment:
-----------------
- 32-bit Windows Server 2003 Standard
- IIS 6
- PHP 5.2.6
- SQL Server 2005 Standard w/SP3 (all post-SP3 patches)
- DB-LIB driver ntwdblib.dll 2000.80.194.0
Reproduce code:
-----------------
<?php
ini_set("display_errors", 1); // ...so we can see all output, good or
bad
ini_set("mssql.max_procs", 5); // default is -1 (unlimited) but
changing this has no effect on the results
$new_link = true; // when true each call to mssql_connect opens a
new
connection and we see problems after 25 connections
$links = array();
for ($k = 1; $k <= 30; $k++) {
echo "Connection attempt $k...";
$link = mssql_connect("localhost", "sa", "hidden", $new_link);
$links[] = $link;
echo "<br/>";
}
?>
Expected result:
----------------
Connection attempt 1...
Connection attempt 2...
Connection attempt 3...
Connection attempt 4...
Connection attempt 5...
Connection attempt 6...
Connection attempt 7...
Connection attempt 8...
Connection attempt 9...
Connection attempt 10...
Connection attempt 11...
Connection attempt 12...
Connection attempt 13...
Connection attempt 14...
Connection attempt 15...
Connection attempt 16...
Connection attempt 17...
Connection attempt 18...
Connection attempt 19...
Connection attempt 20...
Connection attempt 21...
Connection attempt 22...
Connection attempt 23...
Connection attempt 24...
Connection attempt 25...
Connection attempt 26...
Connection attempt 27...
Connection attempt 28...
Connection attempt 29...
Connection attempt 30...
Actual result:
--------------
Connection attempt 1...
Connection attempt 2...
Connection attempt 3...
Connection attempt 4...
Connection attempt 5...
Connection attempt 6...
Connection attempt 7...
Connection attempt 8...
Connection attempt 9...
Connection attempt 10...
Connection attempt 11...
Connection attempt 12...
Connection attempt 13...
Connection attempt 14...
Connection attempt 15...
Connection attempt 16...
Connection attempt 17...
Connection attempt 18...
Connection attempt 19...
Connection attempt 20...
Connection attempt 21...
Connection attempt 22...
Connection attempt 23...
Connection attempt 24...
Connection attempt 25...
Connection attempt 26...
Warning: mssql_connect() [function.mssql-connect]: Unable to connect to
server: localhost in D:\phptest\_test_mssql.max_procs.php on line 8
Connection attempt 27...
Warning: mssql_connect() [function.mssql-connect]: Unable to connect to
server: localhost in D:\phptest\_test_mssql.max_procs.php on line 8
Connection attempt 28...
Warning: mssql_connect() [function.mssql-connect]: Unable to connect to
server: localhost in D:\phptest\_test_mssql.max_procs.php on line 8
Connection attempt 29...
Warning: mssql_connect() [function.mssql-connect]: Unable to connect to
server: localhost in D:\phptest\_test_mssql.max_procs.php on line 8
Connection attempt 30...
Warning: mssql_connect() [function.mssql-connect]: Unable to connect to
server: localhost in D:\phptest\_test_mssql.max_procs.php on line 8
------------------------------------------------------------------------
[2005-02-03 05:02:49] sni...@php.net
Bogus it is.
------------------------------------------------------------------------
[2005-01-11 22:50:23] david dot blair at nsi1 dot com
We've retired the server that was having the issue...the new production
server is as stable as the development servers...so I'm not sure what to
do with this bug report...
Should it be closed? Handed off? Bogused? Do I scratch this up as a
corrupt install of SQL or PHP or the OS on that server...?
------------------------------------------------------------------------
[2004-10-07 18:15:07] phpbugreport at workboy dot com
If you have access to a packet sniffer and the basic knowhow, I would
recommend sniffing the connection to and from port 1433 of your MSSQL
server (if you are using 1433 that is) and the web server having the
connect problems. I was able to determine that this problem was on the
SQL side of things by looking at the packet trace. I would log
mssql_connect failures that were corrected by a second attempt and
matched their times exactly to TCP RESET packets being sent from the
MSSQL server back to the web server requesting a connection to port
1433. This allowed me to rule out PHP as the source of the failure.
As to why newer versions of PHP cause this more than older versions, all
I can assume is that the newer PHP is more efficient in some way in
opening TCP connections and this is causing greater concurrency in TCP
connection requests to the SQL server.
With a listen backlog of 25 on my SQL server I still see occasional
failures but that is within an acceptable range now as they are handled
by trying again immediately. If the number starts to go up, I will try
increasing my listen backlog.
I also observed that an entirely separate problem on my SQL server
related to the RAID card increased the failure rate so there appear to
be other mitigating factors on the SQL end. Fixing the RAID issue make
the connect failures rate drop down to zero again.
Charles
------------------------------------------------------------------------
[2004-09-29 20:29:38] david dot blair at nsi1 dot com
(In response to two posts ago) Charles, I tried your fix by setting the
winsocklistenbacklog to 16.
Didn't work.
Had three more incidents today where the first connection was refused
but on an instant reconnect it worked.
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/bug.php?id=29074
--
Edit this bug report at http://bugs.php.net/bug.php?id=29074&edit=1
