Peter ZaitsevTips from the trenches for over-extended MySQL DBAs (2.12.2014, 08:00 UTC)

This post is a follow-up to my November 19 webinar, “Tips from the Trenches: A Guide to Preventing Downtime for the Over-Extended DBA,” during which I described some of the most common reasons DBAs experience avoidable downtime. The session was aimed at the “over-stretched DBA,” identified as the MySQL DBA short of time or an engineer of another discipline without the depth of the MySQL system. The over-stretched DBA may be prone to making fundamental mistakes that cause downtime through poor response time, operations that cause blocking on important data or administrative mishaps through the lack of best practice monitoring and alerting. (You can download my slides and view the recorded webinar here.)

Monitor the things
One of the aides to keeping the system up and running is ensuring that your finger is on the pulse of the environment. Here on the Percona Managed Services team, we leverage Percona Monitoring Plugins (open source plugins for Nagios, Cacti and Zabbix) to ensure we have visibility of our client’s operations. Having a handle on basics such as disk space, memory usage and MySQL operational metrics ensures that we avoid trivial downtime that would affect the client’s uptime or worse, their bottom line.

Road Blocks
One of the most common reasons that an application is unable to serve data to its end user is that access to a table is being blocked due to another ongoing operation. This can be blamed on a variety of sources: backups, schema changes, poor configuration and long running transactions can all lend themselves to costly blocking. Understanding the impact of actions on a MySQL server can be the difference between a happy end user and a frustrated one.

During the webinar I made reference to some resources and techniques that can assist the over extended DBA avoid downtime and here are some highlights….

Monitoring and Alerting
It’s important that you have some indications that something is reaching its capacity. It might be the disk, connections to MySQL or auto_increment limit on a highly used table. There is quite the landscape to cover but here are a handful of helpful tools:
* Percona Monitoring Plugins
* Monyog
* New Relic

Query Tuning
Poorly performing SQL can be indicative that the configuration is incorrect, that there’s a missing index or that your development team needs a quick lesson on MySQL anti-patterns. Arm yourself with proof that the SQL statements are substandard using these resources and work with the source to make things more efficient:
* Percona Cloud Tools
* pt-query-digest, explain, indexes

High Availability
If you need to ensure that your application survives hiccups such as hardware failure or network impairment, a well deployed HA solution will give you the peace of mind that you can quickly mitigate bumps in the road.
Percona XtraDB Cluster, Galera
* Percona Replication Manager
* LinuxHA/Corosync/DRBD

A wise man once quoted “A backup today saves you tomorrow.” Covering all bases can be the difference between recovering from a catastrophic failure and job hunting. Mixing logical, physical and incremental backups while adding in some offsite copies can provide you with the safety net in the event that a small mistake like a dropped table is met or worse, all working copies of data and backups are lost in a SAN failure. It happens so be prepared.
* Percona XtraBackup
* mydumper
* mysqldump
* mysqlbinlog (5.6)
* mylvmbackup

We had some great questions from the attendees and regrettably were unable to answer them all, so here are some of them with my response.

Truncated by Planet PHP, read more at the original (another 5958 bytes)

Peter ZaitsevFaster restarts for MySQL and Percona Server 5.6.21+ (1.12.2014, 12:34 UTC)

Faster restarts for MySQL and Percona Server 5.6.21+By default in MySQL 5.6, each time MySQL is started (regular start or crash recovery), it iterates through all the binlog files when GTIDs are not enabled. This can take a very long time if you have a large number of binary log files. MySQL and Percona Server 5.6.21+ have a fix with the simplified-binlog-gtid-recovery option. Let’s explore this issue.

Understanding the issue

It was first reported by Yoshinori @ Facebook (bug #69097).

Let’s start by looking at a MySQL 5.6 instance where binary logging is enabled but GTIDs are disabled.

If we restart MySQL with strace, we’ll see:

# strace -e open service mysql start
open("/var/lib/mysql5621/mysql-bin.000004", O_RDONLY) = 13
open("/var/lib/mysql5621/mysql-bin.000003", O_RDONLY) = 13
open("/var/lib/mysql5621/mysql-bin.000002", O_RDONLY) = 13
open("/var/lib/mysql5621/mysql-bin.000001", O_RDONLY) = 13

MySQL opens all binary log files in reverse order. This can bite if you have lots of binlog files or if the binlog files are large.

This does not happen with MySQL 5.5, so why such a change? The reason is … GTIDs. If you look at the bug report, MySQL tries to initialize a few GTID-related settings even if gtid_mode = OFF

The same kind of issue happens when you have binlog files with GTIDs and binlog files without GTIDs:

# strace -e open service mysql start
open("/var/lib/mysql5621/mysql-bin.000010", O_RDONLY) = 13
open("/var/lib/mysql5621/mysql-bin.000001", O_RDONLY) = 13
open("/var/lib/mysql5621/mysql-bin.000002", O_RDONLY) = 13
open("/var/lib/mysql5621/mysql-bin.000003", O_RDONLY) = 13
open("/var/lib/mysql5621/mysql-bin.000004", O_RDONLY) = 13
open("/var/lib/mysql5621/mysql-bin.000005", O_RDONLY) = 13
open("/var/lib/mysql5621/mysql-bin.000006", O_RDONLY) = 13  -> First binlog file with GTIDs

Actually you can see that MySQL will do 2 scans: a reverse scan and a forward scan. Here not all binlogs need to be opened: MySQL will stop scanning files as soon as it finds GTID information. But that can again bite if you have just turned on GTIDs (and therefore most binlog files do not contain any GTID information).

Now what happens if you set gtid_mode = ON from the start or if all the binlog files without any GTID information have been removed?

# strace -e open service mysql start
open("/var/lib/mysql5621/mysql-bin.000011", O_RDONLY) = 13
open("/var/lib/mysql5621/mysql-bin.000006", O_RDONLY) = 13

Only the newest and the oldest binlog files are opened, which is expected.

The fix

The fix is easy: simply add simplified-binlog-gtid-recovery = 1 in your configuration file. When this is set, MySQL will open at most 2 binlog files: the newest one and the oldest one. See the documentation.

Let’s see what happens with our server containing some binlog files without GTID information:

# strace -e open service mysql start
open("/var/lib/mysql5621/mysql-bin.000012", O_RDONLY) = 13
open("/var/lib/mysql5621/mysql-bin.000001", O_RDONLY) = 13

What is the performance overhead of the binlog scans? Of course YMMV, but I did a quick test on my laptop by creating 80x 100MB binlog files: by default, startup takes 6s while with simplified-binlog-gtid-recovery=1 it only takes 2s. 3x improvement with a single setting, not bad!


It is good to see that regressions introduced in MySQL 5.6 are being fixed over time. This one is pretty nasty as most people not using GTIDs will never think that something related to GTIDs can actually create performance issues. Is there any drawback if you enable this setting? I can’t think of any, so I’m hoping it will be enabled by default in 5.7.

The post Faster restarts for MySQL and Percona Server 5.6.21+ appeared first on MySQL Performance Blog.

Venu AnugantiTemporary Post Used For Theme Detection (91f1f984-c02e-49fd-b202-5c107fc2477d – 3bfe001a-32de-4114-a6b4-4005b770f6d7) (1.12.2014, 06:31 UTC)
This is a temporary post that was not deleted. Please delete this manually. (95854237-8fb8-4524-99be-b6bef2063e6c – 3bfe001a-32de-4114-a6b4-4005b770f6d7)
Jean-Jerome SchmidtMulti-source Replication with MariaDB Galera Cluster (30.11.2014, 16:33 UTC)
December 1, 2014
By Severalnines

MariaDB 10 supports multi-source replication, and each MariaDB Galera node can have up to 64 masters connected to it. So it is possible to use a MariaDB Cluster as an aggregator for many single-instance MariaDB master servers.

In this blog post, we are going to show you how to setup multi-source replication with MariaDB Galera Cluster, where one of the Galera nodes is acting as slave to 3 MariaDB masters (see diagram below). If you would like to set this up with Percona XtraDB Cluster or Galera Cluster (Codership), please read this post instead.




MariaDB has a different implementation of Global Transaction ID (GTID), and is enabled by default starting from MariaDB 10.0.2. Multi-source replication in MariaDB works with both GTID and the legacy binlog file and position, as compared to the MySQL implementation

A GTID consists of three separated values:

  • Domain ID - Replication domain. A replication domain is a server or group of servers that generate a single, strictly ordered replication stream.
  • Server ID - Server identifier number to enable master and slave servers to identify themselves uniquely.
  • Event Group ID - A sequence number for a collection of events that are always applied as a unit. Every binlog event group (eg. transaction, DDL, non-transactional statement) is annotated with its GTID.

The figure below illustrates the differences between the two GTIDs:

In MariaDB, there is no special configuration needed on the server to start using GTID. Some of MariaDB GTID advantages:

  • It is easy to identify which server or domain the event group is originating from
  • You do not necessarily need to turn on binary logging on slaves
  • It allows multi-source replication with distinct domain ID
  • Enabling GTID features is dynamic, you don’t have to restart the MariaDB server
  • The state of the slave is recorded in a crash-safe way

Despite the differences between these two, it is still possible to replicate from MySQL 5.6 to MariaDB 10.0 or vice versa. However, you will not be able to use the GTID features to automatically pick the correct binlog position when switching to a new master. Old-style MySQL replication will work. We highly recommend you to read the MariaDB GTID knowledge base.


MariaDB Galera Cluster as Slave


In our setup, we used MariaDB 10.0.14 on masters and MariaDB Galera Cluster 10.0.14 as slave. We have three master servers (mariadb1, mariadb2, mariadb3) and each master has a separate database: mydb1, mydb2 and mydb3. The 3 servers replicate to a Galera node (mgc1) in multi-source mode.

When using multi-source replication, where a single slave connects to multiple masters, each master needs to be configured with its own distinct domain ID.

read more

Jean-Jerome SchmidtWe’re hiring (again)! This time we’re looking for a great support engineer! (27.11.2014, 12:11 UTC)
November 27, 2014
By Severalnines



We're looking for a great and customer focused support engineer (preferably in North American/European timezones) to join our small and agile support team. This position is full-time and pay is negotiable. The hours are flexible and work can be done remotely; in fact, it will need to be done remotely, we all work from home ;-)


Severalnines is a self-funded startup with a dozen employees; headquartered in Stockholm, Sweden and with a globally distributed, home-office based team. We provide automation and management software for database clusters. Our ClusterControl product is the leading database automation platform for database clusters and is used by thousands of companies.


We were founded in 2011 and launched our product that same year, so you’ll be working with a popular, tried & tested product. There is opportunity to have an immediate impact though as we’re a small team and there is plenty of work to be done as the product continues to gain in popularity and our user-base continuously grows. 


The ideal candidate has the following skills set (see full description): 

  • 5 years experience with MySQL in production environments
  • Advanced knowledge of MySQL Clustering and Replication
    • Galera
    • NDB
    • MySQL Replication (async/semi)
  • Experience with database design, administration and architecture
  • Experience with NoSQL databases in production environments
    • For example MongoDB, Cassandra, Riak
  • Experience with AWS and/or OpenStack administration in production environments
    • Expertise in virtualization technologies (docker, xen, AWS EC2, vmware for example)
  • Experience in working remotely from home and collaborating with team members located in other countries
  • Good written/spoken English skills


A full job description can be found here:


read more

Jean-Jerome SchmidtNew Webinar: Galera Cluster for MySQL vs MySQL (NDB) Cluster: A High Level Comparison (26.11.2014, 16:01 UTC)
November 26, 2014
By Severalnines

Galera Cluster for MySQL, Percona XtraDB Cluster and MariaDB Cluster (the three “flavours” of Galera Cluster) make use of the Galera WSREP libraries to handle synchronous replication.MySQL Cluster is the official clustering solution from Oracle, while Galera Cluster for MySQL is slowly but surely establishing itself as the de-facto clustering solution in the wider MySQL eco-system.  

In this webinar, we will look at all these alternatives and present an unbiased view on their strengths/weaknesses and the use cases that fit each alternative.

This webinar will cover the following:

  • MySQL Cluster architecture: strengths and limitations
  • Galera Architecture: strengths and limitations
  • Deployment scenarios
  • Data migration
  • Read and write workloads (Optimistic/pessimistic locking)
  • WAN/Geographical replication
  • Schema changes
  • Management and monitoring




Thursday, December 11th 2014 at 09:00 GMT (UK) / 10:00 CET (Germany, France, Sweden)

Register Now

North America/LatAm

Thursday, December 11th 2014 at 9:00 Pacific Time (US) / 12:00 Eastern Time (US)

Register Now



Alex Yu, VP of Products, Severalnines


read more

Peter ZaitsevMySQL benchmarking: Know your baseline variance! (26.11.2014, 11:00 UTC)

Often enough I find MySQL benchmark results where the difference between results is 1% or even less and some conclusions are drawn. Now it is not that 1% is not important – especially when you’re developing the product you should care about those 1% improvements or regressions because they tend to add up. However with such a small difference it is very important to understand whenever this is for real or it is just the natural variance for your baseline test.

Take a look at this graph:
Click the image for a larger view)


MySQL benchmarking: Know your baseline variance

This is the result for a simple in-memory, read-only “select by primary key” SysBench benchmark on dedicated physical hardware that is otherwise idle, simple 1 socket system. I tried to stabilize it as much as possible, for example disabling CPU frequency scaling. But still I saw some 3% difference between “good runs” and bad runs.

What is the difference between those runs? Simply mysqld restarts.

Does this mean you can’t measure a smaller difference? You can by setting the appropriate test plan. Often having several runs makes sense, in others you need to make sure the system warms up before taking measurements or having benchmark runs that are long enough. Whatever method you use it is a good idea to apply your test methodology by conducting several runs of your baseline run to ensure the results are stable enough for your purpose. For example If I decided to do five 30-minute runs and average the results, if they all run within 0.1% I will consider 0.3% differences as meaningful.

Another practical trick that often helps me to separate real differences from some side effects is mixing the tests. Say if I have configurations I’m testing A and B instead of doing AAA BBB I would do ABABAB which helps with the case when there is some regression that can accumulate over time, such as with Flash.

You should also note that in modern systems there is almost always something happening in the background that can change performance – the SSD is doing garbage collection, MySQL (or Kernel) is flushing dirty pages, the CPU can even simply cool off and as a result being able to support Turbo-boost operations a little longer. So when you are stat running your benchmarks make sure you keep the idle time between runs the same – scripting benchmarks and iterating scenarios helps here.

Happy MySQL benchmarking!

The post MySQL benchmarking: Know your baseline variance! appeared first on MySQL Performance Blog.

Peter ZaitsevPercona XtraDB Cluster 5.6.21-25.8 is now available (25.11.2014, 15:45 UTC)

Percona XtraDB Cluster 5.6.21-25.8Percona is glad to announce the new release of Percona XtraDB Cluster 5.6 on Novmeber 25th 2014. Binaries are available from downloads area or from our software repositories. We’re also happy to announce that Ubuntu 14.10 and CentOS 7 users can download, install, and upgrade Percona XtraDB Cluster 5.6 from Percona’s software repositories.

Based on Percona Server 5.6.21-70.1 including all the bug fixes in it, Galera Replicator 3.8, and on Codership wsrep API 25.7, Percona XtraDB Cluster 5.6.21-25.8 is now the current General Availability release. All of Percona‘s software is open-source and free, and all the details of the release can be found in the 5.6.21-25.8 milestone at Launchpad.

New Features:

  • Galera 3.8 introduces auto-eviction for nodes in the cluster experiencing network issues like packet loss. It is off by default and is turned on with evs.auto_evict option. This feature requires EVS protocol version (evs.version) 1. During the EVS protocol upgrade all membership changes are communicated over EVS protocol version 0 to preserve backwards compatibility, protocol is upgraded to the highest commonly supported version when forming a new group so if there exist a single node with older version in the group, the group protocol version remains as 0 and auto-eviction is not functional. (#1274192).
  • Percona XtraDB Cluster now supports backup locks in XtraBackup SST (in the default xtrabackup-v2 wsrep_sst_method). Backup locks are used in lieu of FLUSH TABLES WITH READ LOCK on the donor during SST. This should allow for minimal disruption of existing and incoming queries, even under high load. Thus, this should allow for even faster SST and node being in ‘donor/desynced’ state. This also introduces following constraints: Percona XtraDB Cluster 5.6.21 requires Percona XtraBackup 2.2.5 or higher; An older (= 5.6.21) donor. This is enforced through SST versioning (sent from joiner to donor during SST) and logged to error log explicitly. (#1390552).
  • Percona XtraDB Cluster is now shipped with Galera MTR test suite.

Bugs Fixed:

  • Percona XtraDB Cluster now shows a warning in case additional utilities, like pv which may not affect critical path of SST, are not installed. Bug fixed #1248688.
  • mysqldump SST can now use username/password from wsrep_sst_auth under group of [sst] in my.cnf in order not to display the credentials in the error log. Bug fixed #1293798.
  • Normal shutdown under load would cause server to remain hanging because replayer failed to finish. Bug fixed #1358701.
  • wsrep_causal_reads variable was not honored when declared as global. Bug fixed

Truncated by Planet PHP, read more at the original (another 5904 bytes)

Peter Zaitsev(More) Secure local passwords in MySQL 5.6 and up (25.11.2014, 08:00 UTC)

I log into a lot of different servers running MySQL and one of the first things I do is create a file in my home directory called ‘.my.cnf’ with my credentials to that local mysql instance:


This means I don’t have to type my password in every time, nor am I tempted to include it on the command line with -p and get the dreaded (but completely accurate):

Warning: Using a password on the command line interface can be insecure.

MySQL 5.6 introduces a utility to make this easier and more secure. First, let’s start with a new mysqld instance with a blank root password and make it more secure:

[vagrant@localhost ~]$ mysqladmin -u root password
New password:secret
Confirm new password:secret
[vagrant@localhost ~]$ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Ok, so I’ve added a password, now I want to create my .my.cnf file:

[vagrant@localhost ~]$ mysql_config_editor set --user=root --password
Enter password:secret
[vagrant@localhost ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 10
Server version: 5.6.21-70.0 Percona Server (GPL), Release 70.0, Revision 688
Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, 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
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

What did ‘mysql_config_editor set’ actually do? It creates a .mylogin.cnf file (which stands in for a .my.cnf) in my home directory that contains my credentials, just in encrypted form:

[vagrant@localhost ~]$ ls -alh .my*
-rw-------. 1 vagrant vagrant 100 Nov 19 16:20 .mylogin.cnf
-rw-------. 1 vagrant vagrant  29 Nov 19 16:20 .mysql_history
[vagrant@localhost ~]$ cat .mylogin.cnf
>NTv&S/,        >ј$%KZ 9iVjK䉦H[
[vagrant@localhost ~]$ mysql_config_editor print
user = root
password = *****

The mysql client picks this up right away and will use it by default. This file has good default filesystem permissions, is local to my homedir, and is a whole lot better than specifying it on the command line or typing it in every time.

This utility also supports a feature called ‘login-path’ wherein you can add multiple mysql logins (perhaps to different servers) and refer to them with the —login-path option in the mysql client:

[vagrant@localhost ~]$ mysql_config_editor set --login-path=remote --host=remote --user=remote --password
Enter password:secure
[vagrant@localhost ~]$ mysql_config_editor print --all
user = root
password = *****
user = remote
password = *****
host = remote
[vagrant@localhost ~]$ mysql --login-path=remote
ERROR 2005 (HY000): Unknown MySQL server host 'remote' (0)

The ‘remote’ host doesn’t exist here, but you get the idea. You can create as many login-paths as you want with varied hostnames, credentials and other login parameters and quickly access them with any client supporting login-path.

Now, how secure is this really?  This isn’t secure from anyone who roots your DB server.  I would say the benefits are more about reducing careless password storage and tidier management of local credentials.

The post (More) Secure local passwords in MySQL 5.6 and up appeared first on MySQL Performance Blog.

Peter ZaitsevPercona Server 5.6.21-70.1 is now available (24.11.2014, 15:28 UTC)

Percona ServerPercona is glad to announce the release of Percona Server 5.6.21-70.1 on November 24, 2014. Download the latest version from the Percona web site or from the Percona Software Repositories.

Based on MySQL 5.6.21, including all the bug fixes in it, Percona Server 5.6.21-70.1 is the current GA release in the Percona Server 5.6 series. Percona Server is open-source and free. Complete details of this release can be found in the 5.6.21-70.1 milestone on Launchpad.

Bugs Fixed:

  • A slave replicating in RBR mode would crash, if a table definition between master and slave differs with an allowed conversion, and the binary log contains a table map event followed by two row log events. This bug is an upstream regression introduced by a fix for bug #72610. Bug fixed #1380010.
  • An incorrect source code function attribute would cause MySQL to crash on an InnoDB row write, if compiled with a recent GCC with certain compilation options. Bug fixed #1390695 (upstream #74842).
  • MTR tests for Response Time Distribution were not packaged in binary packages. Bug fixed #1387170.
  • The RPM packages provided for CentOS 5 were built using a debugging information format which is not supported in the gdb version included with CentOS 5.10. Bug fixed #1388972.
  • A session on a server in mixed mode binlogging would switch to row-based binlogging whenever a temporary table was created and then queried. This switch would last until the session end or until all temporary tables in the session were dropped. This was unnecessarily restrictive and has been fixed so that only the statements involving temporary tables were logged in the row-based format whereas the rest of the statements would continue to use the statement-based logging. Bug fixed #1313901 (upstream #72475).

Other bugs fixed: #1387227, and #1388001.

Release notes for Percona Server 5.6.21-70.1 are available in the online documentation. Please report any bugs on the launchpad bug tracker

The post Percona Server 5.6.21-70.1 is now available appeared first on MySQL Performance Blog.

LinksRSS 0.92   RDF 1.
Atom Feed