Diamond Notes

What do you do when a developer walks up and says I need this file of approximately 100 million rows imported into this table?

I don’t know if you have ever had the pleasure of doing a file import on something like that but it is glacially slow when done via one large file.  To be honest, I understand Innodb well enough to know why it is slow, but not well enough to explain why so I have to leave it at that.
That being said, I decided this time around to break things up.  On of the characteristics of Innodb is that imports happen much faster when they are smaller transactions.  So, I used the Unix/Linux ’split’ command to convert the file with 100 million rows into 100 files of one million rows.  Next I wrote a bash script to actually pull in each file.  Here it is:

#!/bin/bash
for i in $( ls -1 /data/tmp ); do
mysql -e “load data infile ‘/data/tmp/$i’ into table db_name.table.name;”
done

You very likely will need to change  the load data statement somewhat.  And of course you will want to change the directory that the script is reading the files from (this is the directory where all the split files are stored .. and don’t put any other files in there or it will try and read them also).

I know this is “simple” or “trivial” for many but maybe it is useful for someone.

As a rough benchmark it looked like after I split things up the files were being pulled in at a rate of just under 20 seconds.  So, 20 seconds x 100 = 2000 seconds (or 33 minutes).  The import of the single file never really finished but it ran for hours.

This is cool.  In the upcoming Spring issue of MySQL Magazine (http://www.mysqlzine.net) I will have a full length article on something very intriguing that will be announced at the MySQL Users Conference (http://en.oreilly.com/mysql2008/public/content/home) that begins on April the 14th.

Join the fun at the conference.  Early registration (and the chance to save money!!) ends February 26th.  I will be releasing the Spring issue at the conference.  Hope to see you there.

Today I scripted a couple of tasks. Now I get to take a little more time sipping some coffee. It was a good introduction to my “Automate Everything” goal for the year. Some of you might find it laughable. That’s OK. Someone might find it useful.

As a DBA it is important to periodically run ‘CHECK TABLE tablename’ and it’s a real good thing to run ‘OPTIMIZE TABLE tablename’ also. I just cron’ed two little code snippets on my slaves to do just that.

The check table problem is very trivial. Here is the crontab entry:

# cat check_tables
MAILTO=”my email address”

0 8 * * sat root /usr/local/mysql/bin/mysqlcheck –all-databases

#
This simply runs mysqlcheck on all databases at 8:00 am Saturday mornings. It emails me the output. I could have it auto-repair the tables, but I would rather do that by hand.

The optimization problem is a little more difficult. I ended up using a php script I found online (with a decent amount of changes). I won’t show the crontab entry here as it it very similar to the previous one.

Here is the script:

#!/usr/bin/php
<?php

//
// code based on this: http://snippets.dzone.com/posts/show/2526
//

set_time_limit( 100 );

$time = microtime();
$time = explode(’ ‘, $time);
$time = $time[1] + $time[0];
$start = $time;

//Connection variables :
$host = ‘localhost’;
$user = ‘username’;
$password = ‘password’;

$db_link = mysql_connect($host,$user,$password);
if (!db_link) {
die (’Could not connect: ‘ . mysql_error());
}
$results = mysql_query(’SHOW DATABASES’) or die(’Could not connect: ‘ . mysql_er ror());
echo ‘Found ‘. mysql_num_rows( $results ) . ‘ databases’ . “\n”;
$dbs = array();
while ( $rec = mysql_fetch_array($results) )
{
$dbs [] = $rec [0];
}

foreach ( $dbs as $db_name )
{
echo “Database : $db_name \n\n”;
$results = mysql_query(”SHOW TABLE STATUS FROM `” . $db_name . “`”) or die(’Quer y : ‘ . mysql_error());
$to_optimize = array();
while ( $rec = mysql_fetch_array($results) )
{
if ( $rec[’Data_free’] > 0 )
{
$to_optimize [] = $rec[’Name’];
echo $rec[’Name’] . ‘ needs optimization’ . “\n”;
}
}
if ( count ( $to_optimize ) > 0 )
{
foreach ( $to_optimize as $table )
{
mysql_db_query($db_name, “OPTIMIZE TABLE `” . $table .”`”, $db_link );
}
}
}

$time = microtime();
$time = explode(’ ‘, $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 6);
echo ‘Parsed in ‘ . $total_time . ‘ secs’ . “\n\n”;
?>

I have written about backups before.  Most recently here — http://www.paragon-cs.com/wordpress/?p=72

This is also about backups - but of another kind.  This is about backups of your brain really.  We had a recent issue that was prolonged because I didn’t put together getting paged at 7:30 in the morning with an on-going experienced problem.  Whenever I have a serious problem I spend some time trying to figure out how to either:

a) keep it from happening again (if possible)  or

b) minimize it as much as possible.

After some thought today I stopped by our local Target store and picked up a composition book that cost less than one dollar.  Starting tomorrow morning it will sit on my desk and when I get paged by Nagios, close a ticket (which generally involves making a change to our systems) or do anything that changes our environment I will log it in my bright red 97 cent book.  Not so glamorous, huh?  No, not really.  But I suspect it will be effective.  Next time we have a serious application issue it will be easy to pull out the book and look back for the last few days of changes to see if there was some change initiated by me or something that had happened to the databases recently that might be causing a problem.

I suppose I could program some gee-whiz program to do all this and eventually I might do that, but for now it will just be me and my red notebook to backup my brain.  Something to think about!!

Ever uncover a bug with MySQL or it’s attendant products? Today I had my first opportunity to file a bug. Just wanted to take a few minutes and write down what the process is so more people will understand how to file a bug. The more (legitimate) bugs people file the better the end product will become. The developers can’t fix a problem if they don’t know about the problem.

Don’t waste the developers time.  Do a little research and find out if someone else has already filed a bug describing your problem.   It will probably take about 10 minutes to file a bug.

I ran into a problem testing MySQL Workbench 5.0.12 (and 5.0.11 for that matter).  The problem appears to be that if you have a table in the ERR diagram that extends off the printed page the bottom part of the table (and anything else on that page) doesn’t get printed.  Another words, you end up with the top part, but not the bottom part of your diagram.

Bugs are filed at http://bugs.mysql.com.  You fill out a fairly intuitive form describing which product is the problem, the OS being run, the nature of the problem and how to repeat the problem.  You can attach a file of output if it would help.  Be as clear as possible, provide step by step instructions on how to repeat the problem.  If I filed my bug saying “it doesn’t print right” - that wouldn’t have been very helpful.  Instead, I explained exactly what I thought was happening and how I can make the problem happen again.

Take a look!  I would say I hope you never have to use it, but if you are a user of any non-trivial software bugs are going to be found.   So I will say this: file a bug..improve the product!!

For the record here is the bug I filed:

http://bugs.mysql.com/bug.php?id=33919

So, I am at least an hour or two late at this and everyone and their three brothers has already blogged about it..but:

http://www.sun.com/aboutsun/pr/2008-01/sunflash.20080116.1.xml

In the past I have worked on Sun products a great deal. I was a system administrator for almost 10 years and probably half of the time was spent on Sun servers and the other half of the time was on Linux. I honestly still get warm fuzzy feelings when I walk into a data center and see a purple case. Their engineering is first class. Their support is phenomenal.

I think that they CAN help engineer a better MySQL server. MySQL has very good engineering talent. More (highly paid, highly motivated and very intelligent) minds working on MySQL can only be better. Versions 6.0 and 6.1 could be very interesting.

That being said I have two concerns:

• Sun is a huge behemoth of a company. Even MySQL could get swallowed up in the corporate beurocracy. Things could stall.
• Sun concentrates their energy on running MySQL on Solaris.

Those being said, I doubt either one will happen. You don’t spend a billion dollars on something and then set it aside. And while MySQL on Solaris will probably get more attention than it has in the past I suspect Linux and other operating systems will continue to get support. That being said, maybe I can convince management to buy me this now:

new server

If you are looking to have a quick question answered or you would like to jump in and help out other users take a look at the irc channel irc.freenode.net #mysql

If you are new to mysql it is a great place to hang out and just watch what goes through.  There is also a #mysql-dev for developers and a #workbench channel for the MySQL Workbench program.  I am sure there are others, always remember that google is your friend.  See you on the channel!

Just four days ago I released the winter issue of MySQL Magazine (http://www.mysqlzine.net). However, I wanted to go ahead and get the jump on the spring issue as this quarter will be quite busy. Many of us are either speaking at the MySQL User’s Conference or attending it in April. The spring issue of the magazine will be released on the first day of the User’s Conference so I wanted to make sure that everyone had time to submit an article if they had interest. Any topic relating to MySQL development or administration is acceptable. In particular, I would love to have some submissions on php development with MySQL. As always, I can be reached at bmurphy at paragon-cs.com.

Hope to hear from you soon!

Just a note while I was thinking about it.  First I hope you are running backups.  If not, shame on you.  Stop whatever you are doing and set up some backups immediately.  There is no excuse.

Now that you are running backups..check them.  Even as I type this post I am copying the backup files of one of my databases over to a test server to restore and see if it went as expected.  This should be done minimally once every three months.

Better safe than sorry!

After a bit of a struggle this time around (pesky holidays got in the way..sorry)  I just posted the new issue of MySQL magazine.  It is available from http://www.mysqlzine.net.

Everyone enjoy.  A special thanks to everyone who contributed!