In MySQL 5.0, with the introduction of information_schema, the SHOW commands were changed to really be wrappers around SELECTs against information_schema. This means that when you issue e.g., a SHOW VARIABLES command, Created_tmp_tables is incremented once, and Handler_write and Handler_read_rnd_next are incremented about once per row in the result. Keep this in mind in your monitoring systems; these numbers could be wildly inaccurate if some part of your application is doing many SHOW commands.

This came to my attention because Connector/J’s ReplicationDriver runs SHOW VARIABLES LIKE ‘tx_isolation’ whenever you switch between setReadOnly(true) and setReadOnly(false), so in large, complex Java applications don’t be surprised if you see a high number1 of Created_tmp_tables. How can this be solved? Use SELECT instead of SHOW. If you need to access one variable, instead of doing:

SHOW VARIABLES LIKE 'tx_isolation'

…

I just finished working with our great dev team (thanks Chad and Giuseppe!) on working out a few minor kinks with the new SQL Profiler that was introduced in the 5.0.37 version of the MySQL Community Server. Overall, the SQL Profiler is a great new diagnostic aid that helps you understand exactly where your queries are spending their time during processing and execution. It’s also a great example of the MySQL community in action as the Profiler was originally developed outside of MySQL by Jeremy Cole of Proven Scaling.

I just finished a new article on the Profiler that demonstrates how to use it for troubleshooting problem SQL queries. Check it out when you can and let me know what enhancements you’d like to see to the Profiler that would make it even better.

One of the great things about MySQL is the superior innovation model that?s used to deliver database server software. Rather than relying solely on internal engineers who create and maintain a piece of software (as in a traditional software company), MySQL partners with the millions of active users across the world who take advantage of the open source model and daily extend the MySQL server to do new and pioneering things. These innovations can then be submitted to MySQL AB, tested, validated, and rolled into the database server so everyone can benefit from the creativity of the very active MySQL community.

I’ve just released three updates to tools in MySQL Toolkit. The updated programs are MySQL Query Profiler (major new features and helper script), MySQL Table Sync (bug fixes, documentation, features), and MySQL Table Checksum (minor sanity check enhancement). MySQL Query Profiler 1.1.0 I’ve added the ability to profile more data, including an educated guess at the number of filesorts. The tool can now profile external programs. There are several ways you can do this:

When I wrote my first article on algorithms to compare and synchronize data between MySQL tables, Webyog’s Rohit Nadhani left a comment on the article mentioning the SQLyog Job Agent, which has a similar function. Although I have been developing MySQL Table Sync essentially in isolation, I have been meaning to give SQLyog Job Agent a try. I recently did so, and then followed that up with an email conversation with Rohit.

Paul Tuckfield from YouTube is leading a session at the upcoming MySQL Conference & Expo April 23-26 on how they scale MySQL to deal with millions of videos from the world's most popular video web site.  YouTube has experienced the kind of exponential growth that every startup dreams of and Paul's practical tips and guidance will give you the benefit of their hard work and close collaboration with MySQL.

There are also other great practical sessions by top MySQL customers including Google, Nokia, FlickR, …

I leaned a new trick today with LOAD DATA INFILE. I’m migrating some data from an external source, and the Date Format is not the MySQL required YYYY-MM-DD, it was DD-MMM-YY. So how do you load this into a Date Field.


$ echo "02-FEB-07" > /tmp/t1.psv
$ mysql -umysql
USE test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(d1 DATE);
# echo "02-FEB-07" > /tmp/t1.psv
LOAD DATA LOCAL INFILE '/tmp/t1.psv'
INTO TABLE t1 (@var1)
SET d1=STR_TO_DATE(@var1,'%d-%M-%y');
SELECT * FROM t1;
EXIT


The trick is to bind the appropriate column within the file being loaded to a variable, @var1 in my example and use the SET syntax to perform a function on the variable. Rather cool.

A good tip to know.

Those that know me, know that I‘m a little paranoid about backup automation around the house (because I‘m lazy, and that if it wasn‘t automated, it wouldn‘t get done).

I‘m also a little paranoid about offsite backups, so therefore I have a VPN between my house and my parents‘ house, and send my nightly backups (via rsync) to their place as well.

Normally, when I check my e-mail in the morning, I see a little report about what got backed up. I didn‘t notice that it was missing this morning, and instead it arrived at 3:00 in the afternoon.

That seemed a little odd, so I went and looked at my cricket instance, and low-and-behold, the outbound rsync had the 768Kbps side of my DSL connection pegged since 02:00 AM. A little “du”ing around on my backup server, and I found out that a backup had taken place in the middle of a torrent of FC6. That wouldn‘t have been a problem, except that BT stores …

Those that know me, know that I'm a little paranoid about backup automation around the house (because I'm lazy, and that if it wasn't automated, it wouldn't get done).

I'm also a little paranoid about offsite backups, so therefore I have a VPN between my house and my parents' house, and send my nightly backups (via rsync) to their place as well.

Normally, when I check my e-mail in the morning, I see a little report about what got backed up. I didn't notice that it was missing this morning, and instead it arrived at 3:00 in the afternoon.

That seemed a little odd, so I went and looked at my cricket instance, and low-and-behold, the outbound rsync had the 768Kbps side of my DSL connection pegged since 02:00 AM. A little "du"ing around on my backup server, and I found out that a backup had taken place in the middle of a torrent of FC6. That wouldn't have been a problem, except that BT stores incomplete files in your …