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 via SELECT FOUND_ROWS();  That simply reads the variable and clears it on the server, it doesn't actually have to look at any table or index data, so it's very fast.

…

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 …

Generally I avoid going down rabbit holes but today I decided to see how deep a particular testing rabbit hole went. This post is a third in what seems be a continuing series of programming anecdotes. It’s not particularly MySQL-related so you can stop reading here unless you grok code stuff.

Before beginning work on issue 720 I ran the mk-table-checksum test suite to make sure it was in working order. No sense writing new tests and code when the old tests and code aren’t reliable. I actually made one seemingly innocuous change to the test suite in preparation for the issue: I changed the –replicate checksum table from MyISAM to InnoDB.

Surprisingly, the test suite proved unstable. Random tests would fail at random times. Some instability was due to new tests for …

In part 1, I discussed why having many disk seeks are bad (they slow down performance), and how fractal tree data structures minimize disk seeks on ad-hoc insertions, whereas B-trees practically guarantee that disk seeks are performed on ad-hoc insertions. As a result, fractal tree data structures can insert data up to two orders of magnitude faster than B-Trees can.

Now that insertion disk seeks are out of the way (and I don’t want to shortchange the importance of getting rid of these seeks!), let’s look at other places where databases perform seeks, and see if we can get rid of them. Over my next couple of posts, I will look at several use cases and analyze whether disk seeks are required. If disk seeks are required, then performance will suffer on large amounts of data, for TokuDB and any other disk-based storage engines.

The problem: Many a times people find that they create index but the query is still slow or the index is not being used by MySQL to fetch the result-set. Mostly the reason is that the index is not created properly, either not the right columns being indexed or the order of columns in the index does not match how its being used in the query. The order of index! What’s that. Well that’s what we will be discussing today. How does the order of column in the index matter? The order of columns in the index matters a lot,...

What’s missing from WebM? VoltDB launches. The importance of profitability. And more.

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca
“Tracking the open source news wires, so you don’t have to.”

# Simon Phipps examined what’s missing from WebM, from an open source perspective.

# Mike Stonebraker’s VoltDB officially launched its open source in-memory OLTP database.

# Jim Whitehurst argued that one of Red Hat’s most valuable contributions to open source is its profitability.

# Infobright appointed former Aleri CEO Don DeLoach as its new …

The latest news for Drizzle!

We completed the Cherry roadmap about a month ago. Right before the MySQL User's Conference. Cherry was out last big release before we were to begin work on finalizing the Beta (aka Dexter).

What is in Dexter?

The Dexter release is all about stabilizing Drizzle so that we can get a Beta shipped out at the end of the summer. Stewart has been working on switching our core Innodb to use the downstream version of the Inndb Embedded Engine (aka HailDB. You can read more about HailDB here. With much of the recent speculation and rumors …

The traditional way to build Cacti templates is through the Cacti web interface. This is an enormous amount of work, and the result is generally not very consistent or good quality. The process is too error-prone. You can export the templates as XML, but they tend to have problems such as version incompatibilities with other Cacti installations, and it’s hard to adapt them for user preferences such as different graph image sizes and polling intervals.

The way I build Cacti templates is exactly the opposite. I create a data structure in a file, which looks like many configuration file syntaxes you’ve probably worked with. It represents the graphs, templates, scripts, and so on. From this, a tool generates the XML template file, which is a universal template definition, and is a breeze to import into Cacti. It is completely consistent and has zero cruft in it. This process prevents errors, and the results are perfect every time. …

I don't know if you have used my MyQuery query tool, which is a MySQL Query and Scripting tool for Windows environments, but that is what has taken up most of my spare time recently. It started with an idea of adding a feature to be able to run any custom SQL SELECT in a non-modal dialog, for customized monitoring of MySQL.

This turned into a much more extensive custimization feature, enabling scripts, plugins, external programs and web-links to extend MyQuery. I'm now pretty close to code complete with this feature, and I think it's rather cool. But in the process of getting this done, and to enable as many features as possible in the extensions, I had to clean up quite a bit of code in other places, where things just weren't as self-contained as I had wanted them to be.

The main things that remains to be done before releasing MyQuery 3.3 is documentation, testing and some cleanup. And I don't know if you've noticed, …

...NoSQL is just larger than a fly's dropping, and MySQL and Oracle are more alike than either of their respective fanboys would like to admit.

Courtesy of Google trends:



I guess I won't be changing my career just yet.


UPDATE: I tried a few terms for "Microsoft SQL Server" before posting (SQL Server, MS SQL) but found none that came up with what I felt like was a realistic volume (they are all much, much lower than I expected). @MarkGStacey suggested trying "SQL 2008", "SQL 2005" and "SQL 2000", and those return much better results indeed (though still much lower than MySQL or Oracle). Anyway - I'd love to have some way of bunching up all those terms and have …