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.
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.
Update March 2014:
MySQL connection speed was always low compared to other brands of RDBMS, but it's getting even better.
See http://mysqlserverteam.com/improving-connectdisconnect-performance/
In MySQL 5.6 we started working on optimizing the code handling connects and disconnects. And this work has accelerated in MySQL 5.7. In this blog post I will first show the results we have achieved and then describe what we have done to get them.
Read the blog for more details and speed comparisons.
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.
mysql_connect() and mysql_pconnect() both are working for database connection but with little difference. In mysql_pconnect(), p stands for persistance connection.
When we are using mysql_connect() function, every time it is opening and closing the database connection, depending on the request.
But in case of mysql_pconnect() function:
First, when connecting, the function would try to find a (persistent) connection 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 connection will remain open for future use (mysql_close() will not close connection established by mysql_pconnect()).
mysql_pconncet() is useful when you have a lot of traffice on your site. At that time for every request it will not open a connection but will take it from the pool. This will increase the efficiency of your site. But for general use mysql_connect() is best.
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.
MYSQL_CONNECT()
1.mysql_connect can be used to close the connection.Every time it is opening and closing the database connection, depending on the request .
2.Here database is opened everytime when the page is loaded in MYSQL connect
3.When the page is loaded, the database is loaded everytime
4.It is used to close the connection
Example:
<?php $conn = mysql_connect(‘host’, ‘mysql_user’, ‘mysql_password’); if(!$conn){ die(‘Could not connect: ‘ . mysql_error()); } echo ‘Connected successfully’; mysql_close($conn); ?>
Description:
host: Specifies a host name or an IP address like localhost.
mysql_user: Specifies the MySQL username
mysql_password: Specifies the MySQL password
MYSQL_PCONNECT()
1.We use the mysql_pconncet(), it initially tries to find an open persistent connection.
2.The mysql_pconncet() opens persistant connection
3.The mysql_pconnect() does not support the close connection
4.mysql_pconnect() cannot close the connection. Here open a persistant connection to the database
5.Here database need not be connected everytime.
6.The database need not be connected every time in mysql_pconncet().
more details:http://prittytimes.com/difference-between-mysql_connect-and-mysql_pconnect/
© 2022 - 2024 — McMap. All rights reserved.
mysqli.max_persistentwhich is the maximum size of the connection pool in that instance of PHP. There still isn't a value in PHP for timing out connections. – Reconciliatory