An ideal summer day is when the sun is shining, the warm sunshine refreshes your body, the light breeze becomes naught with your hair, the birds sitting on intoxicated leaves smile at you, and you to top all of that you get the latest hot issue of the Log Buffer. Yes to put cherry on your cake, Log Buffer #227 is here.

Oracle:

David Kurts tells us about applying Hints to Objects inside Database Views.

Jonathan Lewis answers another burning question on on multi-column bitmap indexes and the inability of Oracle to create …

I just finished my last MariaDB 5.3 feature before we go beta:

Progress reporting for ALTER TABLE, LOAD DATA INFILE etc.

This is a feature that I think every MySQL user who has ever waited for an ALTER TABLE to finish has wanted for a long time! I know people who have written scripts to monitor the size of the result files to try to estimate how much an ALTER TABLE has progressed.

Progress reporting means that:

• There is a new column Progress in SHOW PROCESSLIST which shows the total progress (0-100 %)
• INFORMATION_SCHEMA.PROCESSLIST has three new columns which allow you to see in which process stage we are and how much of that stage is completed:
  • STAGE
  • MAX_STAGE
  • PROGRESS_DONE (within current stage).
• The client receives out-of-band progress messages which it can display to the user …

There are many new features in MariaDB 5.3. I’m looking forward to many of them, but one of the ones I’m most excited about is Progress Reporting.

It’s a fact of life in the database world that some commands take longer to run than others. Commands like ALTER TABLE, LOAD DATA INFILE, and adding and dropping an index simply take time to run, depending (of course) on your data and schema. I always have hated having to wait for those commands to run with no indication of how much progress has been made or how much is left to do. All of that changes with the upcoming release of MariaDB 5.3.

In MariaDB 5.3 there is a new “Progress” column in the output of SHOW PROCESSLIST (this can be turned off) which …

Indexes can reduce the amount of data your query touches by orders of magnitude. This results in a proportional query speedup. So what happens when you define a nice set of indexes and you don’t get the performance pop you were expecting? Consider the following example:

mysql> show create table t;
| t     | CREATE TABLE `t` (
  `a` varchar(255) DEFAULT NULL,
  `b` bigint(20) NOT NULL DEFAULT '0',
  `c` bigint(20) NOT NULL DEFAULT '0',
  `d` bigint(20) DEFAULT NULL,
  `e` char(255) DEFAULT NULL,
  PRIMARY KEY (`b`,`c`),
  KEY `a` (`a`,`b`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Now we’d like to perform the following query:

select sql_no_cache count(d) from t where a = 'this is a test' and b between 8000000 and 8100000;

Great! We have index a, which cover this query. Using a should be really fast. You’d expect to use the index to jump to the beginning of the ‘this is a test’ values for …

During my years at MySQL AB I had the unfortunate task of manually maintaining the download page for enterprise customers. This involved a ton of boring, error prone work and almost always led to some sort of error every release. Some of our downloads were eventually replaced with an automated system written by the web team but the memory of all that time wasted still hurts me. So when I joined Monty Program and saw our downloads were manually maintained in mediawiki I knew something had to change.

Most of the websites for Monty Program and the MariaDB project are written with Django so this is where I started. I used our existing website code base and just created a new django application for downloads.  There are many models / tables involved in the system but the important ones are:

• Releases: A list of all the releases we have made, i.e. MariaDB 5.2.7, MariaDB …

This is a tale of the bug lp:798213. The bug report has the initial report, and a summary of the real problem obtained after detailed analysis, but it does not describe the processes of getting from the former to the latter. I thought it would be interesting to document this, as the analysis of this bug was rather tricky and contains several good lessons.

Background

The bug first manifested itself as a sporadic failure in one of our random query generator tests for replication. We run this test after all MariaDB pushes in our Buildbot setup. However, this failure had only occured twice in several months, so it is clearly a very rare failure.

The first task was to try to repeat the problem and get some more data in the form of binlog files and so on. Philip kindly helped with this, and …

I’ve done a minor update to the hdlatency tool (get it from Launchpad), it now has a –quick option to have it only do its tests with 16KB blocks rather than a whole range of sizes. This is much quicker, and 16KB is the InnoDB page size so it’s the most relevant for MySQL/MariaDB deployments.

However, I didn’t just remove the other stuff, because it can be very helpful in tracking down problems and putting misconceptions to rest. On SANs (and local RAID of course) you have things like block sizes and stripe sizes, and opinions on what might be faster. Interestingly, the real world doesn’t always agree with the opinions.

We Mark Callaghan correctly pointed out when I first published it, hdlatency does not provide anything new in terms of functionality, the db IO tests of sysbench cover it all. A key advantage of hdlatency is that it doesn’t have any …

By popular request, here’s the PDF of the slides of this talk as presented in January 2011 in brisbane; it’s fairly self-explanatory. Note that it’s not really extensive “tuning”, it just fixes up a few things that are usually “wrong” in default installs, creating a more sane baseline. If you want to get to optimal correctness and more performance, other things do need to be done as well.

OLX, a free classifieds site, is serving up 40 million pages a day using MariaDB. Not an insignificant task.

There’s a nice write-up in the MariaDB knowledgebase with particulars. In short, the 5.2 series of MariaDB and some of the unique features of the project have made a migration easy and valuable.

It’s nice to hear such stories. Both because we like interesting sites and projects, as well as our natural interest in larger scale or larger visibility deployments. Got a story to share? Please create a KB entry, or e-mail the community team.

Quick pointer that MariaDB 5.2.7 is now released. Highlights from the release notes: RHEL 5 RPMs (in addition to the CentOS 5 RPMs), and the inclusion of the HeidiSQL GUI client for the Windows MSI package. As always, the complete changelog, and what are you waiting for – download it now and give it a try!