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.

…

Percona was working closely with Virident on evaluating tachIOn as solution for MySQL, and as result you can find whitepaper “Scaling MySQL Deployments Efficiently Using Virident tachIOn Drives”, available from Virident website. It was done as part of our consulting practice, but all results and numbers are certified by Percona. I personally really enjoyed performance and stability provided by tachIOn card, and you actually saw bunch of benchmarks results published on our blog which proves it.
Virident tachIOn will be on the list of our recommendations for customers looking to improve performance or building high-performance solution based on MySQL.

About a year ago, I started a study of emergency incidents that our customers filed with us. What I found was really surprising, and defied conventional wisdom. I learned a lot about preventing emergencies. I just published the outcome as a white paper, including checklists that you can use and modify for your own servers. (Analysis of the nature and causes of emergencies is due to be published in the next issue of IOUG’s quarterly SELECT journal.)

A customer called with an emergency issue: A server that normally runs many MySQL instances wouldn’t start them up. Not only would it not start all of them, it wouldn’t even start the first one. The multiple instances were started through the mysql_multi init script. Perhaps you already know what was wrong!

It turns out that this server’s /etc/init.d/mysql_multi wouldn’t start unless it found the text “mysqld_multi” in the /etc/my.cnf file. Not a [mysqld_multi] config file section, but the text string “mysqld_multi”. It was using this text as a proxy for “I found a [mysqld_multi] configuration section.” This was a rather brittle test, as you can imagine.

After reading the source, I determined that the my.cnf file was fine and the configuration should not be changed, and I could not understand what had changed since it was previously working. Perhaps an automated upgrade or a similar change to the system had …

I’ve been working with an application which does a lot of CREATE and DROP table for Innodb tables and we’ve discovered DROP TABLE can take a lot of time and when it happens a lot of other threads stall in “Opening Tables” State. Also contrary to my initial suspect benchmarking create/drop table was CPU bound rather than IO bound.

I’ve got an oprofile run and saw the following:

15753796 56.0725  no-vmlinux               no-vmlinux               /no-vmlinux
11834143 42.1213  mysqld                   mysqld                   buf_LRU_invalidate_tablespace
168823    0.6009  mysql                    mysql                    completion_hash_update(hashtable*, char*, unsigned int, char*)
53667     0.1910  oprofiled                oprofiled                /usr/bin/oprofiled
42116     0.1499  mysqld                   mysqld                   buf_calc_page_new_checksum
32107     0.1143  mysqld                   mysqld …

Xaprb (Baron) recently blogged about how InnoDB performs a checkpoint , I thought it might be useful to explain another important mechanism that affects both response time and throughput – The transaction log.

The InnoDB transaction log handles REDO logging, this is the mechanism that provides the A (Atomic) C (Consistent)  and D (Durability) in ACID. The transaction log keeps a complete record of every change that occurs to the pages inside the database. Instead of logging whole pages (Value / Physical logging) where both the before and after image of a page is logged or by logging logical changes  (Logical logging) to the dataset, InnoDB uses  …