Isolation is an important part of ACID properties that guarantee that transactions are processed in a reliable manner. But there are four different levels of isolation available and you have to understand each one of them to be able to select the correct one for your needs. This post intends on explaining the four levels together with their effects on locking and performance.

I tried to come up with a number of topics for this post, but none seemed to really convey what I really feel.. And really this blog is about all of them..

• A look at PERFORMANCE_SCHEMA.STAGES and PERFORMANCE_SCHEMA.STATEMENTS
• Graphing MySQL Statement Execution
• A MySQL visual EXPLAIN plan with PERFORMANCE_SCHEMA
• Tracing Sessions with PERFORMANCE_SCHEMA

If that doesn’t whet your appetite (and trust me, I need to, this post is long, but I feel is worth reading all the way to the end), then let me start out by asking the question:

Wouldn’t you like to be able to trace what a SQL statement did, either in the same or another session, on a production instance, after the fact? Wouldn’t you like to know where all of it’s time was spent, and some statistics on what it was doing specifically?

I know, …

Follow it over on launchpad.

After having fixed an incredibly odd compiler warning (and with -Werror that we build with, error) on OSX (die die die) – xtrabackup for Drizzle is ready to be merged. This will bring it into our next milestone: freemont. Over the next few weeks you should see some good tests merged in for backup and restore too.

While not final final, I’m thinking that the installed binary name will be drizzlebackup.innobase. A simple naming scheme for various backup tools that are Drizzle specific. This casually pre-empts a drizzlebackup tool that can co-ordinate all of these (like the innobackupex script).

Backups are an integral and very important part of any system. Backups allow you to recover your data and be up and running again, in the advent of problems such as system crashes, hardware failures or users deleting data by mistake. I had been evaluating backup solution for a while but to be honest I really wasn't satisfied with the solutions available until I came across XtraBackup and I am loving it since. In this post I intend on showing how to do backups and restores using XtraBackup.

When writing my Dropping ACID: Eating Data in a Web 2.0 Cloud World talk for LCA2011 I came to the realisation that I had forgotten a lot of the things I had worked on in MySQL and MySQL Cluster. So, as a bit of a retrospective as part of the Drizzle7 GA release, I thought I might try and write down a (incomplete) list of the various things I’ve worked on in Drizzle.

I noticed I did a lot of code removal, that’s all fine and dandy but maybe I won’t list all of that… except perhaps my first branch that was merged :)

2008

• First ever branch that was merged: some mysys removal (use POSIX functions instead of wrappers that sometimes have different semantics than their POSIX functions), some removal of NETWARE, build scripts that weren’t helpful (i.e. weren’t what any build team ever used to build a release) and some other dead code removal.
…

I have been doing some performance testing with PBMS and found a few things that were kind of interesting. The main finding was that you start to see performance improvements when data sizes start to reach the 20K level. This was seen when replacing a 20 K varchar field with a longblob column in a PBMS enabled table.

The following graph shows the performance differences for 'select' and 'insert' statements using a PBMS enabled version of InnoDB on an 8 core machine.

 
The test compares the insert and select performance of LongBlob columns with PBMS support against that of varchar and longtext columns when using InnoDB.
The test shows that depending on if your application is more heavily weighted towards Inserts or selects it may be beneficial to replace columns …

In Part 1, and Part 2 of this series, I presented some thoughts on partitioning. I heard some great feedback on why people use partitioning. Here, I present a flow chart that summarizes what I’ve learned. In summary: with TokuDB in the picture there’s almost no reason to use partitioning. Or I should say, there are almost always better (higher performing, more robust, lower maintenance) alternatives to partitioning.

Here goes:

1. Spindle contention? In other words, are you partitioning in order to spread your query work load across many disks? I’ve yet to see a compelling technical case that RAIDing your disks doesn’t do this as well, with much less setup and maintenance.
2. …

innodb_buffer_pool_instances is, perhaps, the single most important tuning parameter in MySQL 5.5. And the default of 1 severely caps MySQL performance on any recent hardware. A more reasonable default is called for, e.g. (innodb_buffer_pool_size in GB + number of CPUs)/2.

This observation is coming from my recent experience of "first run" tuning of MySQL 5.5 on several installations: this is the first thing that was changed on all of them.

Every ones loves hands-on tutorials with code snippets and stuff to establish the knowledge that something can be done. So here is my first one; MySQL Enterprise Backup 3.5. The new and shiny backup solution for MySQL. Our clients, for a long time, are asking for an enterprise ready, stable, safe, quick, easy, feature rich, [...]

SERVER SETTINGS (my.cnf)==========================================================================================================#The MySQL server always must be started with the option ignore_builtin_innodb, as long as you want to use the InnoDB Plugin as a shared library.ignore_builtin_innodb#load the InnoDB Plugin and all Information Schema tables implemented in the InnoDB Plugin when the server starts:plugin-load = innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;
innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=
ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so#To use compression, enable the "file per table" and "file compression"innodb_file_per_table=1
innodb_file_format=barracuda#innodb_buffer_pool_size= 512M (Better performance if increased. suggested value is 50-80% of …