Monitor NDB memory usage with Nagios

We are planning to put online a MySQL NDB Cluster soon (more on this in another post), and one thing you have to do before putting anything in production is to monitor it for problems. In the case of a NDB cluster, you should care about monitoring your limited resources – basically because is a in-memory database – and be alerted when your developers are filling up the dedicated tablespace.

You can do this in two ways: performing a SELECT on the ndbinfo database through the MySQL interface to NDB or parsing the ndb_mgm output. I prefer the latter because maybe I’m using another frontend to the data (native NDB API, memcached etc) and I don’t want to maintain a MySQL server frontend just to check how much space I still have free.

So, you can use this script on GitHub

https://github.com/vide/nagios-ndb/blob/master/check_ndb_usage.sh

to parse its output and know if your tablesapce is OK, warning or critical. Feel free to post any comment, fork it and send patches! :)

/etc/hosts and the thousand-characters-long line

This is a self-note in the case I encounter another strange behaviour like this. We were experiencing a strange problem with MySQL and DNS. I was trying to do this:

$ mysql -h server.mysql
Unknown MySQL server host 'server.mysql' (-1)

but both dig and a normal ping (which in turns uses libc and nsswitch to do the name resolving) were working:

$ dig +short server.mysql
192.168.10.1

$ ping server.mysql
PING server.mysql (192.168.10.1) 56(84) bytes of data.
64 bytes from server.mysql (192.168.10.1): icmp_req=1 ttl=64 time=0.399 ms


and obviously connecting using the MySQL client and the IP address worked. So, what was happening? The smarter amongst you maybe already know the problem: a very very large line in /etc/hosts was driving the mysql client crazy (but not ping). Removing the “files” database fron the hosts entry in /etc/nsswitch.conf showed where the problem lied, and fixing the bad-ass line fixed the problem

Column names with trailing spaces in tables and views

Just found out a bizarre feature of MySQL: you simply cannot create a table with a trailing space in a column name. Something like this, doesn’t work:

create table a (`a ` int);
ERROR 1166 (42000): Incorrect column name 'a '


There’s a thread in MySQL’s lists explaining it

Ok, it seems there’s a technical limitation but this solution seems like a lazy workaround to me.
The problem is with CREATE VIEW. In recent MySQL builds you cannot either create a view with a trailing space in a column name. But recent means, at least, greater than 5.0.51a, cause this version DOES let you create a view with a trailing space.

# mysql --version
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2

mysql> create view b as select a `a ` from a;
Query OK, 0 rows affected (0.00 sec)


There’s a big problem here: 5.0.51a is the default and supported version in Debian Lenny! So, if you have Lenny as a master of a more recent MySQL installation, be careful cause a CREATE VIEW with a trailing space in a column name will break your replication. Grrrr. MySQL, I hate you, really.

MySQL slaves and the “corrupted” relay log/binlog problem

When trying to pull up to date a new slave, maybe using a binlog server (a MySQL server with no true tablespace, just acting as master for slaves, feeding them with replication data), you may encounter an error like this in your mysql.err file, after noticing the replication has just broken

091028 11:32:49 [ERROR] Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
091028 11:32:49 [Warning] Slave: Field 'owner' doesn't have a default value Error_code: 1364
091028 11:32:49 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysqld-bin.000780' position 786513508


Well, everything on the web points to a “OMG, my file is corrupt!! My disk is freaking out!”. Probably, instead, it’s just simpler: you have forgotten a FLUSH LOGS; before backing up your binary logs from the original master. So, when you are recreating the binlog server from the backup, the final slave arrives to the last statement wich obviously is not complete/correct and booom, it stops.

The solution is to copy the correct and complete binlog file (in my case mysqld-bin.000780) again in the binlog server, then issue a
STOP SLAVE;
CHANGE MASTER TO master_log_file="mysqld-bin.000780", master_log_pos=786513508;
SLAVE START;


and… problem solved!
(remember to adjust file name and position to suite your needs)

Lesson learned (once again): always test your backups and do not hesitate to flush with MySQL :)

HOWTO: Install Mysql 5.1 for SPARC64 under Debian Lenny

If you happen to own a SPARC64 box, you’ll probably already know that even if the kernel is 64bit the userland comes from the normal SPARC Debian port, so it’s 32bit. Mysql is no exception, with all the 32bit limitations – mainly the 4GB RAM per process limit.

This is really  a PITA because if you have a SPARC64 box probably it has got plenty of RAM and you want to use it at its full potential, without having to messing around with Solaris (yeah, I don’t like it very much, I’m sorry).

This guide covers Mysql 5.1 installation in Debian Lenny, so we have to use SID repositories.


# echo "deb http://ftp.de.debian.org/debian/ sid main" >> /etc/apt/sources.list
# echo "deb-src http://ftp.de.debian.org/debian/ sid main" >> /etc/apt/sources.list


then let’s edit our apt preferences to avoid massive update on next dist-upgrade :)

# vim /etc/apt/preferences
Package: *
Pin: release a=stable
Pin-Priority: 900
Package: *
Pin: release a=sid
Pin-Priority: 100


and then update our repo list

# aptitude update


And here we go:

# apt-get build-dep mysql-server-5.1
# mkdir /tmp/mysql-build; cd /tmp/mysql-build
# apt-get source mysql-server-5.1
# vim mysql-dfsg-5.1*/debian/rules


here we touch a little the rules for compiling cause there are a couple of things that are not going to work by default.

The MAKE_J variable doesn’t work very well, so you can modify the grep to look for “CPU” instead of “processor” or you could hardcode it to the number of processor you have. This will make compilation a lot faster.

MAKE_J = -j$(shell if [ -f /proc/cpuinfo ] ; then grep -c CPU* /proc/cpuinfo ; else echo 1 ; fi)


then edit the CFLAGS variable because it’s used to compile some library that will ignore the environment variables we are going to set later in this howto.

CFLAGS=$${MYSQL_BUILD_CFLAGS:-"-O3 -DBIG_JOINS=1 -m64 -mcpu=niagara2 ${FORCE_FPIC_CFLAGS}"} \

it should be about line 73. Please note that -m64 will make it 64bit so it’s mandatory while the mcpu flag it’s to optimize the executable for your CPU. In my case it’s a niagara2 chip but you can use another CPU as well. Check the GCC documentation for more details
Save and quit and then we can start with the compilation process:

# export CFLAGS="-m64 -mcpu=niagara2 -O2 -g"
# export CXXFLAGS="-m64 -mcpu=niagara2 -O2 -g"
# export CPPFLAGS="-m64 -mcpu=niagara2 -O2 -g"
# export LDFLAGS="-m64 -mcpu=niagara2 -O2 -g"
# export DEB_BUILD_OPTIONS="nocheck"
# debuild -us -uc --preserve-env


that’s it. After some minutes (depending on your HW), you should have in /tmp/mysql-build all your new DEBs which you can install with dpkg -i. I advice to install the stock mysql-server-5.1 with aptitude before to get all dependencies installed, then you can use dpkg with your new DEBs.

Kill every MySQL SELECT older than X seconds

If you’re managing large MySQL installations where lot of queries (written by lot of people) are executed, this is going to sound familiar.  Sometimes due to some faulty query or to heavy traffic, your MySQL server could be overloaded and queries start to stall there, making your PROCESS LIST grow and grow and grow…

So, here it is a quick script that kills every SELECT (we don’t want to kill other query types) older than a certain amount of second and that can help you when you find yourself in one of these situations.

It’s rather quick’n’dirty, but for it’s small use case, it works. It expects GNU userland


#!/bin/bash
SEC=$1
IFS='|'
if [[ $SEC -lt 1 ]]; then
echo "Usage: $0 SECONDS"
exit 1
fi
mysqladmin proc -v|grep Query|grep -Evi "delete|update|insert|alter table" |while read dummy qid qusr qhost qdb qstat qsec qstat2 query
do
if [ $qsec -gt $SEC ]; then
echo "Killing query $qid..."
mysqladmin kill $qid
fi
done


HOWTO: Install recent Mysql Community 5.0.x in Debian Etch

EDIT: If you want to upgrade a 5.0.x installation on Etch, forget this howto and use the packages provided by http://www.backports.org which is the official way to do it. You may still use this howto as an inspiration if you plan to install Mysql 5.1.x, until it get backported ;) (although I doubt it will ever be).
Anyway, for the lazy:

• Add this line to /etc/apt/sources.list

deb http://www.backports.org/debian etch-backports main contrib non-free

• Then execute these commands:
  apt-get update
apt-get -t etch-backports install mysql-server

---

THIS IS DEPRECATED!!If you install a Debian 4.0 (Etch) system and you do an apt-get install mysql-server you will install an old 5.0.32 version. So, since we know there are a couple of critical bugs fixed in more recient versions (solving for example scalability issues on SMP machines), you may want to install a more recent version from the official Mysql Community web site. This link will let you download a dinamically-linked RPM which needs glibc 2.3, which is the version we have in our Etch system.After the download, install the Alien tool
apt-get install alienand then magically convert the RPM to a DEB package withalien --scripts MySQL-server-5.0.*.glibc23.i386.rpmand in a couple of minutes you will have a new mysql-server_5.0.45-1_i386.deb (for example, the number depends on the Mysql version you have downloaded and the architecture you are using).Now, let’s install the old, default Debian mysql version, with

apt-get install mysql-server
that will install mysqld in the Debian way and create all the things it needs.

WARNING:
Before proceding furthermore, we have to remove the mysql-server-5.0 package with

apt-get remove mysql-server-5.0

This is needed because otherwise when upgrades to this package appear in the official Debian repository, you will be forced to install them (if you’re not using pinning) even if you don’t need them. Why? Because the mysql-server package that we’re replacing in this howto is only a meta-package for mysql-server-5.0 which is in Debian the real package. I’m working on a better solution, I’ll keep you informed.

Now, with the deb we have just generated with Alien, we can update the Debian installation, since they both provide the same package (mysql-server)

Install it with:

dpkg -i --force-overwrite mysql-server_*i386.deb

the –force-overwrite is needed because there are files in conflict between the Debian meta-package mysql-server-5.0 and the deb we are installing right now. This could be solved modifying the manifesto of our debian package telling the system that our deb provides a mysql-server-5.0 installation or, since the original RPM is LSB-compliant, with this force-overwrite option.

Now, let’s stop the mysql daemon just a moment

/etc/init.d/mysql stop


create this symlink (because the RPM init script will look for /etc/my.cnf)

ln -s /etc/mysql/my.cnf /etc/my.cnf


and then let’s start again Mysql

/etc/init.d/mysql start

and that’s all, it should work (at least it worked for me).