MySQL information_schema comes with useful information about the database instance, status, … etc. which is needed for daily DBA work.
There are some simple queries on the information_schema that I use on my daily basis in which I’m writing this post for my reference and maybe a good reference for someone else too …

Finding tables without Primary or Unique Keys:

PKs are so important, especially, for InnoDB tables as MySQL uses PKs as a clustered index and having no PKs might lead to severe performance problems.

Also having no PKs is one of the main causes of slave lagging problems mainly when using RBR (Row-Based Replication), e.g. if a delete statement on the master will delete 1 million rows on a table without PK, a full table scan will take place. This “might” not be a problem on the master but on the slave 1 million full table scan will take place – because changes to the individual rows are being …

People using OpenStack Trove instances can hit a common issue in the MySQL world: how to perform schema change operations while minimizing the impact on the database server? Let’s explore the options that can allow online schema changes.

Summary

With MySQL 5.5, pt-online-schema-change from Percona Toolkit is your best option for large tables while regular ALTER TABLE statements are only acceptable for small tables. Also beware of metadata locks.

With MySQL 5.6, almost all types of schema changes can be done online. Metadata locks can also be an issue. pt-online-schema-change can still be worth using as it is also online on read replicas.

Regular ALTER TABLE with MySQL 5.5

If you are still using MySQL 5.5, almost all schema changes will require a table …

MySQL table alterations can interrupt production traffic causing bad customer experience or in worst cases, loss of revenue. Not all DBAs, developers, syadmins know MySQL well enough to avoid this pitfall. DBAs usually encounter these kinds of production interruptions when working with upgrade scripts that touch both application and database or if an inexperienced admin/dev engineer perform the schema change without knowing how MySQL operates internally.

Truths
* Direct MySQL ALTER table locks for duration of change (pre-5.6)
* Online DDL in MySQL 5.6 is not always online and may incurr locks
* Even with Percona Toolkit‘s pt-online-schema-change there are several workloads that can experience blocking

Here on the Percona MySQL …

I recently encountered a situation in which after running Percona Toolkit’s pt-table-checksum on a customer system, 95% of the table on the MySQL master was different on the MySQL slave. Although this table was not a critical part of the infrastructure, from time to time, writes to the table from the master would break replication. Additionally, this table has about 6 million rows, and running pt-table-sync would take sometime. Rebuilding the slave from backup of the master would not be an easy option as well since the slave acts as an archive where it has a lot more data than the master.

So how did we solve it? With pt-online-schema-change and a NOOP ALTER.

pt-online-schema-change --alter 'ENGINE=INNODB' D=dbname,t=tblname

How is it …

Yay! My first blog post! As long as at least 1 person finds it useful, I’ve done my job. Recently, one of my long-term clients was noticing that while their INSERTs were succeeding, a particular column counter was not incrementing. A quick investigation determined the column was of type int(11) and they had reached the maximum value of 2147483647. We fixed this by using pt-online-schema-change to change the column to int(10) unsigned, thus allowing values up to 4294967295.

My client was now concerned about all his other integer-based columns and wanted me to check them all. So I wrote a quick-n-dirty script in Go to check all integer-based columns on their current value compared to the maximum allowed for that column type.

You …

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 affecting the performance as well as our users. Therefore even if we start ALTER it’s really important to know when it will finish. Even while creating the index, ALTER TABLE will not rebuild the table if fast_index_creation is ON but still it might lock the table.

…

December 10, 2013 By Severalnines

As a follow-up to the Webinar on Zero Downtime Schema Changes in Galera Cluster, we’ll now walk you through the detailed steps on how to update your schema. The two methods (TOI and RSU) have both their pros and cons, and given parameters like table size, indexes, key_buffer_size, disk speed, etc., it is possible to estimate the time taken for the schema to be upgraded. Also, please note that a schema change is non-transactional so it would not be possible to rollback the DDL if it fails midway. Therefore, it is always recommended to test the schema changes and ensure you have recoverable backups before performing this on your production clusters. 

This post examines the way DDL changes are propagated in Galera, and outlines the steps to upgrade the …

Recently, I helped a client that was having issues with deployments causing locking in their production databases.  At a high level, the two key components used in the environment were:

• Capistrano (scripted deployments) [website]
• Liquibase (database version control) [website]

At a high level, they currently used a CLI call to Liquibase as a sub-task within a larger deployment task.  The goal of this engagement was to modify that sub-task to run Liquibase in a non-blocking fashion as opposed to the default that just runs native ALTERS against the database.

As I wasn’t very familiar with Liquibase, I took this opportunity to learn more about it and it seems like a very valuable tool.  Essentially, it does the following:

• Adds two version control tables to …

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 huge pain once your tables get a significant amount of data. Planning for maintenance is becoming more and more difficult, and your worldwide users want the service to be up and running 24/7, while on the other hand, your developers desire to introduce schema changes every week.

PITA

But what is the real problem here? Let me illustrate very typical case:

Session1> ALTER TABLE revision ADD COLUMN mycol tinyint;
Query OK, 1611193 rows affected (1 min 5.74 sec)
Records: 1611193  Duplicates: 0 …

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. The bug fix is bug …