Recently, in a new PHP + MySQL application I'm building, I was faced with a choice of using persistent connections. The benefit of persistent connections is that you don't have to keep opening new connections every time you need to hit the database for something. There's a connection already waiting for you. Yay, right? Well, with MySQL, connecting is actually really really cheap, and frankly, if you are using persistent connections, you might encounter some issues with Apache going zombie on processes that use a connection, effectively taking that connection out of use. Grrr.

"To Google!", I said. "The Interwebs must have a solution for this!" I've searched and searched trying to find a definite answer about whether to use or not to use persistent connections in a LAMP application. There seem to be lots of differing opinions, namely this famous presentation by Rasmus which basically "proves" that persistent connections are awesome. However, the PHP community seemed to be pretty against them, despite that evidence.

At this point, it was just annoying. Do I use persistent connections or not? Having this conversation internally with Ben Ramsey and Robert Swarthout, it seemed a good idea to have some hard numbers to back up our claims, so we wouldn't have to say "well, guy x said so" as part of the argument.

Here are the results of Robert's tests: Benchmarking of MySQL Persistent Connections vs Non-Persistent Connections. As Robert says:

Basically what the numbers above shows us is that in an isolated environment it makes no difference which connection type you are going to use.

Jay Pipes says that "if you use Apache, Apache can zombie a PHP process and cause the mysql connection to be held until the mysql server restarts..." Given the risk, the possible management overhead and the results of the test, we're not going to use persistent connections on this upcoming project.

Slides for Angering Database Gods from php|tek 2008.

I was really surprised how many people also have issues with ORM implementations in PHP frameworks! Also, give me coffee for 9am presentations...

For some follow-up reading, Sebastian recommends Why Active Record Sucks.

Slides for Keeping Your Database and PHP in Sync from php|tek 2008.

For those of you interested whether I'm writing this tool to release, check out dbMorph on SourceForge.

Most of you are probably familiar with The Daily WTF: Curious Perversions in Information Technology. Yes, other people's crappy code is hilarious, but it's also educational to see. (And maybe you ARE doing something crappy and you just hid it so well other people haven't found it yet?)

If you enjoy this type of thing (who doesn't?) and you're into databases and especially Oracle, there's a site inspired by The Daily WTF just for Oracle blunders! Check it out: Oracle WTF!

I found out about this via Tom Kyte's post, so I might as well also share some of his awesomeness:

As an aside, anyone that knows me, knows my mantra - written many times:

• You should do it in a single SQL statement if at all possible.
• If you cannot do it in a single SQL Statement, then do it in PL/SQL (as little PL/SQL as possible!).
• If you cannot do it in PL/SQL, try a Java Stored Procedure. The number of times this is necessary is extremely rare today with Oracle9i and above.
• If you cannot do it in Java, do it in a C external procedure. This is most frequently the approach when raw speed, or the use of a 3rd party API written in C is needed.
• If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it...

Ok, back to writing good code, not crappy at all! :)

The Oracle feature set is often brought up as an argument (for? against?) when comparing it against other databases. And it is indeed a huge feature set and it's pretty great to use, but granted, the Oracle documentation can be pretty dry to read.

For example, check out the documentation for the NTILE Function. Yeah, sounds kind of boring. What good could it be in the wonderful world of web development? After all, who manages employee or sales tables anymore...

Answer: generating tag clouds.

Continue reading "NTILE() - easy way to generate tag clouds " »