We’ve been doing a lot of performance analysis of MySQL and SATA disks over the last few days and I wanted to get some thoughts out in the open to see what you guys thought.

Now that Debian Etch is out we’re seriously looking at making the jump to a full 64bit OS with 8G of memory on each of our boxes.

This also involves benchmarking SATA and potentially migrating away from MyISAM which, while very trusty, is seriously showing its age for us.

First off is raw IO tuning.

XFS benchmarks show about a 20% performance boost from bonnie++ over ext3. This is pretty obvious. Tuning readahead with ‘blockdev’ didn’t seem to help performance much. SATA seems pretty tuned when compared to hdparm and IDE drives.

After fully tuning it seems we can get about 90MBps out or our disks. Not to shabby. The Linux md driver on RAID 0 didn’t seem to boost IO performance much (and I’m pretty disappointed). Even …

This release of the innotop MySQL and InnoDB monitor is a major upgrade in terms of functionality, code quality, and interface consistency. It is the result of me working for over a month to get innotop into shape for the recent MySQL Conference and Expo. Here’s a summary of the changes: Major changes Quick-filters to easily filter any column in any display Compatibility with MySQL 3.23 through 6.0 Improved error handling when a server is down, permissions denied, etc Use additional SHOW INNODB STATUS information in 5.

The MySQL User’s Conference provides very fertile environment for new ideas. For example, last year we came to the conference with a conviction to build a MySQL agent for our Amanda Enterprise backup and recovery software. However, after talking to many MySQL users about their backup needs we got convinced that stand-alone solution optimized for MySQL DBAs was a better way to go, and that is how Zmanda Recovery Manager for MySQL was conceived.

This year we received a lot of insightful feedback and suggestions from MySQL users and our customers. Special thanks to MySQL engineering and product management for providing their perspective. Here are the major takeaways for us, the MySQL backup guys:

• Back up the MySQL server configuration files when you do backup of the database — that will simplify …

I just got off the phone with Carl Skelton from Polytechnic University and we have finalized the dates for the next camp. MySQL Camp II will be held on Thursday and Friday, August 23-24, in Brooklyn, New York. Polytechnic University has graciously offered to host the camp. More details to follow about the new camp website, places to stay around Polytechnic, and much more. But I wanted to give everyone a heads up. If MySQL Camp II is anything like MySQL Camp I, I expect it to be a breeding ground for new hacks, ideas, and of course, beer.

Connector/Net's MySqlDataAdapter class has a method named FillSchema.  This method is intended to populate the metadata for a given query without actually returning any of the data from the query.  The only syntax that MySQL has to help with this is SQL_SELECT_LIMIT which limits the number of rows that are returned for a given select.  Indeed this is what we are using to prevent the transfer of data across the wire when we only want the metadata.

However, there is a problem.  SQL_SELECT_LIMIT does not work in stored procedures and also does not affect inserts, updates, or deletes.  So, the upshot of this is that calling FillSchema on a stored procedure will, in fact, execute the stored procedure and return all of the data to the client.  This is a waste of bandwidth but it's worse than that.  It will also execute all other DML statements in the procedure.

This is different than what happens with …

Yesterday I blogged about the release of our latest Connector/Net product.  Version 5.1, this release includes the addition of a new namespace MySql.Web.  This namespace includes implementations of a Membership and Role provider.  These providers work together with the membership and role systems in ASP.Net 2.0 to provide a simple mechanism for web developers to add authentication and role support to their web properties.

As we were unable to include proper documentation for these new providers before release, I thought it proper to write down a few thoughts on how these work and how to use them.

1. Provide a connection string

The Connector/Net installer will register the providers in the proper sections of the machine.config file.  When registered, they reference a connection string that is non-existent.  To use the providers, you must provide a connection string that points …

It's starting to feel like conference season is in full tilt.  Last week we had the MySQL Conference with over 1,500 people and later this week is the SugarCRM developer conference.  Next week is the Red Hat Summit in San Diego.  I've been to this conference in the past, and it's a great technical event.  Lots of in-depth technical sessions with the gurus from Red Hat, their partners and the community.  The keynotes are also high quality (not commercial pitches) and the parties are great.  The conference is a good deal and includes 3 nights hotels and conference meals.  I hope to be at the conference for some announcements we're making. …

I'm sure I have a lot of cleanup to do... but....


mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=FEDERATED_ODBC DEFAULT CHARSET=latin1 CONNECTION='obdc://Driver=myodbc3;Server=localhost;Database=federated_odbc;Port=5555;socket=/tmp/mysql-5555.sock;Option=3;UID=root:t1' |
…

Alrighty, I'm flying down in the morning and will spend Thursday at the OpenCeBIT conference and exhibition.

While in Sydney, I'll be scouting for potential MySQL support engineers in the APAC region, and signing up more sponsors for the OSDC conference. Just catch me on the ground at OpenCeBIT if you want to chat with me about either (or anything else), I'll be wearing a blue MySQL shirt.

Thursday evening (6.30pm) we're having the OSIA AGM so if you're an OSIA member this is a reminder to be there!