Tweaking MySQL Galera Cluster to Handle Large Databases – open_files_limit

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.

Edit my.cnf  and change:

open_files_limit=131070

Save and exit.

Before restarting the MySQL server also ensure that the Operating System does support the value you have set open_files_limit to. On Linux based systems do:

$ ulimit -n
65535

In this case, the Operating System imposes the upper limit. You must change this and there are lots of blogs about that. If you are a Severalnines user then we have a convenient script for this called s9s_ulimit:

On the ClusterControl server:

git clone https://github.com/severalnines/s9s-admin.git
cd s9s-admin/cluster
./s9s_ulimit  -i1 -t nofile -n 131070

This script will now modify the open files limit (-t nofile) in /etc/security/limits.conf on all nodes part of cluster 1 (-i 1) and set it to 131070, and also modify /root/.bashrc. The setting is effective immediately and will be used the next time a new shell is opened on one of the nodes part of the cluster.

Now restart the MySQL server (be careful so you do not end up with a partitioned cluster), and try to resync the failed MySQL Server.

Make the changes (and restart the MySQL Server) on one node at a time.

Note: If you have innodb_file_per_table enabled then you may also want to increase innodb_open_files (default 300), but it is not necessary for the sake of Galera recovery.

Using tmpdir /tmp in SST and Streaming Backups

Xtrabackup will by default use the same tmpdir as the MySQL server for temporary storage when streaming backups. By default the MySQL server tmpdir is set to /tmp. As the DONOR will stream xtrabackup to the JOINER during an SST, then the SST may fail because you run out of space in /tmp.

Here is an excerpt from the innobackup.backup.log written on the DONOR:

...
innobackupex: Created backup directory /tmp
...

Now if xtrabackup would fail and the error message would be something like:

archive_write_data() failed: Write error (errno = 32)

[01] xtrabackup: Error: xtrabackup_copy_datafile() failed.
[01] xtrabackup: Error: failed to copy datafile.

then it could indicate that you have run out of space on /tmp.

By default, xtrabackup will use the same tmpdir as is specified in the [mysqld] in my.cnf (which defaults to /tmp which is often mounted on the root partition / ). However, in many cases it is not possible or feasible to use the tmpdir, especially if the tmpdir is on tmpfs (ramdisk). The risk of running out of space is too big.

If the transaction log is growing we recommend that you ensure you have sufficient space in /tmp. Alternatively, create a new group very last in your my.cnf called [xtrabackup] and specify tmpdir there:

[xtrabackup]
tmpdir=/mytmp/

Make sure you create the dir and chown it to mysql:

mkdir /mytmp
chown -R mysql:mysql /mytmp

How much space you need in tmpdir depends a lot on how fast the transaction log is growing (i.e how much you write to the MySQL server) during the backup.