Gcache and IST

The Gcache is a memory-based cache of recent Galera transactions that is local to each node in a cluster.  If a node leaves and rejoins the cluster, it can use the gcache from another node that stayed in the cluster (i.e., its donor node) to fetch the transactions it missed (IST) as opposed to doing a full state snapshot transfer (SST).  However, there are a few nuances that are not obvious to the beginner:

• The Gcache is lost when a node restarts
• The Gcache is fixed size and implemented as a LRU.  Once it is full, older transactions roll off.
• Donor selection is made irregardless of the gcache state
• If the given donor for a restarting node doesn’t have all transactions needed, a full SST (read: full backup) is done instead
• Until recent developments, there was no way to tell what, precisely, was in the Gcache.

So, with (somewhat) …

The Open Source movement is full of myths, there are different myths from inside the movement (i.e. those who live and breath Open Source or at least thinks it's a good thing) and outside (i.e. those who do not think Open Source really is a good idea to those who thinks Open Source is the work of the devil in a bad way (as I think some people think that something that is the work of the devil has to be a good thing. And they have a point, but let's not get into that right now).

I want to cover some of those myths here and how I look at them here. Also, I am aware that I might be stomping on one or another sensitive Open Source toe here.
Open Source software creates better codeNo, I don't think this is so. But I do think that using an Open Source model properly can potentially help us create better code. But this is not same as saying that OSS always means better code. I am old enough to have been a developer when C was a "hot" …

MaxScale is the new proxy server from the SkySQL/MariaDB team. It provides Connection Load Balancing (CLB) and Statement Load Balancing (SLB) out of the box. This post is a [relatively] quick “how to” install, configure and test SLB with the read/write splitting module.

Step 1 - Server preparationIf you do not have many HW resources, you may run everything on a single Linux instance, but the best way to test MaxScale is to use at least 4 servers: one for MaxScale and for the client apps, one as Master and two as slaves - so, 4 in total. In this post I am going a bit further, I will use 5 servers:
Max 0 - For client apps (192.168.56.20)
Max 1 - The master server (192.168.56.21)
Max 2 - The first slave (192.168.56.22)
Max 3 - The second slave (192.168.56.23)
Max 4 - The third slave (192.168.56.24)
Max 6 - The MaxScale server (192.168.56.26)

In order to do proper tests …

I built a new image on VMWare Fusion for my class, which required installing MySQL 5.6 on Fedora, Version 20. If you don’t know how to add your user to the sudoers list, you should check this older and recently updated blog post.

1. Download the MySQL Yum Repository and launch the downloaded RPM.

1. Install MySQL on Fedora, Version 20, which you can find with the following command:

shell> rpm -qa | grep mysql
mysql-community-release-fc20-5.noarch

The fc20-5 changes with point releases, but assuming that you’re installing the fc20-5 release:

      shell> sudo yum localinstall …

Today we had an interesting situation where the same query was executed significantly slower when it was written with GROUP BY instead of DISTINCT and I saw many people still had the assumption that these two types of queries are actually equivalent which is simply not true. Although DISTINCT queries can be implemented using GROUP BY but not every GROUP BY query can be translated to DISTINCT. Depending on the brand and the optimizer the database server may actually use group by internally for the execution of distinct but that won’t make them equivalent. Let’s see why…

GROUP BY as the name suggest groups the result by some set of parameters and evaluate the whole result set. In most databases group by is implemented based on sorting and the same rules applies to it as well.

DISTINCT will make sure that the same row won’t be returned in the result set twice. Distinct doesn’t necessary …

New Continuent Tungsten Replicator 2.2 is now available for download at www.continuent.com/software and http://code.google.com/p/tungsten-replicator/downloads/list.Tungsten Replicator is a high performance, open source, data replication engine for MySQL and Oracle, released under a GPL V2 license. Tungsten Replicator has all the features you expect from enterprise-class data replication products

In the last 10 years I have worked a lot with replication systems, and I have developed a keen interest in the topic of multiple masters in a single cluster. My interest has a two distinct origins:

• On one hand, I have interacted countless times with users who want to use a replication system as a drop-in replacement for a single server. In many cases, especially when users are dealing with applications that are not much flexible or modular, this means that the replication system must have several points of data entry, and such points must work independently and in symbiosis with the rest of the nodes.
• On the other hand, I am a technology lover (look it up in the dictionary: it is spelled geek), and as such I get my curiosity stirred whenever I discover a new possibility of implementing multi-master systems.

The double nature of this professional curiosity makes me sometimes forget that the …

MySQL and Scaling-up (using more powerful hardware) was always a hot topic. Originally MySQL did not scale well with multiple CPUs; there were times when InnoDB performed poorer with more  CPU cores than with less CPU cores. MySQL 5.6 can scale significantly better; however there is still 1 big limitation: 1 SQL query will eventually use only 1 CPU core (no parallelism). Here is what I mean by that: let’s say we have a complex query which will need to scan million of rows and may need to create a temporary table; in this case MySQL will not be able to scan the table in multiple threads (even with partitioning) so the single query will not be faster on the more powerful server. On the contrary, a server with more slower CPUs will show worse performance than the server with less (but faster) CPUs.

To address this issue we can use a parallel …

I’m writing this blog as 2013 draws to an end. Like many people do at this time of year, I want to take a moment to reflect on the year gone by and to plan for the year ahead. For some, this might be a time to think about a new job or to hire a new team member/employee.

As I reflect, one of the questions that I have been asked repeatedly throughout the year is, “When looking or hiring a MySQL DBA, what are the best questions to ask during an interview?” Over my professional career, I have been on both sides of this question as a potential candidate and as an interviewer.

read more

This week we discuss administering Percona Replication Manager. Ear Candy is about repository packages from MariaDB and Oracle, and At the Movies presents a Riak engineer talking about "convergent replicated data types" - data structures that tolerate eventual consistency.

Events
DB Hangops - every other Wednesay at noon Pacific time

FOSDEM 2014 - Sat February 1 - Sun February 2 in Brussels, Belgium.
Upcoming MySQL events

The call for papers for OSCon 2014 is open until Thursday, January 30th

read more