The last two weeks I have been working on a .NET application using MySQL (3.21) for data storage. The application is basically a stand-alone PC application where MySql is used to store a lot of data from test equipment.

This is a bit different from what I spend most of my time on. No web site with lost of concurrent session, but instead a lot of installations of the software on local PC's. The also means we can not tune parameters on a live system, we have to set them before delivering to customers. Basically this is the main problem, since we don't know how the system will be run.

Well, if the scenario is different, the most important action was the same. Changing the field order in one of the indexes to match the queries better was our number one action, and the impact was larger than all other actions together. If your indexes are not righ, you should fix it before anything else.

An upgrade to MySql …

So the other day I stumbled over the slides of the InnoDB talk at this years MySQL users conf and I noticed "gap locking" somewhere in the middle. I have never heard of "gap locking" so I was quite intrigued by what that might be. From what I understand its InnoDB's solution to implementing REPEATABLE READ (though until MySQL 5.1 it seems this feature is also enabled for READ COMMITTED). I guess its a fairly unique approach and from my current understanding to be feasible it expects short running transactions, which luckily are quite common in web applications that most of us care about.

The problem InnoDB is trying to solve here are situations where overlapping transactions cause phantoms. Now the first surprise was when I read that gap locking is implemented by locking the relevant ranges on the index or more particular …

Paul Doscher, CEO of JasperSoft, has proved me wrong. Two years ago when I joined as an advisor to JasperSoft, I worried that a company so new to open source (despite the Jasper community on which it was built) would struggle to truly get open source religion.

Paul, however, has adeptly made the shift from CEO to open source CEO, with JasperSoft doing exceptionally well in the market in consequence. In this eighteenth installment of our Open Source CEO Series, I talked with Paul about the transition.

Name, position, and company of executive
Paul Doscher, President and CEO, JasperSoft, an open source Business Intelligence company.

Year company was founded and year you joined it
JasperSoft was originally founded as Panscopic Software in 2001. I joined the company in February 2004 and led …

If you haven’t got MySQL Proxy yet, then stop and get it now. Jan announced the release a few days ago of this new product offering from MySQL.

I first heard about MySQL Proxy at the recent MySQL Conference 2007 and actually used it a few weeks later to help address slow running queries during benchmarking with a granularity of milliseconds — Wow. The product has grown immensely since then and I’ve watched in true amazement at the speed of development by Jan, who I only found out recently was the creator of …

I was reading some points on a Blog post today here. I didn’t see it via Planet MySQL but via a Google Alert.

The post has several good beginner points on MySQL Performance, but included the point “Literal current date is better than using the CURRENT_DATE() function because literals will use the query cache but functions won?t.”

How true, I must admit I’d not considered this in reviewing code for sites that use the Query Cache heavily. Non deterministic functions invalid use of the Query Cache (Except for recent Bug #29053 which is also an interesting read. Note to self, read the new bugs more often when free time permits). Back on point.

So if you have a query like “Select the news for …

Untangle releases open source security software for SMBs. Trolltech to offer Greenphones to select open source community members. GroundWork unveils training program. (and more)

Untangle Brings the Open Source Movement to Small Business Network Security, Untangle (Press Release)

Trolltech Offers Open Source Community a Mobile Development Platform with Qtopia Greenphone Grant Program, Trolltech (Press Release)

Groundwork Breaks Ground On Groundwork University, GroundWork Open Source (Press Release)

…

• compressed backup patch
  • actually works rather well… and restoring from compressed backup too.
  • need to modify the rate-limiting code though… may as well rate limit the writing of the *compressed* data stream… otherwise the option isn’t nearly as useful
• compressed LCP patch
  • well… the *restoring* of compressed LCPs…. can write them
• working out exactly what more information I want out of the linux memory manager to find out what kswapd is really doing (and the patch that exports the right info)
• re-jigging my procmail filters for commits@lists.mysql.com
• fixing up my offlineimap patch and getting it in upstream
• disk pre-allocation for MythTV recordings
• buying workstation
• unpacking the last few boxes around the house
• finishing this …

Over the past N weeks/couple of months, we’ve been making a number of improvements to how backups are done in MySQL Cluster.

Once you get to large data sets, you start to really care about how long a backup takes.

Traditionally, MySQL Cluster has been in-memory only. The way to back this up is to just write from memory to disk (rate limited) and synchronised across the cluster.  Since memory is really fast (compared to the rate we’re writing out to disk) - never had a problem.

In MySQL 5.1 (and Cluster Carrier Grade Edition- CGE), disk based attributes are supported. This means that a row has both in memory and disk based parts.  As we all (should) know, disk seeks take a very long time. We don’t want to seek.

So, at some point recently we changed the scanning order from in-memory order (which previously made perfect sense) to on disk order. Randomly seeking through RAM is much cheaper than all the …

I just posted version 3 of mysqlbackup to MySQL Forge.

Small bugfix: Added option --add-drop-table to the default options for mysqldump. This was causing a failure in restoring views.

I would like to remember this day. It was just crazy. Most of my day went into troubleshooting InnoDB locking issues. At this point, the situation is all clear and I am very pleased with the results. Here are two graphs from MySQL Network Monitoring tool:

Keep manageable threads/connections and your server will be very happy.