Recently, I found a very handy tool, innodb-recovery, that can break an InnoDB file in pages. I am a visual type person so what a better occasion to try to learn more about the InnoDB file structure. To explore the file structure, I used the following table:

Create Table: CREATE TABLE `test_innodb_index` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(50) DEFAULT NULL,
  `spacer` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idxdata` (`data`)
) ENGINE=InnoDB AUTO_INCREMENT=1901 DEFAULT CHARSET=latin1

and inserted a bunch of rows like these:

select * from test_innodb_index limit 4;
+----+-------+--------+
| id | data  | spacer |
+----+-------+--------+
|  1 | aaaaa | |      |
|  2 | aaaaa | |      |
|  3 | aaaaa | |      |
|  4 | aaaaa | |      |
+----+-------+--------+
4 rows in set (0.00 sec)

After the insertion, the table status looked like this:

mysql> show table status like …

It is well known that MySQL can use a lot of temporary files and that, using a ramdisk (tmpfs on Linux) can improve performance drastically as Matt has shown here. The problem with tmpfs is its limited size. You need just one query creating a huge temporary file that happens once per day to screw up the tmpfs scenario. I have done some fuse programming in my previous job and I think it is possible to use fuse to mix a tmpfs filesystem with a regular filesystem, the regular filesystem being used only when the tmpfs is full. Fuse has a lower performance level than a regular filesystem but I think the possibility to use tmpfs the vast majority of the queries needing a temporary file will overcome the performance limitation of fuse.

That is the explanation for the MiniWheatFS name, the frosted side is the tmpfs and the healthy side is the regular filesystem. There are many fuse …

Tuning queries for MySQL NDB cluster is way trickier than tuning for any other storage engines. This is especially true for highly normalized schema. Look at the following query trying to retrieve all male account from a given city. Since a city is not unique, we also need to specify the state.

If you look at how the query will be executed with NDB, you realize that many hops over the network will be needed, at least one per table. If you push the cluster a lot, these extra hops will limit the performance. Since tables like Cities, States and especially Gender are …

Recently, I have been traveling a lot to the west coast (I live in Quebec) so I had plenty of time to update WaffleGrid while flying. Here are the new features/ports:

So now, if you update the innodb_memcached_servers with a statement like “set global innodb_memcached_servers = ’serverA,serverB:11212′;” then MySQL will disconnect from the current servers and reconnect to the new ones. The key prefix is incremented to prevent cache coherency problems.

You might have noticed that Matt added many WaffleGrid statistic counters like shown here. Those counters have been ported to the InnoDB plugin version.

I also added a CRC32 checksum for each block sent to memcached, the …

With the NDB realtime options, you can choose on which CPU the execution thread and the maintenance will be running. The point is, which CPUs to use.

The output of “cat /proc/interrupts” will help you determine which CPU to use. Here is an example of a dual quad-cores box:

For the ones who are interested, there will be a webinar on WaffleGrid next Tuesday, February the 17th at 10:00 am PST (1:00 pm EST, 18:00 GMT). Matt will present benchmarks he did with Dbt2 and Sysbench using GbE, Dolphin, SSD while, for my part, I will present the WaffleGrid concepts, status and roadmap. I will also be a good way to learn how to get started with WaffleGrid if you want to start experimenting!

Registration is free!

In case you have not noticed, while I remain the Original Big DBA Head…  Yves Trudeau Has earn the title of Big DBA Head as well.  I have noticed that I was getting credit for a couple of his posts, so I wanted to set the record straight.   While Yves and I actually both started on the same day in the professional services group for MySQL, we are not the same person:)

I was recently involved in a project where the main requirement was the smallest possible latency for queries. The queries were simple insert and update statements, the update being by primary key. The cluster was using regular Gigabits Ethernet and formed by 3 servers with 8 cores each, one Management node and 2 data nodes. Since throughput was not a concern, a design decision has been made to locate the MySQL daemons on the same servers as the data nodes, in order to save network hops. MySQL chooses the closest NDB data node as its transaction coordinators and the closest on will be reachable on localhost.

Apart from the architectural decision to host MySQL and the NDB nodes on the same boxes, we decided to benchmark the new real time options of NDB. The options are the following:

If you followed the recent blog posts by Matt, you have noticed the nice performance. This confirms that the Wafflegrid concept is a good one and we need to continue its development and improve it.

You have also probably noticed there has been no recent WaffleGrid release, the reason is that we were thinking about how to distribute WaffleGrid. The current “patch” like approach is, let’s say, pretty poor. Another approach would be to work on the whole tree of MySQL or Drizzle but then, it would be pretty hard for someone to use InnoDB and WaffleGrid at the same time, not ideal either.

Hey…. MySQL has a plugin interface for storage engines, why not just add a WaffleGrid storage engine. It would be much easier to add WaffleGrid to MySQL and Drizzle and we would also be far less impacted by new releases of the databases. So, the next release will be a branch of InnoDB plugin and from there, integrating to Drizzle will be also …

Have you ever been upset by the Linux tendancy to swap… Especially when trying to allocate a large InnoDB buffer pool.. Look at the following output:

yves@yves-laptop:~$ free
total used free shared buffers cached
Mem: 2041888 1991096 50792 0 52 954592
-/+ buffers/cache: 1036452 1005436
Swap: 975200 1308 973892

There is still 50792 + 52 + 954592 = 1005436 of free memory and Linux starts to swap!!! The reason is hidden here:

yves@yves-laptop:~$ cat /proc/sys/vm/swappiness
60

The swappiness controls the Linux to swap for the File cache. For a file server or a web server or even MySQL with MyISAM tables, the file cache is interesting but for InnoDB or NDB Cluster it is close to useless. Only put a “0″ in that proc entry (echo 0 > /proc/sys/vm/swappiness) and add …