MySQL is an easy database to get running initially but it can be tricky to run in demanding environments. High Availability MySQL Cookbook is a relatively thin book packed with information.

The first four chapters are on the care and feeding of a MySQL 7.x Cluster. It starts simply with an initial configuration, covers backups & replication, covers user defined partitioning, and covers troubleshooting node failures. The examples are short, to the point, and devoid of any unneeded filler.

Next come chapters on replication including the very tricky Multi Master Replication. Used shared storage is covered in a separate chapter from DRBD. The book finishes strongly with performance tuning.

The good points -- the book is concise and not padded with filler. The bad is that it is too short. Considering how well the …

I’m happy to announce that the MySQL community has been given the opportunity to speak at the upcoming Oracle Developer Tools User Group (ODTUG) Kaleidoscope conference in Washington DC. We will be releasing more details this week of the MySQL presentations and topics and we are finalizing details of possible options to include the local MySQL community during the event.

The various independent Oracle User Groups in North America that embody “by the community and for the community” have been very positive with including the MySQL community. With the Sun/MySQL now Oracle community team of Giuseppe Maxia, Lenz Grimmer, Kaj Arnö and Oracle ACE Directors Sheeri K Cabral and myself we have been happy with the openness and willingness to include us in the larger Oracle ecosystem.

We’ll announce the schedule when we finalize it, but we have had a great response from …

I don't usually post these simple tricks, but it came to my attention today and it's very simple and have seen issues when trying to get around it. This one tries to solve the question: How do I restore my production backup to a different schema? It looks obvious, but I haven't seen many people thinking about it.

Most of the time backups using mysqldump will include the following line:

USE `schema`;
This is OK when you're trying to either (re)build a slave or restore a production database. But what about restoring it to a test server in a different schema?

The actual trick
Using vi (or similar) editors to edit the line will most likely result in the editor trying to load the whole backup file into memory, which might cause paging or even crash the server if the backup is big enough (I've seen it happen). Using sed (or similar) might take some time with a big …

I think the opportunity has passed for the Twitter API to become a lingua franca for the real-time web. WordPress.com, Tumblr, Typepad, SocialCast, and Status.net all added support for the API in a way to make it as easy as possible for Twitter client developers — all they had to do was change the endpoint. The clients would then become a hub for users across different services, and had the ability to flourish regardless of the direction of the service they originally built on.

However because of perceived lack of market or a rush trying to keep up with …

I always knew RBR and unindexed tables didn't play along very well, but never realized just how much you can distress a slave can in some cases.
Consider this statement (yeah yeah, i know :)
mysql> delete from t1 order by rand(); Query OK, 78130 rows affected (2.61 sec)
t1 has no indexes and is an int field with numbers from 1 to 78130. However, this will cause the slave to re-read entire table for each row deleted! Here it's still running, causing 100% cpu usage:
---TRANSACTION 0 1799, ACTIVE 2390 sec, OS thread id 3672 fetching rows mysql tables in use 1, locked 1 153 lock struct(s), heap size 30704, 78281 row lock(s), undo log entries 35423
Number of rows inserted 78130, updated 0, deleted 35423, read 1076560253 0.00 inserts/s, 0.00 updates/s, 17.58 deletes/s, 367099.91 reads/s
Over a billion row reads 40 minutes later and it's not even half done yet.For a large table this could take weeks or years to …

Huge performance improvement was came with MySQL 5.5.4, and looking on the following picture it's very easy to see why:

It's a one hour Read+Write dbSTRESS workload with 32 sessions running non-stop on 16 cores server. The left part of the picture corresponds to MySQL 5.1 activity, and the right one to MySQL 5.5.4. The first graph represents TPS levels, and the second one - the mutex waits observed during each test.

Even without going in details you may see how dramatically were reduced mutex waits! And I would say one of the greatest InnoDB features was a compact presentation of the "show innodb mutex" output which gave the way to monitor InnoDB more in depth and understand its contentions on each workload! - it's still not perfect, but anyway a big step ahead :-) and helped a lot to improve 5.5.4.

From the mutex waits graph you may see that the most hot "visible" contention now is on the index mutex (well, the …

Without a doubt, it was the statistic that surprised me the most in the surveys I did at MySQL. In short, MySQL is huge on the Windows platform. In terms of downloads, no other platform comes close to Windows, even when you total up all the Linux variants. And when I specifically asked on our surveys what production O/S platform is used for MySQL, Windows was #2 for MySQL Enterprise customers, with RHEL being number one, and (very surprising!) #1 for Community users. Moreover, when asked what...

Last week I was struggling to find an easy way to simulate a troubled Data Node (ndbd process) using MySQL Cluster. It's as simple as pancackes: using the kill command!

To freeze a process you just need to kill the process using the SIGSTOP signal. To let the processes continue, use SIGCONT. Here's an example shell script showing how you would use these two signals on a data node:

# 2010-05-03 08:11:46 [ndbd] INFO     -- Angel pid: 542 ndb pid: 543
 NDBDPID=`grep 'Angel pid' ndb_3_out.log | tail -n1 | awk '{ print $11 }'`
 kill -STOP $NDBDPID
 sleep 10
 kill -CONT $NDBDPID

I'm using the out-log because the file ndb_3.pid contains only the PID of the Angel process. The sleep command is …

Just in case you haven’t noticed, Ubuntu 10.04 “Lucid Lynx” has arrived. I upgraded my personal box over the weekend, and am happy to report that things went pretty flawlessly for me. So I’m now a happy lucid user.

What’s more important for the ever-growing cluster community is the fact that this LTS release comes with built-in support for the Pacemaker cluster stack — and indeed, it’s the first commercially-supported distribution that comes with support for both the Heartbeat and the Corosync cluster messaging layer. And it’s easy, too!

Here’s how I install the Pacemaker stack with Heartbeat (which I prefer) on an Ubuntu box:

aptitude …

Cassandra is one of the most interesting NoSQL platforms at the moment.  And by most interesting what I really mean is the most clearly justifiable.  Some NoSQL platforms offer new data models, improved query interfaces and/or good single node performance through relaxed consistency models.  As a database guy however, the justification for throwing out the RDBMS baby and bathwater is still difficult at this point as NoSQL platforms tend to be highly focused in one aspect of data management, and very immature in all other areas.  Cassandra is somewhat different as it is more mature in a number of key areas (albeit still immature in others).  Areas that can make Cassandra more justifiable for the right project, when compared with a more traditional RDBMS based solution.  This is because Cassandra’s primary capabilities can’t easily be replicated on those …