Control Your Database Infrastructure

Benchmark of Load Balancers for MySQL/MariaDB Galera Cluster

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.


When to Load Balance Galera Requests


Although Galera Cluster does multi-master synchronous replication, you should really read/write on all database nodes provided that you comply with the following:

  • Table you are writing to is not a hotspot table
  • All tables must have an explicit primary key defined
  • All tables must run under InnoDB storage engine
  • Huge writesets must run in batch, for example it is recommended to run 100 times of 1000 row inserts rather than one time of 100000 row inserts
  • Your application can tolerate non-sequential auto-increment values.

If above requirements are met, you can have a pretty safe multi-node write cluster without the need to split the writes on multiple masters (sharding) as  you would need to do in a MySQL Replication setup because of slave lag problems. Furthermore, having load balancers between the application and database layer can be very convenient where load balancers may assume that all nodes are equal and no extra configuration such as read/write splitting and promoting a slave node to a master are required.

Note that if you run into deadlocks with Galera Cluster, you can send all writes to a single node and avoid concurrency issues across nodes. Read requests can still be load balanced across all nodes. 


Load Balancers




HAProxy stands for High Availability Proxy, it is an open source TCP/HTTP-based load balancer and proxying solution. It is commonly used to improve the performance and availability of a service by distributing the workload across multiple servers. Over the years it has become the de-facto open source load balancer, is now shipped with most mainstream Linux distributions.

Data Warehouse in the Cloud - How to Upload MySQL data into Amazon Redshift for reporting and analytics

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.

Now, in case you are running e.g. a Galera Cluster for MySQL, why not dedicate one of the cluster nodes for reporting? This is very doable, but if you’ve got reports generating long running queries, it might be advisable to decouple the reporting load from the live cluster. Having an asynchronous slave might help, but depending on the amount of data to be analyzed, a standard MySQL database might not be good enough. The great news is that Redshift is based on a columnar storage technology that’s designed to tackle big data problems. 

In this blog post, we’re going to show you how to parallel load your MySQL data into Amazon Redshift.


Loading Data to Amazon Redshift


There are several ways to load your data into Amazon Redshift. The COPY command is the most efficient way to load a table, as it can load data in parallel from multiple files and take advantage of the load distribution between nodes in the Redshift cluster. It supports loading data in CSV (or TSV), JSON, character-delimited, and fixed width format.

After your initial data load, if you add, modify, or delete a significant amount of data, you should follow up by running a VACUUM command to reorganize your data and reclaim space after deletes. You should also run an ANALYZE command to update table statistic.

Using individual INSERT statements to populate a table might be prohibitively slow. Alternatively, if your data already exists in other Amazon Redshift database tables, use SELECT INTO ... INSERT or CREATE TABLE AS to improve performance.

Resources for Database Clusters: 9 DevOps Tips, ClusterControl 1.2.8 Release, HAProxy Webinar Replay & More

October 23, 2014
By Severalnines

Check Out Our Latest Technical Resources for MySQL, MariaDB & MongoDB Clusters


Here is a summary of resources & tools that we’ve made available to you in the past weeks. If you have any questions on these, feel free to contact us!


New Technical Webinar

If you are in DevOps, you will know that deploying and managing databases has its challenges! Monitoring, managing schema changes and pushing them in production, performance optimizations, configurations, version upgrades, backups; these are all aspects to consider – preferably before going live!

In this new webinar, we will walk you through 9 key tips to consider before specifically taking Galera Cluster into production. So if you are in devops, then this webinar is for you ;-)

Read more details and register here!


Technical Webinar - Replay

If you missed this webinar with our guest speaker from HAProxyTechnologies (or would just like to view it again), it’s online on demand.

New Webinar: 9 DevOps Tips for Going in Production with Galera Cluster for MySQL - November 11th

October 21, 2014
By Severalnines


Galera is a MySQL replication technology that can simplify the design of a high availability application stack. With a true multi-master MySQL setup, an application can now read and write from any database instance without worrying about master/slave roles, data integrity, slave lag or other drawbacks of asynchronous replication.


And that all sounds great until it’s time to go into production. Throw in a live migration from an existing database setup and devops life just got a bit more interesting ...


So if you are in devops, then this webinar is for you!


Operations is not so much about specific technologies, but about the techniques and tools you use to deploy and manage them. Monitoring, managing schema changes and pushing them in production, performance optimizations, configurations, version upgrades, backups; these are all aspects to consider – preferably before going live.


Let us guide you through 9 key tips to consider before taking Galera Cluster into production. 


New Webinar: 9 DevOps Tips for Going in Production with Galera Cluster for MySQL - November 11th





Tuesday, November 11th at 09:00 GMT (UK) / 10:00 CET (Germany, France, Sweden)

Register Now


North America/LatAm

Tuesday, November 11th at 9:00 Pacific Time (US) / 12:00 Eastern Time (US)

Register Now



Johan Andersson, CTO, Severalnines


Integrating ClusterControl with FreeIPA and Windows Active Directory for Authentication

October 17, 2014
By Severalnines

Integrating ClusterControl with a corporate LDAP directory is a common task for many IT organizations. In an earlier blog, we showed you how to integrate ClusterControl with OpenLDAP. In this post, we will show you how to integrate with FreeIPA and Windows Active Directory. 


How ClusterControl Performs LDAP Authentication


ClusterControl supports up to LDAPv3 protocol based on RFC2307. More details on this in the documentation.


When authenticating, ClusterControl will first bind to the directory tree server (LDAP Host) using the specified Login DN user and password, then it will check if the username you entered exists in the form of uid or cn of the User DN. If it exists, it will then use the username to bind against the LDAP server to check whether it has the configured group as in LDAP Group Name in ClusterControl. If it has, ClusterControl will then map the user to the appropriate ClusterControl role and grant access to the UI.


The following flowchart summarizes the workflow:




FreeIPA is a Red Hat sponsored open source project which aims to provide an easily managed Identity, Policy and Audit (IPA) suite primarily targeted towards networks of Linux and Unix computers. It is easy to install/configure, and is an integrated security information management solution combining Linux (Fedora), 389 Directory Server, MIT Kerberos, NTP, DNS, Dogtag (Certificate System).


Database Automation - Private DBaaS for MySQL, MariaDB and MongoDB with ClusterControl

October 9, 2014
By Severalnines

Installing, configuring, deploying databases and performing repetitive administrative tasks are all part of a DBA’s or sysadmin’s job. This can get pretty repetitive and overwhelming if you are part of a centralized IT team, running multiple databases for your organization’s different departments, or a managed hosting provider responsible for setting up and operating databases for external clients. One way to get out of this ‘manual, repetitive task’ business is through a Database as a Service (DBaaS).

DBaaS is a way of delivering database functionality as a service to one or more consumers. A DBaaS platform would provide automated procedures for database deployment, monitoring, backups, recovery/repair, scaling, security/multi-tenancy, etc. This type of automation is especially useful where agility is needed, e.g. for systems that require elasticity by scaling out or scaling back at short notice, or for temporary deployments associated with dev/test/QA. Now that you’ve automated the repetitive stuff, you can start using your time and skills to optimize your schemas and configurations, help developers write better queries that scale, and work on system architecture or strategic database initiatives.

In this post, we’ll have a look at how enterprise companies or managed hosters can use ClusterControl to implement a DBaaS for MySQL, MariaDB and MongoDB. 


Multitenancy - Organizations, Users, Roles and Clusters 


Some basics first, let’s have a look at how ClusterControl handles multiple users and clusters. ClusterControl has an admin module through which an admin can create users, specify their roles and the organization each user belongs to. 

By default, ClusterControl provides three types of roles:

  • Super Admin - Sees all clusters/DBs that are registered with ClusterControl. The Super Admin can also create organizations and users. Only the Super Admin can transfer a cluster from one organization to another.
  • Admin - Belongs to a specific organization, and sees all clusters registered in that organization.
  • User - Belongs to a specific organization, and only sees the cluster(s) that she registered.

It is also possible to create custom roles with specific access control, for more fine-grained access to functionality. 

As a roundup, here is how the different entities relate to each other:


More information can be found in the ClusterControl User Guide.


Example Organizational Structure


Let’s assume we have an organization with separate departments, each having their own applications and databases. An IT department is responsible for running all the databases, but the Marketing and HR departments also have their own developers who need access to their respective databases.




Access Control


Business analytical system with MongoDB replica set

System Analyst

Full access to assigned cluster


Staging cluster running on MariaDB Galera Cluster

System Administrator

Full access to all clusters

Human Resource

HR system running on standalone MariaDB 10.1

Application Developer

Limited access to assigned cluster (read-only)


ePayment Processing Data over multi-Datacenter MariaDB Cluster - Paytrail chooses ClusterControl

September 26, 2014
By Severalnines

Paytrail is a leading e-payment method provider from Finland, and is expanding globally. Established in 2007, Paytrail currently has over 4,000 business customers, works with over 350 partners and its solution is available in all SEPA (Single Euro Payments Area) countries.


Paytrail offers a new online shopping solution that allows consumers to use one login for all of their online purchases. And it provides everything that is needed for online shopping in addition to traditional payment methods (bank e-payments, credit and debit card payments, invoicing and installments). 


This new case study describes how Paytrail came to work with Severalnines to achieve a fault-tolerant database cluster across two data centers.

"To go from initial evaluation to full production within 4 months would not have been possible without Severalnines”, says Niko Lehtonen. “Their support team was extremely helpful, and we got timely assistance when needed and that allowed us to keep the project moving."


Maintaining Uptime and Supporting Growth


Paytrail have two main concerns when it comes to their systems: maintaining uptime, at all times, and growth. They cannot afford any downtime, as that brings down the merchants who use their solution, which in turn prevents the merchants’ customers to shop online. The company recently made the Deloitte Fast 500 EMEA 2013 list, a ranking of the 500 fastest growing technology companies in EMEA.

mysqldump or Percona XtraBackup? Backup Strategies for MySQL Galera Cluster

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 backup utility from Percona. It is a combination of xtrabackup (built in C) and innobackupex (built on Perl) and can back up data from InnoDB, XtraDB and MyISAM tables. 

Xtrabackup does not lock your database during the backup process. For large databases (100+ GB), it provides much better restoration time as compared to mysqldump. The restoration process involves preparing MySQL data from the backup files before replacing or switching it with the current data directory on the target node. However, the restoration process is not very straightforward. We have covered some backup best practices and an example of how to restore with xtrabackup in this blog post.

ClusterControl allows you to schedule backups using Xtrabackup and mysqldump. It can store the backup files locally on the node where the backup is taken, or the backup files can also be streamed to the controller node and compressed on-the-fly.


PagerDuty Incident Alerting for ClusterControl

September 22, 2014
By Severalnines

Need to add phone and SMS alerting to ClusterControl? ClusterControl 1.2.8 introduces support for PagerDuty, an alerting service for Ops teams to schedule on-calls and add phone and SMS notifications to IT tools. By integrating PagerDuty with ClusterControl, you can start receiving phone, SMS and email notifications for all important database events as monitored by ClusterControl. Alerts go directly to the right person who can solve the issue.

This integration is possible thanks to a new plugin interface, that takes ClusterControl alarms in JSON format and outputs to an external system via plugins. Plugins can be either scripts or executable binaries.


We have built a few example plugins utilizing this plugin interface, available from our Github repository:

  • This plugin forwards the alarm raise/close events to the PagerDuty system
  • This plugin writes the new alarms instantly to the syslog


Plug-in configuration options


Let’s have a quick look at how the plugin works, but feel free to go directly to the PagerDuty setup instructions.

A plugins directory can be set through the CMON configuration file (config file entry is plugin_dir), or in the cmon_configuration table found in CMON DB (PLUGIN_DIR key).


When no value is set, the /var/cmon/plugins path will be used. The controller tries to execute the 'executable' scripts/binaries from the directory, while the non-executable files are skipped.


How does the plugin execute?


Whenever an alarm is raised, CMON feeds the alarm event to its standard input (stdin) and all executable scripts or binaries (plugins) under plugin_dir path will be executed. A JSON message will be written to those standard inputs. The expected JSON message syntax is as follows:

    // currently only the "alarm" is supported
    "type": "alarm",
    // whether it is a new alarm, or an update for an old alarm or an alarm removal
    "action": "new|update|remove",
    // a JSON map which contains the alarm details
    "alarm": { },
    // the hostname of the cmon controller (could be used to determine the web-ui url)
    "cmon_hostname" : "ip-address/or-host-name"


Tweaking MySQL Galera Cluster to handle large databases - open_files_limit

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.




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.
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
innobackupex: Using mysql server version 5.6.16
innobackupex: Created backup directory /tmp
140912 19:10:16  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/etc/mysql/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tmp --tmpdir=/tmp --extra-lsndir='/tmp' --stream=tar
innobackupex: Waiting for ibbackup (pid=4274) to suspend
innobackupex: Suspend file '/tmp/xtrabackup_suspended_2'
xtrabackup: open files limit requested 65535, set to 65535
>> log scanned up to (2145319596)
>> log scanned up to (2145319596)
2014-09-12 19:24:09 7f5d2080a740  InnoDB: Operating system error number 24 in a file operation.
InnoDB: Error number 24 means 'Too many open files'.
InnoDB: Some operating system error numbers are described at
InnoDB: Error: could not open single-table tablespace file ./db_2760/t_0.ibd


In this case the DONOR failed because of the error:

2014-09-12 19:24:09 7f5d2080a740 InnoDB: Operating system error number 24 in a file operation

At this point you have to increase the open_files_limit in your my.cnf file. Unfortunately, open_files_limit is not a global variable, and you must restart the MySQL server in order for the change to be effective.


Keep in touch
Signup for our newsletter:

Alex's Blog

Quickstart $ git clone $ cd docker/percona-xtradb-5.6/centos $ ./ $ ./ 3 $ ./ $ ssh root@ (root123) $ mysql -...
34 weeks 4 days ago

Johan's Blog

FOREIGN KEYs in MySQL Cluster is a big step forward. It is now possible to run enterprise software with NDB Cluster as the storage backend. Over the years, the lack of FOREIGN KEYs have been one of...
1 year 19 weeks ago