Now that flash storage is becoming more popular, IO alignment question keeps popping up more often than it used to when all we had were rotating hard disk drives. I think the reason is very simple – when systems only had one bearing hard disk drive (HDD) as in RAID1 or one disk drive at all, you couldn’t really have misaligned IO because HDDs operate in 512-byte sectors and that’s also the smallest amount of disk IO that systems can do. NAND flash on the other hand can have a page size of 512-bytes, 2kbytes or 4kbytes (and often you don’t know what size it is really) so the IO alignment question becomes more relevant.

It was and still is, however, relevant with HDD RAID storage – technology we have been using for many years – when there’s striping like in RAID0, 5, 6 or any variation of them (5+0, 1+0, 1+0+0 etc.). While IO inside the RAID is perfectly aligned to disk sectors (again due to the fact operations are done in …

Mythbusters: How to configure InnoDB buffer poll on large MySQL servers

Yesterday I wrote about the dangers in using top on systems with 100+ GB of RAM, not to mention future systems with 1+ TB. A related topic is, how should I configure MySQL on such a large system?

There is a classic rule of thumb that on a dedicated MySQL server one should allocate 80% of memory to the InnoDB buffer pool. On a 128GB system that is 102.4 GB. This means that I would leave 25.6 GB of RAM "unused". So surely on these large systems, this old piece of advice cannot hold anymore. If the database was previously running on a server that in total had less than that altogether, it seems wrong to leave so much memory just unused. Let's label the old rule of thumb tentatively a "myth" and ask mythbusters to figure out a new MySQL configuration …

I’ve seen a number of solutions for finding long running transactions or sessions within InnoDB / MySQL now. Every single one of them has (in the past by necessity) been implemented as a script (other than one, more on that one later) that is either invoked manually, or via some cron job, that then connects and tries to find, and possibly diagnose or kill, transactions that break some “long …

To follow on to my earlier links to Brendan Gregg’s blog posts on measuring I/O latency, there is a third one discussing DTrace, and then a very detailed response from Mark Leith showing how to do it with the PERFORMANCE_SCHEMA in MySQL 5.5.

Related posts:

1. Disk latency versus filesystem latency
2. Measuring the popularity of the Percona MySQL build
3. Thoughts on the new …

There are multiple metrics that are really useful for read workload analysis, that should all be tracked and looked at in performance-critical environments.

The most commonly used is of course Questions (or ‘Queries’, ‘COM_Select’) – this is probably primary finger-pointing metric that can be used in communication with different departments (“why did your qps go up by 30%?”) – it doesn’t always reveal actual cost, it can be increase of actual request rates, it can be new feature, it can be fat fingers error somewhere in the code or improperly handled cache failure.

Another important to note is Connections – MySQL’s costly bottleneck. Though most of users won’t be approaching ~10k/s area – at that point connection pooling starts actually making sense – it is worth to check for other reasons, such as “maybe we connect when we shouldn’t”, or needlessly reconnect, or …

In the InnoDB plugin, a new variable was added named innodb_io_capacity, which controls the maximum number of I/O operations per second that InnoDB will perform (which includes the flushing rate of dirty pages as well as the insert buffer (ibuf) batch size).

First off, let me just say this is a welcome addition (an addition provided by the Google Team, fwiw).

However, before this was configurable, the internal hard-coded value for this was 100. But when this became configurable, the default was increased to 200.

For many systems, this is not an issue (i.e., the overall system can perform 200 IOPS).

However, there are still many disks (which is often the bottleneck) out there that are …

There are two ways InnoDB can organize tablespaces. First is when all data, indexes and system buffers are stored in a single tablespace. This is typicaly one or several ibdata files. A well known innodb_file_per_table option brings the second one. Tables and system areas are split into different files. Usually system tablespace is located in ibdata1 file and every InnoDB table has two files e.g.  actor.frm and actor.ibd.

The annoying thing about .ibd files you can’t easily copy the an .ibd file to another MySQL server. If you try to very often you’ll get an error in the log:

InnoDB: Error: tablespace id is 10 in the data dictionary
InnoDB: but in file ./sakila/actor.ibd it is 15!

However sometimes you have to connect the .ibd file to an alien ibdata1.

There are several situation when you have to:

1. ibdata1 is erroneously removed

2. ibdata1 is …

One of the more exciting new features in MySQL 5.6 is the InnoDB to Memcached interface. Basically memcached runs as a daemon plugin and can bypass the SQL optimizer and parser for NoSQL access.

The first step is to download the new MySQL 5.6 with the InnoDB-Memcache preview. Sorry, Linux only at this time. And install memcached.

Second, run the provided configuration script mysql> < scripts/innodb_memcached_config.sql. This will do a lot of the work to get things running out of the box and one of the links below details what is happening behind the scenes when you run the script. Third, load the plugin mysql> install plugin daemon_memcached soname “libmemcached.so”;. Forth, to make sure we can see recently inserted data, you will need to set the transaction level mysql> set session TRANSACTION ISOLATION LEVEL read uncommitted;

…

InnoDB uses an index-organized data storage technique, wherein the primary key acts as the clustered index and this clustered index holds the data. Its for this reason that understanding the basics of InnoDB primary key is very important, and hence the need for these notes.

MySQL Enterprise Monitor collects a huge amount of information about MySQL and the systems that it runs on. To do this, it employs an “Agent” to collect these statistics.

This can either sit locally to the database server, or on a remote host – perhaps even the same host as the Dashboard server if you decide you don’t want to distribute it to many boxes – and checks when it is local or remote, to decide whether to collect OS statistics or not. A single agent can monitor either a single or multiple instances from the same process.

Not all of the information that it can collect is fully exposed within the UI yet however (there are so many things …