I have been meaning to, for months, get working on the FederatedX storage engine. There are a lot of great features it has -- such as better support for transactions as well as some architectural changes, thanks to the work of Antony Curtis. It now is designed so that the connection is abstracted into a federated_io_ class. This allows you to be able to subclass different connection schemes/drivers. For instance, I have the code for Federated ODBC that I need to take the code from and port to a federated_io_odbc class.

FederatedX has been pulled into an older MariaDB repository (thanks Antony!) that I need to merge with the latest, as well as figure out how to get the test suite to load the plugable storage engine. I've tried to add 'INSTALL PLUGIN federated SONAME 'ha_federatedx.so'' to the test, but the server expects ha_federated.so to be in a particular file location. That needs to be solved. I looked at PBXT's test suite but …

So during preparation of XtraDB template for EC2 I wanted to understand what IO characteristics we can expect from EBS volume ( I am speaking about single volume, not RAID as in my previous post). Yasufumi did some benchmarks and pointed me on interesting behavior, there seems several level of caching on EBS volume.

Let me show you. I did sysbench random read IO benchmark on files with size from 256M to 5GB with step 256M. And, as Morgan pointed me, I previously made first write, to avoid first-write penalty:

dd if=/dev/zero of=/dev/sdk bs=1M

for reference script is:

PLAIN TEXT CODE:

1. #!/bin/sh
2. set -u
3. set -x
…

Do you test your application systematically when you upgrade or reconfigure your database server? You should! Here’s a real (anonymized) story of what happens if you don’t. When we upgraded to 5.0.62 (from 5.0.27 and 5.0.45), our code broke for queries like this: SELECT SUM(amt) FROM daily_amt WHERE day = FROM_UNIXTIME(1222889772); The problem here was a wrong DATE/DATETIME comparison and other bug fixes in MySQL 5.0.62; it was stricter in enforcing the comparison.

I put together the following diagram to explain what the origin of the current MySQL forks and deltas looks like:

But in the distributed revision control world we live in, it's never really that simple. Here are some other notes:

• XtraDB is an InnoDB fork and "mostly" only a storage engine. It has a future in MariaDB and Drizzle, but may not make it into MySQL (about to be owned by Oracle, who also owns InnoDB).
• OurDelta collects more patches than just the Percona patches, and enables the PBXT storage engine. There's also a repository of OurDelta for 5.1 - binaries just aren't out yet.
• Like XtraDB, PBXT has a future in Drizzle, MariaDB and *maybe* also MySQL (Oracle may be more friendly towards PBXT than XtraDB, but Oracle is not known to be friendly so who knows!). I left PBXT absent from the diagram for not having much of a lineage with previous MySQL releases (flames welcome if you disagree).
…

Welcome to the 157th edition of Log Buffer, the weekly, cross-platform review of database blogs.

SQL Server

We start with Michelle Ufford, the SQL Fool, who gives us the poor (wo)man’s graph, a fast and ingenious way to create handsome text-based graphs.

What is the importance of running regular consistency checks? Paul S. Randal returns with some survey results and analysis. He writes, “The results are actually surprising – I didn’t expect so many people to be running consistency …

I’ve been editing a task description in our worklog:
WL#946 TIME/TIMESTAMP/DATETIME with fractional seconds.

This has been around for a long time, and will be around for a long time. But I did think it’s at least time that we should let people know that we think about it. The specific impetus today was a fairly thorough survey of the feature request’s history in a MySQL DBA’s blog posting Once upon a timestamp(milliseconds)….

In my 2008-05-04 blog posting about the roadmap, I included WL#946 in a list with the heading “Let’s not forget that these will fit in somewhere in the 6.x / 7.x period”. There’s a lot to do — I know that’s not clear …

Very often we get the requests from customer to restore a "tiny" table from the nightly backup into the development/staging environment. This request need to be done on urgent basis as its just a matter of a one table and customer don't want to restore entire database of 10GB so the restoration might take less than 5 min - this is how most of the customer/managers thinks and they are abs right as why should it take more time for a single table?. This can be addressed by playing with the privileges and with the help of parameters available to 'mysql' command line utility

Steps to restore single table from dump file

# Create a user & GRANT him all rights on the table which need to be restored
# Start importing as show below

mysql -uRestUser -pSecret --force --one-database DB_NAME < /path/to/dumpfile.sql

ResetUser - "user" created for restoring single table

…

Monty Widenius dissects MySQL’s dual license. Intuit moves to the EPL. And more.

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca
“Tracking the open source news wires, so you don’t have to.”

# Monty Widenius blogged about the apparent changes to the dual licensing of MySQL.

# Intuit announced that its code.intuit.com will be moving from CPL to EPL.

# Matt Asay asked whether Google’s open source advocacy might be a scheme to lower the value of patents.

# Vision Mobile’s Andreas Constantinou explained the differences between open source …

A while ago I wrote about a tool to help make upgrades safer. Since then, we have gotten several people to help sponsor development on this tool, and a few of our customers are using it to help find problems before they upgrade their systems.

I can’t think of a single one of the Maatkit tools that didn’t grow out of the need for deeper insight into some part of the system. This tool is no exception. And as always, these tools are like flashlights. When you crouch down near the floor, and shine your flashlight under the refrigerator, you should expect to find a few things that make you cringe.

The other day, one of our customers was using this tool and we started getting an error. The error was caused by the part of the tool that verifies that result sets are the same. Our thought on how to do this was to checksum the results of a query. You can …

On our road to a MySQL Proxy GA release, there are quite a few things that need attention, one of them being multithreading.

We are actively working on release 0.8, which brings the first stage of multithreading to the table: Enabling worker threads to handle network events in parallel. The code has been available for quite some time now and we’ve started to implement several performance benchmarks to track our progress, catch regressions and deficiencies early on.

Benchmarking is an interesting field, especially since you can screw up so easily :)

To avoid making mistakes and to spend less time reinventing the wheel, we are doing the same as we are doing with the code: …