Introduction

This article will explain how the data is organized in InnoDB storage engine. First we will look at the various files that are created by InnoDB, then we look at the logical data organization like tablespaces, pages, segments and extents. We will explore each of them in some detail and discuss about their relationship with each other. At the end of this article, the reader will have a high level view of the data layout within the InnoDB storage engine.

The Files

MySQL will store all data within the data directory. The data directory can be specified using the command line option –data-dir or in the configuration file as datadir. Refer to the Server Command Options for complete details.

By default, when InnoDB is initialized, it creates 3 important files in the data directory – ibdata1, ib_logfile0 and …

Looking for some substantial reading material as the days grow longer?  You might consider dedicating a good chunk of time to review the MySQL 5.6.11 changelog.  The MySQL Engineering team at Oracle has been busy, and it shows in this maintenance release.  Stewart Smith recently noted the growth of the code base in 5.6 compared to 5.5.  That may or may not be the best measure of productivity, but the number of fixed bugs in a maintenance release like 5.6.11 is sure a good indicator.  A few general observations based on my quick study:

• 201 individual notes in the changelog
• 198 referenced bug reports (total, not de-duplicated)
• 61 bugs from community bugs system
• The oldest bug …

Following on from my fun post on Where are they now: MySQL Storage Engines, I thought I’d cover the few storage engines that are really just interfaces to a collection of things. In this post, I’m talking about MERGE.

The MERGE engine was basically a multiplexer down to a number of MyISAM tables. They all had to be the same, there was no parallel query execution and it saw fairly limited use. One of the main benefits was that then you could actually put more rows in a MyISAM table than your “files up to 2/4GB” file system allowed. With the advent of partitioning, this really should have instantly gone away and been replaced by it. It wasn’t.

It is another MySQL feature that exists likely due to customer demand at the time. It’s not a complete solution by any means, PARTITIONING is way more complete and …

This morning, this Percona XtraBackup bug came to my attention: https://bugs.launchpad.net/bugs/1170340 - basically, it’s now really quite tricky to determine if a MySQL server you’re connected to supports partitioning or not.

If you’re connected to anything less than MySQL 5.6, you can use have_partitioning variable. But since that’s gone in 5.6, you’re going to get a false negative if you’re connected to 5.6. You could use INFORMATION_SCHEMA.PLUGINS table, but that’s not there in 5.0, so you have some added workarounds to add there too.

A simple version check could be the solution… but what if you compiled the server without partitioning support?

There’s an Indiegogo fundraising effort for Julian Cash to come to the Percona Live MySQL Conference and Expo this year to take your photo! I’ve thrown in a bunch of money to help make this happen. Why? It’s much much much cheaper than getting any professional photo shoot done, and it’s by the awesome Julian, who makes pretty awesome photos.

You’ll get full resolution images too! Basically, this is the cheapest way you’re going to get this quality of photos done of yourself outside of dating a professional portrait photographer. Since I’m not dating a professional portrait photographer, this is an excellent and affordable way to have some truly …

Come see out new cool ‘cryogenic dolphin’ at the MySQL Community Reception Monday. Come hear about the latest 5.6 news from MySQL Engineers, see Cluster on Raspberry Pis, and walk away with our giveaways. And did I mention it is FREE?!?!

Here’s the last nail in the coffin. We hope this convinces you not to write to multiple masters with MySQL replication. Reason 10 – No tools to reconcile inconsistent masters For all the endless reasons we’ve already outlined MySQL replication is prone to failure. We know it’s going to happen, you now know too. When [...]

If you’re not convinced yet that writing to dual masters is a bad idea, we have a couple more reasons. 9. Temp tables break replication after restart MySQL’s replication is sensitive to temporary tables. You shouldn’t use them. If your queries create them to work against, and a node crashes, the temp tables will be [...]

Read the original article at Crashed nodes corrupt your MySQL cluster

Writing to two masters is like walking around with a loaded shotgun. Eventually one of your instances will fail and when it does, replications position & synchronization information could easily become corrupt! Reason 8 – Crashed nodes cause big problems MySQL instances, unfortunately can crash. When that happens, they don’t always sync the replication position [...]

For more articles like these go to Sean Hull's Scalable Startups

Related posts:

1. MySQL Cluster In The Cloud – Managers Guide
2. …

Read the original article at Transaction isolation breaks when writing two masters

Continuing our discussion of multi-master replication, we hit on five more reasons why writing to two masters aka active-active replication is very dangerous. Click through to the end for multi-master solutions that work with MySQL. Reason 6 – You lose transaction isolation This may sound like a theoretical point to some. But hopefully we can [...]

For more articles like these go to Sean Hull's Scalable Startups

Related posts:

1. MySQL needs single master to check data integrity
2. …