July 28, 2014

A case for MariaDB’s Hash Joins

MariaDB 5.3/5.5 has introduced a new join type “Hash Joins” which is an implementation of a Classic Block-based Hash Join Algorithm. In this post we will see what the Hash Join is, how it works and for what types of queries would it be the right choice. I will show the results of executing benchmarks […]

Verifying backup integrity with CHECK TABLES

An attendee to Espen’s recent webinar asked how to check tables for corruption. This kind of ties into my recent post on InnoDB’s handling of corrupted pages, because the best way to check for corruption is with CHECK TABLES, but if a page is corrupt, InnoDB will crash the server to prevent access to the […]

Which Linux distribution for a MySQL database server? A specific point of view.

One of the more common questions I get asked is which Linux distribution I would use for a MySQL database server. Bearing the responsibility for someone else’s success means I should advise something that is stable, reliable, easy to manage and has plenty of resources available online. It should also allow running MySQL without too […]

MySQL Life Cycle. Your Feedback is needed.

When I started with MySQL 3.22 I would start running MySQL from early beta (if not alpha) and update MySQL the same date as release would hit the web. Since that time I matured and so did MySQL ecosystem. MySQL is powering a lot more demanding and business critical applications now than 12 years ago […]

MLC SSD card lifetime and write amplification

As MLC-based SSD cards are raising popularity, there is also a raising concern how long it can survive. As we know, a MLC NAND module can handle 5,000-10,000 erasing cycles, after which it gets unusable. And obviously the SSD card based on MLC NAND has a limited lifetime. There is a lot of misconceptions and […]

How Innodb Contention may manifest itself

Even though multiple fixes have been implemented in Percona Server and MySQL 5.5, there are still workloads in which case mutex (or rw-lock) contention is a performance limiting factor, helped by ever growing number of cores available in the systems. It is interesting though the contention may manifest itself in the different form from the […]

The case for getting rid of duplicate “sets”

The most useful feature of the relational database is that it allows us to easily process data in sets, which can be much faster than processing it serially. When the relational database was first implemented, write-ahead-logging and other technologies did not exist. This made it difficult to implement the database in a way that matched […]

Different flavors of InnoDB flushing

In my recent benchmarks, such as this one about the Virident TachIon card, I used different values for innodb_buffer_pool_size, like 13GB, 52GB, and 144GB, for testing the tpcc-mysql database with size 100G. This was needed in order to test different memory/dataset size ratios. But why is it important, and how does it affect how InnoDB works […]

How to Identify Bad Queries in MySQL

Finding bad queries is a big part of optimization. A scientific optimization process can be simplified to “can anything be improved for less than it costs not to improve it? – if not, we’re done.” In databases, we care most about the work the database is doing. That is, queries. There are other things we […]

The perils of uniform hardware and RAID auto-learn cycles

Last night a customer had an emergency in selected machines on a large cluster of quite uniform database servers. Some of the servers were slowing down in a very puzzling way over a short time span (a couple of hours). Queries were taking multiple seconds to execute instead of being practically instantaneous. But nothing seemed […]