Well, it’s that time of the year to get excited again!  Anyone involved in the MySQL world obviously knows our big User Conference is coming up April 14 – 17 in Santa Clara, and this year Jay Pipes and crew have outdone themselves again by working with our great community, customers, and partners to put together an outstanding series of sessions, tutorials, BOF’s, and more. 

As usual, it’s going to be tough to make decisions on which sessions and such to attend, so if you’re a MySQL DBA, Architect, Performance Analyst, or other MySQL sysadmin, I thought I’d try to help and point you to some sessions that I know are going to help you in your DBA job.  Rather than break it down by subject matter/discipline like I did last year, I thought I’d work through each day and try and highlight some of the sessions that I don’t think you’re going to want to miss.

Tutorial day

Those of you having or …

Now that the GA (General Availability) release of MySQL Workbench is coming up it is a good point in time to discuss what the commercial Standard Edition subscription will be all about and what will be covered by it. While I am not a lawyer I will try to explain the ideas behind the concept. Please make sure to read the subscription details when you get it.

Key points:

• USD 99
• Single seat license (license packs for a large number of seats (>30) possible)
• Software never expires
• Includes all updates of Standard Edition for a full year

The Basics

After the official launch you will be able to buy subscriptions in the MySQL Online Shop. Apart from payment with standard credit cards it has been updated to also allow for PayPal payment. A subscription will cost USD 99 (good for people in Euro countries, I know) and is valid for a single seat. If you are a bigger …

Properties:

Description:

One of the Peter's "what to …

It is interesting to read RedmondDeveloper News’s take on Oracle’s attitude to open source this morning, especially this paragraph quoting Monica Kumar, Oracle’s senior director for Linux and open source product marketing:

“”We haven’t seen our customers asking for open source databases,” she told me. “Not many customers are interested in looking into the code and mucking around with it, and making changes to it. All they care about is ‘give me the best support, give me the lowest price of entry’.” For that Kumar pointed to Oracle Express.”

It is difficult to disagree with the second part of Monica’s statement. Cost savings are routinely cited as the biggest driver for open source database adoption, while the lack of robust support is the biggest barrier to open source adoption.

Certainly these were the findings of our …

While I was updating my blog recently, I read the top part of it which said "My adventures in database developing". I realized that I have really neglected database developing with all my research into other programming languages.
So I was trying to play around with SQL statements to brush up on my skills.

Luckily (or unluckily), someone at my work, a java developer, needed to do a data export and thought that I can do it in 1 SQL line. He said.... and I quote "You only need 1 SQL line. Whats the problem?".
Whenever I hear the "whats the problem" line, there usually is one.
So, with him having great confidence in my SQL-ing ability or a complete misunderstanding of database developing, I was forced to do the data export.

I realized early on that it would take a long time. Just to start off, I needed like 20+ columns, but each column was a row somewhere in 4 different tables.

…

I'm now at Sun Tech Days St Petersburg. MySQL got involved in it on a very short notice so there are no MySQL sessions or tutorials, and we only have a small booth. It's decently manned though, there are six MySQLers here in total and we have no less than three people on the booth at nearly all times.

We organized some ad hoc sessions, so far, Alexey (aka Kaamos)'s talk about MySQL and dtrace attracted the most listeners, and Kostja was the second with his talk about new MySQL 5.1 features.

We get lots of questions on wide range …

I am one of the 10 jury members for the first international Hackontest. Basically this is a 24 hour open source coding competition that takes place on September 24/25 here in Zurich. However in order to make it to the competition a first hurdle is to get "past" the jury, which includes Jeremy Alison and Bruce Perens, who get to select the top 3 most promising teams on August 1st. So if you think you got what it takes, head on over to the Hackontest site and register your team! All my favorite OSS projects (PHP, MySQL and PostgreSQL) have not registered teams yet.

Aside from being a general all around cool thing for open source and a fun challenge for the teams that make it to the competition, this is also pretty exciting for me personally of course, since the jury holds many people that I would also …

Here is a situation I’ve run into a few times when dealing with mysql databases. We’re trying to run a one-off query against a high-traffic, large table and the WHERE condition is against a non-indexed field. Let’s say our table is 5GB in size. We issue the following:

SELECT count(*) from five_gb_myisam_table WHERE non_idx_field = 'asdf';

and we wait…

and wait some more.

5GB is not a small table, but this ideally should not take more than a few minutes on a relatively modern system.

iostat is your friend

In cases like this, iostat -x 5 is your friend. While the query was running, this was a typical 5 second interval:

avg-cpu: %user %nice %sys %iowait %idle
2.30 0.00 1.30 96.40 0.00

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.70 …

MySQL Proxy, in addition to dealing with the packets sent between client and server, can optionally send text messages to the terminal window where it was launched. These messages, using the built-in function print(), can be very useful when you develop an application, because can give you information on what is going on. However, when the script is well tuned, all these messages can be distracting and even annoying.
OTOH, if you plan to extend the development of the script, leaving the telling messages in place can be very useful. One handy solution is to include conditional print messages, controlled by an environment variable.

1. Change all occurrences of print to print_debug;
2. Create a function print_debug that will print the message depending on the value of a local variable DEBUG;
3. At the start of the script, initialize the DEBUG variable from the …

I’m happy to report that my patch for a potentially nasty bug in the mysqlhotcopy script has been accepted into the mysql codebase. It’s a great feeling to finally contribute something, however small, back to the mysql community.

If you have a master/slave environment with multiple slaves, and you do periodic backups of your system on the slave with mysqlhotcopy, the –record_log_pos parameter will pull the wrong co-ordinates from the master and cause you to restore new copies of your database that are potentially corrupt.
An example failure scenario can be found here.