Control Your Database Infrastructure
Severalnines
 

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. 

 


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. 

 


Resources for Database Clusters: Performance Tuning for HAProxy, Support for MariaDB 10, Technical Blogs & More

 
August 28, 2014
By Severalnines

Check Out Our Latest 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 Webinars

 

performance-haproxy-webinar.png

Performance Tuning of HAProxy for Database Load Balancing

09 September 2014 - with Baptiste Assmann of HAProxy Technologies

Do you know what HAProxy can tell you about your application and database instances? Do you know the difference between short-lived connections and persistent connections, and how they affect the performance and availability of your applications? As a follow-up to our previous webinar on MySQL Load Balancing and HAProxy, we are glad to organize a deep dive into Performance Tuning of HAProxy.

Register here!

 

mariadbgaleracluster.jpg

Automation & Management of MariaDB Galera Clusters: European Webinars with SkySQL - The MariaDB Company

30 Sept. (English) / 07 Oct. (French) / 08 Oct. (German) - with the MariaDB Team

MariaDB Galera Cluster involves more effort and resource to administer than standalone MariaDB systems. In this webinar, we will give you practical advice on how to introduce clusters into your MariaDB environment, automate deployment and make it easier for operational staff to manage and monitor the cluster using ClusterControl.

Register here!

 

Technical Webinar - Replay

 

webinar-haproxy-replay.png

Webinar Replay & Slides: How To Set Up SQL Load Balancing with HAProxy

In this webinar, we covered the concepts around the popular open-source HAProxy load balancer, and demonstrated how to use it with your SQL-based database clusters. We also discussed HA strategies for HAProxy with Keepalived and Virtual IP.

View the replay & read the slides!

 


9 Tips for Going in Production with Galera Cluster for MySQL

 
August 25, 2014
By Severalnines

Are you going in production with Galera Cluster for MySQL? Here are 9 tips to consider before going live. These are applicable to all 3 Galera versions (Codership, Percona XtraDB Cluster and MariaDB Galera Cluster). 

 

1. Galera strengths and weaknesses

 

There are multiple types of replication and cluster technologies for MySQL, make sure you understand how Galera works so you set the right expectations. Applications that run on single instance MySQL might not work well on Galera, you might need to make some changes to the application or the workload might not be appropriate. We’d suggest you have a look at these resources: 

 

2. Database schema

 

Synchronous replication has implications on how well some transactions will run, so consider the following:

  • Each table must have at least one explicit primary key defined.
  • Each table must run under InnoDB or XtraDB storage engine.
  • Chunk up your big transaction in batches. For example, rather than having one transaction insert 100,000 rows, break it up into smaller chunks of e.g., insert 1000 rows per transaction. 
  • Your application can tolerate non-sequential auto-increment values.
  • Schema changes are handled differently. Watch this webinar for details.
  • Handle hotspots/Galera deadlocks by sending writes to a single node.

 

3. Hardware and network

 

Galera will perform as fast as the slowest node. This is due to the nature of synchronous replication, where all nodes need to acknowledge a writeset before committing. Choosing uniform hardware for all nodes is recommended. If you’re running on virtualized environments, monitor CPU Steal Time (the percentage of time a virtual CPU waits for a real CPU while the hypervisor is servicing another virtual processor). 

The disk subsystem is important (and also applies to single instance MySQL). Remember that upgrading the disk layer is a bigger hassle than upgrading the other components like RAM or network interface card. Hardware upgrades can be performed in round-robin fashion, i.e upgrading one node at a time.


Automation & Management of MariaDB Galera Clusters: New European Webinars with SkySQL - The MariaDB Company

 
August 21, 2014
By Severalnines

MariaDB Galera Cluster involves more effort and resource to administer than standalone MariaDB systems. If you would like to learn how to better manage your MariaDB cluster, then this webinar series is for you. 

 

We will give you practical advice on how to introduce clusters into your MariaDB / MySQL  environment, automate deployment and make it easier for operational staff to manage and monitor the cluster using ClusterControl.

 

Language, Date & Time: 

 

English - Tuesday, September 30th @ 11am CEST: Management & Automation of MariaDB Galera Clusters

French - Tuesday, October 7th @ 10am CEST: Gestion et Automatisation de Clusters Galera pour MariaDB

German - Wednesday, October 8th @ 10am CEST: Verwaltung und Automatisierung von MariaDB Galera Cluster

 

High availability cluster configurations tend to be complex, but once they are designed, they tend to be duplicated many times with minimal variation. Automation can be applied to provisioning, upgrading, patching and scaling. DBAs and Sysadmins can then focus on more critical tasks, such as performance tuning, query design, data modeling or providing architectural advice to application developers. A well managed system can mitigate operational risk, that can result in significant savings and reduced downtime. 

 

mariadbgaleracluster.jpg

 

MariaDB Roadshow - London

 

And if you’re in London this September, do join us at the MariaDB Roadshow event on Thursday, September 18th. We’ll be talking about Automation & Management of Database Clusters there as well and would love to talk to you in person! 

 


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 -...
28 weeks 6 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 13 weeks ago