Diamond Notes

As I blogged about recently I was going to have a chance to do some testing.  Last week we started benchmarking our two new servers.  I am running a variety of tests on them and I will begin publishing the results shortly.  This has been the first time I have seriously used the query processing programs for testing.  So far, it has performed fairly well.  Initially, I had intended to use the EVMS (Enterprise Volume Management System) to perform rollbacks of the database files after each test.  However, due to complexity and the overhead that this adds (and we don’t use EVMS in production) I decided to go with a standard setup.  I just wrote a script to stop MySQL, delete the database files, and copy the baseline files from a storage directory.  Then just restarting MySQL and I have a fresh setup.  Yes, it takes longer, but I script the entire run anyways so a little extra time is no big deal.

I guess due to learning a few things during my SAN testing last year the progress has been much faster this time are.  I should have the first set of test results up in a few days..by the end of the week at the latest.

 If you who don’t currently read planetmysql.org, add it to your favorite RSS reader.  It is one of the best sources of information about MySQL available on the Internet.  It is an aggregation of a large number of blogs by some of the best MySQL minds in the world.

Most importantly, the information that you get from these blogs is up-to-date and cutting edge.

The order that NULL is sorted in varies from database server to database server.  Some sort NULL first (in an ascending list), some sort NULL last in an ascending list.  As far as I know PostgreSQL, in the new version 8.3, is the only database server that allows you to specify which way the sort is applied.

When sorting your data it is important to know where NULL will fall when sorted.  Without further ado:

mysql> create table sort (id int not null, data varchar(5));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into sort (id,data) values (1,’a');
Query OK, 1 row affected (0.39 sec)

mysql> insert into sort (id,data) values (2,’b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sort (id,data) values (3,’c');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sort (id,data) values (4,’c');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sort (id,data) values (5,’d');
Query OK, 1 row affected (0.02 sec)

mysql> insert into sort (id,data) values (6,’e');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sort (id,data) values (7,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from sort;
+—-+——-+
| id    | data     |
+—-+——-+
|  1    | a          |
|  2    | b          |
|  3    | c          |
|  4    | c          |
|  5    | d          |
|  6    | e          |
|  7    | NULL |
+—-+——+
7 rows in set (0.00 sec)

mysql> select id, data from sort order by data asc;

+—-+——+
| id    | data   |
+—-+——+
|  7    | NULL |
|  1    | a          |
|  2    | b         |
|  3    | c          |
|  4    | c          |
|  5    | d         |
|  6    | e         |
+—-+——+
7 rows in set (0.24 sec)

mysql> select id, data from sort order by data desc;

+—-+——-+
| id    | data     |
+—-+——-+
|  6    | e          |
|  5    | d          |
|  3    | c          |
|  4    | c          |
|  2    | b          |
|  1    | a          |
|  7    | NULL |
+—-+——+
7 rows in set (0.00 sec)

mysql>

As you can see NULL is sorted before the other characters in the default (ascending) collation.  Just something to keep in mind!

Tonight here in the Raleigh/Durham/Chapel Hill “Triangle” area we are having our monthly MySQL Users Group meeting.  It is a great chance to meet other MySQL’ers, talk, network and learn something new.  This month we have a DBA coming up from Columbia, SC to talk about his experience with migrating to MySQL from Oracle.

If you don’t belong to a MySQL Users Group, join one.  The main page to see if you have one in your area is here:  http://forge.mysql.com/wiki/List_of_MySQL_User_Groups

If there isn’t one in your area consider starting one yourself.  The benefits are invaluable!

As a DBA you will develop a “bag of tools” that you use on a day-in and day-out basis.  I will outline some of the tools I use every day:

mytop (http://jeremy.zawodny.com/mysql/mytop/) - this is a tool that is very similar in use to the “top” tool of the Unix world.  In almost real time it provides information  such as what queries are running, how many queries per second and how many threads are being utilized.

bash (for shell programming) - while there are many choices I use tried and true bash for scripting mysql server installs and the hundred other  tasks a DBA ends up having to script to automate tasks and make life easier.

maatkit (http://maatkit.sourceforge.net) - If I had to have one tool in my bag — this would be it.  The collection of utilities is astonishing and cover everything from a dump and restore utility that runs multiple threads to a table sync tool that compares tables on two servers and can correct any problems between them.  The complete list of utilities is too long to list here.  Do yourself a favor and download it if you don’t have it already.

nagios (http://www.nagios.org) - I don’t enjoy getting paged at 2:00 a.m. but sometimes it is necessary.  Nagios can monitor everything from disk space to cpu usage and there are many plug ins written specifically for MySQL.  To keep on top of things you need monitoring.  Nagios is among the best for that task.

ganglia (http://ganglia.sourceforge.net/)  - ganglia is also a monitoring system, but it is of a slightly different bent.  Ganglia produces nice graphs that you can use to track various system statistics over time and see how your system loads are changing.

That’s all for now.   I am sure if you have been a DBA for a while you might have some more.  Feel free to comment.  If you aren’t using these tools already give them a try.

When doing large imports into a table it can be very helpful to do different optimizations to speed up the import.  Sometimes this can make a dramatic difference.  For example, just recently I imported 106 million rows into a MyISAM table.  Initially I just took the data and imported it into the server.  This took about 60 hours.  Then, because a mistake was made in the setup of the data, we had to re-import.  This time the data was sorted before importing.  While this operation took several hours, the import time dropped to one hour.  Sorting the data might not always be practical, but in this case it worked — and worked dramatically.

There are other things to do that can speed up data imports.  Sounds like good topics for future tips!!