galera cluster

How Not to do MySQL High Availability: Geographic Node Distribution with Galera-Based Replication Misuse

MySQL High Availability 2Let’s talk about MySQL high availability (HA) and synchronous replication once more. It’s part of a longer series on some high availability reference architecture solutions over geographically distributed areas. Part 1: Reference Architecture(s) for High Availability Solutions in Geographic Distributed Scenarios: Why Should I Care? Part 2: MySQL High Availability On-Premises: A Geographically Distributed Scenario The Problem […]

MySQL High Availability On-Premises: A Geographically Distributed Scenario

On-Premises MySQL High AvailabilityIn this article, we’ll look at an example of an on-premises, geographically distributed MySQL high availability solution. It’s part of a longer series on some high availability reference architecture solutions over geographically distributed areas. Part 1: Reference Architecture(s) for High Availability Solutions in Geographic Distributed Scenarios: Why Should I Care? Percona consulting’s main aim is to identify […]

Handling long duration SST(timeout) in PXC with systemd

In this blog post, We will be explaining about the timeouts in SST on systemd implementation which we faced recently in Percona XtraDB Cluster  during our Consulting with a client. State Snapshot Transfers (SST) refers to complete data sync from one of the nodes from the cluster to the joining node.
SST will happen for one or more reasons listed below.

Initial sync to join a node to cluster.
Node is out of cluster and lost its ability to join back due to data corruption or inconsistencies and also when the node went far behind the node, Starting point of recovery from gcache (Where recovery logs are written) is purged or rotated.

It’s very important to understand the timeout related to SST as in a large size cluster implementation, Where it’s going to take hours to complete the SST. If it fails on timeout in mid it can ruin your day.
We will be looking for SST timeouts on two large scale galera cluster implementations, Percona XtraDB Cluster and MariaDB Cluster with the systemd startup process.
Percona XtraDB Cluster (PXC):

PXC Version: 5.6.38

Systemd Service Script: /usr/lib/systemd/system/mysql.service

[Service]
ExecStartPre=/usr/bin/mysql-systemd start-pre
# pre check script to check if an instance of mysql is already running and exit if it found one.

ExecStart=/usr/bin/mysqld_safe –basedir=/usr
# when it passes pre check it goes and starts MySQL

ExecStartPost=/usr/bin/mysql-systemd start-post $MAINPID
# post check script to verify the pid is created and server is running.
# startup will complete when the post script returns success.
When the nodes goes for SST, Startup script will be waiting on ExecStartPost to give OK.

We can see, post check script calls /usr/bin/mysql-systemd with argument start-post, It goes through the below switch case call.

“start-post”)
      wait_for_pid created  “$pid_path”; ret=$?
      if [[ $ret -eq 1 ]];then
          log_failure_msg “MySQL (Percona XtraDB Cluster) server startup failed!”
      elif [[ $ret -eq 2 ]];then
          log_info_msg “MySQL (Percona XtraDB Cluster) server startup failed! State transfer still in progress”
      fi
      exit $ret
    ;;

Inside start-post, wait_for_pid function is invoked with argument created and pid path. Script will then be looping through wait_for_pid function until the SST completes.
Just pasting the code related to this discussion from the function wait_for_pid.

i=0
while [[ $i -lt $service_startup_timeout ]]; do
    if [[ $verb = ‘created’ ]];then
        if ([[ -e $sst_progress_file ]] || grep -q — ‘–wsrep-new-cluster’ <<< “$env_args” ) \
        && [[ $startup_sleep -ne 10 ]]; then
            echo “State transfer in progress, setting sleep higher”
            startup_sleep=10
        fi
    fi
    i=$(( i+1 ))
    sleep $startup_sleep
done
 This while loop tries for service_startup_timeout number of times, Each time it waits for startup_sleep of 10 sec, The value for service_startup_timeout is hardcoded in the script as 900.

service_startup_timeout=900

So, SST will only wait for only 900 * 10 = 9000 Seconds = 2 hrs 30 min to complete on systemd implementation and It timeout after that.
For a cluster of huge size, Its’ a bottleneck, For a bigger data set SST can take more time, Failing in middle is very bad thing that can happen. Error it throws when such event happens is misleading and it’s not clear.

Testing:
In our testing with PXC Version: 5.6.38 and OS: Centos 7 of data set 1.5 TB, SST timed out in middle when almost 700G copied in approx. 2 hrs 30 min.
Error Logs:
Joiner:
2018-03-14 19:13:04 16392 [Note] WSREP: Member 2.0 (node4) requested state transfer from ‘node3’. Selected 1.0 (node3)(SYNCED) as donor.
WSREP_SST: [INFO] Waiting for SST streaming to complete! (20180314 19:13:05.350)
WSREP_SST: [ERROR] Removing /data/mysql//.sst/xtrabackup_galera_info file due to signal (20180314 21:42:44.532)
WSREP_SST: [ERROR] Cleanup after exit with status:143 (20180314 21:42:44.535)
2018-03-14 21:42:44 16392 [ERROR] WSREP: SST failed: 2 (No such file or directory)
Donor:
WSREP_SST: [INFO] Streaming the backup to joiner at xxx.xx.xx.xx 4444 (20180314 19:13:13.446)
WSREP_SST: [INFO] Evaluating innobackupex –defaults-file=/etc/my.cnf –defaults-group=mysqld –no-version-check $tmpopts $INNOEXTRA –galera-info –stream=$sfmt $itmpdir 2>${DATA}/innobackup.backup.log | socat -u stdio TCP:xxx.xx.xx.xx:4444; RC=( ${PIPESTATUS[@]} ) (20180314 19:13:13.449)
2018/03/14 21:42:42 socat[13974] E write(6, 0x55d29d806d00, 8192): Broken pipe
 SST Duration: 19:13:04 – 21:42:44 ~ Timeout In 2 hrs 30 min
Solutions:
Method 1:

Edit /usr/bin/mysql-systemd file and set service_startup_timeout from 900 to a much higher value. In our case, We have set it to 8 hours (2880).(2880*10)/60/60 = 8 hrs

# sed -i ‘s/service_startup_timeout=900/service_startup_timeout=2880/g’ /usr/bin/mysql-systemd

Method 2:

On /usr/bin/mysql-systemd, We can see it is also reading this variable from mysqld_safe tag

service_startup_timeout=$(parse_cnf service-startup-timeout $service_startup_timeout mysqld_safe)

It’s mentioned on the mysql.service script mysql.service, But it’s not clear.
# Timeout is handled elsewhere
# service-startup-timeout in my.cnf
# Default is 900 seconds -> This value BTW is not seconds

So we can also define service_startup_timeout variable in the /etc/my.cnf under [mysqld_safe] tag

Variable under /etc/my.cnf takes higher precedence.

This behaviour is reported to Percona team: https://jira.percona.com/browse/PXC-2080
MariaDB Cluster:
MariaDB Version: 10.1.31
MariaDB has provided clear information on how to increase the timeout for SST in their documentation for upgrading.
https://mariadb.com/kb/en/library/upgrading-from-mariadb-galera-cluster-100-to-mariadb-101/

On Linux distributions that use #systemd# you man need to increase the service startup timeout as the default timeout of 20 minutes may not be sufficient if a SST becomes necessary

create a file /etc/systemd/system/mariadb.service.d/timeout.conf with the following data.[Service]TimeoutSec=infinity

If you are using a systemd version older than version 229 you have to replace infinity with 0

Execute # systemctl daemon-reload after the change for the new timeout setting to take effect.

It’s also very interesting that, It has provided with very good documentation on the systemd startup script and variable details. you can read at the following link.
https://mariadb.com/kb/en/library/systemd/
mariadb-service-convert script to generate the systemd startup script variables from /etc/my.cnf is just fascinating. I m not going into much details on that as it’s out of the scope for this blog. I really admire the fact, the documentation is very clear.
Key Takeaways:
SST on systemd implementation has timeouts.
Percona XtraDB Cluster it’s 2 hours 30 minutes.
MariaDB Cluster it’s 20 minutes.
If your data copy during SST is going take more than that, Use the solutions provided to avoid surprises in the production.

Announcing Galera Cluster Security Release for MySQL 5.5.59, 5.6.39, 5.7.21 with Galera 3.23.

Codership is pleased to announce the release of Galera Replication library 3.23, implementing wsrep API version 25.
This release incorporates all changes up to MySQL 5.7.21, MySQL 5.6.39 and MySQL 5.5.59, including several fixes to vulnerabilities reported by Oracle in here.
New features and notable fixes in Galera replication since last binary release
by Codership (3.22):

Write set serialization has been changed to use proper memory alignment in order to avoid crashes on platforms which do not allow unaligned memory access (http://www2.galeracluster.com/e/38852/codership-galera-issues-445/88b8yp/875788176)

 
Notable bug fixes in MySQL 5.7.21:

Changing the variable wsrep_slave_threads was not effective after
a node drops from the cluster and then joins back(http://www2.galeracluster.com/e/38852/dership-mysql-wsrep-issues-319/88b8yr/875788176)

 
Notable bug fixes in 5.6.39 :

Changing the variable wsrep_slave_threads was not effective after
a node drops from the cluster and then joins back(http://www2.galeracluster.com/e/38852/dership-mysql-wsrep-issues-319/88b8yr/875788176)

 
READ THE RELEASE NOTES:
Galera Replication library 3.23
MySQL-wsrep 5.5.59
MySQL-wsrep 5.6.39
MySQL-wsrep 5.7.21
 
 
 

This Week in Data with Colin Charles 26: Percona Live Schedule is Near Completion, FOSDEM Underway and a Percona Toolkit Use Case

Colin CharlesJoin Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community. Percona Live Santa Clara 2018 update: tutorials have been picked, and the schedule/press release should be announced by next week. We’ve (the committee) rated over 300+ talks, and easily 70% of the schedule should go […]

Bulgarian National Lottery chooses Galera Cluster

“We wanted a solution which was backward compatible with our frameworks, as we have used mainly MySQL, which was reliable and nearly 100% data consistent.  If our application were to execute a commit and receive a successful response, we needed a 100% guarantee that we have rows of data in our database — and for more than that one instance.”
There were many options E-CARD explored. “First, we tried MySQL NDB Cluster. That was a very frustrating experience. After that we tried a few other plugins and extensions of MySQL, but they weren’t complete solutions. At the end of this process, we found Galera with MySQL. It responded to our needs nearly 100% of the time.”
 
Read the full story
Bulgarian National Lottery chooses Galera Cluster

Announcing Galera Cluster for MySQL 5.5.58, 5.6.38, 5.7.20 with Galera 3.22.

Codership is pleased to announce a new release of Galera Cluster for MySQL consisting of MySQL-wsrep 5.5.58, 5.6.38, 5.7.20 and new Galera 3.22 library, wsrep API version 25.
 

NEW FEATURES AND NOTABLE FIXES IN THE GALERA REPLICATION LIBRARY SINCE LAST BINARY RELEASE BY CODERSHIP (3.21):

 
New features and notable fixes in Galera replication since last binary release
* Reporting last committed write set fixed to respect commit ordering (MW-341)
* GComm socket level error handling improved to avoid backend thread exit
in case of unexpected input from ASIO IO service (GAL-518)
* Race condition fixed in GComm message sending codepath (GAL-520)
* Fix for EVS protocol stall due to exhausted send window setting. This
bug could stall cluster messaging until the next keepalive was sent by
some node, causing intermittent pauses in write set replication. (GAL-521)
* Code changes to avoid name collisions with FreeBSD system headers (GAL-523)
Read the full release notes (how to install, repository structure) 
 
 
NOTABLE BUG FIXES IN MYSQL-WSREP:
 
Version MySQL-wsrep 5.7.20 and Galera 3.22, wsrep API version 25.
* Preserve –wsrep-recover log for future reference when starting the server.
The preserved log is stored in a file under MySQL data directory,
either in wsrep_recovery.ok or wsrep_recovery.fail depending on recovery
success. (MW-318)
* Avoid returning outdated values for wsrep_ready status variable (MW-384)
* A bug which caused stored procedure with an error handler to commit
a statement even in case of certification error was fixed. (MW-388)
* Crash during LOAD DATA for partition engine was fixed (MW-394)
* Fixed a crash caused by a dangling reference to wsrep status variables
array. (MW-399)
* Fixes to processing of foreign key cascades. (MW-402)
* ACL checks are now enforced before replication for all DDL operations
(MW-416)
* ALTER EVENT statement failure on slave was fixed (MW-417)
Read the full release notes  (known issues, how to install, repository structure) 
 
 
Version MySQL-wsrep 5.6.38 and Galera 3.22, wsrep API version 25
* Preserve –wsrep-recover log for future reference when starting the server.
The preserved log is stored in a file under MySQL data directory,
either in wsrep_recovery.ok or wsrep_recovery.fail depending on recovery
success. (MW-318)
* Avoid returning outdated values for wsrep_ready status variable (MW-384)
* A bug which caused stored procedure with an error handler to commit
a statement even in case of certification error was fixed. (MW-388)
* Crash during LOAD DATA for partition engine was fixed (MW-394)
* Fixed a crash caused by a dangling reference to wsrep status variables
array. (MW-399)
* Fixes to processing of foreign key cascades. (MW-402)
* ACL checks are now enforced before replication for all DDL operations
(MW-416)
Read the full release notes (known issues, how to install, repository structure)

 
 
Version MySQL-wsrep 5.5.58 and Galera 3.22, wsrep API version 25
Notable bug fixes in MySQL-wsrep:
* Avoid returning outdated values for wsrep_ready status variable (MW-384)
* Crash during LOAD DATA for partition engine was fixed (MW-394)
* Fixes to processing of foreign key cascades. (MW-402)
* ACL checks are now enforced before replication for all DDL operations
(MW-416)
Read the full release notes (known issues, how to install, repository structure)
 

Joint Webinar with Severalnines: How to manage Galera Cluster using ClusterControl

Since its creation, Galera Cluster has established itself as the most popular high availability solution for MySQL and MariaDB users worldwide.
ClusterControl is the go-to automation and management system for Galera Cluster users.
And together, we’re going to walk you through all the different aspects that make Galera Cluster such a popular high availability solution for MySQL and MariaDB and how to best manage it with ClusterControl.
We’ll hear about the latest features of Galera Cluster directly from Codership, the creators of Galera Cluster. And we’ll look at how to automate everything from deployment, monitoring (how about ASCII-art graphs?), backups, failover, recovery, rolling upgrades and scaling using the ClusterControl CLI (for a change, we also have a GUI of course).

AGENDA
Introduction
About Codership, the makers of Galera Cluster
About Severalnines, the makers of ClusterControl
What’s new with Galera Cluster
Core feature set overview
What’s coming up
ClusterControl for Galera Cluster
Deployment
Monitoring
Management
Scaling
Live Demo
Q&A
 
Join EMEA timezone webinar Tue November 14, 10 AM CET
 
Join USA timezone webinar Tue November 14, 9 AM PST
 
Presenting:
Krzysztof Książek, Senior Support Engineer at Severalnines, is a MySQL DBA with experience managing complex database environments for companies like Zendesk, Chegg, Pinterest and Flipboard.
Seppo Jaakola, Codership CEO

Joint Webinar with Severalnines: How to manage Galera Cluster using ClusterControl

Since its creation, Galera Cluster has established itself as the most popular high availability solution for MySQL and MariaDB users worldwide.
ClusterControl is the go-to automation and management system for Galera Cluster users.
And together, we’re going to walk you through all the different aspects that make Galera Cluster such a popular high availability solution for MySQL and MariaDB and how to best manage it with ClusterControl.
We’ll hear about the latest features of Galera Cluster directly from Codership, the creators of Galera Cluster. And we’ll look at how to automate everything from deployment, monitoring (how about ASCII-art graphs?), backups, failover, recovery, rolling upgrades and scaling using the ClusterControl CLI (for a change, we also have a GUI of course).
AGENDA
Introduction
About Codership, the makers of Galera Cluster
About Severalnines, the makers of ClusterControl
What’s new with Galera Cluster
Core feature set overview
What’s coming up
ClusterControl for Galera Cluster
Deployment
Monitoring
Management
Scaling
Live Demo
Q&A
 
Join EMEA timezone webinar Tue November 14, 10 AM CET
 
Join USA timezone webinar Tue November 14, 9 AM PST
 
Presenting:
Krzysztof Książek, Senior Support Engineer at Severalnines, is a MySQL DBA with experience managing complex database environments for companies like Zendesk, Chegg, Pinterest and Flipboard.
Seppo Jaakola, Codership CEO
 

Galera Cluster is looking for Quality Assurance Engineer and C++ Software Developer

Join Galera Cluster team!  Both jobs are key positions at Codership. You will influence a great deal to Galera Cluster user experience.  We have thousands of users.
 
Galera Cluster QA Engineer
The QA Engineer/release manager will be responsible for working with the development team at Codership and create and execute tests, make builds of Galera Cluster and develop and extend test automation frameworks.
You will be also working closely with MariaDB development and quality assurance teams to prioritize software bug fixes.
 
Tasks

Continuously refine the QA process to improve product quality
Troubleshoot and work with the development team to isolate issues
Create and maintain automated tests
Make Galera software builds (our build pipeline uses python, Jenkins, Docker and Qemu)
Assist customers with their questions and support tickets

Desired Skills and Experience

3 years of experience in QA on Linux
Proven record of implementing test automation in a scalable, sustainable manner
Experience in designing, documenting, and analyzing test cases
Familiarity with RDBMS problematics (MySQL in particular)
Familiarity with C/C++ languages and ability to interpret gdb dumps
Fluent English

 
The position also offers an opportunity to take on other roles within the company, such as giving conference presentations and webinars, writing documentation content and blog posts.
 
Senior Software Developer
We are looking for someone with 3-5 years of C++ programming experience (linux) and preferably distributed computing experience as well. A solid understand of SQL databases and replication technology is a must and a good understanding of MySQL and InnoDB is an advantage.
Beyond those technical skills you really ought to be confident with all other aspects software development and testing.

Good English is required as all communication is done remotely so you must be able to understand what we require and vice versa.
We work remotely with face-to-face meetings 2-3 times per year.

 
Join Galera Cluster team!  Both jobs are key positions at Codership. You will influence a great deal to Galera Cluster user experience.  We have thousands of users.
 
SEND YOUR APPLICATIONS AND CV TO JOBS@GALERACLUSTER.COM

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569