October 31, 2014 By Severalnines

When running a MariaDB Cluster or Percona XtraDB Cluster, it is common to use a load balancer to distribute client requests across multiple database nodes. Load balancing SQL requests aims to optimize the usage of the database nodes, maximize throughput, minimize response times and avoid overload of the Galera nodes. 

In this blog post, we’ll take a look at four different open source load balancers, and do a quick benchmark to compare performance:

• HAproxy by HAproxy Technologies
• IPVS by Linux Virtual Server Project
• Galera Load Balancer by Codership
• mysqlproxy by Oracle (alpha)

Note that there are other options out there, e.g. MaxScale from the MariaDB team, that we plan to cover in a future post.

…

October 27, 2014 By Severalnines

The term data warehousing often brings to mind things like large complex projects, big businesses, proprietary hardware and expensive software licenses. With Hadoop came open source data analysis software that ran on commodity hardware, this helped address at least some of the cost aspects. We had previously blogged about MongoDB and MySQL to Hadoop. But setting up and maintaining a Hadoop infrastructure might still be out of reach for small businesses or small projects with limited budgets. Well, perhaps then you might want to have a look at Redshift.

…

HAProxy is frequently used as a load-balancer in front of a Galera cluster. While diagnosing an issue with HAProxy configuration, I realized that logging doesn’t work out of the box on CentOS 6.5. Here is a simple recipe to fix the issue.

If you look at the top of /etc/haproxy/haproxy.cfg, you will see something like:

global
    log         127.0.0.1 local2
[...]

This means that HAProxy will send its messages to rsyslog on 127.0.0.1. But by default, rsyslog doesn’t listen on any address, hence the issue.

Let’s edit /etc/rsyslog.conf and uncomment these lines:

$ModLoad imudp
$UDPServerRun 514

This will make rsyslog listen on UDP port 514 for all IP addresses. Optionally you can limit to 127.0.0.1 by adding:

$UDPServerAddress …

September 25, 2014 By Severalnines

Coming up with a backup strategy that does not affect database performance or lock your tables can be tricky. How do you backup your production database cluster without affecting your applications? Should you use mysqldump or Percona Xtrabackup? When should you use incremental backups? Where do you store the backups? In this blog post, we will cover some of the common backup methods for Galera Cluster for MySQL/MariaDB, and how you can get the most out of these. 

Backup Method

There are various ways to backup your Galera Cluster data:

• xtrabackup (full physical backup)
• xtrabackup (incremental physical backup)
• mysqldump (logical backup)
• binary logging 
• replication slave

Xtrabackup (full backup)

Xtrabackup is an open-source MySQL hot …

September 18, 2014 By Severalnines

Galera Cluster is a popular choice for achieving high availability using synchronous replication. Though if you are planning to run huge sites with many DB objects (tables), a few tweaks are necessary. 

Yes, you might have been successful in loading your 1000s of databases and 1000s of tables, but what happens if you have a node failure and Galera recovery fails?

In this blog post we will show you how to determine one common error related to the open_files_limit that MySQL imposes, and also to spot another potential pitfall.

Open_files_limit

If you are using wsrep_sst_method=xtrabackup or wsrep_sst_method=xtrabackup-v2 then you will find a log file in the data directory of the donor node. This log file is called innobackup.backup.log.

140912 19:10:15  innobackupex: Done. …

September 10, 2014 By Severalnines

If you are automating your infrastructure using Puppet, then this blog is for you. We are glad to announce the availability of a Puppet module for ClusterControl. For those using Chef, we already published Chef cookbooks for Galera Cluster and ClusterControl some time back.  

ClusterControl on Puppet Forge

The ClusterControl module initial release is available on Puppet Forge, installing the module is as easy as:

$ puppet module install severalnines-clustercontrol

If you haven’t change the default module path, this module will be installed under /etc/puppet/modules/clustercontrol on …

September 9, 2014 By Severalnines

We are excited to announce the availability of YUM/APT repositories for ClusterControl, making new releases of ClusterControl  easily accessible using YUM or APT package managers. The repo is found at http://repo.severalnines.com, with instructions provided on the landing page. Our Cluster Configurators will be using these repositories. As a result, users upgrading from s9s_upgrade_cmon starting from version v.1.2.8 will be configured with the package repository.

ClusterControl requires extra post-installation setup steps, such as generating an API token, configuring cmon/dcps database schema, grant privileges on cmon schema, setting up SSL and so on. We provide a post-installation script for this purpose at [Apache document …

Daniel was tracking down what appeared to be a networking problem….

• server reported 113 (No route to host)
• However, an strace did not reveal the networking stack ever returning that.
• On the other side, IP packets were actually received.
• When confronted with mysteries like this, I get suspicious – mainly of (fellow) programmers.
• I suggested a grep through the source code, which revealed  return -EHOSTUNREACH;
• Mystery solved, which allowed us to find what was actually going on.

Lessons:

1. Don’t just believe or presume the supposed origin of an error.
2. Programmers often take shortcuts that cause grief later. I fully appreciate how the above code came about, but I still think it was wrong. Mapping a “similar” situation onto an existing …

Join me online next week (September 10 at 10 a.m. PDT) for my live webinar on Migrating to Percona XtraDB Cluster.  This was a popular webinar that I gave a few years ago, so I’m doing it again with updates for Percona XtraDB Cluster 5.6 (PXC) and all the latest in the Galera world.

This webinar will be really good for people interested in getting an overview of what PXC/Galera is, what it would take to adopt it for your application, and some of the differences and challenges it brings compared with a conventional MySQL Master/slave setup.  I’d highly suggest attending if you are considering Galera in your environment …

September 3, 2014 By Severalnines

Running your database cluster on AWS is a great way to adapt to changing workloads by adding/removing instances, or by scaling up/down each instance. At Severalnines, we talk much more about scale-out than scale up, but there are cases where you might want to scale up an instance instead of scaling out. 

In this post, we’ll show you how to change instance sizes with respect to RAM, CPU and IOPS, and how to tune your Galera nodes accordingly. Moreover, this post assumes that instances are launched using Amazon VPC.

When do we need to upgrade an instance?

You typically need to upgrade an instance when you run out of server resources. This includes CPU, RAM, storage capacity, disk throughput and bandwidth. You must allow enough headroom for …