EVENTs - The hidden MySQL 5.1+ feature

MySQL 5.1 and up has EVENTs, but this features seems to be little used. I started using them some time ago, and once I was using them, I found them more and more useful. There are a few bugs with them, I reported this one for example, and this alone tells me that probably not too many use this feature.

I will post a few example soon of things I use EVENTs for, but right now, let me tell you a few basics:

• One IMPORTANT reason I like them: In comparison to, say, crontab jobs, I run them inside the database itself, which I do not have to put MySQL passwords in files or scripts on the server. BIG advantage.
• For anything but the most simple of actions, write a procedure and then call the procedure from the EVENT, instead of having everything in the event itself, this just makes things difficult to manage.
• Error handling: the MySQL server error-log is your friend!
• Using the INFORMATION_SCHEMA tables instead of SHOW commands, where possible, making writing events easier and in many cases, this is the only way to do things. Simple cursors on INFORMATION_SCHEMA tables is the way to go. And have a second look at the INFORMATION_SCHEMA tables, there has been a whole bunch added over time since 5.1.
• The syntax is sometimes weird, but once you get used to it, it works.
• Have a look at the bug mentioned above. It may well bite you during development (which is one reason I recommened using a procedure at first, and then one this works as expcted, create the EVENT that calls this).
• For a DBA, events are cool and useful, and easier to use an crontab and also cross platform. As long as what you are doing can be done in a procedure, chances are that EVENTs are better than crotab jobs.
• The event scheduler must be running for EVENTS to work, and this is done by setting the variable event_scheduler to 1. Note that this is a dynamic variable, so this is valid syntax:
  SET GLOBAL event_scheduler=1;

/Karlsson
See you with some more event information soon

Oh no, what have I done! Or: My cloud evangelism got cloudy. Or: The dog ate my network..

At the recent MySQL User Conference, I had a talk on how we at Recorded Future use Amazon EC2 to keep our servers humming (the slides for the talk are available here). And of cource, Amazon EC2 turned back on me (and us all at RF) about a week later. I will not go into details, but somehow, we still don't know exactly why ("The cleaning lady unplugged THE SERVER to plug in the vacuum-cleaner", "The dog ate my network"?).

The thing has been down for 24+ hours now, and there is no end in sight, as far as I can tell. As I said in my talk, we are considering a move to Amazon RDS instead of running our MySQL servers ourselves, and one of my first reactions to this trouble was that we really should have done that already. That was until I realized that the Amazon RDS service was affected as well. Which all goes to show: The more things you ut in one SPOF, then more things will fail when that SPOF fails. And we are not alone, Reddit, Quora and many more in the Amazon us-east1 Availability Zone are in a similar situation. I wonder how the other database HA Solutions for Amazon survived (xeround at all)? Did they do OK or not, if they did, then this would be a selling argument for them. And if I was Rackspace (which I am not), I would launch a compaign right now...

Today we have been trying to set up our services in another Availability Zone. Our EBS disks are no good, but the snapshots are, so we should have something up an running real soon.

Cheers
/Karlsson
Who will stay with EC2, but will look at managing more things myself and to prepare for a solid backup plan, without Amazon intervention (Amazons statement that the Availability Zones are isolated wasn't really true, it seems). And who will not translate HA into "I let Amazon handle that"...

Large Datasets on Amazon EC2 talk slides are available here...

The slides from my talk on tuesday at the MySQL Users Conference 2011 on "Large Datasets on Amazon EC2" are available for download here.

I also want to thank everyone coming to my talk, as well as to the people arranging the conference and make it happen, and to all the sponsors. This conference is a blast, as usual!

/Karlsson

Getting the best of Clouds, Internet and BI

The internet really is full of stuff, and we are starting to fill the Clouds with all sorts of software that moves from the serverrooms and in to the clouds. One aspect here that is often missing is what a Cloud environment and the Internet in itself can add to what we already know and have. Or in other words, running in the cloud is not only about lowering costs, and if you look at it by lowering costs by running things in the cloud the same way you used to run it on your private server, then chances are you might not gain anything, running in a cloud is also about having distinct advantages not easily available somewhere else.

Christopher Ahlberg here at Recorded Future just blogged about another way of looking at it: What do we have access to on the internet that is not readily available in the Server room, and the answer is: data. A lot of data. For someone in the field of BI, not using Internet data, and sticking to traditional querterly results as the base for analysis, is really missing the point.

Read Christophers blog here for the full Monty (!). This is an interesting perspective, and way you look at it, and as an amateur computer historial, this is particularily interesting as one of the few commercial uses for the worlds probably only useful hydralic computer was just this: Using analysis to project sales. (This is actually largely true. Feel freee to ask me more on this last subject at the upcoming MySQL UC. This is truly weird geeky history!).

/Karlsson

Hey, it's time for the MySQL User Conference again! Come see me!

And I mean that, come see me, say hello, buy me a beer (extra points!). Or even more so, come see my session at 10:50 on tuesday morning April 12. I'll be speaking on how to manage large datasets in an Amazon EC2 environment, and this is largely based on my experiences at doing just that at my new job (or new, I've been doing it for more that 6 months now) as Database Architect at Recorded Future.

This will not be an incredibly technical presentation, in terms of showing actual code and things. Rather, I will look at some of the issues when running in an EC2 environment, and how we manage it here at Recorded Future. Also, I will present a bit of how our architecture works, which is more relevant that one may thinks, as we have Cloud based architectures on mind all the time, all our development, testing and productions servers run in the cloud.

Anyway, this is going to be a lot of fun, and if you have specific requests or questions before my talk, then drop me an email at anders@recordedfuture.com.

/Karlsson

Slave Readahead 1.2 available

Version 1.2 of Slave Readahead is now available for download here. If you don't know what this little project is about, it is used to pre-warm the MySQL Cache for the Replication thread on MySQL slaves.

It is built to support MySQL 5.5 and up only, as it uses some new commands in MySQL (like the SHOW RELAYLOG EVENTS admin command). For more information regarding this little project, either read this blogpost or download the documentation for the project.

/Karlsson

MyQuery 3.4.2 available

MyQuery, the ad-hoc query and scripting tool for MySQL, as always running on Windows, supporting servers on any platform, is now available in version 3.4.2. This version has a few minor bug-fixes in the "Break long lines" plugin that cleans up SQL text, and also a few additions to this plugin.
I have not heard of any issues with MyQuery itself since I released 3.4.1, but I will not take the 3.4 version into GA until I have fixed a few more things in the "Break long lines" plugin to turn it into a more full-features SQL beautifier with a lot more features than now.
Until then though, 3.4.2 is what you want, and the Compact Spaces option in the Break Long Lines plugin that is added with this release, should provide useful.

In addition to all this, if you want to meet up and discuss this tool, then I am at your service at the MySQL Usre Conference coming up next week.

Best regards
Anders Karlsson