So this is about a SELECT COUNT(*) FROM tblname without a WHERE clause. MyISAM has an optimisation for that since it maintains a rowcount for each table. InnoDB and PBXT can’t do that (at least not easily) because of their multi-versioned nature… different transactions may see a different number of rows for the table table!

So, it’s kinda known but nevertheless often ignored that this operation on InnoDB is costly in terms of time; what InnoDB has to do to figure out the exact number of rows is scan the primary key and just tally. Of course it’s faster if it doesn’t have to read a lot of the blocks from disk (i.e. smaller dataset or a large enough buffer pool).

I was curious about PBXT’s performance on this, and behold it appears to be quite a bit faster! For a table with 50 million rows, PBXT took about 20 minutes whereas the same table in InnoDB took 30 minutes. Interesting! …

With Paul McCullagh’s PBXT storage engine getting integrated into MariaDB 5.1, it’s never been easier to it out. So we have, on a slave off one of our own production systems which gets lots of inserts from our Zabbix monitoring system.

That’s possibly an ideal usage profile, since PBXT is a log based engine (simplistically stated, it indexes its transaction logs, rather than rewriting data from log into index and indexing that) so it should require less disk I/O than say InnoDB. And that means it should be particularly suited to for instance logging, which have lots of inserts on a sustained basis. Note that for short insert burst you may not see a difference with InnoDB because of caching, but sustain it and then you can notice.

Because PBXT has such different/distinct architecture there’s a lot of learning involved. Together …

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 …

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.

…

This is a Request for Input. Dual MySQL masters with MMM in a single datacentre are in common use, and other setups like DRBD and of course VM/SAN based failover solutions are conceptually straightforward also. Thus, achieving various forms of resilience within a single data-centre is doable and not costly.

Doing the same across multiple (let’s for simplicity sake limit it to two) datacentres is another matter. MySQL replication works well across longer links, and it can use MySQL’s in-built SSL or tools like stunnel. Of course it needs to be kept an eye on, as usual, but since it’s asynchronous the latency between the datacentres is not a big issue (apart from the fact that the second server gets up-to-date a little bit later).

But as those who have tried will know, having a client (application server) connection to a MySQL instance in a remote data-centre is a whole other matter, latency becomes a big issue and is generally …

Bookmark and Share this post

After Sun Microsystems was acquired by Oracle, there has been a large amount of discussions in the business and developer community on the future of MySQL community involved in its development.

A Community Fork?

Interestingly, MySQL community has been able to create a new Database by a fork from the public branch and has revived the project as MariaDB.

On it’s website, AskMonty.org [founded by Michael “Monty” Widenius, the founder and creator of MySQL] states that its aim is,

To provide a community developed, stable, and always Free branch of MySQL that is, on the user level, compatible with the main version. We strive for total interoperability with both our own, and our upstream, communities.

…

At this year’s conference, I was pleasantly surprised with the high level of interest in Open Query’s proactive services for MySQL and MariaDB, and specifically our focus on preventing problems, while explicitly not offering emergency services.

I’ll describe what this is about first, and why I reckon it’s interesting. When you think about it, most IT related support that includes emergency (24×7) operates similar to this:

You have a house that has the front and back doors wide open with no locks, and you take out an insurance policy for the house contents. After a short time you call the insurance company “guess what, the most terrible thing happened, my TV got stolen.” Insurance company responds “that’s dreadful, you poor soul, let us fix it all up for you with getting a new TV and installing it. It’ll be our pleasure to serve you.” A few weeks later you call the insurance company again …

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:

Tweet

Is MariaDB really a drop in replacement for MySQL?   I’m running CentOS 5.4.  What happens if…

1. Use “mysqldump –all-databases > FullBackup.sql” to make a full backup. (Better safe then sorry)
2. Go to http://askmonty.org/wiki/MariaDB:Download and download the CentOS 5 packages.
3. Stop msql “service mysqld stop”
4. Pull MySQL out by the roots with “rpm -e mysql-server mysql –nodeps”
5. Install Maria with “rpm -i Maria-*”

And the install start mysql up again.  Wow.  That’s “Drop in”.

If your reading this, you’re seeing it works.

A big congratulations to Ubuntu for the release of 10.04 LTS. While I haven’t had the chance to upgrade, I see everyone on Twitter and in the blogosphere say they are really like the Lucid experience.

A couple of days ago, I made mention that there were VirtualBox images of MariaDB out there. Turns out there were so many downloads, Mark has had to upgrade his Internet connection!

Anyway, to the point: Mark has created Ubuntu 10.04 LTS VM’s with MariaDB 5.1.44 and MariaDB 5.2 BETA. Don’t hesitate to download them, and send feedback.

Have a good weekend …