The latest from the MySQL community

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 …

Better late then never: I've now uploaded a few pics that I've taken during this year's FrOSCon to my gallery. Have fun!
 

Monty Taylor posted interesting investigation of the fact read_buffer_size variable affects connection speed.

This is not something you would expect right ? me too. Not only global user data is expected to be cached on startup but even if it is not why would you do full table scan to fetch single user information ? Something is fishy here and perhaps bug needs to be filed either resulting in fixes or docummentation updates.

The great thing you see from this post is - o_profile (often together with strace) are great tools for MySQL Performance analyses. Too many people end tuning on plans and indexes while there well could be some internal gotchas.

The next Monty comes up with 2 posts on …

Using the instructions followed in JRuby Hack Day and taking some help from Nick, I figured out how to use the JDBC connection pools configured in GlassFish using the JNDI names.

All the commands given below are relevant for GlassFish but the same concept will work where ever you deploy your WARed up JRuby on Rails application.

1. Follow the bullet #1 and #2 from …

my girlfriend's grandfather wants a blog!