So far, I’ve analyzed point and range queries.  Now it’s time to talk about insertions and deletions.  We’ll call the combination updates.  Updates come in two flavors, and today we’ll cover both.

Depending on the exact settings of your database, the updates give a varying amount of feedback.  For example, when a key is deleted, all rows with that key are deleted (assuming the database allows duplicate keys).  The normal behavior is to return the number of rows deleted.  The normal behavior when deleting a key that has no corresponding rows in the database is to return an error message.  On insertion, one can allow duplicate or not.  In the latter case, the storage engine returns an error message if a duplication insertion is attempted. 

We’ll see that the details of error messages have a profound influence on the lower-bound arguments I’ve been making (and we’ll see a bit …

Following up on yesterday’s post on the MySQL slow query log, I’ve been doing quite a bit of analysis of our slow query log entries.

To make sure we catch everything, we’ve also turned on log_queries_not_using_indexes. This gives us a couple of problems

• Partly because we’re at an early stage of deployment, some tables are so small that even though the correct indexes are defined, they’re not used.
• Certain tables are updated frequently, with lots of inserts and deletes taking place. Most of the time, the tables in question contain relatively few rows, again causing the optimizer to ignore the defined indexes and the slow query log to be updated.

In other words, we’re having a boatload of false positives — just a quick analysis reveals more than 100k of the 144k queries picked up so far by the slow query log are …

I am very pleased to announce that we - that is, our excellent documentation team - set up the errata page for the MySQL 5.1 Cluster Certification Study Guide!. For those that don't know, this guide is the official and authoritative guide to prepare for the Certified MySQL 5.1 Cluster DBA exam. Apart from being a study guide, it is probably the most up-to-date text on MySQL Cluster.

As more people are buying a copy, we are receiving more and more feedback. It's mostly …

Have you ever wanted to play around with(test ;) MySQL replication/clustering techniques, LVS/Apache load balancing etc but didn't have the hardware available and where smart enough not to use a production environment?

Well an easy way to be able to do this is by creating a sandbox environment using something like qemu, xen, vmware or UML which allow you to create virtual machines running inside a protected environment on your own desktop machine or whatever hardware you have spare, just be sure you have enough memory. This article will cover setting up a sandbox using UML.

My GNU distro of choice is Archlinux and this article will be based around it, but you should be able to take the …

I came across this very thought provoking blog post by Brain Aker of MySQL and /. fame. I think he raises some very important points about OSS development and how to create a successful new project. However some of the advice can also be applied retroactively to existing projects. I especially find his comments regarding "old boys clubs" important. Sometimes when you have a successful project it becomes hard to manage the influx of new people. So the old boys start to feel most comfy when they ignore the new guys. Patches are a good basis (though you still need people to have a look at the patches) of judgement to let new guys in. After all with good patches the project should be moving forward. I think in PHP we have a very similar policy to the "Three Commits, Ding, Ding, Ding" policy Brain applies. And that is a good thing.

A bit more on the "old boys club" stuff …

I was looking today at the set_current_time() call in memcached looking for a bug and noticed this bit of code:

new_time = (rel_time_t) (time(0) - stats.started);

What is the issue? Time!

No two time calls cost the same.

The difference?

gettimeofday() 14.558
time() 14.664
clock_gettime() 13.958

All of these were compared in a loop. The obvious winner is clock_gettime() though it Linux specific so an ifdef is needed so that other platforms can use gettimeofday().

I suspect it can be made to be faster :)

Evgen Potemkin is a developer in query optimizer team at MySQL, we've been working together for about 3 years now. So, it was a bit unexpected to read in the PostgreSQL news that (originally) his CONNECT BY patch has made it into PostgreSQL. No, we don't have people coding for two DBMSes at the same time (yet?), the patch has been developed years before Evgen has joined MySQL.

One of the questions raised by the prospect of becoming a part of Sun was whether/how we will handle being a part of company that develops other DBMS as well. I suppose this patch news sets a good start.

UPDATE
It turns out the patch is not in the PostgreSQL tree after all. It is just people at www.postgresql.at (which is NOT just a different name for www.postgresql.org, and that was my error) merged the patch into a 8.3 version of PostgreSQL and made the …

I've finally found time to put together a couple of wiki pages describing what work we're doing for subquery optimizations in MySQL 6.0:

• The Subquery_Works page has an overview of what we've done so far, we're doing now and our future plans.
• The 6.0 Subquery Optimization Cheatsheet has a short, easy-to-read description of what has been released in MySQL 6.0.3. That should be enough if you just want to get the new version and see if your subquery is/can be faster now or not. If you want to know more details, the nearest occasion is my MySQL University session on February, 28.
• I've done a quick preliminary assessment of the impact of new optimizations. The …

As documented, the FLOAT datatype does not guarantee precise storage of decimal values. But where the non-precision is apparent can be a little confusing - take the following example:

mysql> CREATE TABLE my_table (a FLOAT);
Query OK, 0 rows affected (0.25 sec)

mysql> insert into my_table (a) VALUES ('2.2');
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM my_table WHERE a = 2.2;
Empty set (0.05 sec)

mysql> SELECT a, IFNULL(a,0) FROM my_table;
+------+-----------------+
| a    | IFNULL(a,0)     |
+------+-----------------+
|  2.2 | 2.2000000476837 | 
+------+-----------------+
1 row in set (0.00 sec)

mysql> SELECT a, a+0 FROM my_table;
+------+-----------------+
| a    | a+0             |
+------+-----------------+
|  2.2 | 2.2000000476837 | 
+------+-----------------+
1 row in set (0.00 sec)

Need precision? Try Decimal.

Today I attended a webex demo of MySQL Enterprise Monitor from MySQL. As most of the Yahoo’s are interested in learning about this tool, so arranged a web-ex demo from MySQL. MySQL is kind enough to host this event.

I always thought a dedicated monitoring and alerting system is completely missing from MySQL product line for all these days, and I can see that this tool is heading in the right direction to capture the market. Currently it monitors all server variables, errors and identifies any critical conditions upfront to avoid a disaster. But the new features that are in the pipeline for the coming months seemed to be promising (like upgrade adviser, Load balancing, query analyzer and connection manager).

As this is not like a single node …