Monthly Archives: October 2013

New MySQL features, related technologies at Percona Live London

by

Call for papers: Percona Live LondonThe upcoming Percona Live London conference, November 11-12, features quite a number of talks about the latest MySQL features and related technologies. There will be a lots of talks about the new MySQL 5.6 features:

  • Opening keynote highlights MySQL 5.6 new features.
  • New InnoDB Compression talk will cover the new compression algorithm, implemented by Facebook and included in MySQL 5.6.
  • New MySQL Replication features, including multi-treaded slave applier, Global Transaction Ids which can help for automatic failover and lots of performance optimizations and much).

Altho MySQL 5.6 is a very important milestone there are much more interesting technologies going on around MySQL. Here are some of the talks, which look pretty interesting (at least for me):

NoSQL World

Hadoop

Hadoop is a relatively new topic at MySQL conferences, however, it gains more and more traction, especially after MySQL applier for Hadoop (alpha version) release. Danil  Zburivsky will be talking about building a data warehouse with Hadoop and MySQL. I personally have a strong interest in Hadoop and recently  did a webinar about this topic. Hadoop concept is very different from MySQL, but there are a lots of real use cases where Hadoop will fit best.

MongoDB

MongoDB is a another interesting technology. There will be full MongoDB tutorial by Stephane Combaudon as well as MongoDB for MySQL Guru talk by Robert Hodges (Continuent) and Tim Callaghan (Tokutek)

New MySQL Cluster features.

MySQL Cluster 7.3 (based on  a mainline MySQL Server 5.6 release + NDBCluster storage engine) was recently released. Johan Andersson will cover some new MySQL Cluster 7.3 features in his MySQL Cluster Performance Tuning talk, including foreign key constrains (Foreign key constrains were the “showstopper” for many customers), memcached integration, etc. I knew Johan from the early MySQL Ab days and he always was (and now is) “the MySQL Cluster guy”, so I’m sure he will show some new MySQL cluster magic.

Other Storage Engines

TokuDB features the fractal tree and compression. Vadim blogged about using TokuDB  for storing timeseries data and it looks promising. Tim Callaghan of Tokutek will talk about Fractal Tree Indexes.

MariaDB contains the CONNECT engine (to join data between Oracle and Cassandra for example) and SPIDER storage engine (for automatic “sharding”). Colin Charles from Monty Program Ab will talk about new MariaDB features

Percona Live London is approaching fast so be sure to register today!

The post New MySQL features, related technologies at Percona Live London appeared first on MySQL Performance Blog.

High-availability options for MySQL, October 2013 update

by

table
{
border-collapse:collapse;
}
table, td, th
{
border:1px solid black;
padding:1px;
vertical-align:top;
font-size:9px;
}
th
{
background-color:lightgray;
}
ul
{
padding:5px;
margin:15px;
}

The technologies allowing to build highly-available (HA) MySQL solutions are in constant evolution and they cover very different needs and use cases. In order to help people choose the best HA solution for their needs, we decided, Jay Janssen and I, to publish, on a regular basis (hopefully, this is the first), an update on the most common technologies and their state, with a focus on what type of workloads suite them best. We restricted ourselves to the open source solutions that provide automatic failover. Of course, don’t simply look at the number of Positives/Negatives items, they don’t have the same values. Should you pick any of these technologies, heavy testing is mandatory, HA is never beyond scenario that have been tested.

Percona XtraDB Cluster (PXC)

Percona XtraDB Cluster (PXC) is a version of Percona Server implementing the Galera replication protocol from Codeship.

Positive pointsNegative points
  • Almost synchronous replication, very small lag if any
  • Automatic failover
  • At best with small transactions
  • All nodes are writable
  • Very small read after write lag, usually no need to care about
  • Scale reads very well and to some extent, writes
  • New nodes are provisioned automatically through State Snapshot Transfer (SST)
  • Multi-threaded apply, greater write capacity than regular replication
  • Can do geographical disaster recovery (Geo DR)
  • More resilient to unresponsive nodes (swapping)
  • Can resolve split-brain situations by itself
  • Still under development, some rough edges
  • Large transactions like multi-statement transactions or large write operations cause issues and are usually not a good fit
  • For quorum reasons, 3 nodes are needed but one can be a lightweight arbitrator
  • SST can be heavy over a Wan
  • Commit are affected by the network latency, this impacts especially Geo DR
  • To achieve HA, a load balancer, like haproxy, is needed
  • Failover time is determined by the load balancer check frequency
  • Performance is affected by the weakest/busiest node
  • Foreign Keys are potential issues
  • MyISAM should be avoided
  • Can be mixed with regular async replication as master or slave but, slaves are not easy to reconfigure after a SST on their master
  • Require careful setup of the host, swapping can lead to node expulsion from the cluster
  • No manual failover mode
  • Debugging some Galera protocol issues isn’t trivial

 

Percona replication manager (PRM)

Percona replication manager (PRM) uses the Linux HA Pacemaker resource manager to manage MySQL and replication and provide high-availability. Information about PRM can be found here, the official page on the Percona web site is in the making.

Positive pointsNegative points
  • Nothing specific regarding the workload
  • Unlimited number of slaves
  • Slaves can have different roles
  • Typically VIP based access, typically 1 writer VIP and many reader VIPs
  • Also works without VIP (see the fake_mysql_novip agent)
  • Detects if slave lags too much and remove reader VIPs
  • All nodes are monitored
  • The best slaves is picked for master after failover
  • Geographical Disaster recovery possilbe with the lightweight booth protocol
  • Can be operated in manual failover mode
  • Graceful failover is quick, under 2s in normal conditions
  • Ungraceful failover under 30s
  • Distributed operation with Pacemaker, no single point of failure
  • Builtin pacemaker logic, stonith, etc. Very rich and flexible.
  • Still under development, some rough edges
  • Transaction maybe lost is master crashes (async replication)
  • For quorum reasons, 3 nodes are needed but one can be a lightweight arbitrator
  • Only one node is writable
  • Read after write may not be consistent (replication lag)
  • Only scales reads
  • Careful setup for the host, swapping can lead to node expulsion from the cluster
  • Data inconsistency can happen if the master crashes (fix coming)
  • Pacemaker is complex, logs are difficult to read and understand

 

MySQL master HA (MHA)

Like with PRM above, MySQL master HA (MHA), provides high-availability through replication. The approach is different, instead of relying on an HA framework like Pacemaker, it uses Perl scripts. Information about MHA can be found here.

Positive pointsNegative points
  • Mature
  • Nothing specific regarding the workload
  • No latency effects on writes
  • Can have many slaves and slaves can have different roles
  • Very good binlog/relaylog handling
  • Work pretty hard to minimise data loss
  • Can be operated in manual failover mode
  • Graceful failover is quick, under 5s in normal conditions
  • If the master crashes, slaves will be consistent
  • The logic is fairly easy to understand
  • Transaction maybe lost is master crashes (async replication)
  • Only one node is writable
  • Read after write may not be consistent (replication lag)
  • Only scales reads
  • Monitoring and logic are centralized, single-point of failure, a network partition can cause a split-brain
  • Custom fencing devices, custom VIP scripts, no reuse of other projects tools
  • Most of the deployments are using manual failover (at least at Percona)
  • Requires priviledged ssh access to read relay-logs, can be a security concern
  • No monitoring of the slave to invalidate it if it lags too much or if replication is broken, need to be done by external tool like HAProxy
  • Careful setup for the host, swapping can lead to node expulsion from the cluster

 

NDB Cluster

NDB cluster is the most high-end form of high-availability configuration for MySQL. It is a complete shared nothing architecture where the storage engine is distributed over multiple servers (data nodes). Probably the best starting point with NDB is the official document, here.

Positive pointsNegative points
  • Mature
  • Synchronous replication
  • Very good at small transactions
  • Very good at high concurrency (many client threads)
  • Huge transaction capacity, more than 1M trx/s are not uncommon
  • Failover can be ~1s
  • No single point of failure
  • Geographical disaster recovery capacity built-in
  • Strong at async replication, applying by batches gives multithreaded apply at the data node level
  • Can scale reads and writes, the framework implements sharding by hashes
  • Not a drop-in replacement for Innodb, you need to tune the schema and the queries
  • Not a general purpose database, some loads like reporting are just bad
  • Only the Read-commited isolation level is available
  • Hardware heavy, need 4 servers mininum for full HA
  • Memory (RAM) hungry, even with disk-based tables
  • Complex to operate, lots of parameters to adjust
  • Need a load balancer for failover
  • Very new foreign key support, field reports scarce on it

 

Shared storage/DRBD

Achieving high-availability use a shared storage medium is an old and well known method. It is used by nearly all the major databases. The share storage can be a DAS connected to two servers, a LUN on SAN accessible from 2 servers or a DRBD partition replicated synchronously over the network. DRBD is by bar the most common shared storage device used in the MySQL world.

Positive pointsNegative points
  • Mature
  • Synchronous replication (DRBD)
  • Automatic failover is easy to implement
  • VIP based access
  • Write capacity is impacted by network latency for DRBD
  • SANs are expensive
  • Only for InnoDB
  • Standby node, a big server doing nothing
  • Need a warmup period after failover to be fully operational
  • Disk corruption can spread

 

The post High-availability options for MySQL, October 2013 update appeared first on MySQL Performance Blog.

Percona Server 5.6.14-62.0 is now available

by

Percona Server version 5.6.14-62.0

Percona Server version 5.6.14-62.0

Percona is glad to announce the release of Percona Server 5.6.14-62.0 on October 24th, 2013 (Downloads are available here and from the Percona Software Repositories.

Based on MySQL 5.6.14, including all the bug fixes in it, Percona Server 5.6.14-62.0 is the current GA release in the Percona Server 5.6 series. All of Percona’s software is open-source and free, all the details of the release can be found in the 5.6.14-62.0 milestone at Launchpad.

New Features:

Bugs Fixed:

  • Percona Server could crash server could crash while accessing BLOB or TEXT columns in InnoDB tables if the Support for Fake Changes was enabled. Bug fixed #1188168.
  • Expanded Program Option Modifiers did not deallocate memory correctly. Bug fixed #1167487.
  • Some Expanded Program Option Modifiers didn’t have an effect if they were specified in non-normalized way (innodb_io_capacity vs innodb-io-capacity). Bug fixed #1233294.
  • Building Percona Server with -DHAVE_PURIFY option would result in an error. Fixed by porting the close_socket function from MariaDB. Bug fixed #1203567.
  • Enabling Enforcing Storage Engine feature could lead to error on Percona Server shutdown. Bug fixed #1233354.
  • Storage engine enforcement (enforce_storage_engine) is now ignored when the server is started in either bootstrap or skip-grant-tables mode. Bug fixed #1236938.
  • When installed Percona Server 5.6.13-61.0 GA release was still showing RC instead of GA on Debian-based systems. Bug fixed #1239418.

Other bugs fixed: bug fixed #1238008, bug fixed #1190604, bug fixed #1200162, bug fixed #1188172, and bug fixed #1214727.

Release notes for Percona Server 5.6.14-62.0 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

The post Percona Server 5.6.14-62.0 is now available appeared first on MySQL Performance Blog.

Percona Server 5.6.14 with TokuDB 7.1 Experimental build

by

We just announced Percona Server 5.6.14 release, and I decided to make this build with the TokuDB 7.1 engine.
The build is available here in the TESTING area.

You are welcome to play with these binaries, but keep in mind:

  • This is an experimental build and not supposed to be used in production environment
  • The binaries built on Ubuntu 12.04 LTS server and may not work on other Linux distributions

The post Percona Server 5.6.14 with TokuDB 7.1 Experimental build appeared first on MySQL Performance Blog.

Percona Server 5.1.72-14.10 is now available

by

Percona Server version 5.1.72-14.10

Percona Server version 5.1.72-14.10

Percona is glad to announce the release of Percona Server 5.1.72-14.10 on October 28th, 2013 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.1.72, including all the bug fixes in it, Percona Server 5.1.72-14.10 is now the current stable release in the 5.1 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.1.72-14.10 milestone at Launchpad.

Bugs Fixed:

  • Due to an incompatible upstream change that went in unnoticed, the log tracker thread would attempt to replay any file operations it encountered. In most cases this were a no-op, but there were race conditions for certain DDL operations that would have resulted in server crash. Bug fixed #1217002.
  • apt-get upgrade of Percona Server would fail in post-installation step if server failed to start. Bug fixed #1002500.
  • Fixed the libssl.so.6 dependency issues in binary tarballs releases. Bug fixed #1172916.
  • Percona Server could crash server could crash while accessing BLOB or TEXT columns in InnoDB tables if Support for Fake Changes was enabled. Bug fixed #1188168.
  • A server could crash due to a race condition between a INNODB_CHANGED_PAGES query and a bitmap file delete by PURGE CHANGED_PAGE_BITMAP or directly on the file system. Bug fixed #1191580.

Other bug fixes: bug fixed #1191589.

Release notes for Percona Server 5.1.72-14.10 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

The post Percona Server 5.1.72-14.10 is now available appeared first on MySQL Performance Blog.

Percona Server 5.5.34-32.0 is now available

by

Percona Server version 5.5.34-32.0

Percona Server version 5.5.34-32.0

Percona is glad to announce the release of Percona Server 5.5.34-32.0 on October 28th, 2013 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.34, including all the bug fixes in it, Percona Server 5.5.34-32.0 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.5.34-32.0 milestone at Launchpad.

New Features:

  • Percona Server has extended the SELECT INTO ... OUTFILE and SELECT INTO DUMPFILE to add the support for UNIX sockets and named pipes.
  • Percona Server now provides additional information in the slow query log when log_slow_rate_limit variable is enabled.
  • A new variable slow_query_log_always_write_time has been introduced. It can be used to specify an additional execution time threshold for the slow query log, that, when exceeded, will cause a query to be logged unconditionally, that is, log_slow_rate_limit will not apply to it.
  • Utility user feature has been extended by adding a new utility_user_privileges that allows a comma separated value list of extra access privileges that can be granted to the utility user.

Bugs Fixed:

  • Due to an incompatible upstream change that went in unnoticed, the log tracker thread would attempt to replay any file operations it encountered. In most cases this were a no-op, but there were race conditions for certain DDL operations that would have resulted in server crash. Bug fixed #1217002.
  • apt-get upgrade of Percona Server would fail in post-installation step if server failed to start. Bug fixed #1002500.
  • Fixed the libssl.so.6 dependency issues in binary tarballs releases. Bug fixed #1172916.
  • Error in install_layout.cmake could cause that some library files, during the build, end up in different directories on x86_64 environment. Bug fixed #1174300.
  • Percona Server could crash while accessing BLOB or TEXT columns in InnoDB tables if Support for Fake Changes was enabled. Bug fixed #1188168.
  • Memory leak was introduced by the fix for bug #1132194. Bug fixed #1204873.
  • The unnecessary overhead from persistent InnoDB adaptive hash index latching has been removed, potentially improving stability of the Multiple Adaptive Hash Search Partitions feature as well. Upstream bug fixed #70216, bug fixed #1218347.
  • Fixed the incorrect dependency with libmysqlclient18-dev from Percona Server 5.5.33-31.1. Bug fixed #1237097.
  • A memory leak in Utility user feature has been fixed. Bug fixed #1166638.
  • Expanded Program Option Modifiers did not deallocate memory correctly. Bug fixed #1167487.
  • A server could crash due to a race condition between a INNODB_CHANGED_PAGES query and a bitmap file delete by PURGE CHANGED_PAGE_BITMAP or directly on the file system. Bug fixed #1191580.
  • Percona Server could not be built with Thread Pool feature and -DWITH_PERFSCHEMA_ENGINE=OFF option. Bug fixed #1196383.
  • Building Percona Server with -DHAVE_PURIFY option would result in an error. Fixed by porting the close_socket function from MariaDB. Bug fixed #1203567.
  • Adaptive hash index memory size was incorrectly calculated in SHOW ENGINE INNODB STATUS and Innodb_mem_adaptive_hash status variable. Bug fixed #1218330.
  • Some Expanded Program Option Modifiers didn’t have an effect if they were specified in non-normalized way (innodb_io_capacity vs innodb-io-capacity). Bug fixed #1233294.
  • Enabling Enforcing Storage Engine feature could lead to error on Percona Server shutdown. Bug fixed #1233354.
  • Storage engine enforcement (enforce_storage_engine) is now ignored when the server is started in either bootstrap or skip-grant-tables mode. Bug fixed #1236938.
  • Fixed the build warnings caused by User Statistics code on non-Linux platforms. Bug fixed #711817.
  • Adaptive hash indexing partitioning code has been simplified, potentially improving performance. Bug fixed #1218321.

Other bugs fixed: bug fixed #1239630, bug fixed #1191589, bug fixed #1200162, bug fixed #1214449, and bug fixed #1190604.

Release notes for Percona Server 5.5.34-32.0 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

The post Percona Server 5.5.34-32.0 is now available appeared first on MySQL Performance Blog.

MySQL 5.6 New Replication Features: Webinar followup Q&A

by

Percona MySQL webinar Q&A: 5.6 New Replication FeaturesI want to thank all attendees of my webinar, “MySQL 5.6 New Replication Features: Benefits, Challenges and Limitations“. We had questions that I didn’t have the time to answer:

Q: If I run on Amazon’s RDS, do I need to worry about enabling crash-safe slaves, or is that already in place?

A: Crash-safe replication is already configured for read replicas using MySQL 5.6.

Q: How the relay log purge will manage in case of multiple db’s replication running on multiple threads?

A: Same thing as with single-threaded replication: when all event in a relay log file have been executed, the relay log is removed. The only difference is that executing the last event of a file no longer means that all previous events have been executed, because execution gaps can happen with multi-threaded replication. However a checkpoint is performed from time to time (see slave_checkpoint_period setting, default is 300ms) to make sure that no event is left behind. In practice, you should not notice any difference between the purge of relay logs with single-threaded replication and with multi-threaded replication.

Q: How can we make slave only for read purpose similar to AWS RDS MASTER- SLAVE Replication where slave is read only?

A: You can set read_only = 1 on the slave. The slave won’t be strictly read-only because all users with the SUPER privilege will be able to write to the slave anyway. But if you don’t grant the SUPER privilege to your users, the slave will act as a true read-only server. This is what happened on RDS read replicas: you don’t have the SUPER privilege so you can’t write on replicas.

Q: How can binlog position be higher on a slave compared to the master – for position-based replication?

A: Each time you restart MySQL, a new binary log file will be created. So let’s say that we start a master and its slave (the slave being configured with log_slave_updates to write events received by replication in its own binary logs), both binary logs will be mysql-bin.0000001. If we now restart the slave twice, the slave will write in mysql-bin.0000003 while the master will still be writing on mysql-bin.0000001.

Q: Can we change statement-based replication to mixed replication after 1000+ transactions or not? What will be the impact?

A: You can easily switch to mixed replication as the binlog_format setting is a dynamic variable. Mixed replication means using statement-based replication by default and switching to row-based replication only for queries that are not safe for statement-based replication. So it is usually quite safe to switch from statement-based to mixed replication.

Q: server_id and server UUID – what is the difference? Do we need both simultaneously?

A: server_id and server_uuid are both identifiers of each server in a replication topology. server_id is used by replication and server_uuid is used to generate GTIDs (server_uuid has been added in MySQL 5.6). You need both.

Q: How to clarify compatibility of GTID and multi-threaded; is there any plans to fix it by Percona?

A: GTID and multi-threaded replication are compatible. The only concern is that the monitoring tools (SHOW SLAVE STATUS and the mysql.slave_relay_log_info table) are confusing when using both. This could make quite hard to fix replication if you have a replication error. We don’t have any plan to fix that at the moment.

Q: What will happen if GTID is used and a transaction that creates a temporary table is run on the master?

A: When enforce-gtid-consistency is set to ON, temporary tables are only allowed if autocommit = 1. Otherwise this will generate an error.

Q: How could remote binary log be used for backups?

A: It can allow you to mirror the binary logs in real time. However this doesn’t replace backups. You can find an example here.

Q: What is the difference between semi-synchrous replication and gtid replication?

A: By default, replication is asynchronous and doesn’t use GTIDs. Optionally, you can enable semi-sync replication to make sure at least one slave has acknowledged the writes from the master, and you can also enable GTIDs (with or without semi-sync replication) to make replication reconfiguration easier.

The post MySQL 5.6 New Replication Features: Webinar followup Q&A appeared first on MySQL Performance Blog.

MySQL on Windows: A survival guide for Linux-based DBAs

by

MySQL on Windows: A survival guide for Linux-based DBAsNext week, on Nov. 6, I will be delivering a webinar about running MySQL on Windows, with a strong focus on Linux-based sysadmins and DBAs – and how not to go crazy in the process.

An interesting (and challenging!) part of working for Percona is that you never know what kind of setup a customer will have, and even though MySQL is still strongly tied to the LAMP stack and therefore Linux, more people are running it on Windows these days.

As someone who last used Windows on a daily basis in 1999, my first reaction was usually not nice. But over time, I have learned to embrace these kind of cases as an opportunity to get out of my comfort zone and learn. I still personally prefer a Unix derivative, but over time, I have gathered some knowledge that I think can be useful to others who arrive at MySQL on Windows from a Linux/Unix background. And who knows? Perhaps I can even give some insight to longtime Windows admins who are newcomers to the world of MySQL!

If you’re interested, register here to attend. The webinar starts at 10 a.m. PST on Nov. 6. It will also be recorded and the replay available using that same link.

The post MySQL on Windows: A survival guide for Linux-based DBAs appeared first on MySQL Performance Blog.

InnoDB adaptive flushing in MySQL 5.6: checkpoint age and io capacity

by

In MySQL 5.6 InnoDB has a dedicated thread (page_cleaner) that’s responsible for performing flushing operations. Page_cleaner performs flushing of the dirty pages from the buffer pool based on two factors:
– access pattern  –  the least recently used pages will be flushed by LRU flusher from LRU_list when buffer pool has no free pages anymore;
– age – the oldest modified non-flushed pages are part of flush_list structure and will be flushed by flush_list flusher based on several heuristics.

There is a good overview of the page_cleaner and also here you may find some details about flushing in MySQL 5.6. Below I describe several additional aspects of the flush_list flushing that was not really covered yet.

flush_list flushing and checkpoint age

The amount of the aged pages that is possible to keep in the flush_list is limited by the combined size of the innodb log files. So the main purpose of the flush_list flushing is to flush pages from this list with such a rate that will also always allow enough free space in the log files. On the other hand, too aggressive flushing means less write combining, unnecessary load on the I/O subsystem, in the end undoing performance benefits of having larger redo logs.  In MySQL 5.6 the amount of pages to flush is calculated in the InnoDB adaptive routine based on the current checkpoint age with the following formula:

 
percentage of the IO capacity that should be used for flushing =
        ((srv_max_io_capacity / srv_io_capacity) * (lsn_age_factor * sqrt(lsn_age_factor))) / 7.5;

We modeled that formula in R and found that it’s possible to improve it such a way that the curve becomes more flat and as a result flushing becomes less aggressive. That new formula is enabled in Percona Server 5.6 by default.

Rplot04

flush_list flushing and io_capacity

InnoDB provides two variables that allow the control of the background flushing rate – innodb_io_capacity and innodb_io_capacity_max. There is quite a detailed description for these vars. However there are several things that are not really covered in the documentation:

– innodb_io_capacity_max is the most important variable in case of adaptive flushing as only that variable actually limiting the flushing rate. See above formula and charts.

– innodb_io_capacity is used for limiting IO operations during merging of the insert buffer and flushing in cases of server inactivity/shutdown.

For practical needs, the above means the following:

– if  the MySQL server is in an active state (serving user requests) you need to adjust innodb_io_capacity_max to increase/decrease flushing rate.
– if the MySQL server is in an idle state or performing shutdown flushing of the pages from flush_list will be limited by innodb_io_capacity value only.

– if change_buffering is ON and server is in active state it will allow to use either 5% of innodb_io_capacity or vary rate from 5% to 55%  if more than 50% of insert buffer size was already used.
– if change_buffering is ON and server is idle it will use 100% of innodb_io_capacity for merge operations

The post InnoDB adaptive flushing in MySQL 5.6: checkpoint age and io capacity appeared first on MySQL Performance Blog.

Percona XtraDB Cluster/ Galera with Percona Monitoring Plugins

by

The Percona Monitoring Plugins (PMP) provide some free tools to make it easier to monitor PXC/Galera nodes.  Monitoring broadly falls into two categories: alerting and historical graphing, and the plugins support Nagios and Cacti, respectively, for those purposes.

Graphing

An update to the PMP this summer (thanks to our Remote DBA team for supporting this!) added a Galera-specific host template that includes a variety of Galera-related stats, including:

  • Replication traffic and transaction counts and average trx size
  • Inbound and outbound (Send and Recv) queue sizes
  • Parallelization efficiency
  • Write conflicts (Local Cert Failures and Brute Force Aborts)
  • Cluster size
  • Flow control

You can see examples and descriptions of all the graphs in the manual.

Alerting

There is not a Galera-specific Nagios plugin in the PMP yet, but there does exist a check that can pretty universally check any status variable you like called pmp-check-mysql-status.  We can pretty easily adapt this to check some key action-worthy Galera stats, but I hadn’t worked out the details until a customer requested it recently.

Checking for a Primary Cluster

Technically this is a cluster or cluster-partition state for whatever part of the cluster the queried node is a part of.  However, any single node could be disconnected from the rest of the cluster, so checking this on each node should be fine.  We can verify this with this check:

$ /usr/lib64/nagios/plugins/pmp-check-mysql-status -x wsrep_cluster_status -C == -T str -c non-Primary
OK wsrep_cluster_status (str) = Primary | wsrep_cluster_status=Primary;;non-Primary;0;

Local node state

We also want to verify the given node is ‘Synced’ into the cluster and not in some other state:

/usr/lib64/nagios/plugins/pmp-check-mysql-status -x wsrep_local_state_comment -C '!=' -T str -w Synced
OK wsrep_local_state_comment (str) = Synced | wsrep_local_state_comment=Synced;;Synced;0;

Note that we are only  warning when the state is not Synced — this is because it is perfectly valid for a node to be in the Donor/Desynced state.  This warning can alert us to a node in a less-than-ideal state without screaming about it, but you could certainly go critical instead.

Verify the Cluster Size

This is a bit of a sanity check, but we want to know how many nodes are in the cluster and either warn if we’re down a single node or go critical if we’re down more.  For a three node cluster, your check might look like this:

# /usr/lib64/nagios/plugins/pmp-check-mysql-status -x wsrep_cluster_size -C '<=' -w 2 -c 1
OK wsrep_cluster_size = 3 | wsrep_cluster_size=3;2;1;0;

This is OK when we have 3 nodes, warns at 2 nodes and goes critical at 1 node (when we have no redundancy left).   You could certainly adjust thresholds differently depending on your normative cluster size.   This check is likely meaningless unless we’re also in a Primary cluster, so you could set a service dependency on the Primary Cluster check here.

Check for Flow Control

Flow control is really something to keep an eye on in your cluster. We can monitor the recent state of flow control like this:

/usr/lib64/nagios/plugins/pmp-check-mysql-status -x wsrep_flow_control_paused -w 0.1 -c 0.9
OK wsrep_flow_control_paused = 0.000000 | wsrep_flow_control_paused=0.000000;0.1;0.9;0;

This warns when FC exceeds 10% and goes critical after 90%.  This may need some fine tuning, but I believe it’s a general principle that some small amount of FC might be normal, but you want to know when it starts to get more excessive.

Conclusion

Alerting with Nagios and Graphing with Cacti tend to work best with per-host checks and graphs, but there are aspects of a PXC cluster that you may want to monitor from a cluster-wide perspective.  However, most of the things that can “go wrong” are easily detectable with per-host checks and you can get by without needing a custom script that is Galera-aware.

I’d also always recommend what I call a “service check” that connects through your VIP or load balancer to ensure that MySQL is available (regardless of underlying cluster state) and can do a query.  As long as that works (proving there is at least 1 Primary cluster node), you can likely sleep through any other cluster event.  :)

The post Percona XtraDB Cluster/ Galera with Percona Monitoring Plugins appeared first on MySQL Performance Blog.