Performance Tuning Best Practices for MySQL

Just noticed a good summary of MySQL performance tuning tips on google video. Not necessarily PHP, but since many of us use MySQL in one way or another, I thought the group might be interested.

Read on for links and more info

“Jay Pipes is a co-author of the recently published Pro MySQL (Apress, … all ยป 2005), which covers all of the newest MySQL 5 features, as well as in-depth discussion and analysis of the MySQL server architecture, storage engines, transaction procesing, benchmarking, and advanced SQL scenarios. You can also see his name on articles appearing in Linux Magazine and can read more articles about MySQL at his website. ”

For those who find this interesting, much of the same information is contained in:

High Performance MySQL

MySQL 4.1.9 (productional) released

Ok…when did I miss that 4.1 was even out of beta? Well, it doesn’t really matter, but the version that brings you subqueries, spacial queries and a whole lot of other great features is not only production ready…but, it just had a new release.

If you are new to MySQL 4.1, they have written up some Interesting Articles on Or, you may just want to go strait for the downloads.

And don’t forget…if you are on MySQL 4.1 and php 5, you can play with the yummy mysqli classes. Mmmm…Tasty.

phpMyAdmin 2.5.7 vulnerable to remote attacks

many of us use phpMyAdmin to help us manage our MySQL databases. If left open to public access, phpMyAdmin is open to a remote attack allowing the attacker to run arbitrary php code.

The best way to protect yourself is to simply password protect phpMyAdmin. On apache, this can usually be done with a .htaccess and .htpasswd file. Hit google for more information on password protecting parts of your website.

The official white paper on the bug is located here:

SQLite and Choosing a Database Abstraction Layer

So, have you heard of this new SQLite embedded database? I was just reading about it the other day.

SQLite is a C library that implements an embeddable SQL database engine. Programs that link with the SQLite library can have SQL database access without running a separate RDBMS process.

Apparently they are going to implement it in the next version of PHP. This is a good thing. Sure, it will be slow and simplistic, and probably not very scalable for heavy use, but it has one thing going for it: distribution. Every PHP installation will have it. Have you ever been writing a program that will be distribute to multiple clients and worried that they won’t have the right database installed? or any database installed? Soon you won’t have to worry, because as long as they have PHP, they will have SQLite.

So now the possibility of using this in the future on servers that aren’t my own has intrigued me. And I’m already intrigued by some of the other free databases out there. I’ve heard PostgreSQL development is really doing well and more and more people prefer it to MySQL. I’ve also heard a little about some new database called Firebird or something like that. So… why am I still coding all my PHP applications with mysql_query() functions? Why am I not using a database abstraction library so that I can easily use my applications with any database I choose? That’s a very good question. Maybe I’m stupid, or simply too lazy to try something new. I’ve thought about it a lot over the past few weeks. I think my biggest fear with choosing a library to use is standards. I know that the built-in mysql functions are going to be around for a while. I don’t know if I can say the same thing about some of these database abstraction layers. Some might claim that PEAR DB is the standard since it comes packaged with PHP. But there is also another called DBX that comes with PHP. So does that make it the standard too?

I just read an interesting forum thread where John Lim, author of ADODB, claims that PEAR DB is obsolete and the new versions are breaking backward compatibility.

Tomas worked hard on PEAR DB, but it was made obsolete by PEAR MDB. It would not have been too much trouble to make MDB compatible with PEAR DB. ADODB has a PEAR DB layer, but Lukas (MDB lead) decided against it. In fact, they are still releasing PEAR DB as the default abstraction layer, so more people are going to hate the switch when MDB becomes the default standard.

Now Lukas has decided that MDB 2.0 will break MDB 1.0 compatibility.

I think Lukas is a smart guy, but i don’t think it wise to treat API’s as toys to play with, particularly if you want to bet your company’s products on a software library.

So long as PEAR remains a coders playground, depending on PEAR does not mean that anyone is looking after your interest either.

Maybe John Lim is just trying to get people to use his product, ADOdb, instead. But if what he says about PEAR DB is true, then thats a big “STAY CLEAR” sign to me. I’m already going to have to rewrite thousands of lines of code if I decide to switch to an abstraction layer. I don’t want to have to do it again each time they come out with a new version. That’s exactly what I am afraid of.

One thing is clear to me. I need to pick an abstraction layer soon. I’m starting new projects all the time and the longer I wait the more code I will need to rewrite later. So which to choose? Right now I think I’m leaning toward ADOdb. It is very fast, easily portable, supports almost every database out there, and has many features above and beyond what I’m used to. I’ve also been watching it for about a year and new versions are released often, but never break backwards compatibility.

Anyway, what are your guys thoughts on this?