The latest from the MySQL community

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 …

Sometimes you need to have the general query log on and even though it causes more disk I/O than you may want, it’s good for troubleshooting. This log can and probably will fill up your disks rather quickly. Then there’s the slow query log - setting log_slow_queries and log_queries_not_using_indexes will write out the queries that take longer than long_query_time to execute, as well as any query not using an index.

So, since MySQL does not apply the expire_logs_days value to these logs - only to the binary log (log_bin), we need another solution. There are probably a bunch of custom scripts out there that do this, but big surprise - we have one as well. This was originally written by Jim Wood until I got my hands on it and made some changes. The changes are listed in the head of the script. This little guy will rotate the logs out to another directory and gzip them. …

Yeah, I'm givin' another one of those webinars today at 1pm EST/10am PST on Performance Coding for MySQL. The webinars are FREE, they're FUN, and I have new slides...mostly!

The slide deck I am using today is a cut-down version of the performance coding session I gave at CodeMash a couple weeks ago. The full slide decks are available in OpenOffice Impress and PDF format below. Enjoy.

• OpenOffice Impress slide deck
• PDF slide deck

This is one of the new slides, which contains a picture of the pygmy marmoset, the world's …

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 …