SysAdmin Valley

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

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

I needed a quick command to delete a specific file or directory that existing in multiple subdirectories. Here is what I came up with.

find . -name “filename” | xargs rm -rf

Note: files can be a filename or directory name. You can use rm with -rf or without

Here are a few tips on how to get around in NANO

To edit a file type: nano filename
*** if you are using one of the legacy Linux servers, it will say NANO Command Not Found. Use PICO instead

• CTRL-Y – Page Up
• CTRL-V – Page Down
• CTRL-W – Where Is (Search)
• CTRL-K – Cut current line (press multiple times to multiple lines)
• CTRL-U – Paste lines just cut (if you cut multiple lines it will paste them all)
• CTRL-C – Word Count / Line Count / Current Position (will be displayed at bottom of page)
• CTRL-O then ENTER – Save file

• If no changes have been made, you can exit by hitting CTRL-X
• If changes have been made, but you DO NOT want to save, hit CTRL-X then N
• If changes have been made, but you DO WANT to save, hit CTRL-X then Y, then ENTER