Today I gave my first MySQL University session as a speaker, talking about Backing up MySQL using file system snapshots. The talk went quite well (at least that was my impression) and we had ~10 people attending. The slides (PDF) and a recording of the session are now available from the Wiki page. Unfortunately the recording lacks the audio track, which is a bit of a bummer. We've submitted a support request with the DimDim folks, so hopefully they can provide us with a complete recording.

There was one question during the session that I was not able to answer myself, so I'm asking for your …

If you are using InnoDB Hot Backup utility and the innobackup.pl wrapper script, be very careful if you are not running backups under the system mysql user. There is a bug which causes InnoDB Hot Backup to sometimes report a successful backup when it actually failed.

The last few dozen lines of the output was from the backup was (after generalizing the db and table names):

InnoDB: File name /var/lib/mysql/data/dbname/TABLE_A.frm
InnoDB: File operation call: 'stat'.
090210Â  3:55:00Â  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name /var/lib/mysql/data/dbname/TABLE_B.frm
InnoDB: File operation call: 'stat'.
090210Â  3:55:00Â  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name …

MySQL 5.1 has a lot of nice new features, partitioning, event scheduler, dynamic logging, etc... Most of these require changing some application code or design logic to make use of them. However, there is a few new features that almost every user of InnoDB can take advantage of right away and that is the ability to set MySQL so that less locking is required for many statements in InnoDB. There are a few changes which make this possible.

The first one is the new auto_increment locking for InnoDB. There is a good article which talks about this here and a large section the in the manual.

In MySQL 5.0 and previous, InnoDB used a special query-duration table level lock to acquire the AUTO-INC …

When you first start browsing the code for Innodb (a Mysql Storage Engine) you will notice several strange macros like UNIV_LIKELY popping up all over the place. Its good to get clear on what those mean so as not to distract you from understanding the actual logic of the code you are reading.

The set of macros described below all wrap GCC builtin functions which are used for performance optimization. The macros are defined as blank space on other platforms not supporting these builtin functions. The macros are in two categories the expect and prefetch macros which I will describe separately:

EXPECT MACROS

These macros take expressions and return the exact same value as the expression would return without the macros. From the user's point of view, they are the same as just enclosing the expression in an extra set of parenthesis. However the macros, provide hints to the optimizer about the expected value of …

One of InnoDB's features is that memory allocated for internal tables definitions is not limited and may grow indefinitely. You may not notice it if you have an usual application with say 100-1000 tables. But for hosting providers and for user oriented applications ( each user has dedicated database / table) it is disaster. For 100.000+ tables InnoDB is consuming gigabytes of memory, keeping definition in memory all time after table was once opened. Only way to cleanup memory is to drop table or restart mysqld - I can't say this is good solution, so we made patch which allows to restrict memory dedicated for data dictionary.

Patch was made by request of our customer Vertical Response and released under GPL, so you can download it there …

The problem with broken group commit was discusses many times, bug report was reported 3.5 years ago and still not fixed in MySQL 5.0/5.1 (and most likely will not be in MySQL 5.1). Although the rough truth is this bug is very hard (if possible) to fix properly. In short words if you enable replication (log-bin) on server without BBU (battery backup unit) your InnoDB write performance in concurrent load drops down significantly.
We wrote also about it before, see "Group commit and real fsync" and "Group commit and XA".

The problem is the InnoDB tries to keep the same order of transactions in binary logs and in transaction logs and acquires mutex to …

Recently a customer mentioned that they were seeing corruption rarely when they copied InnoDB files using LVM to setup a new slave. Investigating further, it turns out that replication would start, but would then hit a lock wait timeout. This locking issue occurred across restarts causing replication to always fail. They would solve this by taking a new LVM snapshot and resetting it up.

This is a classic case of an XA transaction in a prepared state persisting across restarts and the LVM sometimes taking a snapshot at the exact instant when this can occur.

Most people don't use actually XA transactions, so they may not consider this possibility. However, it can still occur even if you do not use them due to XA being used internally for the binary log and InnoDB. We will be able to identify that case later.

You can tell this …

Are you running MySQL on Debian or Ubuntu with InnoDB? You might want to disable /etc/mysql/debian-start. When you run /etc/init.d/mysql start it runs this script, which runs mysqlcheck, which can destroy performance.

It can happen on a server with MyISAM tables, if there are enough tables, but it is far worse on InnoDB. There are a few reasons why this happens -- access to open an InnoDB table is serialized by a mutex, for one thing, and the mysqlcheck script opens all tables. One at a time.

It's pretty easy to get into a "perfect storm" scenario. For example, I'm working with one client right now who has a hosted multi-tenanting application that keeps each customer in its own database. So they have a lot of databases and a lot of tables. And they're running on Amazon EC2 with 8G of RAM and EBS storage, which is slower than typical directly-attached server-grade RAID storage. Since they have a lot of tables, InnoDB uses …

This Thursday (January 29th), we're continuing our series of sessions on MySQL performance measuring and improvements with David Lutz' presentation titled Scalability Challenges in an InnoDB-based Replication Environment. David works in the Performance and Applications Engineering department at Sun Microsystems, so again, expect to get some deep insights into the inner workings of the MySQL Server.

David is based in California, so note that this session will take place in the morning (America) or evening (Europe), respectively.

For MySQL University sessions, point your browser to this page. You need a browser with a working …

Like a few other folks, I decided to play with iiBench and see how fast I could insert 1B rows into an indexed InnoDB table. Mark Callaghan published  an excellent writeup of the theoretical and practical limits when index size >> buffer pool... but I bent the rules a bit ;)
The platform I ran iiBench on has four dual-core Xeon 3.2Ghz, internal 10k RPM 8 disk RAID 10, and 128GB of RAM. That's a lot, I know, but it was built precisely to avoid InnoDB's performance drop when an index does not fit in memory. It took just 22 hours to run a single iiBench process and 10 hours to run four processes in parallel (each doing 250m rows)! This was achieved without modifications to the iiBench code, using standard MySQL 5.1.30 binaries, the following …