October 24, 2014

How to reclaim space in InnoDB when innodb_file_per_table is ON

When innodb_file_per_table is OFF and all data is going to be stored in ibdata files. If you drop some tables of delete some data then there is no any other way to reclaim that unused disk space except dump/reload method. When Innodb_file_per_table is ON, each table stores data and indexes in it’s own tablespace file. […]

InnoDB performance optimization basics (redux)

I recently stumbled upon a post that Peter Zaitsev published back in 2007 titled “Innodb Performance Optimization Basics.” It’s a great post and reading it inspired me to examine what’s changed in the nearly six years that have followed in terms of MySQL, Percona Server – as well as in all of the other now-available […]

How to move the InnoDB log sequence number (LSN) forward

This post focuses on the problem of the InnoDB log sequence number being in the future. Preface: What is an InnoDB log sequence number? The Log sequence number (LSN) is an important database parameter used by InnoDB in many places. The most important use is for crash recovery and buffer pool purge control. Internally, the InnoDB […]

Why is the ibdata1 file continuously growing in MySQL?

We receive this question about the ibdata1 file in MySQL very often in Percona Support. The panic starts when the monitoring server sends an alert about the storage of the MySQL server – saying that the disk is about to get filled. After some research you realize that most of the disk space is used […]

InnoDB Full-text Search in MySQL 5.6: Part 3, Performance

This is part 3 of a 3 part series covering the new InnoDB full-text search features in MySQL 5.6. To catch up on the previous parts, see part 1 or part 2 Some of you may recall a few months ago that I promised a third part in my InnoDB full-text search (FTS) series, in […]

Checking B+tree leaf nodes list consistency in InnoDB

If we have InnoDB pages there are two ways to learn how many records they contain: PAGE_N_RECS field in the page header Count records while walking over the list of records from infimum to supremum In some previous revision of the recovery tool a short summary was added to a dump which is produced by […]

Quickly finding unused indexes (and estimating their size)

I had a customer recently who needed to reduce their database size on disk quickly without a lot of messy schema redesign and application recoding.  They didn’t want to drop any actual data, and their index usage was fairly high, so we decided to look for unused indexes that could be removed. Collecting data It’s […]

Review of MySQL 5.6 Defaults Changes

James Day just posted the great summary of defaults changes in MySQL 5.6 compared to MySQL 5.5 In general there are a lot of good changes and many defaults are now computed instead of hardcoded. Though some of changes are rather puzzling for me. Lets go over them: back_log = 50 + ( max_connections / […]

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

Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs

LVM snapshots is one powerful way of taking a consistent backup of your MySQL databases – but did you know that you can now restore directly from a snapshot (and binary logs for point in time recovery) in case of that ‘Oops’ moment? Let me show you quickly how. This howto assumes that you already […]