October 31, 2014

Data compression in InnoDB for text and blob fields

Have you wanted to compress only certain types of columns in a table while leaving other columns uncompressed? While working on a customer case this week I saw an interesting problem where a table had many heavily utilized TEXT fields with some read queries exceeding 500MB (!!), and stored in a 100GB table. In this […]

InnoDB file formats: Here is one pitfall to avoid

UPDATED: explaining the role of innodb_strict_mode and correcting introduction of innodb_file_format Compressed tables is an example of an InnoDB feature that became available with the Barracuda file format, introduced in the InnoDB plugin. They can bring significant gains in raw performance and scalability: given the data is stored in a compressed format the amount of […]

Innodb row size limitation

I recently worked on a customer case where at seemingly random times, inserts would fail with Innodb error 139. This is a rather simple problem, but due to it’s nature, it may only affect you after you already have a system running in production for a while.

Blob Storage in Innodb

I’m running in this misconception second time in a week or so, so it is time to blog about it. How blobs are stored in Innodb ? This depends on 3 factors. Blob size; Full row size and Innodb row format. But before we look into how BLOBs are really stored lets see what misconception […]

Real-Life Use Case for “Barracuda” InnoDB File Format

In one of his recent posts Vadim already gave some information about possible benefits from using new InnoDB file format but in this post I’d like to share some real-life example how compression in InnoDB plugin could be useful for large warehousing tasks.

Testing InnoDB “Barracuda” format with compression

New features of InnoDB – compression format and fast index creation sound so promising so I spent some time to research time and sizes on data we have on our production. The schema of one of shards is

Heikki Tuuri Innodb answers – Part I

Its almost a month since I promised Heikki Tuuri to answer Innodb Questions. Heikki is a busy man so I got answers to only some of the questions but as people still poking me about this I decided to publish the answers I have so far. Plus we may get some interesting follow up questions […]

The MySQL ARCHIVE storage engine – Alternatives

In my previous post I pointed out that the existing ARCHIVE storage engine in MySQL may not be the one that will satisfy your needs when it comes to effectively storing large and/or old data. But are there any good alternatives? As the primary purpose of this engine is to store rarely accessed data in disk […]

Calvin Sun on MySQL at Twitter, Percona Live 2014 and Facebook

Twitter’s Calvin Sun (@Calvinsun2012) is looking forward to the fast-approaching Percona Live MySQL Conference and Expo this April 1-4 in Santa Clara, Calif. He’ll be speaking, yes, but he’s also looking forward to learning from his peers – particularly those at Facebook. Both companies, he explained, are in the rather unique positions of unprecedented rapid […]

10 MySQL settings to tune after installation

When we are hired for a MySQL performance audit, we are expected to review the MySQL configuration and to suggest improvements. Many people are surprised because in most cases, we only suggest to change a few settings even though hundreds of options are available. The goal of this post is to give you a list […]