December 23, 2013 By Severalnines

This post is a continuation of our previous post on Online Schema Upgrade in Galera using TOI method. We will now show you how to perform a schema upgrade using the Rolling Schema Upgrade (RSU) method. This requires that the new schema is backward compatible with the old schema, so it requires a bit more planning than the TOI method. In some cases, you might need to turn off any connections to the target node. 

If you are using non-uniform hardware across your Galera cluster, RSU might be preferable as the more predictable process. RSU does not slow down your cluster.

As described in details in the previous …

Once upon a time, a long long time ago, when there was no Solr and lucene used to be a search engine api available for php developers to use, they used to struggle for using lucene. Most people reverted back to Mysql full text search. And some ventured into using sphinx - another free full text search engine. Then came Solr and php developers were thrilled with the ease of use over the http

It is wonderful to see some of my original pet peeves fixed in MySQL 5.7.3! It has not even taken 10 years

I remember when starting my work at MySQL Support that I would recommend using UNION ALL instead of plain UNION because it would not require duplicate elimination, and as such, would not require the creation of a temporary table in basic cases. Guess what? MySQL actually did it anyway. The learning experience from this incident was to never use common sense alone when thinking about MySQL – test and validate your assumptions because for one reason or another things may work differently from what “obviously makes sense.” I do not want to single out MySQL – this approach has helped me to be successful with problem analyses of all kinds of software.

The Multi Column IN optimization is another one that …

The third, and final article in my series on migrating data to and from Hadoop and SQL databases is now available:

Big data is a term that has been used regularly now for almost a decade, and it — along with technologies like NoSQL — are seen as the replacements for the long-successful RDBMS solutions that use SQL. Today, DB2®, Oracle, Microsoft® SQL Server MySQL, and PostgreSQL dominate the SQL space and still make up a considerable proportion of the overall market. In this final article of the series, we will look at more automated solutions for migrating data to and from Hadoop. In the previous articles, we concentrated on methods that take exports or otherwise formatted and extracted data from your SQL source, load that into Hadoop in some way, then process or parse it. But if you want to analyze big data, you probably don’t want to wait while exporting the data. Here, we’re going to look at some methods and tools that enable a …

Several users have reported that certain queries with IN predicates can’t use index scans even though all the columns in the query are indexed. What’s worse, if you reformulate your query without IN, the indexes are used. Let’s take some example query. Suppose we have a table with two indexed columns:CREATE TABLE t1 ( 
  col1 INTEGER,
  col2 INTEGER,
  …   KEY key1( col1, col2 ) ); Let’s take a look at some queries that could take advantage of the key1 index to read rows without accessing the table.

1. SELECT col1, col2 FROM t1 WHERE col1 = 100;
2. SELECT col1, col2 FROM t1 WHERE col1 > 100 AND col1 < 200;
3. SELECT col1, col2 FROM t1 WHERE col1 > 100 AND col1 < 200 OR col1 > 300 AND col1 < 400;
4. SELECT col1, col2 FROM t1 WHERE col1 = 100 AND col2 > 100 AND cold2 < 200;

These queries will use what MySQL …

In the past few days I have been working on a puppet module for galera. It's not done yet, but a work in progress can be found here. One of the benefits of developing a puppet module is that you need to go through the config files thoroughly to determine which settings do what exactly. While doing that, I thought I'd post a blog outlining the most important ones.

I believe that inter-node communication should preferrably take place on an unmetered private network interface if possible. For this article, we assume each machine has a private and a public interface, like so (Debian-style):

root@node1:~# cat /etc/network/interfaces
[..snip..]
# Public interface
auto eth0
iface eth0 inet static
address 5.1.2.3
netmask 255.255.255.248
gateway 5.1.2.254

# Private interface
auto eth1
iface eth1 …

In the past few days I have been working on a puppet module for galera. It's not done yet, but a work in progress can be found here. One of the benefits of developing a puppet module is that you need to go through the config files thoroughly to determine which settings do what exactly. While doing that, I thought I'd post a blog outlining the most important ones.

I believe that inter-node communication should preferrably take place on an unmetered private network interface if possible. For this article, we assume each machine has a private and a public interface, like so (Debian-style):

root@node1:~# cat /etc/network/interfaces
[..snip..]
# Public interface
auto eth0
iface eth0 inet static
address 5.1.2.3
netmask 255.255.255.248
gateway 5.1.2.254

# Private interface
auto eth1
iface eth1 …

Important variables for configuring galera with Percona XtraDB Cluster 5.6 Walter Heck Sat, 12/21/2013 - 23:32

Important variables for configuring galera with Percona XtraDB Cluster 5.6

In the past few days I have been working on a puppet module for galera. It's not done yet, but a work in progress can be found here. One of the benefits of developing a puppet module is that you need to go through the config files thoroughly to determine which settings do what exactly. While doing that, I thought I'd post a blog outlining the most important ones.

I believe that inter-node communication should preferrably take place on an unmetered private network interface if possible. For this article, we assume each machine has a private and a public interface, like so (Debian-style):

root@node1:~# cat /etc/network/interfaces
[..snip..]
# …

Important variables for configuring galera with Percona XtraDB Cluster 5.6 Walter Heck Sat, 12/21/2013 - 23:32

Important variables for configuring galera with Percona XtraDB Cluster 5.6

In the past few days I have been working on a puppet module for galera. It's not done yet, but a work in progress can be found here. One of the benefits of developing a puppet module is that you need to go through the config files thoroughly to determine which settings do what exactly. While doing that, I thought I'd post a blog outlining the most important ones.

I believe that inter-node communication should preferrably take place on an unmetered private network interface if possible. For this article, we assume each machine has a private and a public interface, like so (Debian-style):

root@node1:~# cat /etc/network/interfaces
[..snip..]
# …

Important variables for configuring galera with Percona XtraDB Cluster 5.6 Walter Heck Sat, 12/21/2013 - 23:32

Important variables for configuring galera with Percona XtraDB Cluster 5.6

In the past few days I have been working on a puppet module for galera. It's not done yet, but a work in progress can be found here. One of the benefits of developing a puppet module is that you need to go through the config files thoroughly to determine which settings do what exactly. While doing that, I thought I'd post a blog outlining the most important ones.

I believe that inter-node communication should preferrably take place on an unmetered private network interface if possible. For this article, we assume each machine has a private and a public interface, like so (Debian-style):

root@node1:~# cat /etc/network/interfaces
[..snip..]
# …