SysAdmin Valley

While working on some MySQL stuff today I came across some interesting projects.  About 2 years ago I attended the Boston MySQL Meetup group which had a guest speaker (Patrick Galbraith) and he spoke about setting up MySQL in a Multi-Master setup.  This is where you have two MySQL database servers and each one is a slave of the other.  Today I came across two projects that look promising, the first is Multi-Master Replication Manager for MySQL (or MMM) and the second is Flipper.

MMM is a set of scripts that perform monitoring/failover and management of MySQL master-master replication.  Flipper is also a set of tools that manage which server in a Multi-Master setup is writable and which is readable by moving IP addresses based on the server’s role.  Both look very promising and hopefully soon I’ll have some free time to play around with them.

I’ve started using Percona’s version of MySQL 5.1 and have run into a few issues trying to get other tools such as mytop or maatkit to install but have been having problems with RPM dependency’s.  I found the solution on this guy’s blog.  Basically, if you install the MySQL-client-percona, MySQL-percona, MySQL-server-percona, MySQL-shared-percona and Percona-XtraDB, instead of installing MySQL-shared-percona, you should download and force upgrade (rpm -Uvh –force packagename) the MySQL-shared-compat library directly from MySQL.  Just make sure you get the same version from MySQL that you’re using of the Percona MySQL.

As you probably gather I do quite a bit of MySQL work, in fact over the years I’ve sort of become the MySQL DBA where I work.  From time to time we’ve had issues where our MySQL server just aren’t performing as well as they could.  Sure we could hire an expensive consultant, but I’ve come across this tool called MySQLTuner which gives me the ability to see what settings I should tune.

It’s a simple perl script and to use it is really easy.  First, download the script to your linux server, change it to be executable, then run it

wget http://mysqltuner.com/mysqltuner.pl
chmod 0700 mysqltuner.pl
./mysqltuner.pl

You’ll get a great one page output which gives you some great detail about what’s going on, and what settings can be used in your my.cnf file.  One word of caution, make sure you backup your my.cnf before making changes, and you should also spend some time learning what the values mean.

I recently had the need to do some troubleshooting on some of my larger MySQL databases.  It seems one of our developers turned off innodb_file_per_table = 1 in the /etc/my.cnf.  This feature tells MySQL to for every Innodb table to create a separate file, if it’s not turned on, it stores all the tables in 1 large file.  This can be extremely painful by itself to maintain as it grows.  Anyway, I had the need to do a little research on database and table sizes and came across a post by Peter from MySQLPerformanceBlog.com

In this first example, I was able to see which Storage Engines where using the most disk space, the number of tables, etc:

1. mysql> SELECT engine,
2. -> count(*) TABLES,
3. -> concat(round(sum(table_rows)/1000000,2),’M') rows,
4. -> concat(round(sum(data_length)/(1024*1024*1024),2),’G') DATA,
5. -> concat(round(sum(index_length)/(1024*1024*1024),2),’G') idx,
6. -> concat(round(sum(data_length+index_length)/(1024*1024*1024),2),’G') total_size,
7. -> round(sum(index_length)/sum(data_length),2) idxfrac
8. -> FROM information_schema.TABLES
9. -> GROUP BY engine
10. -> ORDER BY sum(data_length+index_length) DESC LIMIT 10;
11. +————+——–+———+———+——–+————+———+
    | engine     | TABLES | rows    | DATA    | idx    | total_size | idxfrac |
    +————+——–+———+———+——–+————+———+
    | MyISAM     |   1243 | 941.06M | 244.09G | 4.37G  | 248.47G    |    0.02 |
    | InnoDB     |    280 | 682.82M | 63.91G  | 32.49G | 96.40G     |    0.51 |
    | MRG_MyISAM |      1 | 13.66M  | 6.01G   | 0.00G  | 6.01G      |    0.00 |
    | MEMORY     |     14 | 0.00M   | 0.00G   | 0.00G  | 0.00G      |    NULL |
    +————+——–+———+———+——–+————+———+
    4 rows IN SET (14.02 sec)

In this next example, I’m able to see which databases are consuming the most:

1. SELECT
2. -> count(*) TABLES,
3. -> table_schema,concat(round(sum(table_rows)/1000000,2),’M') rows,
4. -> concat(round(sum(data_length)/(1024*1024*1024),2),’G') DATA,
5. -> concat(round(sum(index_length)/(1024*1024*1024),2),’G') idx,
6. -> concat(round(sum(data_length+index_length)/(1024*1024*1024),2),’G') total_size,
7. -> round(sum(index_length)/sum(data_length),2) idxfrac
8. -> FROM information_schema.TABLES
9. -> GROUP BY table_schema
10. -> ORDER BY sum(data_length+index_length) DESC LIMIT 10;
11. +——–+——————–+——-+——-+——-+————+———+
    | TABLES | table_schema       | rows  | DATA  | idx   | total_size | idxfrac |
    +——–+——————–+——-+——-+——-+————+———+
    |     48 | cacti              | 0.01M | 0.00G | 0.00G | 0.00G      |    0.72 |
    |     17 | mysql              | 0.00M | 0.00G | 0.00G | 0.00G      |    0.18 |
    |      4 | pdns               | 0.00M | 0.00G | 0.00G | 0.00G      |    1.00 |
    |      2 | test               | 0.00M | 0.00G | 0.00G | 0.00G      |    0.12 |
    |     16 | information_schema | NULL  | 0.00G | 0.00G | 0.00G      |    NULL |
    +——–+——————–+——-+——-+——-+————+———+
    5 rows IN SET (0.32 sec)

I had the need to do a backup and compress a mysql database, so this is how I did it.

mysqldump --opt databasename -uUsername -p | gzip > databasename.sql.gz