Control Your Database Infrastructure
Severalnines
 

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.


paytrail-logo-200x200.png
"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:

  • pagerduty.py: This plugin forwards the alarm raise/close events to the PagerDuty system
  • syslog.py: 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.

 

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.
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: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
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.

 


ClusterControl 1.2.8 Released

 
September 17, 2014
By Severalnines

The Severalnines team is pleased to announce the release of ClusterControl 1.2.8. This release contains key new features along with performance improvements and bug fixes. We have outlined some of the key new features below. 

 

Highlights of ClusterControl 1.2.8 include:

  • YUM/APT repositories for ClusterControl
  • Deployment and scaling of single-node MySQL, MariaDB and MongoDB
  • Alerts and incident tracking with PagerDuty 
  • Unified Event Viewer
  • New flexible alarms/email notification system
  • Audit logging - Administrator activity tracking
  • Global MySQL User Management
  • New default dashboards for MySQL/MariaDB
  • Puppet Module for ClusterControl 
  • Improved LDAP support for Active Directory and FreeIPA

 

For additional details about the release:

 

YUM/APT repositories for ClusterControl: We are excited to announce the availability of YUM/APT repos for ClusterControl, making new releases of ClusterControl easily accessible using YUM or APT package managers. The repo is found at http://repo.severalnines.com. For step by step instructions on how to use the repo, see this blog post.

 

Deployment of single-node MySQL, MariaDB and MongoDB: It is now possible to deploy single database nodes from ClusterControl. Once a single node is deployed, it can then be managed from the ClusterControl interface. Single nodes can be scaled into clusters with a single click of a button. MySQL and MariaDB are turned into Galera Clusters, and MongoDB into a ReplicaSet. So you can now start small, and easily add scalability/HA when the business or your development/test environment requires it.

 

ClusterControl integration with PagerDuty - Alert and Incident Tracking: PagerDuty is an operations performance management platform used by thousands of Ops teams. You can now integrate ClusterControl’s alerting with your PagerDuty account. By integrating with PagerDuty, you can be alerted when alarms are triggered within your ClusterControl environment. 

For detailed step by step instructions see this blog post.


Webinar Replay & Slides: Performance Tuning of HAProxy for Database Load Balancing

 
September 11, 2014
By Severalnines

 

Thanks to everyone who attended and participated in this week’s webinar on ‘Performance Tuning of HAProxy for Database Load Balancing’. And special thanks to our guest speaker, Baptiste Assmann of HAProxy Technologies. 

 

If you missed the sessions or would like to watch the webinar again & browse through the slides, they are now available online.

 

Watch the replay of this webinar to learn about what HAProxy can tell you about your application and database instances. And understand the difference between short-lived connections and persistent connections, and how they affect the performance and availability of your applications. 

 

Watch the replay

 

Read the slides

 

This webinar replay covers the performance tuning basics for HAProxy and explains how to take advantage of some of the new features in 1.5, released in June 2014 after 4 years of development work.

 

Full Agenda

  • New features in HAProxy 1.5
  • What can HAProxy tell you about your application and your database
  • OS/Network performance tuning for databases with short living connections
  • Handling persistent connections
  • Dynamic re-configuration
  • HAProxy multi-process: advantages and limitations
  • HAProxy active/active failover setups
  • Security considerations

 


Puppet Module for ClusterControl - Adding Management and Monitoring to your Existing Database Clusters

 
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 your Puppet master host. ClusterControl supports following database clusters:

  • Galera Cluster
    • MySQL Galera Cluster by Codership
    • Percona XtraDB Cluster by Percona
    • MariaDB Galera Cluster by MariaDB
  • MySQL Cluster
  • MySQL Replication
  • MongoDB or TokuMX Clusters
    • Sharded Cluster
    • Replica Set

 

Severalnines Package Repository

 

This module makes use of the Severalnines repository for yum and apt packages. This repository hosts the latest stable release of ClusterControl and all of its components.

 

ClusterControl and all of its components requires post-installation procedures, like setting up MySQL, granting users, setting up Apache and etc. This module will automate most of these. 

 

If you lookup the Severalnines package repository, you will find the following packages:

  • clustercontrol - Severalnines ClusterControl Web Application. Frontend for clustercontrol-controller. Previously known as cc-ui.
  • clustercontrol-cmonapi - Severalnines ClusterControl REST API. Previously known as cc-cmonapi.
  • clustercontrol-agent - Agent for ClusterControl. Manage and monitor MySQL, MySQL Cluster and Galera Cluster for MySQL
  • clustercontrol-controller - ClusterControl Controller. Manage and monitor MySQL, MySQL Cluster and Galera Cluster for MySQL

 


Announcing Severalnines Package Repository

 
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 root]/clustercontrol/app/tools/setup-cc.sh. If you are installing for the first time, you are required to run this script to ensure ClusterControl is properly set up.

 

In this blog post, we will show you how to use the Severalnines repository to install and manage ClusterControl packages in your infrastructure. 

 

New Package Naming

 

For the purpose of streamlining installations based on repository, we have changed the package names as follows:

Package

Old Package Name

New Package Name

ClusterControl CMON Controller

cmon-controller

clustercontrol-controller

ClusterControl CMON Agent (deprecated)

cmon-agent

clustercontrol-agent

ClusterControl REST API

cc-cmonapi

clustercontrol-cmonapi

ClusterControl UI

cc-ui

clustercontrol

 


How to change AWS instance sizes for your Galera Cluster and optimize performance

 
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 your database to operate and grow. Performance tuning will allow you to get the most out of your servers, but in some cases, this might not be enough.

 


How to Manage All Your MySQL or MariaDB Databases

 
September 1, 2014
By Severalnines

According to Forrester, a DBA in a large enterprise manages between 8 and 275 databases, with the industry average being 40 databases to a DBA. Larger databases usually require extra effort around tuning, backup, recovery and upgrade. Cloud, as well as automation and management tools can help improve the number of databases managed by one DBA.  

 

With that background, we were pretty excited to introduce support for management of single-instance MySQL and MariaDB databases in ClusterControl 1.2.6. The majority, if not all of the cluster users out there, use single-instance or master-slave replicated setups along their mission-critical clusters. So today, it is possible to manage an entire MySQL/MariaDB environment from one interface.

 

Ok, so what about Nagios or Zabbix? Unless Nagios is deploying your instances, scaling them, recovering them if they fail, taking backups, upgrading them to newer versions, telling you about slow running queries or suboptimal configuration parameters, we’d argue that you probably ought to look into a management tool. 

 

In today’s blog post, we’ll show you how you set that up.

 

Our setup consists of MySQL servers running different versions (5.1, 5.5 and 5.6) and on different hosts:

 

Installing ClusterControl

 

Deploy ClusterControl on a dedicated host (192.168.197.100) using these simple steps:

$ wget http://www.severalnines.com/downloads/cmon/install-cc.sh
$ chmod u+x install-cc.sh
$ sudo ./install-cc.sh

 

This will automate the installation of ClusterControl, but you can read more about this in the ClusterControl Quick Start Guide.

 

Once the installation is complete, login to the ClusterControl UI at http://192.168.197.100/clustercontrol using your email address (that you entered during the installation process) and default password ‘admin’. You should see something like below:

 

Preparing the Database Hosts

 

You do not need to install any agent on the database hosts, but ClusterControl needs to be able to SSH into them and connect to the database instance to gather statistics. 

 


Search
Keep in touch
Signup for our newsletter:
Name:
*Email:

Alex's Blog

Quickstart $ git clone https://github.com/alyu/docker.git $ cd docker/percona-xtradb-5.6/centos $ ./build.sh $ ./start-servers.sh 3 $ ./bootstrap-cluster.sh $ ssh root@172.17.0.2 (root123) $ mysql -...
30 weeks 1 day 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 14 weeks ago