Showing entries 21 to 26
« 10 Newer Entries
Displaying posts with tag: pt-online-schema-change (reset)
Online Schema Upgrade in MySQL Galera Cluster using TOI Method

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 …

[Read more]
Integrating pt-online-schema-change with a Scripted Deployment

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 …
[Read more]
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 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 …
[Read more]
Percona Toolkit 2.2.3 released; bug fixes include pt-online-schema-change

Among all of the excellent events going on like YAPCVelocity, 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 …

[Read more]
pt-online-schema-change and binlog_format

Statement-based or row-based, or mixed?  We’ve all seen this discussed at length so I’m not trying to rehash tired arguments.  At a high level, the difference is simple:

  1. Statement based replication (SBR) replicates the SQL statements to the slave to be replayed
  2. Row based replication (RBR) replicates the actual rows changed to the slave to be replayed
  3. Mixed mode uses RBR in the event of a non-deterministic statement, otherwise uses SBR

Recently, I worked with a client to optimize their use of pt-online-schema-change and keep replication delay to a minimum.  We found that using RBR in conjunction with a smaller chunk-time was the best result in their environment due to reduced IO on the slave, but I wanted to recreate the test locally as well to see how it looked in the generic sense (sysbench for data/load).

Here was my local setup:

[Read more]
5 Percona Toolkit Tools for MySQL That Could Save Your Day: April 3 Webinar

On April 3 at 10 a.m. PST, I’ll be giving a webinar titled “5 Percona Toolkit Tools for MySQL That Could Save Your Day.” In this presentation you’ll learn how to perform typical but challenging MySQL database administration tasks.

My focus will be on the following tools:

  • pt-query-digest, to select the queries you should try to improve to get optimal response times
  • pt-archiver, to efficiently purge purge data from huge tables
  • pt-table-checksum/pt-table-sync, to check if data on replicas is in sync with data on the master
  • pt-stalk, to gather data when performance problems happen randomly or are very short
  • pt-online-schema-change, to run …
[Read more]
Showing entries 21 to 26
« 10 Newer Entries