Chip Turner has begun work on a new storage engine that makes it easy to export files as if they were database tables. Why is this good? I have written code to export data from /proc in SHOW STATUS. This makes it possible to track changes in system resource consumption, such as the output from vmstat. The code I wrote is not portable, as it only exports a few values. The work underway by Chip makes it possible to read many more files. This code isn't ready for production, but it is amazing that it only takes a few days to get something running.

My description doesn't do this justice, the example that follows does. And another example is here.

create table foo (load1 DECIMAL(3, 2),
                  load5 DECIMAL(3, 2),
                  load15 DECIMAL(3, 2),
                  active_processes VARCHAR(12),
                  lastpid INTEGER)
engine=filesystem connection="/proc/loadavg";

select * from foo;

+-------+-------+--------+------------------+---------+
| load1 | load5 | load15 | active_processes | lastpid |
+-------+-------+--------+------------------+---------+
|  0.01 |  0.39 |   0.65 | 1/98             |    1713 |
+-------+-------+--------+------------------+---------+

create table vmstat (label varchar(64), value integer)
engine=filesystem connection="/proc/vmstat";

select * from vmstat order by label limit 4;

+-------------------+--------+
| label             | value  |
+-------------------+--------+
| allocstall        |      0 |
| kswapd_inodesteal |   1393 |
| kswapd_steal      | 133347 |
| nr_anon_pages     |   2265 |
+-------------------+--------+

I am lucky right now to be working for a company that when they make public statements about open source database software, MySQL and InnoDB, such statements are noticed. I am even luckier that such statements are true.

I went to the MySQL Replication Roadmap and Vision talk by Lars Thalmann today. I am very happy with what I heard as they are working on the problems that when solved, will make MySQL deployments more highly available, even if you don't use MySQL Cluster. We have published patches that solve some of these problems for us today, but we much prefer a solution provided by MySQL, as they will then provide support for those features and we can reduce the overhead of maintaining our patches.

Some of the features that I really want are listed below. Were these available today, we would not have done our patches and would have had much more spare time to visit the gym and work off the great lunches and dinners that are provided to us.

• including the original SQL statement when row-based replication is used
• tools for converting row-based replication events into a format I can read
  
• use of SQL tables for slave replication state and making updates transactional
• support for some variations of synchronous replication
• support for making binlog offsets usable on multiple servers
• support for fast failover
  

When you manage a large number of database servers you need tools that scale. You won't be able to tune individual servers, because you want to have many more servers than DBAs. You will need to debug the occasional problem, and this usually happens on servers that you otherwise ignore. This means that you need to extract and archive the values of performance counters so that you can look at them long after the problem has occurred. It is even better when you can graph and query this data. And it best when you can create alerts on changes in the values of the counters. MONyog provides some of this. It is probably able to provide all of this. This is great news.

Synchronous replication for MySQL is here, well almost here. Solid has announced a product that will be available in 2007 to provide this. I anxiously await the details. And now, source code is available for an implementation of semi-synchronous replication that works with MySQL 4.0.26 and InnoDB. The implementation is reasonably generic and can work with other storage engines.

What is semi-synchronous replication?

You can attend this talk to find out. Semi-sync replication blocks return from commit on a master until at least one slave has acknowledged receipt of all replication events for that transaction. It was relatively easy to extend MySQL to support this, and the changes are well isolated. Semi-sync replication also co-exists with the current replication (async) code in MySQL. Some slaves can use semi-sync while other slaves use async.

Why is semi-sync replication important?

MySQL replication is asynchronous. When a master fails, the most recent transactions can be lost because they might not have been copied to a slave. MySQL Cluster make this type of loss much less likely, but some applications work best with InnoDB. Semi-sync replication decreases the chance that transactions are lost when a master fails.

Check out this code. It is a patch for MySQL 4.0.26 that includes support for semi-synchronous replication with InnoDB. It also contains many other features.

And now even more choices. Solid will support several variations of replication with their storage engine. This appears to be replication native to Solid, rather than MySQL replication as it support synchronous replication and two variations of semi-synchronous replication (acknowledge the transaction when the replica buffers it or when the replica applies but has yet to commit it).

I am very interested in this project, but I am not fond of the name. The project provides tools do automate failover from the failed master to the new master. This is extremely useful. But the name makes me think it is a utility for managing a multi-master MySQL deployment. I don't have much use for that -- how do you maintain database consistency when updates occur on multiple masters?

I am speaking at the MySQL Conference about the benefits of using MySQL for large scale deployments of database clusters.

Want to know how to minimize the cost of managing your database servers? Underutilize them. Servers with spare capacity are much better at tolerating surges from misbehaving applications and the occasional slow query from a bad query plan.

Which features matter in an RDBMS? Given a choice between a single-node performance, availability and manageability, which two would you choose? Assume you have far more database servers than people to manage them. Availability and manageability trump single-node performance because they enable it. Read capacity is increased by adding more replicas. Write capacity is increased by sharding the data across several servers. But you don't want to add more servers unless they are easy to manage, likely to keep running and affordable.

MySQL embodies this philosophy. Clusters of MySQL database servers are easy to deploy and manage. They scale to support extreme transaction and query processing workloads. MySQL doesn't have the features that optimize single-instance performance. That is good. Those features are complex and expensive. Use more servers rather than bigger servers.