Download PDF Presentation

Thanks to Tim Callaghan for speaking Tuesday night at the Effective MySQL New York meetup on Fractal Tree Indexes : Theory and Practice (MySQL and MongoDB). There was a good turnout and a full room to learn how the TokuDB storage engine from Tokutek is changing how to handle big data in MySQL.

Also interesting is how the same technology has been applied for use in MongoDB including giving MongoDB transactions; a big change for NoSQL.

Related News: …

In case you missed it, more and more people are now spreading the word about the Performance Schema, which is a very good thing.

#DBHangOps 4/10/13

Mark Leith presents the Performance Schema and ps_helpers.

Random quotes from the recording:
"I am already seeing so many benefits out of this, ..."
"This looks fantastic"
"Very cool"

OurSQL Episode 139: Starting to Perform

Sheeri and Gerry present the Performance Schema.

Ramdom quote from the recording:
"I am looking at this feature [digests], and I think it's amazing"

Webinar: MySQL 5.6 Performance Schema

PeterZ …

(This is part one of a two part series) Over the past few weeks I have been looking at a preview release of Virident’s vCache software, which is a kernel module and set of utilities designed to provide functionality similar to that of FlashCache. In particular, Virident engaged Percona to do a usability and feature-set comparison between vCache and FlashCache and also to conduct some benchmarks for the use case where the MySQL working set is significantly larger than the InnoDB buffer pool (thus leading to a lot of buffer pool disk reads) but still small enough to fit into the cache device. In this post and the next, I’ll present some of those results.

Disclosure: The research and testing for this post series was sponsored by Virident.

Usability is, to some extent, a subjective call, as I may have preferences …

We are pleased to announce RethinkDB 1.5 (The Graduate), so go download it now!

This release includes the long-awaited support for secondary indexes, a new algorithm for batched inserts that results in an ~18x performance improvement, support for soft durability (don't worry -- off by default), and over 180 bug fixes, features, and enhancements.

Upgrading to 1.5? Make sure to migrate your data before upgrading to RethinkDB 1.5. →

Secondary indexes

…

MySQL has two great features which historical haven't played well together:

1. Index-only queries:  In some cases, MySQL can resolve a query directly from the index, without having to read the underlying table.
2. Prefix indexes:  This allows you to specify how many bytes to index, which can reduce index size or allow you to index the larger data types (ie. BLOB/TEXT).  The drawback being that the entire field isn't stored in the index, so you can't do index-only queries.

One common optimization we do to reduce IOP consumption on database servers is to add additional columns to indexes in order to allow more queries to be index-only.  However, sometimes we have these large TEXT fields in order to allow for larger content -- even if the content is normally very small.

For example:

CREATE TABLE tbl (

  …

MySQL has two great features which historical haven't played well together:

1. Index-only queries:  In some cases, MySQL can resolve a query directly from the index, without having to read the underlying table.
2. Prefix indexes:  This allows you to specify how many bytes to index, which can reduce index size or allow you to index the larger data types (ie. BLOB/TEXT).  The drawback being that the entire field isn't stored in the index, so you can't do index-only queries.

One common optimization we do to reduce IOP consumption on database servers is to add additional columns to indexes in order to allow more queries to be index-only.  However, sometimes we have these large TEXT fields in order to allow for larger content -- even if the content is normally very small.

For example:

CREATE TABLE tbl (

  …

I finally posted a copy of the slides from my Percona Live presentation, “Creating a Benchmarking Infrastructure that Just Works”.  The PDF is available via this link.

The content comes from my personal experiences over many years benchmarking and testing databases, usually focusing on performance.  It was an opportunity to see how far my personal benchmark infrastructure has evolved, but even better has inspired me to improve it in several areas.

I never had a chance to to my own post-conference wrap-up regarding the Percona Live show.  While waiting for my flight home at SFO airport I concluded that it was by far the best technology conference I’ve ever attended.  The …

This is part 2, you can find part 1 here.

So in part 1 we learned how to calculate the free space within InnoDB. But unfortunately that won't always work perfectly.

The first issue: the DATA_FREE column in the INFORMATION_SCHEMA.TABLES table will not show a sum of the free space of each partition. This means that if you have innodb_file_per_table disabled and are using partitioning then you must divide DATA_FREE by the number of partitions.
This is Bug #36312.

Example:

mysql> SELECT CONCAT(T.TABLE_SCHEMA,'.',T.TABLE_NAME) AS TABLE_NAME,
    -> P.PARTITION_NAME AS PART,IBT.SPACE,IBD.PATH,T.DATA_FREE AS T_DATA_FREE,
    -> P.DATA_FREE AS P_DATA_FREE FROM INFORMATION_SCHEMA.TABLES T 
    -> LEFT …

It is a central part of the MySQL philisophy to try and help you as much as you can. There are many occasions when it could tell you that what you are asking for is utterly stupid or give you a bad execution plan because "you asked for it". But we're friendly here. We don't do that. One of those cases is when you run a query with an outer join but you really meant an inner join, or you don't care.

Inner joins are almost always cheaper to execute than outer joins and that is why MySQL rewrites them to inner joins whenever it can.

An outer join may have WHERE conditions on any of the columns in the result set. In fact, it is a common trick to find non-matching rows using the IS NULL predicate. Here's an example:

TABLE person

name  id
Tom   1
Dick  2
Harry 3

TABLE car

brand …

Many people write blogs and emails about various anniversaries. I had an anniversary when I started writing this blog on the 2 may where I celebrated 23 years since I my start date according to my employment contract. 2 may 1990 was the first day I worked at Ericsson where I started working on databases and where NDB Cluster was born that later grew into MySQL Cluster.

Actually this is a year of anniversaries for me. I started by becoming half a century old a few months ago, then I celebrated a quarter of a century as member in the LDS church, my wife and I just celebrated our 25th wedding day and in september I will celebrate 10 years of working with MySQL together with the other people that joined MySQL from Ericsson 10 years ago.

So I thought this was an appropriate timing to write down a little history of my career and particulary focused on how it relates to MySQL and MySQL Cluster. The purpose of this isn't to …