October 26, 2014

How to monitor ALTER TABLE progress in MySQL

While working on a recent support issue as a Percona Support Engineer,  I got one question from a customer asking how to monitor ALTER TABLE progress. Actually, for MySQL 5.5 and prior versions, it’s quite difficult to ALTER the table in a running production environment especially for large tables (with millions records). Because it will rebuild and lock the table […]

Renaming database schema in MySQL

One of the routine tasks for a DBA is renaming database schemas, and as such MySQL added a command to carry out that purpose called “RENAME DATABASE <database_name>”. However this command just made it through a few minor releases before being discontinued (from MySQL 5.1.7 to 5.1.23). Here’s a link to the reference manual regarding […]

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

Schema changes – what’s new in MySQL 5.6?

Among many of the improvements you can enjoy in MySQL 5.6, there is one that addresses a huge operational problem that most DBAs and System Administrators encounter in their life: schema changes. While it is usually not a problem for small tables or those in early stages of product life cycle, schema changes become a […]

Percona Toolkit 2.2.3 released; bug fixes include pt-online-schema-change

Among all of the excellent events going on like YAPC, Velocity, and Percona MySQL University, we recently released Percona Toolkit 2.2.3. It’s a small update that includes the following: pt-online-schema-change did not handle the failure of the DROP TRIGGER statements correctly Created pt-agent pt-query-digest –output json now includes more data The pt-online-schema-change issue had a high importance. […]

Percona Server 5.1.69-14.7 now available: A drop in replacement for MySQL

Percona is glad to announce the release of Percona Server 5.1.69-14.7 on June 10, 2013. A drop in replacement for MySQL, downloads are available  here and from the Percona Software Repositories. Based on MySQL 5.1.69, this release will include all the bug fixes in it. All of Percona’s software is open-source and free, all the […]

Implementing SchemaSpy in your MySQL environment

Lately I have been working with a set of customers on a longer term basis which has given me time to explore new tools using their environments.  One tool that I am finding very helpful is called SchemaSpy. SchemaSpy is a Java-based tool (requires Java 5 or higher) that analyzes the metadata of a schema in […]

MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

So far most of the benchmarks posted about MySQL 5.6 use the sysbench OLTP workload.  I wanted to test a set of queries which, unlike sysbench, utilize joins.  I also wanted an easily reproducible set of data which is more rich than the simple sysbench table.  The Star Schema Benchmark (SSB) seems ideal for this. […]

Knowing what pt-online-schema-change will do

pt-online-schema-change is simple to use, but internally it is complex.  Baron’s webinar about pt-online-schema-change hinted at several of the tool’s complexities.  Consequently, users often want to know before making changes what pt-online-schema-change will do when it runs.  The tool has two options to help answer this question: –dry-run and –print. When ran with –dry-run and –print, pt-online-schema-change changes nothing […]

How to recover a single InnoDB table from a Full Backup

Sometimes we need to restore only some tables from a full backup maybe because your data loss affect a small number of your tables. In this particular scenario is faster to recover single tables than a full backup. This is easy with MyISAM but if your tables are InnoDB the process is a little bit […]