Mysql | Closer to Code

Sometimes we want to perform some sort of tasks that consume whole available IO bandwidth. This may lead to some unexpected behaviours from our OS. OS might even kill the given process due to resource lack.

Lets take an example. We want to tar.gz a huge directory with a lot of files in it. Our machine also have a web-server which serves several sites. If we start "taring" our directory, it might lead to timeouts on server (it won't be able to respond as fast as we would expect). On the other hand, we don't care so much about the time needed to create archive file. We always can throw it in screen and detach it.

# Standard approach - will slow IO response time
tar -cvf ./dir.tar ./dir

pv to the rescue!

To slow things down to a tolerable level we will use pv tool. pv allows a user to see the progress of data through a pipeline, by giving information such as time elapsed, percentage completed (with progress bar), current throughput rate, total data transferred, and ETA. It can also limit the speed of incoming data, if used wisely.

To tar a file with a given speed (in MB) we need to use following command:

tar -chf - ./dir | pv -L 2m > ./dir.tar

Above example will allow us to tar ./dir with max speed 2MB/s.

We could use the same method to slow down a mysqldump method:

mysqldump --add-drop-table -u user -p password -h host db_name | bzip2 -c > ./dump.sql.bz2

Easier than I thought (all stuff as SU or using SUDO):

First we need to install some Mysql-Munin perl libraries:

apt-get install libipc-sharelite-perl

Also some Perl stuff will be needed:

perl -MCPAN -eshell
install IPC::ShareLite

Next let's activate Munin Mysql plugin:

# Assuming you have already installed both munin & mysql
ln -s /usr/share/munin/plugins/mysql_* /etc/munin/plugins

Create a user for a Mysql database (so Munin will be able to get stats as this user):

mysql> CREATE USER `munin` @`localhost` IDENTIFIED BY 'somepassword';
mysql> GRANT SUPER ON *.* TO `munin` @`localhost`;
mysql> FLUSH PRIVILEGES;

Edit /etc/munin/plugin-conf.d/munin-node file and find [mysql*] section:

[mysql*]
user root
env.mysqlopts --defaults-file=/etc/mysql/debian.cnf
env.mysqluser munin

Let's also disable WARN: MySQL InnoDB free tablespace information (in most cases it is not valid):

Create a file /etc/munin/plugin-conf.d/mysql_innodb and place following lines into it:

[mysql_innodb]
env.warning 0
env.critical 0

or if You don't need InnoDB part, just turn it off by removing the symlink:

rm /etc/munin/plugins/mysql_innodb

Restart both Apache & Munin:

/etc/init.d/apache2 restart
/etc/init.d/munin-node restart
su munin -c /usr/bin/munin-cron

Wait until charts regenerate and You're ready to go! Example (generated for the first time):

Tag: Mysql

Slowing down (limiting) tar, mysqldump or other processes to save IO bandwidth

pv to the rescue!

Making Munin work with Mysql on Debian