<pedrocorreia.net ⁄>

<Are PHP persistent connections evil ? ⁄ >

clicks: 3612 3612 2006-11-14 2006-11-14 goto tecnologia myNews tecnologia  Bookmark This Bookmark This

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.

este é só um excerto do artigo, para aceder ao artigo completo, clique no link em baixo:
this is just a small excerpt from the article, to access the full article please click in the link below:


Subscribe News RSS  Subscribe News Updates by E-mail

myNews <myNews show="rand" cat="tecnologia" ⁄>

MongoDb Architecture new ...

NOSQL has become a very heated topic for large web-scale deployment where scalability and semi-struc (...)

clicks: 19235 19235 2012-05-14 2012-05-14 goto url (new window) horicky.blogspo... goto myNews tecnologia

A Hopefully Fair and Useful Comparison of Haskell Web Frameworks new ...

Recently there has been a lot of discussion and questions about the differences between the big thre (...)

clicks: 21273 21273 2012-05-13 2012-05-13 goto url (new window) softwaresimply.... goto myNews tecnologia

Retina graphics for your website new ...

retina.js is an open source script that makes it easy to serve high-resolution images to devices wit (...)

clicks: 15540 15540 2012-05-11 2012-05-11 goto url (new window) retinajs.com/ goto myNews tecnologia

What every programmer should know about memory, Part 1 new ...

In the early days computers were much simpler. The various components of a system, such as the CPU, (...)

clicks: 17084 17084 2012-05-10 2012-05-10 goto url (new window) lwn.net/Article... goto myNews tecnologia

The State Of HTML5 Video

HTML5 has entered the online video market, which is both exciting and challenging for developers in (...)

clicks: 7076 7076 2012-02-14 2012-02-14 goto url (new window) longtailvideo.c... goto myNews tecnologia

A Tour of Amazon's DynamoDB

Amazon's recent release of DynamoDB, a database whose name is inspired by Dynamo, the key-value data (...)

clicks: 6546 6546 2012-02-13 2012-02-13 goto url (new window) paperplanes.de/... goto myNews tecnologia

Video: CSS3 Secrets: 10 things you might not know about CSS3

A number of prominent front-end developers delivered helpful talks at this year's Fronteers 2011 con (...)

clicks: 7067 7067 2011-11-30 2011-11-30 goto url (new window) css.dzone.com/a... goto myNews tecnologia

Google BigQuery Service: Big data analytics at Google speed

Rapidly crunching terabytes of big data can lead to better business decisions, but this has traditio (...)

clicks: 6365 6365 2011-11-30 2011-11-30 goto url (new window) googlecode.blog... goto myNews tecnologia