specializing in digital media technologies

The performance of any Web application is greatly impacted by how well the database application is being used (if being used, of course). The four biggest impacts on performance are:

• The storage engine(s) used
• How columns are defined
• The existence of proper indexes
• How queries are written and executed

MySQL supports many different storage engines, each with its own strengths and weaknesses. Which you choose isn’t just a performance issue, it’s also one of features, like whether you need to support FULLTEXT searches (and therefore MyISAM tables) or transactions (which means InnoDB). Sometimes you can get away with using HEAP tables, which exist only in memory. There’s no permanent storage with HEAP tables, but the performance can’t be beat.

For the column definitions, the first goal is to use the smallest possible size for each column. This will be a limitation in your application, so the column’s size still needs to meet or exceed the largest possible value to be stored, but, generally speaking, the more data you want to possibly store, the more space will be required for all the data stored. You should also avoid allowing NULL values in columns. This is a factor in both good database design and in performance optimization.

The indexes you establish, and how they are used by queries, is a serious subject that requires a book in itself. To start, make sure you read MySQL’s own guide to how MySQL uses indexes. Second, learn to run your queries using EXPLAIN to see what, exactly, MySQL is doing when it executes that query. Also, of course, only select the data your application will actually need. No need to retrieve information that won’t be used. And always go back and double-check that your queries need all the clauses and references they’ve got. I’ve been known to leave legacy conditionals in queries that have no effect whatsoever.

For more on some of these topics, see the MySQL manual, of course (which has a section on optimization), the PlanetMySQL blog, and this article at DatabaseJournal.com. Also, if it applies to you, you can tweak MySQL’s behavior from a hardware/OS perspective. For an introduction to this subject, with respect to Linux, check out this article by Jeremy Zawodny, an expert on MySQL performance tuning.

I’ve just recently come across a piece of software called Jet Profiler for MySQL. This is a program that runs on Mac OS X, Linux, and Windows (it’s runs on Java), and comes in both a free and commercial version. Jet Profiler for MySQL is a diagnostic tool, used to analyze and report upon your database’s performance, specifically in terms of the queries run, the tables used, and the MySQL users. It also has more advanced capabilities, like reporting upon master/slave relationships, locking, threads, and so forth. One particularly interesting feature is that it doesn’t require any special server configuration; in fact, it doesn’t even need to be installed on the server. You can install the application on your desktop computer, create a new MySQL user on the server, and the software will connect through it. Software like this is another good way to better understand your database and your Web application.

In two previous posts, I discussed analyzing my site using Yahoo!’s YSlow Firefox plug-in. I went through the initial test results and the steps I took to improve my score. At the end of part 2, I was left with a grade of 95 (whohoo!) but still two hurdles: ETags and sending compressed JavaScript and CSS. (continue reading…)