We had our first ever MySQL User Conference in Japan this week, put on by our very own Larry Stefonic (Larry-san as we call him) and our all-star Japanese sales & marketing team.  We had several hundred people at this multi-day event and it was standing room only.  There were keynotes by Marten Mickos as well as Yukihiro Matsumoto (Matz) the author of Ruby, the director of HP Japan and the General Manager of Sumisho, one of our top …

I’m working on a project at the moment where we’re doing an upgrade and need to do the dump-and-restore method. It’s 300G, so that’s never going to be fun, but I found a tool today that helped significantly.

Multi threaded mysqldump is not an utopia any more. mysqlpdump can dump all your tables and databases in parallel so it can be much faster in systems with multiple cpu?s.

I ran mysqlpdump (with one patch I’ll send in soon to put quotes around table names) today with 16 threads on a 4 core system and did all 300G in ~3.5 hours. Additionally, since it wraps mysqldump but iterates over the tables, I got a sql file for each table, which is going to make writing a script to restore a piece of cake. It understands that I wanted to do –master-data and it had an option to gzip each sql file as it went.

All in all, I’m thrilled. kudos! And thanks for the tool.

…

I didn't realize InfoWorld was doing open source awards, until I read about it in Dave's blog.  But here they are.  And looks like MySQL won as best database.  Check out the "BOSSIE" awards.

• InfoWorld: Blog, …

Microsoft and Sun expand partnership. QNX open sources realtime operating system. Sun acquires Lustre file system. (and more)

Microsoft and Sun Expand Strategic Alliance, Microsoft / Sun Microsystems (Press Release)

QNX Publishes Neutrino Source Code and Opens Development Process, QNX Software Systems (Press Release)

Sun Microsystems Expands High Performance Computing Portfolio with Definitive Agreement to Acquire Assets of Cluster File Systems, Including the Lustre File System, Sun Microsystems (Press Release)

Microsoft and Novell Open …

I thought of a brilliant QA plan. Create sql rainbow tables! Just trust me, this system of QA will be very powerful in bug finding. Especially important will be the comparison of old/new versions of mysql. Writing a prototype now, on my day off.

Just during prototype development, I've discovered a handful of bugs. I'm testing all functions that are documented in the manual. This is alot that I've written up:

mysql> select count(*),category from func group by category;
+----------+--------------+
| count(*) | category     |
+----------+--------------+
|        6 | arithmetic   |
|        7 | bit          |
|       60 | casting      |
|       47 | comparison   |
|       52 | datetime     |
|       18 | encryption   |
|      127 | geometry     |
|       17 | information  |
|        7 | logical      |
|       32 | mathematical |
|        9 | misc         |
| …

What if you have to customize the sequence of order by clause on a column. Say, you have a integer column 'weight' that can have values ranging from 1 to 5 and you want to get the rows in the sequence 4,2,5,1,3. So what do you do? Here is a simple trick using the field function.

1: mysql> SELECT * FROM table ORDER BY FIELD(weight, 4, 2, 5, 1, 3);

Now what does it do. Field function returns the index of the first argument in the rest of the list. So, effectively the order by logic will be the same, just that the values that are being ordered are generated on the fly.

Other approach can be to use CASE.. WHEN .. END statement as mentioned in the MySQL Documentation. The above code can be rewritten as:

   1: mysql> SELECT *, CASE 

   2:     -> WHEN weight = 1 THEN 4

 …

Quite frequently I run into question like this "I'm using SQL_NO_CACHE but my query is still much faster second time I run it, why is that ?

The answer to this question is simple - because SQL_NO_CACHE only bypasses query cache but it has no change on other caches, which are
MySQL Caches - Innodb Buffer Pool and Key Buffer are best example though Falcon, PBXT and other storage engines have similar buffers. There is also table_cache both MySQL side and Internal Innodb one which can affect query execution speed.
OS Caches Operation Systems typically cache file IO unless you explicitely bypass it by using O_DIRECT flag or mounting file system in direct IO mode.
Hardware Caches State of CPU cache may affect query execution speed but only lightly, the hardware IO cache may however cause dramatic difference. Hardware RAID cache is the one but more important …

So you use InnoDB, have indexes on your table, think of row level locking and concurrent queries, feel good and go to sleep. All this while forgetting that even UPDATE and SELECT .... FOR UPDATE statements will (or may) also use the same index for scanning or updating. Then what? You may ask.

Well, InnoDB row level locking works in a somewhat different manner when using indexes. In this case, InnoDB locks index records in shared or exclusive mode when searching or scanning an index. Therefore, as mentioned in MySQL Documentation, the row level locks are actually index record locks.

To complicate matters (or resolve issues) further, the lock is a gap lock. A gap lock refers to a lock that only locks a gap before some index record.

As per the example in …

Whenever we have seminars or events on performance tuning of MySQL we always get a good crowd. We've got two performance tuning seminars in the afternoons next week in DC and Boston on Tuesday and Wednesday. There are also morning sessions targeted to managers who want to reduce risk and costs. These are free events and are targeted primarily to OEM / ISV users who embed or bundle MySQL. Still, they are a good way to get some tunings tips and meet others. We'll also figure out a way to get this content online for folks outside of these cities.... READ MORE

Tomorrow, Thursday 13th Sept. at 13:00 UTC (15:00 CEST/9:00am EST/6:00am PST) Jan will perform a MySQL University session providing an Overview of the MySQL Proxy. If you like to attend, please add your name to the session page and read the Instructions for Attendees. All you need is a PDF viewer to see the slides, an IRC client to post questions and comments and an application capable of playing an OGG audio stream.

Version 0.6.0 of the proxy has just been tagged and we're waiting for the mirror sites to catch up before we publicly announce …