An update of MySQL from 5.0 to 5.5 on CentOS 5.5 64bit has not resulted in a good experience. The mysqld process would then crash every few minutes with the following message.

101120 8:29:27 InnoDB: Operating system error number 22 in a file operation.
InnoDB: Error number 22 means ‘Invalid argument’.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/operating-system-error-codes.html
InnoDB: File name /tmpfs/#sql6cf3_5c_0.ibd
InnoDB: File operation call: ‘aio write’.
InnoDB: Cannot continue operation.

The work around was to change the tmpdir=/tmpfs (which was a 16G tmpfs filesystem) to a physical disk.

The referenced URL didn’t provide any more information of help. Unlike Bug #26662 O_DIRECT is not specified as the flush method.

I'll try to pass on some basic knowledge about those confusing stack traces we sometimes see in the mysql error logs.  What can you tell from them, what are they useful for, and how to validate them?
Debugging Crashes
We tried to improve postmortem debugging of crashes + stack traces in the error log:o) old versions of mysqld only printed numerical numbers instead of function names (if you're lucky!)o) some platforms/architectures printed no stack trace what-so-ever!o) …

While some may think I’m daft, I have a legitimate reason for wanting to crash mysqld. However first we need to find a way to crash it.

Great thanks to Alan K, Mark L, Harrison and Hartmut on #mysql-dev for several suggestions and a config option I was unaware of. My investigation even lead to a documentation bug logged as #51739.

My first thought was to find a known bug and if necessary install the correct version to test that. A good one was suggested, Bug #48508 which fails on several versions that I will use to demonstrate with, however the simplest way is to issue kill -11

By default, no core file will be produced which is what I’m seeking but with the right options this is possible. First, the user running mysqld probably has a core file limit size of 0.

$ ulimit …

There are those that are very adamant about letting people know that using INFORMATION_SCHEMA can crash your database. For example, in making changes to many tables at once Baron writes:

“querying the INFORMATION_SCHEMA database on MySQL can completely lock a busy server for a long time. It can even crash it. It is very dangerous.”

Though Baron is telling the truth here, he left out one extremely important piece of information: you can actually figure out how dangerous your INFORMATION_SCHEMA query will be, ahead of time, using EXPLAIN.

In MySQL 5.1.21 and higher, not only were optimizations made to the INFORMATION_SCHEMA, but new values were added so that EXPLAIN had better visibility into what MySQL is actually doing. As per …

There’s one stage in InnoDB crash recovery where it reads log file, you know, this one:

InnoDB: Doing recovery: scanned up to log sequence number 354164119040
InnoDB: Doing recovery: scanned up to log sequence number 354169361920

On a machine with bigger logs it will start spending nearly 100% CPU somewhere in recv_scan_log_recs. Guess what it does…. -fno-inline builds to the rescue:

#0  mem_block_get_len at ./include/mem0mem.ic:86
#1  mem_heap_get_size at ./include/mem0mem.ic:591
#2  recv_scan_log_recs at log/log0recv.c:2727

And:

samples  %        symbol name
8467     72.9222  mem_heap_get_size
291       2.5062  recv_add_to_hash_table
95        0.8182  mem_block_get_len

To speak in layman’s terms, InnoDB does SUM(LENGTH(allocation)) on its relatively wide memory (tens, hundreds of thousands of entries) arena, FOR EVERY LOG SEGMENT, to make sure it didn’t run out of available …

Many configs have thread_stack_size configured explicitly, but that can cause rather bad trouble:

• if the stack inside a thread it’s too small, you can get segfault crashes (stack overflow, essentially). Particularly on 64-bit.
• if the stack is too large, your system cannot handle as many connections since it all eats RAM.

Let mysqld sort it out, on startup it does a calculation based on the CPU architecture, and that’s actually the most sensible. So for almost all setups, remove any thread_stack_size=… line you might have in my.cnf.

I generally use the following MySQL INFORMATION_SCHEMA (I_S) query to Calculate Your MySQL Database Size. This query and most others that access the MySQL INFORMATION_SCHEMA can be very slow to execute because they are not real tables and are not governed by physical data, memory buffers and indexes for example but rather internal MySQL data structures.

Mark Leith indicates in his post on innodb_stats_on_metadata that Innodb performs 8 random(ish) dives in to the index, when anybody accesses any of SHOW TABLE STATUS, SHOW INDEX, INFORMATION_SCHEMA.TABLES,INFORMATION_SCHEMA.STATISTICS for InnoDB tables. This can have an effect on performance, especially with a large number of Innodb tables, and a poor ratio of innodb_buffer_pool_size to disk data+index footprint.

What is even more …

As all you Mac users (and probably many non-Macies) know Apple released Snow Leopard (Mac OS X 10.6) recently, even though this release was announced for September previously. Since a large part of the MySQL Workbench user base works on OS X it was clear that many of you will test Workbench on the new OS. However, so far we haven’t had the opportunity to do the same (too busy fixing bugs on released OSes) and hence we did not know about incompatibility problems there.

In the meantime several users did tests and reported us a crash on startup of the application, which means you cannot use MySQL Workbench on Snow Leopard for the time being. We are currently preparing build, test and developer machines with it and will hand out a fixed WB release as soon as possible. So, please stay patient. It’s only a matter of days.

Do you know if your production MySQL servers will come back up when restarted? A recent support episode illustrates a number of best practices. The task looked trivial: Update a production MySQL server (replication master) with a configuration tuned and tested on a development server. Clean shutdown, change configuration, restart. Unfortunately, the MySQL daemon did not just ‘come back’, leaving 2 sites offline. Thus begins an illuminating debugging story. First place to look is the daemon error log, which revealed that the server was segfaulting, seemingly at the end of or just after InnoDB recovery. Reverting to the previous configuration did not help, nor did changing the InnoDB recovery mode. Working with the client, we performed a failover to a replication slave, while I got a second opinion from a fellow engineer to work out what had gone wrong on the server. Since debug symbols weren’t shown in the stack trace, we needed …

While I hope the MySQL Proxy never crashes, it will happen, there will be some strange (or maybe not so strange) usage or workload and it will die.

To avoid this, you could decide not to use it, or maybe you could use something like Linux HA to have more than one MySQL Proxy running at all times. Or you could use one of the new features that comes with the version 0.7.0.

What is it?
We now have a --keepalive option. As the name indicates, if the mysql proxy process dies/crashes, it will come back up in a few seconds (less than 5 seconds).

How does it work?
If you start the MySQL Proxy with the keepalive option, there will be two processes with the same name. One will be very small, about 600KB. and then you …