Percona announces the availability of Percona Toolkit 3.0.0rc-2 with new MongoDB tools on February 6, 2017. This is a release candidate.
Percona Toolkit is a collection of advanced command-line tools to perform a variety of MySQL and MongoDB server and system tasks that are too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source.
This is the first release candidate in the 3.0 series. It includes new features and bug fixes. Downloads are available from the Percona Software Testing Repositories.
Over the last few months, I’ve had the opportunity to meet and talk with many of Percona’s customers. I love these meetings, and I always get a bunch of questions about what we’re doing, what our plans are and what releases are coming.
I’m pleased to say there is a great deal going on at Percona, and I thought giving a quick talk about our current software and services, along with our plans, would provide a simple reference for many of these questions.
A full recording of this webinar, along with the presentation slide deck, can be found here.
Percona Solutions and Services
Let me start by laying out Percona’s company purpose:
To champion unbiased open source database solutions.
What does this mean? It means that we write software to offer you better solutions, and we use the best of what software and technology exist in the open source community.
Percona stands by a set of principles that we feel define us as a company, and are a promise to our customers:
100% free and open source software
Focused on finding solution to maximize your success
Open source database strategy consulting and implementation
No vendor lock-in required
We offer trusted and unbiased expert solutions, support and resource in a broad software ecosystem, including:
We also have specialization options for PaaS, IaaS, and SaaS solutions like Amazon Web Services, OpenStack, Google Cloud Platform, OpenShift, Ceph, Docker and Kubernetes.
Percona’s immediate business focus includes building long-term partnership relationships through support and managed services.
The next few sections detail our current service offerings, with some outlook on our plans.
98% Customer Satisfaction Rating
Over the last six months, Percona has consistently maintained a 98% Customer Satisfaction Rating!
Customer Success Team
Our expanded Customer Success Team is here to ensure you’re getting most out of your Percona Services Subscription.
Managed Services Best Practices
Unification based on best practices
Organization changes to offer more personal service
Increased automation
Ongoing Services
Consulting and Training. Our consulting and training services are available to assist you with whatever project or staff needs you have.
Onsite and remote
4 hours to full time (weeks or months)
Project and staff augmentation
Advanced HA Included with Enterprise and Premier Support. Starting this past Spring, we included advance high availability (HA) support as part of our Enterprise and Premier support tiers. This advanced support includes coverage for:
Percona XtraDB Cluster
MariaDB Galera Cluster
Galera Cluster for MySQL
Upcoming MySQL group replication
Upcoming MySQL Innodb Cluster
Enterprise Wide Support Agreements. Our new Enterprise Wide Support option allows you to buy per-environment support coverage that covers all of the servers in your environment, rather than on a per-server basis. This method of support can save you money, because it:
Covers both “MySQL” and “MongoDB”
Means you don’t have to count servers
Provides highly customized coverage
Simplified Support Pricing. Get easy to understand support pricing quickly.
To discuss how Percona Support can help your business, please call us at +1-888-316-9775 (USA),
+44 203 608 6727 (Europe), or have us contact you.
New Percona Online Store – Easy to Buy, Pay Monthly
Percona Memory Engine for MongoDB® is a 100 percent open source in-memory storage engine for Percona Server for MongoDB.
Based on the in-memory storage engine used in MongoDB Enterprise Edition, WiredTiger, Percona Memory Engine for MongoDB delivers extremely high performance and reduced costs for a variety of use cases, including application cache, sophisticated data manipulation, session management and more.
Below are some benchmarks that we ran to demonstrate Percona Memory Engine’s performance.
Percona Live Open Source Database Conference 2017 is right around the corner!
The Percona Live Open Source Database Conference is the premier event for the diverse and active open source database community, as well as businesses that develop and use open source database software. The conferences have a technical focus with an emphasis on the core topics of MySQL, MongoDB, PostgreSQL and other open source databases. Tackling subjects such as analytics, architecture and design, security, operations, scalability and performance, Percona Live provides in-depth discussions for your high-availability, IoT, cloud, big data and other changing business needs. This conference is an opportunity to network with peers and technology professionals by bringing together accomplished DBA’s, system architects and developers from around the world to share their knowledge and experience – all to help you learn how to tackle your open source database challenges in a whole new way
This conference has something for everyone!
April 24-27, 2017
Santa Clara, CA, USA
MySQL, MongoDB, PostgreSQL, other open source databases
In this blog post, we’ll look at how to replace MySQL with Percona Server for MySQL on a CPanel, WHM VPS or dedicated server.
In general, CPanel and WHM have been leaning towards support of MariaDB over other flavors. This is partly due to the upstream repos replacing the MySQL package with MariaDB (for example, on CentOS).
MySQL 5.6 is still supported though, which means they are keeping support for core MySQL products. But if you want to get some extra performance enhancements or enterprise features for free, without getting too many bells and whistles, you might want to install Percona Server.
I’ve done this work on a new dedicated server with the latest WHM and CPanel on CentOS 7, with MySQL 5.6 installed. Besides the backup, this is a fairly quick process.
It’s pretty simple. From the Percona Server for MySQL 5.7 installation doc, we can get the YUM repo. (Run commands as root if you can, otherwise as sudo.)
Now that it’s installed ensure the mysql service is running. If it isn’t, start it. Now let’s upgrade:
mysql_upgrade
NOTE. This works if you can log in as root without a password; if you can’t, you will need to specify the
-u
and
-p
flags.
Once you run the upgrade, restart the mysql service:
service mysql restart
And there you go, you are now running on Percona Server for MySQL 5.7. If your managed providers tell you it’s not supported, don’t worry! It works as long as CPanel supports MySQL 5.6.
If you have any issues, just restore the backup.
NOTE: One thing to keep in mind is that 5.7 breaks CPanel’s ability to create users in MySQL. I believe this is due to the changes to the mysql.user table. If this is an issue for you, you can always use Percona Server for MySQL 5.6.
In this blog, we’ll discuss changing the tablespace directory using pt-online-schema-change.
One of the most annoying situations in the life of a DBA is realizing that the disk where the datadir resides is running out of space. If you’re lucky enough to run over an LVM volume or a RAID (depending on the level, though), it is easy to add disk space. But what if you are not that lucky, and your datadir is running on a single disk? Not so funny!
That is the exact situation we recently faced with a customer, for both the master and slave server. When trying to figure out a solution we saw that:
There was enough space on a different partition within the same server.
The tables have their own tablespace (innodb_file_per_table = on)
In the slave, we were able to stop the replication and move the tables. A problem occurred when we wanted to do the same on the master, since no downtime was allowed.
This is where
pt-online-schema-change
came to the rescue!
We could use
pt-osc
to do the table placing without downtime, but there’s a catch:
pt-osc
only works when what you want to do is possible by using an ALTER TABLE statement, and in order to use the
With the help of the main developer of the Percona Toolkit, Carlos Salguero, adding this new feature was possible in record time. Now moving the tablespace to another place without downtime is possible.
Let’s see an example. The following table resides in the default datadir:
mysql> show create table sbtest5;
*************************** 1. row ***************************
Table: sbtest5
Create Table: CREATE TABLE `sbtest5` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_5` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 MAX_ROWS=1000000
1 row in set (0.00 sec)
Now, let’s move it to the directory /opt/datadir, which owner is the MySQL user:
[root@ps56-1 percona]# pt-online-schema-change --data-dir="/opt/datadir" --execute D=percona,t=sbtest5
No slaves found. See --recursion-method if host ps56-1 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `percona`.`sbtest5`...
Creating new table...
Created new table percona._sbtest5_new OK.
2016-11-01T19:22:27 Creating triggers...
2016-11-01T19:22:27 Created triggers OK.
2016-11-01T19:22:27 Copying approximately 1000 rows...
2016-11-01T19:22:27 Copied rows OK.
2016-11-01T19:22:27 Analyzing new table...
2016-11-01T19:22:27 Swapping tables...
2016-11-01T19:22:28 Swapped original and new tables OK.
2016-11-01T19:22:28 Dropping old table...
2016-11-01T19:22:28 Dropped old table `percona`.`_sbtest5_old` OK.
2016-11-01T19:22:28 Dropping triggers...
2016-11-01T19:22:28 Dropped triggers OK.
Successfully altered `percona`.`sbtest5`.
Okay, all good. Let’s see the new table definition:
mysql> show create table sbtest5;
*************************** 1. row ***************************
Table: sbtest5
Create Table: CREATE TABLE `sbtest5` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_5` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 MAX_ROWS=1000000 DATA DIRECTORY='/opt/datadir/'
DATA DIRECTORY='/opt/datadir/'
is in the right place!
And from the filesystem, the *.ibd file is in the new directory:
[root@ps56-1 opt]# ls -l /opt/datadir/percona/ | grep sbtest5
-rw-rw---- 1 mysql mysql 344064 Nov 1 19:22 sbtest5.ibd
And in the datadir, we can see the *isl file:
[root@ps56-1 opt]# ls -l /var/lib/mysql/percona/ | grep sbtest5
-rw-rw---- 1 mysql mysql 8632 Nov 1 19:22 sbtest5.frm
-rw-rw---- 1 mysql mysql 32 Nov 1 19:22 sbtest5.isl
This blog post will look how to use encryption to secure your database credentials.
In the recent blog post Use MySQL Shell Securely from Bash, there are some good examples of how you might avoid using a ~/.my.cnf – but you still need to put that password down on disk in the script. MySQL 5.6.6 and later introduced the –login-path option, which is a handy way to store per-connection entries and keep the credentials in an encrypted format. This is a great improvement, but as shown in Get MySQL Passwords in Plain Text from .mylogin.cnf, it is pretty easy to get that information back out.
Let’s fix this with gpg-agent, mkfifo and a few servings of Bash foo…
If you want to keep prying eyes away from your super secret database credentials, then you really need to encrypt it. Nowadays most people are familiar with GPG (GNU Privacy Guard), but for those of you that aren’t it is a free implementation of the OpenPGP standard that allows you to encrypt and sign your data and communication.
First steps…
Before we can go on to use GPG to encrypt our credentials, we need to get it working. GnuPG comes with almost every *nix operating system, but for this post we’ll be using Ubuntu 16.04 LTS and we’ll presume that it isn’t yet installed.
Once the packages are installed, there is a little configuration required to make things simpler. We’ll go with some minimal settings just to get you going. First of all, we’ll create our main key:
$ gpg --gen-key
gpg (GnuPG) 1.4.12; Copyright (C) 2012 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
Please select what kind of key you want:
(1) RSA and RSA (default)
(2) DSA and Elgamal
(3) DSA (sign only)
(4) RSA (sign only)
Your selection? 1
RSA keys may be between 1024 and 4096 bits long.
What keysize do you want? (4096)
Requested keysize is 4096 bits
Please specify how long the key should be valid.
0 = key does not expire
<n> = key expires in n days
<n>w = key expires in n weeks
<n>m = key expires in n months
<n>y = key expires in n years
Key is valid for? (5y)
Key expires at Tue 05 Oct 2021 23:59:00 BST
Is this correct? (y/N) y
You need a user ID to identify your key; the software constructs the user ID
from the Real Name, Comment and Email Address in this form:
"Heinrich Heine (Der Dichter) <heinrichh@duesseldorf.de>"
Real name: Ceri Williams
Email address: notmyrealaddress@somedomain.com
Comment: Encrypted credentials for MySQL
You selected this USER-ID:
"Ceri Williams (Encrypted credentials for MySQL) <notmyrealaddress@somedomain.com>"
Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O
You need a Passphrase to protect your secret key.
After typing a password and gaining sufficient entropy you will have your first key! You can show your private keys as follows:
$ gpg --list-secret-keys
/home/ceri/.gnupg/secring.gpg
-----------------------------
sec 4096R/C38C02B0 2016-10-06 [expires: 2021-10-05]
uid Ceri Williams (Encrypted credentials for MySQL) <notmyrealaddress@somedomain.com>
We’ll now create our “gpg.conf” in which to keep a few settings. This sets the key that is used by default when encrypting, enables the gpg-agent and removes the copyright message.
Now we’ll add a few settings for “gpg-agent” and allow the key to be saved for one day to reduce the number of times you need to enter a password. Also, as this post concentrates on command line programs, we’ve enabled the ncurses pinentry to specify the password when requested.
You can find more information about setting up and using GPG in the GNU Privacy Handbook.
Encrypt your credentials
If all has gone well so far, you should be able to encrypt your first message. Here is a simple example to create armored (ASCII) output for a recipient with key “C38C02B0”:
Now that we have GPG working, we can secure our credentials and encrypt them to use later on. One of the default files MySQL reads is “~/.my.cnf”, which is where you can store your user credentials for easy command line access.
There you go, everything is nice and secure! But wait, how can anything use this?
Bash foo brings MySQL data to you
Most MySQL and Percona tools will accept the “–defaults-file” argument, which tells the program where to look to find what configuration to run. This will allow us to use our encrypted config.
The following script carries out the following actions:
Creates a temporary file on disk and then removes it
Creates a FIFO (a socket-like communication channel that requires both ends to be connected)
Decrypts the config to the FIFO in the background
Launches the “mysql” client and reads from the FIFO
#!/bin/bash
set -e
declare -ra ARGS=( "${@}" )
declare -ri ARGV=${#ARGS[@]}
declare -r SEC_MYCNF=$(test -f ${1:-undef} && echo $_ || echo '.my.cnf.asc')
declare -r SEC_FIFO=$(mktemp)
declare -a PASSTHRU=( "${ARGS[@]}" )
test ${ARGV} -gt 0 &&
test -f "${ARGS[0]}" &&
PASSTHRU=( "${ARGS[@]:1}" )
set -u
function cleanup {
test -e ${SEC_FIFO} && rm -f $_
return $?
}
function decrypt {
set +e
$(which gpg) --batch --yes -o ${SEC_FIFO} -d ${SEC_MYCNF} >debug.log 2>&1
test $? -eq 0 || $(which gpg) --yes -o ${SEC_FIFO} -d ${SEC_MYCNF} >debug.log 2>&1
set -e
}
function exec_cmd {
local -r cmd=${1}
set +u
${cmd} --defaults-file=${SEC_FIFO} "${PASSTHRU[@]}"
set -u
}
trap cleanup EXIT
test -e ${SEC_MYCNF} || exit 1
cleanup && mkfifo ${SEC_FIFO} && decrypt &
exec_cmd /usr/bin/mysql
You can use this script as you would normally with the “mysql” client, and pass your desired arguments. You can also optionally pass a specific encrypted config as the first argument:
$ ./smysql.sh .my.test.asc
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 56
Server version: 5.7.14-8 Percona Server (GPL), Release '8', Revision '1f84ccd'
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
smysql (none)>
There we go, MySQL access via an encrypted “–defaults-file” – and as long as your key is unlocked in the agent you do not need to enter the password.
But wait . . . what about all of the other tools that you might want to use? Well, with a slight tweak you can make the script a little fancier and get other tools to use the config, too (tools such as mysqladmin, mysqldump, pt-show-grants, pt-table-checksum, etc.). The key part of the next script is the specification of accepted commands (“ALIASES”) and the use of symbolic links to alias the script:
#!/bin/bash
set -e
declare -ra ARGS=( "${@}" )
declare -ri ARGV=${#ARGS[@]}
declare -rA ALIASES=(
[smysql]=mysql
[smysqldump]=mysqldump
[smysqladmin]=mysqladmin
[spt-show-grants]=pt-show-grants
[spt-table-checksum]=pt-table-checksum
[spt-table-sync]=pt-table-sync
[spt-query-digest]=pt-query-digest
)
declare -r PROGNAME=$(basename ${0})
declare -r SEC_MYCNF=$(test -f ${1:-undef} && echo $_ || echo '.my.gpg')
declare -r SEC_FIFO=$(mktemp)
declare -a PASSTHRU=( "${ARGS[@]}" )
test ${ARGV} -gt 0 &&
test -f "${ARGS[0]}" &&
PASSTHRU=( "${ARGS[@]:1}" )
set -u
function cleanup {
test -e ${SEC_FIFO} && rm -f $_
return $?
}
function decrypt {
set +e
$(which gpg) --batch --yes -o ${SEC_FIFO} -d ${SEC_MYCNF} >debug.log 2>&1
test $? -eq 0 || $(which gpg) --yes -o ${SEC_FIFO} -d ${SEC_MYCNF} >debug.log 2>&1
set -e
}
function check_cmd {
local k
local cmd=${1}
for k in "${!ALIASES[@]}"; do
test "${cmd}" = ${k} &&
test -x "$(which ${ALIASES[${k}]})" &&
echo $_ && return 0
done
return 1
}
function exec_cmd {
local -r cmd=${1}
set +u
${cmd} --defaults-file=${SEC_FIFO} "${PASSTHRU[@]}"
set -u
}
function usage {
local realfn=$(realpath ${0})
cat <<EOS | fold -sw 120
USAGE: $(basename ${0}) enc_file.gpg [--arg=val]
use a GPG-encrypted my.cnf (default: ${SEC_MYCNF})
currently supports:
${ALIASES[@]}
create a symlink to match the alias (real app prefixed with 's')
e.g.
sudo ln -s ${realfn} /usr/local/bin/smysql
sudo ln -s ${realfn} /usr/local/bin/spt-show-grants
EOS
}
trap cleanup EXIT ERR
test -e ${SEC_MYCNF} || { usage; exit 1; }
cmd=$(check_cmd ${PROGNAME})
test $? -eq 0 || { echo ${ALIASES[${PROGNAME}]} is not available; exit 3; }
cleanup && mkfifo ${SEC_FIFO} && decrypt &
exec_cmd ${cmd}
Now we can set up some symlinks so that the script can be called in a way that the correct application is chosen:
Percona is pleased to announce the availability of Percona Toolkit2.2.19. Released August 16, 2016. Percona Toolkit is a collection of advanced command-line tools that perform a variety of MySQL server and system tasks that DBAs find too difficult or complex for to perform manually. Percona Toolkit, like all Percona software, is free and open source.
This release is the current GA (Generally Available) stable release in the 2.2 series. Downloads are available here and from the Percona Software Repositories.
New Features:
1221372: pt-online-schema-change now aborts with an error if the server is a slave, because this can break data consistency in case of row-based replication. If you are sure that the slave will not use row-based replication, you can disable this check using the --force-slave-run option.
1485195: pt-table-checksum now forces replica table character set to UTF-8.
1517155: Introduced --create-table-engine option to pt-heartbeat, which sets a storage engine for the heartbeat table different from the database default engine.
1595678 and 1595912: Introduced --slave-user and --slave-password options to pt-online-schema-change, pt-table-sync, and pt-table-checksum.
1610385: pt-online-schema-change now re-checks the list of slaves in the DSN table. This enables changing the contents of the table while the tool is running.
Bugs Fixed:
1581752: Fixed pt-query-digest date and time parsing from MySQL 5.7 slow query log.
1592166: Fixed memory leak when pt-kill kills a query.
1592608: Fixed overflow of CONCAT_WS when pt-table-checksum or pt-table-sync checksums large BLOB, TEXT, or BINARY columns.
1593265: Fixed pt-archiver deleting rows that were not archived.
1610386: Fixed pt-slave-restart handling of GTID ranges where the left-side integer is larger than 9.
1610387: Removed extra word ‘default’ from the --verbose help for pt-slave-restart.
1610388: Fixed pt-table-sync not quoting enum values properly. They are now recognized as CHAR fields.
This article discusses how to salvage a crashed pt-online-schema-change by leveraging pt-archiver and executing queries to ensure that the data gets accurately migrated. I will show you how to continue the data copy process, and how to safely close out the pt-online-schema-change via manual operations such as RENAME TABLE and DROP TRIGGER commands. The normal process to recover from a crashed pt-online-schema-change is to drop the triggers on your original table and drop the new table created by the script. Then you would restart pt-online-schema-change. In this case, this wasn’t possible.
A customer recently needed to add a primary key column to a very busy table (with around 200 million rows). The table only had a unique key on one column (called our_id below). The customer had concerns about slave lag, and wanted to ensure there was little or no lag. This, as well as the fact that you can’t add a primary key as an online DDL in MySQL and Percona Server 5.6, meant the obvious answer was using pt-online-schema-change.
Due to the sensitivity of their environment, they could only afford one short window for the initial metadata locks, and needed to manually do the drop swap that pt-online-schema-change normally does automatically. This is where no-drop-triggers and no-swap-tables come in. The triggers will theoretically run indefinitely to keep the new and old tables in sync once pt-online-schema-change is complete. We crafted the following command:
You can see some of the specifics of other flags and why we used them in the Percona Toolkit Manual.
Once we ran the command the customer got concerned, as their monitoring tools weren’t showing any work done (which is by design, pt-online-schema-change doesn’t want to hurt your running environment). The customer ran strace -p to verify it was working. This wasn’t a great choice as it crashed pt-online-schema-change.
At this point, we knew that the application (and management) would not allow us to take new metadata locks to create triggers on the table, as we had passed our metadata lock window.
So how do we recover?
First, let’s start with a clean slate. We issued the following commands to create a new table, where __largetable_new is the table created by pt-online-schema-change:
CREATE TABLE mynewlargetable LIKE __largetable_new;
RENAME TABLE __largetable_new TO __largetable_old, mynewlargetable TO __largetable_new;
DROP TABLE __largetable_old;
Now the triggers on the original table, largetable are updating the new empty table that has our new schema.
Now let’s address the issue of actually moving the data that’s already in largetable to __largetable_new. This is where pt-archiver comes in. We crafted the following command:
We use pt-archiver to slowly copy records non-destructively to the new table based on our_id and WHERE 1=1 (all records). At this point, we periodically checked the MySQL data directory over the course of a day with ls -l to compare table sizes.
Once the table files were close to the same size, we ran counts on the tables. We noticed something interesting: the new table had thousands more records than the original table.
This concerned us. We wondered if our “hack” was a mistake. At this point we ran some verification queries:
select min(our_id) from __largetable_new;
select max(our_id) from __largetable_new;
select min(our_id) from largetable;
select max(our_id) from largetable;
We learned that there were older records that didn’t exist in the live table. This means that pt-archiver and the DELETE trigger may have missed each other (i.e., pt-archiver was already in a transaction but hadn’t written records to the new table until after the DELETE trigger already fired).
We verified with more queries:
SELECT COUNT(*) FROM largetable l WHERE NOT EXISTS (SELECT our_id FROM __largetable_new n WHERE n.our_id=l.our_id);
They returned nothing.
SELECT COUNT(*) FROM __largetable_new n WHERE NOT EXISTS (SELECT our_id FROM largetable l WHERE n.our_id=l.our_id);
Our result showed 4000 extra records in the new table. This shows that we ended up with extra records that were deleted from the original table. We ran other queries based on their data to verify as well.
This wasn’t a huge issue for our application, and it could have been easily dealt with using a simple DELETE query based on the unique index (i.e., if it doesn’t exist in the original table, delete it from the new one).
Now to complete the pt-online-schema-change actions. All we need to do is the atomic rename or drop swap. This should be done as soon as possible to avoid running in a degraded state, where all writes to the old table are duplicated on the new one.
RENAME TABLE largetable TO __largetable_old , __largetable_new TO largetable;
Then drop the triggers for safety:
DROP TRIGGER pt_osc_website_largetable_ins;
DROP TRIGGER pt_osc_website_largetable_upd;
DROP TRIGGER pt_osc_website_largetable_del;
At this point it is safer to wait for the old table to clear out of the buffer pool before dropping it, just to ensure there is no impact on the server (maybe a week to be safe). You can check information_schema for a more accurate reading on this:
SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME = '`website`.`__largetable_old`';
+----------+
| count(*) |
+----------+
| 279175 |
+----------+
1 row in set (8.94 sec)
Percona announces the availability of Percona Toolkit2.2.18, released on June 24, 2016.
Percona Toolkit is a collection of advanced command-line tools to perform a variety of MySQL server and system tasks that are too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source.
This release is the current stable release in the 2.2 series. It includes new features and bug fixes as well as improved MySQL 5.7 compatibility. Full details are below. Downloads are available here and from the Percona Software Repositories.
New features:
1537416: pt-stalk now sorts the output of transactions by id
PT-24: Added the --no-vertical-format option for pt-query-digest, allowing compatibility with non-standard MySQL clients that don’t support the G directive at the end of a statement
Bug fixes:
1402776: Fixed error when parsing tcpdump capture with pt-query-digest
In this blog post we’ll discuss pt-online-schema-change, and how to correctly use it.
Always use pt-osc?
Altering large tables can be still a problematic DBA task, even now after we’ve improved Online DDL features in MySQL 5.6 and 5.7. Some ALTER types are still not online, or sometimes just too expensive to execute on busy production master.
So in some cases, we may want to apply an
ALTER
first on slaves, taking them out of traffic pool one by one and bringing them back after the
ALTER
is done. In the end, we can promote one of the already altered slaves to be new master, so that the downtime/maintenance time is greatly minimized. The ex-master can be altered later, without affecting production. Of course, this method works best when the schema change is backwards-compatible.
So far so good, but there is another problem. Let’s say the table is huge, and
ALTER
takes a lot of time on the slave. When it is a DML-blocking type ALTER (perhaps when using MySQL 5.5.x or older, etc.), there will be a long slave lag (if the table is being written by replication SQL thread at the same time, for example). So what do we to speed up the process and avoid the altered slave lag? One temptation that could tempt you is why not use pt-online-schema-change on the slave, which can do the ALTER in a non-blocking fashion?
Let’s see how it that would work. I need to rebuild big table on slave using MySQL version 5.6.16 (“null alter” was made online since 5.6.17) to reclaim disk space after some rows are deleted.
This example demonstrates the process (db1 is the master, db2 is the slave):
[root@db2 ~]# pt-online-schema-change --execute --alter "engine=innodb" D=db1,t=sbtest1
No slaves found. See --recursion-method if host db2 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `db1`.`sbtest1`...
Creating new table...
Created new table db1._sbtest1_new OK.
Altering new table...
Altered `db1`.`_sbtest1_new` OK.
2016-05-16T10:50:50 Creating triggers...
2016-05-16T10:50:50 Created triggers OK.
2016-05-16T10:50:50 Copying approximately 591840 rows...
Copying `db1`.`sbtest1`: 51% 00:28 remain
(...)
The tool is still working during the operation, and the table receives some writes on master:
which are applied on slave right away, as the table allows writes all the time.
(...)
Copying `db1`.`sbtest1`: 97% 00:01 remain
2016-05-16T10:51:53 Copied rows OK.
2016-05-16T10:51:53 Analyzing new table...
2016-05-16T10:51:53 Swapping tables...
2016-05-16T10:51:53 Swapped original and new tables OK.
2016-05-16T10:51:53 Dropping old table...
2016-05-16T10:51:53 Dropped old table `db1`.`_sbtest1_old` OK.
2016-05-16T10:51:53 Dropping triggers...
2016-05-16T10:51:53 Dropped triggers OK.
Successfully altered `db1`.`sbtest1`.
Done! No slave lag, and the table is rebuilt. But . . . let’s just make sure data is consistent between the master and slave (you can use pt-table-checksum):
db1 {root} (db1) > select max(k) from db1.sbtest1 where id<100;
+--------+
| max(k) |
+--------+
| 392590 |
+--------+
1 row in set (0.00 sec)
db2 {root} (test) > select max(k) from db1.sbtest1 where id<100;
+--------+
| max(k) |
+--------+
| 392586 |
+--------+
1 row in set (0.00 sec)
No, it is not! The slave is clearly missing the updates that happened during a pt-osc run. Why?
The explanation is simple. The
pt-online-schema-change
relies on triggers. The triggers are used to make the writes happening to the original table also populate to the temporary table copy, so that both tables are consistent when the final table switch happens at the end of the process. So what is the problem here? It’s the binary log format: in ROW based replication, the triggers are not fired on the slave! And my master is running in ROW mode:
db1 {root} (db1) > show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)
So, if I used
pt-online-schema-change
on the master, the data inconsistency problem doesn’t happen. But using it on the slave is just dangerous!
will work well enough. As in my example, to rebuild the table separately on each slave in lockless mode, I would just need to upgrade to the more recent 5.6 version.
BTW, if you’re wondering about Galera replication (used in Percona XtraDB Cluster, etc.) since it also uses a ROW-based format, it’s not a problem. The pt-osc triggers are created in all nodes thanks to synchronous write-anywhere replication nature. It does not matter which node you start
pt-online-schema-change
on, and which other nodes your applications writes on at the same time. No slaves, no problem!
Percona is pleased to announce the availability of Percona Toolkit2.2.17. Released March 7, 2016. Percona Toolkit is a collection of advanced command-line tools to perform a variety of MySQL server and system tasks that are too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source.
This release is the current GA (Generally Available) stable release in the 2.2 series. It includes multiple bug fixes for pt-table-checksum with better support for Percona XtraDB Cluster, various other fixes, as well as MySQL 5.7 general compatibility. Full details are below. Downloads are available here and from the Percona Software Repositories.
New Features:
Percona Toolkit 2.2.17 has implemented general compatibility with MySQL 5.7 tools, documentation and test suite.
Bugs Fixed:
Bug 1523685: pt-online-schema-change invalid recursion method where a comma was interpreted as the separation of two DSN methods has been fixed.
Bugs 1480719 and 1536305: The current version of Perl on supported distributions has implemented stricter checks for arguments provided to sprintf. This could cause warnings when pt-query-digest and pt-table-checksum were being run.
Bug 1498128: pt-online-schema-change would fail with an error if the table being altered has foreign key constraints where some start with an underscore and some don’t.
Bug 1336734: pt-online-schema-change has implemented new --null-to-non-null flag which can be used to convert NULL columns to NOT NULL.
Bug 1362942: pt-slave-restart would fail to run on MariaDB 10.0.13 due to a different implementation of GTID.
Bug 1389041: pt-table-checksum had a high likelihood to skip a table when row count was around chunk-size * chunk-size-limit value. To address this issue a new --slave-skip-tolerance option has been implemented.
Bug 1506748: pt-online-schema-change could not set the SQL_MODE by using the --set-vars option, preventing some use case schema changes that require it.
Bug 1526105: pt-online-schema-change would fail if used with –no-drop-old-table option after ten times. The issue would arise because there was an accumulation of tables that have already had their names extended, the code would retry ten times to append an underscore, each time finding an old table with that number of underscores appended.
Bug 1529411: pt-mysql-summary was displaying incorrect information about Fast Server Restarts for Percona Server 5.6.
Percona announces the availability of Percona Toolkit 3.0.0rc-2 with new MongoDB tools on February 6, 2017. This is a release candidate.
In this blog post we’ll discuss pt-online-schema-change, and how to correctly use it.
