ramblings on PHP, SQL, the web, politics, ultimate frisbee and what else is on in my life
back

Persistent connections with MSSQL

Hoping someone can explain to me what is going on here. We are connecting to SQL Server via mssql_pconnect(). MaxChilds is set to 256 and we are only establishing one connection per request. So as a result I am expecting a maximum of 256 established connections. A client went into production yesterday and due to a missing index the server ended up being insanely loaded, as the queries started to block each other. The sysadmin checked the state of things via netstat and found that there were close to 500 tcp connections to the SQL Server. What gives?

I tried figuring out of maybe I need to adjust something in FreeTDS, but I did not find anything there. The RHEL default for MaxRequestsPerChild seems to be 4000. So I hope that even if something is going wrong there, things will clean themselves up at some point.

I am also wondering if there is some way at some end to ensure that a connection is killed if its idle for a certain amount of time. I am not worried about a bit of overhead as things are slowly ramping up during high traffic periods. I just want to make sure that usually there should be a few connections around that have an active connection, especially during high traffic time I do not want the child processes to have to wait for the tcp connection to be established. At the same time however I need to make sure I stay within the Ok'ed range of a couple hundert connections and not get into the thousands.

Comments



Re: Persistent connections with MSSQL

that is interesting,
we have seen kind of the same behavior with Oracle and persistent connection.... at some point, there was too many TCp conection open, and moreover they would all stay in FIN_WAIT1 state and never be closed, which is obviously overloaded the machine at some point.
I guess 11g connection pooling for oracle should be looked,
and this comment is not going to help a lot since it is not related to mssql,
but anyway maybe that is a clue that this is not rdbms specific, perhaps something in php or OS (this was RHEL also).
Who knows...

Re: Persistent connections with MSSQL

This isn't googling up now, but back when I was using php+freetds on a high traffic site a few years ago, it was a pretty well known problem on the server side. The answer was "don't use persistent connections and set the timeout low on the server side" back when we were dealing with it .

Re: Persistent connections with MSSQL

Not sure how cheap creating connections are with MSSQL, but for MySQL you generally never use persistent connections, as this is not a really scalable way to go about it.

Re: Persistent connections with MSSQL

@Karl: Which timeout do you mean?

@Evert: The connection overhead is bigger with MSSQL, though we have not measured it yet. Furthermore, if you are connecting via TCP to MySQL, some people also start to consider persistent connections.

Re: Persistent connections with MSSQL

Not to flame -- but did persistent connections ever work with any database and the standard PHP setup? :)

Can you disclose what else you run, e.g. mod_php or php-cgi, webserver, etc.. I only figured out RHEL from your post. Regardless - can you look into a pool? Sort of like a mysql proxy thingy for SQL Server?

I'm only guessing, but maybe you need a proxy of some sort in between. Sort of like connections are handle in Java. The proxy would keep take care of your connection pool, e.g. it would make sure the connections are kept open and then you'd be able to utlize them again without the overhead of a new connect. Your app connects to the proxy -- maybe or hopefully through regular mssql_foo().

Doesn't sound like you can do it all in PHP. You probably need something else.

Re: Persistent connections with MSSQL

I briefly googled this quickly, a lot of MS people suggest the SQLSRV driver for PHP. But I don't know much about it.

http://msdn.microsoft.com/en-us/library/dd162414.aspx

Maybe you can ask Helgi to get you a contact -- he's at some MS event, or maybe that was yesterday. Regardless, I'm sure he knows who to talk to.

P.S.
Commenting on your blog is still hard as hell... the captcha still redirects me to the homepage and the link to your wiki syntax is broken.

Re: Persistent connections with MSSQL

Yeah well, such a connection pooling solution does not exist yet. Well I guess there is SQL Relay which oddly enough nobody seems to be talking about.

The SQLDRV only works on Windows and we are running Linux on the server. This server is RHEL and its sole purpose is to run our two PHP applications that talk to SQL Server 2005 SP1. There is not even a local email server. Oddly enough both applications have only very simple queries. Actually the more complex queries are in the application that is running without issues. Neither application uses transactions. The only thing that is "more advanced" in the application that is having issues is that its using 2 stored procedures with a cursor. The other one also uses stored procedures without cursors, and the stored procedures are called embedded inside an SQL statement.

Oh and as for your problem with the login. I could never reproduce it. I will update the link to the wiki syntax. Afaik someone ported it over. I am just using PEAR::Text_Wiki.

Re: Persistent connections with MSSQL

Re: Timeout: Frankly, I can't remember, and they have different names in different versions. It's the one that decides how long before the server times out a persistent connection with no traffic.

We ended up giving up on MSSQL and porting to MySQL for the high load stuff because of the issues we had. I have heard that the micrsoft-authored DLL that can be used with PHP/IIS is a lot more stable, esp with more recent versions of SQL Server. Microsoft's hostile licensing stalled FreeTDS development a while ago.

Before you can post a comment please solve the following captcha.
your name