MySQL/PHP _connect() _pconnect() Scaleability Discussions (long)

Hello PHPDB! I'm on a scouting mission from the php-nuke team looking into the system issues between PHP and MySQL and the growing problems of MySQL/PHP systems crashing under heavy load. This is starting to occur with many sites as the web-time increases the load on the connections in a web-time enviroment with many, many users hitting a PHP front end talking to a MySQL backend. To bring interested folks up to speed on this issue, here are two summary/tutorial posts from the php-nuke community. Before reading the long summaries, I would like to say thank you in advance for your interest in helping. I've been over the php-db archives and did not see this level of discussion, so I hope the topic is of interest. (two summary messages below): Thread: [PHP-NUKE] Very Serious Mysql problem with PHP.... Oct 27 00 --------------- Perhaps this will help: Reference Professional PHP Programming, pg 268 - mysql_connect() "if another mysql_connect call is made with the same arguments, a new connection will not be created to the server. The link identifier of the connection already open will be returned." The connection will close when a mysql_close call or made or the PHP script exists. mysql_pconnect() "the difference between mysql_connect() and mysql_pconnect() is that the conection created with mysql_pconnect() is not closed when the PHP program exit or a mysql_close call is made. mysql_pconnect() should be used in PHP apps where, over a short period of time, a large number os connections will be made qo the MySQL server using the same username and password, saving the overhead of creating and closing a connection. Note the mysql_pconnect only will work if PHP is configured as a module in the web server. ---------------------\ My Note: I do not see any function parameters for either which accept a timeout value, so I assume that the open connection is based on the internal PHP 'socket linger' value and not directly in PHP. This is a c programming issue. ---=-\ Based on the above system commands, we appear to have a problem with mysql_pconnect because the link identifer is not passed interprocess or between user sessions. Therefore, if correct, many users hitting phpnuke with pconnect will always have a lot of lingering sockets even after the user session ends. The tradeoff using connect() is that there will be some performance degradation in user sessions because of the need to reopen and close the database connection. My intuition tells me that there should be a configuration flag for this in the phpnuke config file and either _pconnect() or _connect() is used (with _close() ) depending on the system loads and traffic model. I do not think that there is one right answer for all systems. Some traffic models will perform better with pconnect() and others with connect(). Giving the system the option makes phpnuke strong and flexible, adaptable to multiple traffic and load models. Just my 0.12 cents analysis. YMMV. -Tim -------------------------------------------------------------------------- This is a followup to the posts on the issues with PHP _connect() and _pconnect(). I have been reading the lastest wrox book: Professional J2EE with BEA Weblogic Server pg. 60 describes the same problem php-nuke is having with _connect() and _pconnect() in the section on Connection Pools (my apologies for not retyping the section). Their solution set: to establish a pool of persistant connections between the application server and the SQL database. These pools are daemon processes that have an persistant connections to the database. All user-client queries to the data base happen through these connection pools. In the current php-nuke architecture, the client-users talk directly to the mySQL back end; so regardless of how PHP mySQL glue is used, either _connect() and _pconnect() there will be scaleablity issues. As we pointed out, either is a silver bullet to solve the problem(s) and future issues. The preferred architecture appears to be to have php-nuke user-queries talk to a decoupled connection pool of pre-established connections (the pool) to the database. Note that this cannot be done using _pconnect() directly in the php-nuke code. _pconnect() or connect() must be called by a MySQL client-daemon routine which sets up the connection pools between the web-time code and the database. php-nuke then would not make any direct _connect() and _pconnect() calls, but would use the connection pools to mySQL for the transactions. (note, this is just applying the same web-time constructs from other multi-tiered web architectures to php-nuke issues.... the ideas are not mine, I'm just suggesting that they apply here as well.) Naturally, I tend to agree with the approach and the indications that php-nuke will need to migrate toward this "mySQL connection pool" approach. This is appears to be the trend in JDBC, ODBC and similar systems with the same issues with database connectivity and scaleability. Does anyone know of any PHP code which creates and load balances queries through connection pools? Does this approach sound like the right evolution target for DB connectivity? -Tim