I have this doubt, I've searched the web and the answers seem to be diversified. Is it better to use mysql_pconnect over mysql_connect when connecting to a database via PHP? I read that pconnect scales much better, but on the other hand, being a persistent connection... having 10 000 connections at the same time, all persistent, doesn't seem scalable to me.
Thanks in advance.
It's very unlikely that you'll reach 10000 connections. Anyhow, go to the official source. (Emphasis mine).
If persistent connections don't have any added functionality, what are they good for?
The answer here is extremely simple -- efficiency. Persistent connections are good if the overhead to create a link to your SQL server is high. Whether or not this overhead is really high depends on many factors. Like, what kind of database it is, whether or not it sits on the same computer on which your web server sits, how loaded the machine the SQL server sits on is and so forth. The bottom line is that if that connection overhead is high, persistent connections help you considerably. They cause the child process to simply connect only once for its entire lifespan, instead of every time it processes a page that requires connecting to the SQL server. This means that for every child that opened a persistent connection will have its own open persistent connection to the server. For example, if you had 20 different child processes that ran a script that made a persistent connection to your SQL server, you'd have 20 different connections to the SQL server, one from each child.
Note, however, that this can have some drawbacks if you are using a database with connection limits that are exceeded by persistent child connections. If your database has a limit of 16 simultaneous connections, and in the course of a busy server session, 17 child threads attempt to connect, one will not be able to. If there are bugs in your scripts which do not allow the connections to shut down (such as infinite loops), the database with only 16 connections may be rapidly swamped. Check your database documentation for information on handling abandoned or idle connections.
Persistent connections should be unnecessary for MySQL. In other databases (such as Oracle), making a connection is expensive and time-consuming, so if you can re-use a connection it's a big win. But those brands of database offer connection pooling, which solves the problem in a better way.
Making a connection to a MySQL database is quick compared to those other brands, so using persistent connections gives proportionally less benefit for MySQL than it would for another brand of database.
Persistent connections have a downside too. The database server allocates resources to each connection, whether the connections are needed or not. So you see a lot of wasted resources for no purpose if the connections are idle. I don't know if you'll reach 10,000 idle connections, but even a couple of hundred is costly.
Connections have state, and it would be inappropriate for a PHP request to "inherit" information from a session previously used by another PHP request. For example, temporary tables and user variables are normally cleaned up as a connection closes, but not if you use persistent connections. Likewise session-based settings like character set and collation. Also, LAST_INSERT_ID() would report the id last generated during the session -- even if that was during a prior PHP request.
For MySQL at least, the downside of persistent connections probably outweighs their benefits. And there are other, better techniques to achieve high scalability.
Basically you have to balance the cost of creating connections versus keeping connections. Even though MySQL is very fast at setting up a new connection, it still costs -- in thread setup time, and in TCP/IP setup time from your web server. This is noticeable on a high-enough traffic site. Unfortunately, PHP does not have any controls on the persistence of connections. So the answer is to lower the idle timeout in MySQL a long way (like down to 20 seconds), and to up the thread cache size. Together, this generally works remarkably well.
On the flip side, your application needs to respect the state of the connection. It is best if it makes no assumptions about what state the session is in. If you use temporary tables, then using CREATE IF NOT EXISTS and TRUNCATE TABLE helps a lot, as does naming them uniquely (such as including as userid). Transactions are bit more problematic; but your code can always do ROLLBACK at the top, just in case.
" Unfortunately, PHP does not have any controls on the persistence of connections."
never heard of "mysql.allow_persistent=Off" into php.ini?