November 25, 2014

Percona XtraDB Cluster: Failure Scenarios with only 2 nodes

During the design period of a new cluster, it is always advised to have at least 3 nodes (this is the case with PXC but it’s also the same with PRM). But why and what are the risks ? The goal of having more than 2 nodes, in fact an odd number is recommended in […]

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 […]

Avoiding auto-increment holes on InnoDB with INSERT IGNORE

Are you using InnoDB tables on MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A simple INSERT IGNORE query creates gaps for every ignored insert, but this is undocumented behaviour. This documentation bug is already submitted. Firstly, we will start with a simple question. Why do we have […]

MySQL opening .frm even when table is in table definition cache

or… “the case of Stewart recognizing parameters to the read() system call in strace output”. Last week, a colleague asked a question: I have an instance of MySQL with 100 tables and the table_definition_cache set to 1000. My understanding of this is that MySQL won’t revert to opening the FRM files to read the table […]

MySQL Limitations Part 1: Single-Threaded Replication

I recently mentioned a few of the big “non-starter” limitations Postgres has overcome for specific use cases. I decided to write a series of blog posts on MySQL’s unsolved severe limitations. I mean limitations that really hobble it for major, important needs — not in areas where it isn’t used, but in areas where it […]

Multiple column index vs multiple indexes

(There is an updated version of the content in this post by Percona’s Stephane Combaudon available here.) After my previous post there were questions raised about Index Merge on Multiple Indexes vs Two Column Index efficiency. I mentioned in most cases when query can use both of the ways using multiple column index would be […]

Rendundant Array of Inexpensive Servers

So you need to design highly available MySQL powered system… how do you approach that ? Too often I see the question is approached by focusing on expensive hardware which in theory should be reliable. And this really can work quite well for small systems. It is my experience – with quality commodity hardware (Dell,HP,IBM […]

Implementing efficient counters with MySQL

On many web sites you would see a counter how many time given object – blog post, forum thread, image, movie etc was viewed. This is sometimes handy feature but it can be rather expensive from performance point of view. The nasty thing with counters as they are implemented the most trivial way – they […]

How to estimate time it takes Innodb to Recover ?

Today seems to be Innodb day in our Blog, but well this is the question which pops ups quite frequently in Innodb talks and during consulting engagements. It is well known to get better performance you should normally set innodb_log_file_size large. We however usually recommend caution as it may significantly increase recovery time if Innodb […]

Falcon Storage Engine Design Review

Now as new MySQL Storage engine – Falcon is public I can write down my thought about its design, which I previously should have kept private as I partially got them while working for MySQL. These thought base on my understanding, reading docs, speaking to Jim, Monty, Arjen and other people so I might miss […]