SysAdmin Valley

Okay, so this isn’t sysadmin or networking related.  But who says everything has to be.  Besides this is just plain cool.  A company called Cyth Systems has developed a Guitar Hero robot called Cythbot.  Check out this video.

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)