InnoDB is a transaction-safe, ACID compliant MySQL storage engine. It has commit, rollback, and crash recovery capabilities, and offers row level locking. The engine's overview page explains, “InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.”

read more

We recommend two open source tools to help with the regular tuning and monitoring of your MySQL database: mysqlreport and mysqlsla. Your website is made from many complex systems. Rapid growth, changes to your site, and other systems can change the load on your MySQL database. It is important that your internal staff become familiar with using these tools and implement routine maintenance. An initial review often leads to significant improvements, and will also help you to implement a monitoring solution for your ongoing performance efforts.

read more

vmstat is a very useful tool, especially when you are doing performance tuning of an application like MySQL. The only problem with the output from vmstat is that it is rather non-visual. A simple solution is to import the output in a spreadsheet like Openoffice Calc and create a graph, but it is long and painful. Creating many such graphs is not fun at all.

Fortunately, in the Linux world, there are tools to generate graph nearly automatically. Here is my own set of tools to creates graphs from vmstat output. You need the gnuplot program and awk, which is on all Linux version.

The output of vmstat looks like this:

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
1  0    164  48604  45136 4871464    0    0    94   111    4    7  1  0 98  0  0
0  2    164  48476  45188 4871552    0    0    35    15 1062  133  1  0 99  0 …

A

It's been a while since I've written about progress on the book. I actually stopped working on it as much at the beginning of the month, because on October 31(st) I managed to finish a first draft of the last big chapter! Now I'm back to full-time work at my employer, and I'm working on the book in the evenings and weekends only. Read on for details of what I've been working on and what's next in the pipeline.

Every programmer loves to optimize, even when we know we shouldn't. To satisfy your cravings MySQL has several keywords that can be placed in your SQL statement to give the database server an explicit optimization instruction.

I should point out that using the hints incorrectly will most likley cause your queries to perform worse, so be sure that it actually makes sense to use them before you go nuts. This means use EXPLAIN and read the documentation on each hint before using.

It's also a good idea to enclose the hints within a SQL comment, for example SELECT /*! SQL_NO_CACHE */ columns FROM table. This can help to make your application a bit more portable.

Let's take a look at some MySQL Optimization Hints:

SQL_NO_CACHE

The SQL_NO_CACHE hint turns off MySQL's …

Jay Pipes is a co-author of the recently published Pro MySQL recently spoke at Google on MySQL Performance Tuning Best Practices

You can check out a video of the presentation on Google Video

There is also a good video from Google called The Paradox of Choice: Why More is Less.