Using the MySQL Event Scheduler to Emulate Threads

The MySQL Event Schedule is one of the new features in MySQL 5.1. It is explained well in this article from the MySQL site, and in the manual itself. I'd like to demonstrate an interesting way to use it. What gave me this idea is actually a feature in SQL Server 2005, called Service Broker. It's a sort of queue manager / messaging platform, which can also be exploited in the same way.

Lets say you are writing a stored procedure, that loads data into the database. Benchmarks show that loading data in parallel from several threads increases overall throughput, as MySQL is unable to utilize several CPUs to process a single SELECT query, moreover a bulk load operation. To work in parallel, you'd probably need to write a script that uses threads, open several connections to the database, and perform those queries in parallel.

I propose an alternative.

The MySQL Event Scheduler is capable of performing several tasks at once, since it's highly probable that several events might need to run at the same time. Why not use it?
What we will create, is a "self-destructible" event, that performs a task once and disappears. The syntax that allows this is self explanatory:

CREATE EVENT IF NOT EXISTS event_name ON SCHEDULE AT NOW() ON COMPLETION NOT PRESERVE
DO statement_or_batch;

I don't think this is the place to descend into the implementation detail of using it for data loads, so I won't. Note, however, how flexible it is.

1. You could use the event scheduler to perform several SELECT statements in parallel into some summary or union table, and collect the returned result afterwards.
2. To see if an event is still running, you can poll the mysql.event system table for the event name, or names (when waiting for several events to complete).
3. Combine it with the FEDERATED engine. That is one feature which is seriously in need of more development efforts. It is not even included in 5.1.24 due to severe problems. When it goes back to a stable status, you could use events to execute remote queries in your cluster in parallel - with a single command, from a single server.
4. Use it for an unordered set of commands that your application shouldn't wait for at all, or it doesn't care when it gets done.
5. Use it to delay execution, just replace NOW() with NOW() + INTERVAL 5 MINUTE, as demonstrated earlier.

The possibilities are endless. Well, not endless, but I'm sure that the event schedule can be tricked into doing a lot more cool things than these... If you can think of anything of that sort, I'd love to hear it.

Some points:

1. As the mysql.events table is using the MyISAM engine, I'm not sure how transactional adding an event is. Most likely, it isn't.
2. If you want to test the scheduler, make sure it's turned on (SET GLOBAL EVENT_SCHEDULER = ON).
3. Each event run adds two lines to the error log (hostname.err) file reporting start and end of the event. This might be a problem similar to leaving the general log on, when using events extensively.
   

Two MySQL Monitoring Tools and a Hidden Knowledge Base

This post is incomplete without mentioning the previous one about development tools, so make sure you read it before/after this.
In this post, I'd like to mention two monitoring tools, and one knowledge base.

Two of the tools are made by Quest Software/ToadSoft that make Toad for MySQL. Both of them are Freeware.
A side note: TOAD was born as "Tool for Oracle Application Developers". Because "Tool for Oracle Application Developers for MySQL" sounds weird, TOAD is known officially as "Tool For Application Developers".

Spotlight for MySQL
Spotlight is a tool perhaps more known among SQL Server and Oracle users. It shows you the entire flow of data throughout your database in a nicely laid out executive-summary kind of way. It has lots of colors and it's impressive to look at. When it works. Seriously, I've had a lot of trouble with it, to make it work the way it should too many times. It's also slow as hell, and tends to hang or crash, like other tools by Quest. But, when it does work - it's very nice. Quest released this tool for MySQL as well, and since it's free it doesn't hurt to try it out.

Knowledge Xpert for MySQL
This is basically a well laid-out knowledge base, of what Quest Software thinks you should know about MySQL. Full of useful examples, and clear explanations for things that the MySQL manual doesn't mention. I recommend installing it and taking a look, you might find something interesting. It was helpful to me when I started learning MySQL.
Warning: practically this is just one big help file, but Quest decided to bundle an annoying interface, a slow installer and one of those load-on-startup-take-memory-do-nothing-system-tray-icon which you need to get rid off after installation.

The next one is made by Webyog, (the same guys that make SQLyog which I mentioned before).

MONyog
MONyog is a small program with a web front-end which can monitor several MySQL servers and supports some customizable metrics. I won't go into detail on the good and bad things in MONyog here (I plan to write a review of it later on), but I do suggest you try the trial version. MONyog is not free, and doesn't have a free or community "lite" version.
At the time of writing the latest stable version is 1.52, but 2.0 beta 2 is already available and brings additional features like historical reports and better graphs.

SQL Server Data Services Coming Soon

Microsoft decided to jump into Amazon's territory and provide a data storage web service accessible from anywhere on the internet.
It's not live yet, and you have to register to see the beta when it comes - but it looks interesting.

Take a look at the FAQ. Looks interesting. Note the following points (taken from the FAQ):

• Benefit: Scale, with virtually no restriction on storage
• Target Customers: Running applications that have limited or batched data access.
• Typical scenarios include business solutions like HR services, healthcare records management, data archiving and internet facing applications like social networking and picture sharing.
• The SQL Server Data Storage Service can store multiple types of data, from birth to archival. Users can upload and query structured data, semi-structured data, stored in flexible entities, and unstructured data. Customers will be able to associate entities with large unstructured data objects (blobs) which could be accessed as URL addressable resources.
• Data is stored in large storage clusters in various Microsoft data centers located across North America. We are planning to offer the service from international locations such as Europe and Asia. Users can group their data into authorities, which are affiliated with specific data centers and therefore provide control over the geographic location of the data.
• Easy access with REST and SOAP protocols

There is not much information available about it yet, so I'll leave my speculations for later.

Formatting Large Volumes Using FAT32

First, some background (Executive Summary below)

During this weekend I purchased a Western Digital My Book Home Edition 500GB. This is the triple-interface model that includes FireWire 400 and eSATA in addition to USB 2.0.
I wanted the FireWire model since it's faster than USB 2.0, and connecting a FireWire drive in a Mac is the same as connecting a USB one. Just perform these two easy steps - connect cable, wait 3 seconds.
Anyway, this is just a prelude to the story, since this drive came pre-formatted with FAT32 to suit both Mac and Windows computers, so I didn't have to format it. Western Digital also sells some drives pre-formatted with HFS+ and some with NTFS, by the way.

The next thing I wanted to do after setting up the My Book was to format my old 60GB NTFS external drive using FAT32, so that I could use it with both computers.
I copied it's contents over to the 500GB drive (OS X has read-only access to mounted NTFS volumes) and plugged it into a Vista machine to format it as one big FAT32 volume.
This is when I found out that Windows Disk Management allows you to format drives larger than 32GB using only NTFS.
So I can use a 500GB FAT32 drive in Windows, but I can't format a 60GB one. This also means by the way, that if you re-partition a 500GB drive you bought, and you don't know how to bypass this limitation, you're basically doomed to creating ~15 32GB partitions to use your drive, unless of course you use NTFS.

I've done my research+trial+error and eventually used my Macintosh to format the drive for Windows. Sort of poetic justice.
The 500GB monster eventually got converted to HFS+ since it's about 2 times faster in writes compared to FAT32. This is no official benchmark: I just monitored the HD read/write sustained speeds while copying files during setup.

Executive Summary

Windows XP/2000/Vista don't allow creating FAT32 partitions larger than 32GB. This is to promote NTFS, and because FAT32 becomes slower as volume size increases. Reading/Writing of any volume size is possible.

If you want to format a drive larger than 32GB with FAT32, you have the following choices:

1. Run the Windows XP installer from the CD - just make sure you quit it in time just after "preparing" the drive for installation.
2. Use any Linux bootable CD - fdisk can do it. If you can boot into Linux, you can also Google for instructions about using fdisk so I'll stop here.
   
3. Use the free FAT32 Formatter. Small windows utility that does what its name implies. I didn't use it myself, but it should do the trick.
   
4. Use your Mac. The Mac "Disk Utility" can format FAT32 of any size.
   Open Disk Utility, select the volume, click erase, and select "MS-DOS (FAT)" as the file system.
   Leopard also includes the fdisk utility so you can use that one if you are some kind of a masochist.

I didn't want to include detailed instructions, since I assume people should know how to use Google and common sense, but if someone has questions about anything, the comments are open as always.

P.S. I published the previous post by a clicking mistake, deleted it right away, checked that it wasn't there - only to find out that blogger entered 3 identical posts, marked 2 as drafts and one got published anyway.

Misleading Error Message When Running 32bit Applications on 64bit Ubuntu/Linux

Short version (aka, Executive Summary)

If you try to run a 32bit program under 64bit Ubuntu, there is a chance you might see this misleading error message:

-bash: ./executable_filename: No such file or directory

What the shell is actually trying to say is "You can't run 32 bit applications here without installing support for 32 applications!".

Longer version

We purchased the Webyog's MONyog MySQL monitoring tool, after some trial version testing in a Windows environment. When trying to run it on one of our Ubuntu servers, I got this:

myuser@myserver:~/MONyog/bin$ ./MONyog-bin
-bash: ./MONyog-bin: No such file or directory

I checked again: the file was there, permissions were okay. Hmm. Why was bash saying the file wasn't there? Here it is! It's not like the error came from the application, it was bash itself saying that it's not found.
I googled for a while till I opened a support ticket at Webyog, and this is how I found out the real reason. The solution is to install support for 32 bit applications which is package called ia32-libs-sdl (which also installs a number of prerequisites). Once it's installed, the program works.

I'll use this opportunity to praise Webyog's support, which was fast and to the point. I got my initial reply with an explanation after few hours, and the solution the next day - without having to chase them for it.

MySQL Date and Time Arithmetic

One thing that sometimes bugs me in MySQL is the huge amount of errata. The database is packed with features and abilities that are side-effects from "historical decisions". Decisions that were made when it wasn't planned to be an enterprise product, or changes made to ease porting applications to MySQL from other databases. Legacy, so to speak.

The timestamp data type is a good example - the timestamp manual section is a long list of exceptions and special cases. I usually re-read the manual page before using it. Another good example is the amount of date and time functions that exist in MySQL. There are way too many of them, some overlapping in function, and some just exist as aliases to other functions for compatibility. Just go over the list.

On the other hand, this artistic freedom has its merits.
For example, take the date arithmetic. Chances are that if you came from SQL Server background like me, you were used to all the variations on the DATEDIFF and DATEADD commands. While it works as well (the DATEADD function is there and works almost the same way), MySQL supports a much readable form of date and time arithmetic.

For example:

SELECT NOW() - INTERVAL 5 MINUTE

Will return, naturally, current date+time minus 5 minutes. Or:

SELECT NOW() + INTERVAL 10 DAY

You guessed it, this will return the current date+time plus 10 days.

Clean and simple, isn't it? The syntax goes like this:

date + INTERVAL expression unit

The unit can be MINUTE, HOUR, DAY, MONTH, YEAR etc. The expression can be any legal T-SQL expression, and the date is just the date variable, which can be a result from another expression, a variable or a table column. Check out the manual for more examples.

This is an elegant solution to a common problem, which is also easy to remember and use. Why so few people use it is a mystery to me - I've almost never seen it in code samples.

Hello Planet MySQL!

My blog been added several days ago to the Planet MySQL feed, and am now one step closer to world domination.

I'll assume that most people who see this entry at their feed readers would be unfamiliar with this blog, so I should be including an introduction here. Instead, I invite you to take a look at the home page and look at some olds posts of mine that might interest you. Not everything is about MySQL, and the Planet MySQL feed takes only the ones that I've labeled as such.

The following posts are the top search engine keywords that this blog gets, so you might want to start there:

• MySQL T-SQL CASE Statement: Bad Language Design
• MySQL Development Tools - The Good, The Bad and The Ugly
• Why not to learn MySQL?

I wonder how many comments of this sort I'll get?