We’ve just published a new white paper analyzing the causes of emergency incidents filed by our customers. The numbers contradict the urban myth that bad SQL is the most common problem in databases. There are a number of surprises in other areas, too, such as the causes of data loss. This is the companion to my earlier white paper suggesting ways to prevent emergencies in MySQL. It is a re-published and re-edited version of an article that just appeared in IOUG’s SELECT magazine. You can download it for free from the MySQL white papers page on the Percona web site.

(This is a cross post from percona.tv – the home of percona material in video form.)

Last month I gave a presentation at the PHP UK Conference on the InnoDB storage engine.  I was a last minute speaker, and I want to thank them for the time-slot and their hospitality at short notice.

The video has been posted online:

The InnoDB Storage Engine for MySQL – Morgan Tocker from PHP UK Conference on Vimeo.

It relates to InnoDB built-in and InnoDB plugin.  I left out Percona Server and XtraDB for simplicity.

If you want to …

HandlerSocket has really generated a lot of interest because of the dual promises of ease-of-use and blazing-fast performance. The performance comes from eliminating CPU consumption. Akira Higuchi’s HandlerSocket presentation from a couple of months back had some really good profile results for libmysql versus libhsclient (starting at slide 15). Somebody in the audience at Percona Live asked about the profile results when using prepared statements and I’m just getting around to publishing the numbers now; I’ll reproduce the original numbers here, for reference:

There are many angles you can look at the system to predict in performance, the model baron has published for example is good for measuring scalability of the system as concurrency growths. In many cases however we’re facing a need to answer a question how much load a given system can handle when load is low and we might not be able to perform reliable benchmark.

Before I get into further details I’d like to look at basics – what resources are really needed to provide resource for given query ? It surely needs CPU cycles, it may need disk IO. You may also need other resources such as network IO or memory to store temporary table, but let us ignore them for a moment. The amount of resources system has will place a limit on amount of queries system can ran, for example if we have query which requires 1 CPU …

Among the many things that can cause a “server stall” is a long-running transaction. If a transaction remains open for a very long time without committing, and has modified data, then other transactions could block and fail with a lock wait timeout. The problem is, it can be very difficult to find the offending code so that it can be fixed. I see this much too often, and have developed a favorite technique for tracking down what that long-running transaction is doing.

Of course, in some cases it’s actually easy to figure out what the long-running transaction is doing. The most obvious is if it’s a long-running query. If that’s the case, then you’ll see the query in the processlist, and you can track down where it’s coming from in the source code. The problem comes when the transaction remains open, but either it isn’t running queries anymore, or it runs such fast queries that you can’t capture them in the processlist.

We receive many requests for help with server stalls. They come under various names: lockup, freeze, sudden slowdown. When something happens only once or twice a day, it can be difficult to catch it in action. Unfortunately, this often leads to trial-and-error approaches, which can drag on for days (or even months), and cause a lot of harm due to the “error” part of “trial-and-error.” At Percona we have become skilled at diagnosing these types of problems, and we can solve many of them quickly and conclusively with no guesswork. The key is to use a logical approach and good tools.

The process is straightforward:

1. Determine what conditions are observably abnormal when the problem occurs.
2. Gather diagnostic data when the conditions occur.
3. Analyze the diagnostic data. The answer will usually be obvious.

Step 1 is usually pretty simple, but it’s the most important to get right. …

A common misunderstanding about innodb_support_xa is that it enables user-initiated XA transactions, that is, transactions that are prepared and then committed on multiple systems, with an external transaction coordinator. This is actually not precisely what this option is for. It enables two-phase commit in InnoDB (prepare, then commit). This is necessary not only for user-initiated XA, but also for internal XA coordination between the InnoDB transaction logs and the MySQL binary logs, to ensure that they are consistent. Consistent is an important word with a special meaning.

We have done some benchmarking and performance research on this option in the past (see also: post 1, post 2). This was motivated by …

For a long time I’ve used a little trick to check whether there are syntax errors in a server’s my.cnf file. I do this when I need to shut down and restart the server, and I’ve either made changes to the file, or I’m worried that someone else has done so. I don’t want to have extra downtime because of a syntax error.

The trick is to examine the currently running MySQL server’s command-line from ps -eaf | grep mysqld, and then copy those options into something like the following:

/usr/sbin/mysqld <options> --help --verbose

However, this requires care. First, it should be run as a user who doesn’t have write privileges to the database directory, so it can’t actually mess with the server’s data if something goes wrong. Second, you need to specify a non-default socket and pid-file location. If you run the command as a privileged user, it will actually remove the pid file from the running …

Much has been written about tools to inspect Linux systems, and much has been written about Solaris’s Big Important Tools such as DTrace. But I don’t recall seeing much in the MySQL blogs about basic tools to find one’s way around a Solaris system and discover the system, get fundamental performance, configuration, and status information, and so on. Here’s a quick list of some key tools.

• Many of the tools you’re used to from Linux will work, although sometimes they output different information. An example is vmstat. Many tools such as uptime will work as you’re used to. And of course, Solaris is POSIX compliant, so if a core UNIX utility doesn’t do what you want, the problem is you
• One example of a tool that is often installed on Solaris but should not be preferred is top. Instead, you should use the native Solaris tool, prstat, which …

The performance problems caused by battery auto learning go many years back. We wrote about it, other people from MySQL Community too. The situation did not get better, at least not with Dell RAID controllers, H700 and H800 have the same problem too. At the same time situation got worse as a lot more people are running Innodb in full durability mode which is dramatically affected by this setting.

…