We’re Open Sourcing some command line tools for accessing some modern system calls on Linux.

The project is named linux-ftools and is over on Google Code.

You’ll have to checkout the hg repo locally and build yourself for now.

Specifically fallocate, fadvise, and fincore (and a few other smaller tools).

They need a bit more cleanup , specifically error handling (and maybe a few more features).

I’d like to try to get these into Debian.

These were designed for use with MySQL but you might be able to use them in your setup as well.

…

This blag was originally posted at http://cafuego.net/2010/05/26/fast-paging-real-world

Some time ago I attended the “Optimisation by Design” course from Open Query¹. In it, Arjen teaches how writing better queries and schemas can make your database access much faster (and more reliable). One such way of optimising things is by adding appropriate query hints or flags. These hints are magic strings that control how a server executes a query or how it returns results.

An example of such a hint is SQL_CALC_FOUND_ROWS. You use it in a select query with a LIMIT clause. It instructs the server to select a limited numbers of rows, but also to calculate the total number of rows that would have been returned without the limit clause in place. That total number of rows is stored in a session variable, which can be retrieved …

Recently Kostja posted two insightful blog posts about his thoughts on the currently fragmented MySQL landscape and quality of a piece of code contributed by a “community member”, which is a MySQL euphemism for a person not employed by MySQL. (Hence, the full time MySQL developers are themselves not members of their own community?)

I wanted to comment on both posts, but found out Kostja only allows logged in LiveJournal users to comment, which I am not. Since the posts were interesting enough, I suppose they deserve a comment in a new blog post like this instead.

From “RDBMS software is difficult” (slightly reordered)

The main reason it is harder to do changes with MySQL is a …

I’ve been meaning on posting about this for a while now but I finally have a good tool to help visualize this problem (seekwatcher).

MyISAM continues to append to the .MYD file as you write to it. Which seems pretty easy to manage from a performance standpoint because if you’re writing 1 file on one disk it will be 100% contiguous.

But what happens if you’re writing 100 files? or 1000? The file becomes fragmented on disk (in a more pure sense, a fresh disk) because each new write is stacked up on top of the previous file’s write.

What needs to happen is that MyISAM needs to fallocate 5-10MB at a time. This way for at least the next 5MB you have a large chunk of contiguous disk to use.

This isn’t just theoretical. Check out the following video. This is on a 11 disk RAID …

When asking about up-time requirements set down in SLAs (Service Level Agreements) with our clients’ clients, we’d hear anything ranging from hours to the familiar five nines, but these days also simply 100% and otherwise penalties apply. From my perspective, there’s not much difference between five nines and 100%, 99.999% uptime over a year amounts to a maximum of little over 5 minutes outage. In many cases, this includes scheduled outages!

So, we can just not have any outages, scheduled or otherwise. Emergency support is not going to help here, because however fast and good they are, you’re already in serious penalty time or well on your way to not having a business any more. Most will respond within say 30 minutes but then need up to a few hours to resolve the issue. That won’t help you, really, will it? And in any case, how are you going to do your maintenance? The answer is, you need to architect things differently.

…

Jack Wallen from TechRepublic put together a Top-10 list of his favorite cross-platform applications. It is humbling to find MySQL Workbench in the company of such popular products like FireFox, Chrome, and Open Office.

Find his full list of applications here.

We can learn a lot from the other app in that list, e.g. we also want to be like FireFox in building a big set of community developed plugins. That is the reason we designed MySQL Workbench in a modular way and made it fully scriptable.

We are currently in the process of updating the documentation of how to write plugins and the scripting API. Watch out for more posts on this topic soon.

In my time at MySQL AB in the Community Relations possition (2004-2006) I wrote several articles on MySQL’s licensing for the MySQL web site. The core reason for having to explain anything was (and still is) the dual licensing of MySQL, in particular the client library. I left MySQL AB years ago, but people still ask me licensing questions. Below is an excerpt from one such question, and my response.

> Hi, Found a post on the mysql website from Arjen Lentz to do with the whole
> mysql licensing question.
> Do you know if the issue with, php scripts (that use a mysql database) issued
> under a proprietary license require you to have a commercial license for
> mysql, or will the issues be covered for the GPL version through the fact
> that the scripts run via php which in-turn connects to the GPL mysql server
> for which the FOSS exception applies.

Note: I am not a …

As the program chair for the recently announced MySQL Track at the ODTUG Kaleidoscope conference located in Washington DC we are also looking into an associated free community event for MySQL locals in addition to a dedicated track for 4 days.

Please let us know your name and email via the form at http://ronaldbradford.com/ODTUG/free-event/ so we can provide more details in the coming week as we try to finalize logistics.

Registration will be necessary for attendance however for now we just want to know who is local so we can provide more details soon!

Updated. Full details of the free Monday night sundown sessions and reception can be found at MySQL track with free event at …

A crosstab query is a specific query used to create aggregate reports on two or more fields, it’s  a handy way to display summary information. At Open Query we have customers using that trick to display production schedules.

The summary table is generated from the database to extract the manufacturing date (mand), unit number (unitn), product area (pro_area), and ranking (rnk). Then we can start using that summary table to process our crosstab. The summary table looks like this:

From Sunday 11.4.2010 to Thursday 22.4.2010, I used my Twitter account @kajarno to send

• 26 Swedish tweets
• 41 German tweets
• 69 English tweets

(not counting direct replies) of which a clear majority (but not all) were of a volcanic nature.

Most Swedish readers can follow German and English too, and most German readers also follow English. Hence the large number of English tweets and the larger number of German tweets.

Here’s a subset of the English tweets. The dates and times are local (California 9 h different from Munich; Chicago 7 h different).

Thursday 15.4.2010

• 13:12 Kaj Arnö wonders whether LH459 will leave at all MUC-SFO tonight 21:00, and if so, which route #iceland #volcao #ash
• 20:18 Damn, flight was cancelled. #lh #iceland #volcano SFO-MUC LH459 …