I wrote one week ago about how to find duplicate indexes. This time we’ll learn how to find unused indexes to continue improving our schema and the overall performance. There are different possibilites and we’ll explore the two most common here. User Statistics from Percona Server and pt-index-usage. User Statistics User Statistics is an improvement [...]
Percona XtraDB Cluster reference architecture with HaProxy
June 20, 2012 By 63 Comments
This post is a step-by-step guide to set up Percona XtraDB Cluster (PXC) in a virtualized test sandbox. I used Amazon EC2 micro instances, but the content here is applicable for any kind of virtualization technology (for example VirtualBox). The goal is to give step by step instructions, so the setup process is understandable and [...]
Data compression in InnoDB for text and blob fields
May 30, 2012 By 2 Comments
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 [...]
Best kept MySQLDump Secret
March 23, 2012 By 13 Comments
Many people use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it works, but did you know there are some cases when you can get table entirely missing from the backup if you use this technique ? The problem comes from the fact how MySQL’s [...]
Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By 3 Comments
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 [...]
Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
I guess I’m first to post in 2012 so Happy New Year all blog readers ! Now back to HardCore MySQL business – foreign Keys. MySQL supported Foreign Keys for Innodb for many years, yet rudimentary support initially added in MySQL 3.23.44 have not been improved in new releases as much as I’d like. We [...]
Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 4 Comments
Statement based replication writes the queries that modify data in the Binary Log to replicate them on the slave or to use it as a PITR recovery. Here we will see what is the behavior of the MySQL when it needs to log “not usual” queries like Events, Functions, Stored Procedures, Local Variables, etc. We’ll [...]
Fishing with dynamite, brought to you by the randgen and dbqp
November 16, 2011 By Leave a Comment
I tend to speak highly of the random query generator as a testing tool and thought I would share a story that shows how it can really shine. At our recent dev team meeting, we spent approximately 30 minutes of hack time to produce test cases for 3 rather hard to duplicate bugs. Of course, [...]
Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
One of the serious limitations in the fast index creation feature introduced in the InnoDB plugin is that it only works when indexes are explicitly created using ALTER TABLE or CREATE INDEX. Peter has already blogged about it before, here I’ll just briefly reiterate other cases that might benefit from that feature: when ALTER TABLE [...]
Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
Percona is glad to announce the release of Percona Server 5.1.59-13.0 on October 15, 2011 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.1.59, including all the bug fixes in it, Percona Server 5.1.59-13.0 is now the current stable release in the 5.1 series. All of Percona’s software is open-source and free, all the [...]
