There are some variables that can affect the replication behavior and sometimes cause some big troubles. In this post I’m going to talk about read_buffer_size and how this variable together with max_allowed_packet can break your replication.

The setup is a master-master replication with the following values:

max_allowed_packet = 32M
read_buffer_size = 100M

To break the replication I’m going to load the 4 million rows with LOAD DATA INFILE:

MasterA (test) > LOAD DATA INFILE '/tmp/data' INTO TABLE t;
Query OK, 4510080 rows affected (26.89 sec)

After some time the SHOW SLAVE STATUS on MasterA gives us this output:

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'mysql-bin.000002' at 74416925, …

Thanks to all who attended my webinar today. The session was recorded and will be available to watch for free soon here.

There were a lot of great questions asked during the session, so I’d like to take this opportunity to try to answer a few of them:

Q: Is there an easy way to leverage the xtrabackup SST and IST in an xtradb cluster to take your full and incremental backups of the cluster’s databases?

Well, if you noticed, all the current SST methods are just commonly used backup tools, and the SST is, in reality, just doing a full backup. So I’m not sure there’s much to leverage with SST.

The straight-forward to way to backup XtraDB Cluster (IMHO) would to simply do run your backup of choice on a node and optionally keep the node out …

The South East LinuxFest is this weekend (June 7-9) in Charlotte, North Carolina and SkySQL will be attending the conference both as a sponsor and with talks. 

read more

Executive Summary

Fast indexing requires the leaves of a Fractal Tree® Index to be big. But some queries require the leaves to be small in order to get any reasonable performance. Basements nodes are our way to achieve these conflicting goals, and here I’ll explain how.

Big Leaves

On many occasions, we at Tokutek have pointed out that TokuDB is write optimized, which means TokuDB indexes data much faster than a B-tree solution such as InnoDB. As with any write-optimized data structure, Fractal Tree indexes need to bundle up lots of small writes into a few big writes. Otherwise, there’d be no way to beat a B-tree. So the question is, how big do the writes have to be?

Consider how long it takes to write k bytes to a disk. First, there is the seek time s, which we can assume to be independent of k. Next, once we’ve moved the disk head somewhere, we need to write the bytes, which …

Many do not need any further introduction to this Monty tradition at MySQL events. For the New York Effective MySQL Meetup group this was a new experience for many that I had the opportunity to share at our recent meeting. In 12 months the group has grown to over 280 members, and now recent attendees have experienced black vodka first hand.

A special thanks to Monty Program AB and Colin Charles for providing the alcohol.

Once MySQL is deployed inside a datacenter environment (i.e. forms a cloud ;-), major feature in it becomes replication. It is used to maintain hot copies, standby copies, read-only copies, invalidate external systems, replicate to external systems, etc. If this functionality is broken, datacenter is broken – components are not synchronized anymore, invalidations not done, data not consistent.

From performance perspective, replication not working properly results in unusable slaves so load cannot be spread. This results in higher load on other machines, including master (especially on master, if environment needs stronger consistency guarantees).

Judging on replication importance in MySQL deployments, it should attract performance engineering as much as InnoDB and other critical pieces. Though slave replication performance is being increased in 5.6, master side is not (well, group commit may help a bit, but not as much).

…

As many of you may know the 2012 UEFA European Football Championship is just around the corner1. For the occasion I developed a web-based betting game to introduce a little bit of competition between me and my friends as well as make the Championships even more exciting. But then I thought “Hey, why not to do this for a bigger community as well”? So dear MySQLers, I invite you to join the special edition of my game here at dba square!

The game is available at http://www.dbasquare.com/euro2012/ or you can simply get there from the blog’s main menu at the top of each page.

We are offering you:

• a little bit of fun
• a free gambling opportunity
• some friendly competition

The game is simple and works similarly to the real sports bookmaking, although there is …

Some time ago I finally had the chance to test the built-in load balancing feature in MySQL's JDBC driver together with a 3 node Galera cluster. I have used this feature at a MySQL Cluster customer many years ago, so I knew it worked and I knew it was great, but I didn't know if it would work with Galera. Galera sometimes returns some error states that are different from what MySQL Cluster does and the main point of the test was to see how the loadbalancing in the JDBC driver reacts to that.

read more

Today was a great day. The MySQL Innovation Day had a great turn out. The  room was packed and numerous others watching the webcast. Many thanks to the MySQL community for coming out to support this event. 

Here is a few quick highlights from MySQL Innovation Day:

The GPL Community version of the MySQL server will have:

    -- MySQL Replication has a lot of different enhancements, the most ever in a release.
         -- High Availability and Fail-over
            -- Global Transaction Ids
            -- Replication Administration and Fail-over Utilities
         -- Better Data Integrity
           -- Crash-Safe …

Back in 2006 we became aware of problems storing large numbers of rows in a single table in cluster. Johan Andersson and Yves Trudeau have each blogged about the problem and the common workaround here and here.  We've since then done some cleanup to provide a more proper "Table is full" error message when running into this problem.

As explained in the referenced blog posts, the problem is the result of a limitation on the size of the hash index of each partition. The hash index for each partition would allow at most ~49 million records. By default an ndbd or ndbmtd node have only 1 local query handler (LQH) block and thus 1 partition per node.  The ndbmtd nodes having MaxNoOfExecutionThreads = 4 or 8 cluster will have 2 or 4 LQH per node respectively. So, the …