There’s an interesting dynamic going on right now in the DBA world. MySQL’s growth and installed base, as a function of its size three or five years ago, is perhaps five if not ten times larger than it was. In 2002 when Pythian’s MySQL services launched, we took on the platform at the explicit request [...]

In one of the last commits I added a SQL Tokenizer which understands the basic tokens of (My)SQL:

• Strings
• Literals
• Numbers
• Comments
• Operators

With this basic understanding we can normalize Queries and build statistics over similar queries.

The idea is simple and already implemented in mysqldumpslow:

  /* login.php:37 */SELECT * FROM tbl WHERE id = 1243 AND name = "jan"

is turned into

  SELECT * FROM `tbl` WHERE `id` = ? AND `name` = ?

The queries look like prepared statements now and can be used the characterize queries of the same kind.

• comments are removed
• whitespaces are stripped
• literals are quoted
• constants are replaced with ?

Taking the famous world-db and executing some simple queries like:

MySQL Connector/Net 1.0.10.1 a new version of the all-managed .NET driver for MySQL has been released.  This is a bug fix release for the current production branch of Connector/Net.  Version 1.0.10.1 is suitable for use with any MySQL version including MySQL-4.1, MySQL-5.0, MySQL-5.1 beta or the MySQL-6.0 Falcon "Preview".
It is now available in source and binary form from the Connector/Net download pages at http://dev.mysql.com/downloads/connector/net/1.0.html and mirror sites (note that not all mirror sites may be up to date at this point of time - if you can't find this version on some mirror, please try again later or choose another download site.)

You can find the release announcement in our forums with a complete change log here.

I'm attending the Google Test Automation Conference (GTAC 2007) in Manhattan, New York right now. It's a two-day single-track event hosted by Google, with mostly non-Google speakers. It's okay, but not great.

As explained in an earlier blog post, we recently started using MySQL master-slave replication on drupal.org in order to provide the scalability necessary to accommodate our growing demands. With one or more replicas of our database, we can instruct Drupal to distribute or load balance the SQL workload among different database servers.

MySQL's master-slave replication is an asynchronous replication model. Typically, all the mutator queries (like INSERT, UPDATE, DELETE) go to a single master, and the master propagates all updates to the slave servers without synchronization or communication. While the asynchronous nature has its advantages, it is also means that the slaves might be (slightly) out of sync.

Consider the following …

As explained in an earlier blog post, we recently started using MySQL master-slave replication on drupal.org in order to provide the scalability necessary to accommodate our growing demands. With one or more replicas of our database, we can instruct Drupal to distribute or load balance the SQL workload among different database servers.

MySQL's master-slave replication is an asynchronous replication model. Typically, all the mutator queries (like INSERT, UPDATE, DELETE) go to a single master, and the master propagates all updates to the slave servers without synchronization or communication. While the asynchronous nature has its advantages, it is also means that the slaves might be (slightly) out of sync.

Consider the following …

I caught most of the second day of MySQL Camp 2007. It was fun and educational as before. The format was a little different than the last Camp; everything was in one room. Google and Proven Scaling provided food. Sessions were loosely organized, to say the least, but that’s what an un-conference is all about. When I got there, Ronald Bradford was presenting on MySQL Proxy. Bob Stein, creator of Visibone charts and cheat-sheets, followed with a session seeking feedback to improve the charts.

I’ve just released changes to all tools in MySQL Toolkit. The biggest changes are in MySQL Table Sync, which I’m beginning to give sane defaults and options to. Some of the changes are incompatible (but that’s what you get with MySQL Table Sync, which is still very rough). I also found and fixed some bugs with MySQL Visual Explain. Thanks to everyone who submitted bug reports. Note, the formatting overflow in MySQL Query Profiler was not a security vulnerability.

Dear Kettle fan,
Since our code is open, we have to be honest: in the past, the performance of Kettle was less than stellar in the “Text File” department. It’s true that we did offer some workarounds with respect to database loading, but there are cases when people don’t want to touch any database at all. Let’s take a closer look at that specific problem…

Reading and writing text files…
Let’s take a look at this delimited (CSV) file (28MB). Unzipped, the file is around 89MB in size.

Suppose you read this file using version 2.5.1 (soon to be out) with a single “Text File Input” step. On my machine, that process consumes most of the available CPU power it can take and takes around 57 seconds to complete. (1M rows/minute or 60M rows/hour)

When we analyze what’s eating the CPU …

adapt.com uses sharding to scale out. In my talk on moving to MySQL, I mentioned a few design issues we had to become aware of before we rolled out a sharded database environment. I promised to give more details, so here they are…

We wanted some keys to be globally unique (eg, userid and accountid). How would we do this?

auto_increment_offset and auto_increment_increment

We had been using autoincremented values. So we considered using MySQL’s auto_increment_offset and auto_increment_increment to keep autoincrement values unique across shards. (This basically tells each shard to start at a certain number (auto_increment_offset), eg 1 for one shard, 2 for the next, etc, then to skip auto_increment_increment between values. So, eg, if we set that to 1000, our next keys would be 1001 for shard one, 1002 for shard 2, etc. Then 2001 for shard one, 2002 for shard 2, and so on. …