Apa itu mysql persistent connection

As you probably know PHP “mysql” extension supported persistent connections but they were disabled in new “mysqli” extension, which is probably one of the reasons some people delay migration to this extension.

The reason behind using persistent connections is of course reducing number of connects which are rather expensive, even though they are much faster with MySQL than with most other databases.

Not only connects are expensive but you also may run into the trouble establishing number of connections you need. The problem is there can be only so many connections active between Host “Apache” and Host “MySQL”: Port 3306 as connection in TCP/IP protocol is identified by pair of IP addresses and pair of ports (local port and remote port). Yes if you’re establishing thousands of connections per second you normally do not keep it open for long time, but Operation System does. According to TCP/IP protocol Ports can’t be recycled instantly and have to spend some time in “FIN” stage waiting before they can be recycled.

On Linux you can adjust “/proc/sys/net/ipv4/ip_local_port_range” to get more local ports available and “/proc/sys/net/ipv4/tcp_fin_timeout” to reduce recycle delay. Reducing last one however will go against protocol requirements so in theory you can get some problems. It worked fine for me however.

Other ways to workaround this problem is of course to use multiple IPs on your MySQL server (you’re probably using Intranet range IPs anyway). So one way or around you can avoid such limit but creating connections will still waste resources and add latency.

The other problem with persistent connections is using too many MySQL server connections. Some people simply do not realize you can increase max_connections variable and get over 100 concurrent connections with MySQL others were beaten by older Linux problems of not being able to have more than 1024 connections with MySQL.

With modern systems you can have thousands of Connections with MySQL, it however might not be overly efficient – managing large number of threads may be a bit more expensive. Threads also take memory resources both on kernel and MySQL size but most problems seems to happen in case of “overload”. In case your number of connections is limited you start getting connection errors which are easy to handle, in case of large number of connections allowed you may have 4000 of queries running at the same time which may never self-resolve as users will get extremely poor response time and will continue press reload. Plus, especially for Innodb tables throughput may drop dramatically – you may see it to be 1/100 of what you get with just few queries concurrently.
If you have these queries doing large sorts, using temporary tables or having other significant memory requirements you may well out of memory and get crash or have MySQL starting to swap aggressively.

This is where connection pooling would be extremely helpful but with default Processed based PHP installations it does not work.

Interesting enough these problem of thousands of connections in most cases comes from misconfiguration or possibly not willing to spend enough time to optimize Web part of configuration. Thousands of concurrent connections usually result from cases when there are many hundreds of apache children processes are running. Most of them will be just holding keep-alive while still keeping MySQL connection open, others will serve static content such as images which also does not need MySQL connection open.

In optimal configuration with Apache talking to local MySQL installation hand having no remote network accesses would be 20-30 apache children. But you need to keep them busy all the time so they should not handle keep alive serve images or perform spoon feeding. You can place squid in front, use apache proxy module or even use lighttpd with FastCGI all can fix this problem.

Lets talk now about why Persistent connections were disabled in mysqli extension. Even though you could misuse persistent connections and get poor performance that was not the reason. The real reason is – you could get much more problems with it.

Persistent connections were added to PHP during times of MySQL 3.22/3.23 when MySQL was simple enough so you could recycle connections easily without any problems. In later versions number of problems however arose – If you recycle connection which has uncommitted transactions you run into trouble. If you happen to recycle connections with custom character set settings you’re in trouble back again, not to mention about possibly changed per session variables.

Many applications are fine – ie if you have read-only page and everything on your site uses same charset and does not use per session variables in some tricky way it would work perfect for you. In some complex applications it however can lead to very hard to track bugs.

What would be proper solution to this ?

MySQL should support command which resets connection and guarantees it will be the same as freshly created connection, so the fact connection is persistent can be completely transparent. May be it even should get fresh connection ID so if you track web requests in MySQL Logs by connection ID it would still work. MySQL has change_user() call which kind of does some of this but it looks like no one knows if it does everything what needs to be done, plus it surely was broken in some MySQL versions.

So persistent connections are not evil but they need some MySQL support to be properly implemented. Now with new “mysqlnd” driver for PHP effort by MySQL team I hope this issue could be fixed. Internal guys sure have more leverage on server development team than external community 🙂