As described in the prior post, the shared-disk performance dilemma is simple:

1. If each node stores/processes data in memory, versus disk, it is much faster.
2. Each node must expose the most recent data to the other nodes, so those other nodes are not using old data.

In other words, #1 above says flush data to disk VERY INFREQUENTLY for better performance, while #2 says flush everything to disk IMMEDIATELY for data consistency.

Oracle recognized this dilemma when they built Oracle Parallel Server (OPS), the precursor to Oracle Real Application Cluster (RAC). In order to address the problem, Oracle developed Cache Fusion.

Cache fusion is a peer-based shared cache. Each node works with a certain set of data in its local cache, until another node needs that data. When one node …

So you thought that just because MySQL Cluster 7.1 is GA on Windows that NDB API was available and you could just download the MySQL Cluster 7.1 binary for Windows and start hacking the NDB API. Nope. But fear not, there is help!

And before I go on with that: For all you *ix users here, who claim that no one in their right mind would use MySQL Cluster on Windows, let me tell you that you may well be right! But, and this may be a not so PC statement: Although I like and use *ix (I've actually been using *ix for close to 30 years) as a server platform, when it comes to the desktop, I think Windows still has the edge. There is just too many options on, say Linux, too little coherence and too little commonality between applications. And it sure is getting better, very much so, but there is still some way to go.

So then, when we have my reasons for me doing what I am currently doing behind our backs, what I am trying to …

InnoDB is known to have crash-recovery capabilities and thus is called a crash safe storage engine (in contrary to MyISAM). Never the less under certain circumstances it seems like InnoDB pages can get corrupt during a crash and then a manual crash-recovery is needed.

Oracle/MySQL blames in such cases the Operating System, the I/O system or the hardware. What we have seen is that such incidents occur more often on Windows systems and when people are running their databases in a virtualized environment (VMWare). Because of the small number of cases we are aware of, this may not be representative.

One of our customers did systematic crash tests with InnoDB in a virtual machine and got the problem below (MySQL error log):

InnoDB: Log scan progressed past the checkpoint lsn 0 548857890
InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: …

On Wednesday 25 August, Johan Anderson and Mat keep will be presenting a free webinar on the best practices for deploying MySQL Cluster. If you’d like to attend then just register here.

Johan has years of MySQL Cluster professional services experience – and so if you’re interested in deploying a product ready MySQL Cluster database then this is a must-view session. Even if you can’t make it live, register anyway and you’ll be sent a link to the recording and charts afterwards.

Content

An invaluable session for those who are about to, or who already have, deployed MySQL Cluster. Delivered by the lead MySQL Cluster professional services consultant in Oracle, this session will present best practices on deploying MySQL Cluster in order to accelerate time to service with the highest levels of availability …

This tip is several months late but here goes anyway.

In my opinion, when you have an environment where you're using multiple MySQL storage engines (using the right tool for the job and all that jazz), there is nothing more annoying than to have tables created or altered into a storage engine that you did not request. The default behaviour of MySQL is to use the default engine if the requested

You take a look at someone’s MySQL (or MariaDB) data directory, and see

mysql
foo
bar -> foo

1. What’s the issue? Identify pattern.
2. What does it mean?  Consequences.
3. Is there any way it can be safe and useful/usable? Describe.

Good luck!

OUTER JOIN queries in SQL are susceptible to two very subtle bugs that I’ve observed a number of times in the real world. Daniel and I have been hammering out ways to automatically detect queries that suffer from these bugs, in a relatively new Maatkit tool called mk-query-advisor. It’s part of our series of advisor tools for MySQL. I wrote a blog post about it a while ago. Automated analysis of bad query patterns is a good thing to write tools to do, because catching buggy queries is hard work if you do it manually.

mysql > start review;
Query OK, 0 rows affected (0.00 sec)

mysql > Being a MySQL DBA, not a developer, I was mostly interested in the MySQL sections but given I have a general interest in scripting I did go through some of the PHP sections. To be honest they were quite advanced for my php knowledge and experience.

The good thing about the book is that even though it assumes you have the basic knowledge, it still provides an introductory background on most of the two (PHP and MySQL) topics. This is not a “PHP and MySQL for dummies” so don’t expect to learn the very basics nor will you become an expert in either topic by just reading the book. Even if you read the book thoroughly, becoming an expert requires years of hard work and experience. Having said that, his book is a good guide to make it there.

If you are at least a basic developer, this book will help you increase your knowledge drastically and …

Recently, Jobin took up the mantle of starting to poke at Windows support for Drizzle. We decided that step 1 is getting libdrizzle building on Windows - not to mention we could solve a few systemic "how to go about it" problems on a much simpler codebase.

We decided to go the mingw route - for two reasons.

1. on windows we wouldn't have to solve the build problem yet
2. on linux we can cross-compile, and then I can spend less time connecting to a windows machine 

I'm happy to say that the branch implementing support for building under mingw has been merged in to trunk - so please go hammer at it and tell me that I'm an idiot.

If you're on linux and want to play, you'll need mingw32 and mingw32-pthread. You may notice that mingw-pthread isn't in the …

A couple of weeks ago one of my colleagues and I worked on a data corruption case that reminded me that sometimes people make unsafe assumptions without knowing it. This one involved SAN snapshotting that was unsafe.

In a nutshell, the client used SAN block-level replication to maintain a standby/failover MySQL system, and there was a failover that didn't work; both the primary and fallback machine had identically corrupted data files. After running fsck on the replica, the InnoDB data files were entirely deleted.

When we arrived on the scene, there was a data directory with an 800+ GB data file, which we determined had been restored from a SAN snapshot. Accessing this file caused a number of errors, including warnings about accessing data outside of the partition boundaries. We were eventually able to coax the filesystem into truncating the data file back to a size that didn't contain invalid pointers and could be read without …