Month: November 2015

Detecting if MySQLdump was running

Ever have those times when your site was down for a few minutes, and you have no way to explain why?

One possible thing to check for would be to see if one your developers accidentally ran a MySQLdump on your production databases during that time. You can see this pretty easily if you suspect its currently running by:

mysql
mysql> show processlist;
...
SELECT /*!40001 SQL_NO_CACHE */ * FROM `your_table`;

You can then verify it by running the following query:

mysql> select COUNT(1) mysqldumpThreads
from information_schema.processlist
where info like 'SELECT /*!40001 SQL_NO_CACHE */%';
+------------------+
| mysqldumpThreads |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

If you get something other than 0, like what is shown above, then mysqldump is running.

If the issue already passed, and you are just trying to do a root cause analysis, check your MySQL slow query log for queries like what is posted below around the timestamp in question:

# Time: 151112 10:34:12
# User@Host: root[root] @ localhost []
# Query_time: 539.418400  Lock_time: 0.000041 Rows_sent: 60547211  Rows_examined: 60547211
SET timestamp=1420389252;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `your_table`;

Increase open-files-limit in MariaDB on CentOS 7 with systemd

In the new age of linux known as systemd, a word that many still prepend colorful words to, I stumbled across another gotcha. Changing MariaDb’s open files limit.

Typically you would configure this in /etc/my.cnf or in /etc/security/limits.conf, but this no longer appears to be the case as systemd wants to control it.

I ran across having to do this cause I was receiving the following errors in the database when running mysqldump, mysqlcheck, etc:

mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_7f96_2.MAI' (Errcode: 24)' when trying to dump tablespaces

mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'EXAMPLE'': Can't read dir of './DB_NAME/' (errno: 24) (1018)

mysqldump: Got error: 23: "Out of resources when opening file './DB_NAME/TABLE.MYD' (Errcode: 24)" when using LOCK TABLES

So to increase the open files limit which is the accepted fix for this, you need to do the following with OS’s that use systemd:

First, check to see what your current open files is set to in MariaDB:

MariaDB [(none)]> SHOW VARIABLES LIKE 'Open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0.00 sec)

Create a directory to store the MariaDB service changes for systemd:

mkdir -p /etc/systemd/system/mariadb.service.d/
cat /etc/systemd/system/mariadb.service.d/limits.conf
[Service]
LimitNOFILE=20000

Now reload the systemd daemon so it is aware of the changes applied to systemd. This does not restart any service:

systemctl daemon-reload

Finally, restart the MariaDB service so it will apply the new setting:

systemctl stop mariadb
systemctl start mariadb

You can now verify that you setting went into place by:

MariaDB [(none)]> SHOW VARIABLES LIKE 'Open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 20000 |
+------------------+-------+
1 row in set (0.00 sec)

More details and the reason behind this is found here:

cat /usr/lib/systemd/system/mariadb.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file "/etc/systemd/system/mariadb.service",
# containing
#	.include /lib/systemd/system/mariadb.service
#	...make your changes here...
# or create a file "/etc/systemd/system/mariadb.service.d/foo.conf",
# which doesn't need to include ".include" call and which will be parsed
# after the file mariadb.service itself is parsed.
#
# For more info about custom unit files, see systemd.unit(5) or
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

# For example, if you want to increase mariadb's open-files-limit to 10000,
# you need to increase systemd's LimitNOFILE setting, so create a file named
# "/etc/systemd/system/mariadb.service.d/limits.conf" containing:
#	[Service]
#	LimitNOFILE=10000

# Note: /usr/lib/... is recommended in the .include line though /lib/...
# still works.
# Don't forget to reload systemd daemon after you change unit configuration:
# root> systemctl --system daemon-reload