SysAdmin Valley

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)

Have you ever spent countless time writing a shell script trying to say you want all “data” between this date and that date. I’ve had numerous scripts like this.  Well, recently I got tired of doing this so I re-read the man page for “date” and came across this helpful time.

Normally when I use date I format it how I like, such as 2008-12-16.  To do this I could do

date +%Y-%m-%d

Or to do 12-16-2008

date +%m-%d-%Y

But here is the real gem.  I needed to find out what date it was 6 months ago, so this is what I did.

date +%Y-%m-%d –date=’6 months ago’

And it gave me the correct date of 2008-06-16

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

Here’s another useful what to query RPM’s installed on your machine. I had the need to see not only the rpm’s installed on the machine but what the size of each package was, so here’s the query I used

rpm -qa --queryformat='%{SIZE} %{NAME}-%{VERSION}-%{ARCH}\n'

You can then sort the output based on size

rpm -qa --queryformat='%{SIZE} %{NAME}-%{VERSION}-%{ARCH}\n' | sort -n

No it’s not Linux related, but it’s something I came across in my job today. I needed to be able to use xp_cmdshell to have SQL Server execute a dos command, but that command is turned off by default (for security reasons).

Open up Query Browser or MS SQL Server Management Studio and log in as SA or another privledged user. Open a new query. Run

sp_configure

Then scroll all the way to the bottom, if xp_cmdshell is there, then skip these 2 steps, otherwise run

sp_configure show_advanced_options, 1
reconfigure with override

Now run the following commands

exec sp_configure xp_cmdshell, 1
reconfigure with override

Now you can use xp_cmdshell in all your scripts

Now that 64bit systems have come along, I’ve come across issues using just

rpm -qa

If you have package that installs both a x86 & x86_64 package, it will only show the package name once for both packages.

Try the following, it will list all packages along with the arch type

rpm -qa --queryformat "%{NAME}-%{VERSION}.%{ARCH}\n"

Say in the past you had run a very long command, if you want to re-run the last “whatever” command type

!whatever

If you want to re-run the last command but substitute v3 for v4 run:

^v3^v4

Easy to use way to search and replace in VI.

1. Open your file: vi filename
2. type: :0,$s/search_term/replace_term/g

This will replace all search_term with replace_term from the beginning (0) to the end ($) of the file. You can also use (.) which means from “here”, so (.,$) would be here to the end. (0,.) would be beginning to here.

You can also use (%) instead of (0,$) to search entire document. Or you can use (23,90) to only search between lines 23 and 90 (substitute your own line numbers).

If you want to kill a process, but don’t know the process id, but you do know the service name, try this. It will get the process id and kill it all in one step.  Replace httpd with the process name you want to kill

ps auxwww | grep httpd | awk ‘{print $2}’ | xargs kill -15