Aug
12
2019
--

ProxySQL 2.0.5 and proxysql-admin tool Now Available

ProxySQL 2.0.5

ProxySQL 1.4.14

ProxySQL 2.0.5, released by ProxySQL, is now available for download in the Percona Repository along with Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

This release includes ProxySQL 2.0.5 which fixes many bugs and introduces a number of features and enhancements. The proxysql-admin tool has been enhanced to support the following new options:

  • The --add-query-rule option creates query rules for synced MySQL users. This option is only applicable for the singlewrite mode and works together with the --syncusers and --sync-multi-cluster-users options.
  • The --force option skips existing configuration checks in mysql_servers, mysql_users and mysql_galera_hostgroups tables. This option will only work together with the –enable option: proxysql-admin --enable --force.
  • The --update-mysql-version option updates the mysql-server_version variable in ProxySQL with the version from a node in Percona XtraDB Cluster.

The ProxySQL 2.0.5 source and binary packages available from the Percona download page for ProxySQL include proxysql-admin – a tool developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 2.0.5 are available as well. You can download the original ProxySQL from GitHub. GitHub hosts the documentation in the wiki format.

ProxySQL 2.0.5 Improvements

  • PSQLADM-49: Create rules for –syncusers. When running with --syncusers or --sync-multi-cluster-users, the --add-query-rule option can now be specified to add the singlewriter query rules for the new users.
  • PSQLADM-51: Update mysql-server_version variable. The --update-mysql-version command has been added to set the mysql-server_version__ global variable in ProxySQL.  This will take the version from a node in the cluster and set it in ProxySQL.

Bugs Fixed

  • PSQLADM-190: The --remove-all-servers option did not work on enable. When running with proxysql-cluster, the galera hostgroups information was not replicated which could result in failing to run --enable on a different ProxySQL node.  The --force option was added for --enable to be able to ignore any errors and always configure the cluster.
  • PSQLADM-199: query-rules removed during proxysql-cluster creation with PXC operator. When using the PXC operator for Kubernetes and creating a proxysql-cluster, the query rules could be removed.  The code was modified to merge the query rules (rather than deleting and recreating).  If the --force option was specified, then a warning was issued in case any existing rules were found; otherwise an error was issued. The --disable-updates option was added to ensure that ProxySQL cluster updates did not interfere with the current command.
  • PSQLADM-200: users were not created for –-syncusers with PXC operator. When using the PXC operator for Kubernetes, the --syncusers command was run but the mysql_users table was not updated.  The fix for PSQLADM-199 that suggested to use --disable-updates also applies here.

ProxySQL is available under Open Source license GPLv3.

Jul
16
2019
--

Percona Kubernetes Operator for Percona XtraDB Cluster 1.1.0 Now Available

Percona Kubernetes Operator for Percona XtraDB Cluster 1.1.0

Percona Kubernetes Operator for Percona XtraDB Cluster 1.1.0We are glad to announce the 1.1.0 release of the  Percona Kubernetes Operator for Percona XtraDB Cluster.

The Percona Kubernetes Operator for Percona XtraDB Cluster automates the lifecycle and provides a consistent Percona XtraDB Cluster instance. The Operator can be used to create a Percona XtraDB Cluster, or scale an existing Cluster, and contains the necessary Kubernetes settings.

The Operator simplifies the deployment and management of the Percona XtraDB Cluster in Kubernetes-based environments. It extends the Kubernetes API with a new custom resource for deploying, configuring and managing the application through the whole life cycle.

The Operator source code is available in our Github repository. All of Percona’s software is open-source and free.

New features and improvements

  • Now the Percona Kubernetes Operator allows upgrading Percona XtraDB Cluster to newer versions, either in semi-automatic or in manual mode.
  • Also, two modes are implemented for updating the Percona XtraDB Cluster my.cnf configuration file: in automatic configuration update mode Percona XtraDB Cluster Pods are immediately re-created to populate changed options from the Operator YAML file, while in manual mode changes are held until Percona XtraDB Cluster Pods are re-created manually.
  • A separate service account is now used by the Operator’s containers which need special privileges, and all other Pods run on default service account with limited permissions.
  • User secrets are now generated automatically if don’t exist: this feature especially helps reduce work in repeated development environment testing and reduces the chance of accidentally pushing predefined development passwords to production environments.
  • The Operator is now able to generate TLS certificates itself which removes the need in manual certificate generation.
  • The list of officially supported platforms now includes Minikube, which provides an easy way to test the Operator locally on your own machine before deploying it on a cloud.
  • Also, Google Kubernetes Engine 1.14 and OpenShift Platform 4.1 are now supported.

Percona XtraDB Cluster is an open source, cost-effective and robust clustering solution for businesses. It integrates Percona Server for MySQL with the Galera replication library to produce a highly-available and scalable MySQL® cluster complete with synchronous multi-master replication, zero data loss and automatic node provisioning using Percona XtraBackup.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system.

Jun
26
2019
--

Percona XtraDB Cluster 5.7.26-31.37 Is Now Available

Percona XtraDB Cluster 5.7

Percona XtraDB Cluster 5.7

Percona is glad to announce the release of Percona XtraDB Cluster 5.7.26-31.37 on June 26, 2019. Binaries are available from the downloads section or from our software repositories.

Percona XtraDB Cluster 5.7.26-31.37 is now the current release, based on the following:

All Percona software is open-source and free.

Bugs Fixed

  • PXC-2480: In some cases, Percona XtraDB Cluster could not replicate CURRENT_USER() used in the ALTER statement. USER() and CURRENT_USER() are no longer allowed in any ALTER statement since they fail when replicated.
  • PXC-2487: The case when a DDL or DML action was in progress from one client and the provider was updated
    from another client could result in a race condition.
  • PXC-2490: Percona XtraDB Cluster could crash when binlog_space_limit was set to a value other than zero during wsrep_recover mode.
  • PXC-2491: SST could fail if the donor had encrypted undo logs.
  • PXC-2497: The user can set the preferred donor by setting the wsrep_sst_donor variable. An IP address is not valid as the value of this variable. If the user still used an IP address, an error message was produced that did not provide sufficient information. The error message has been improved to suggest that the user check the value of the wsrep_sst_donor for an IP address.
  • PXC-2537: Nodes could crash after an attempt to set a password using mysqladmin

Other bugs fixedPXC-2276PXC-2292PXC-2476,  PXC-2560

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

Jun
19
2019
--

Percona XtraDB Cluster 5.6.44-28.34 Is Now Available

Percona XtraDB Cluster 5.7

Percona XtraDB Cluster 5.7

Percona is glad to announce the release of Percona XtraDB Cluster 5.6.44-28.34 on June 19, 2019. Binaries are available from the downloads section or from our software repositories.

Percona XtraDB Cluster 5.6.44-28.34 is now the current release, based on the following:

All Percona software is open-source and free.

Bugs Fixed

  • PXC-2480: In some cases, Percona XtraDB Cluster could not replicate CURRENT_USER() used in the ALTER statement. USER() and CURRENT_USER() are no longer allowed in any ALTER statement since they fail when replicated.
  • PXC-2487: The case when a DDL or DML action was in progress from one client and the provider was updated
    from another client could result in a race condition.
  • PXC-2490: Percona XtraDB Cluster could crash when binlog_space_limit was set to a value other than zero during wsrep_recover mode.
  • PXC-2497: The user can set the preferred donor by setting the wsrep_sst_donor variable. An IP address is not valid as the value of this variable. If the user still used an IP address, an error message was produced that did not provide sufficient information. The error message has been improved to suggest that the user check the value of the wsrep_sst_donor for an IP address.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

Jun
04
2019
--

DDL Queries on Foreign Key Columns in MySQL/PXC

DDL Queries on Foreign Key Columns

DDL Queries on Foreign Key ColumnsRecently, I received a support request where the customer wanted to convert an INTEGER column to BIGINT on two tables. These tables are related by a foreign key, and it is a 3 node PXC cluster. These tables are 20GB and 82 GB in size and DDL’s on such tables in a production environment is always a challenge. We have options like direct ALTER or using pt-online-schema-change to get this done, but this is a very special case where none of them will be able to do this DDL. To demonstrate why this is so, please follow the table schema and the example below about DDL queries on foreign key columns. In the end, I will discuss an easy workaround, too.

Please take a look at table schema before reading further. In the below schema, the product_catalog_id column from the product_details table refers to catalog_id column from the product_catalog table. Both these fields are INT(11) and the customer wanted to convert them to BIGINT:

mysql> show create table product_catalog \G
*************************** 1. row ***************************
       Table: product_catalog
Create Table: CREATE TABLE `product_catalog` (
  `catalog_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  .....
  PRIMARY KEY (`catalog_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
mysql> show create table product_details \G
*************************** 1. row ***************************
       Table: product_details
Create Table: CREATE TABLE `product_details` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_catalog_id` int(11) unsigned NOT NULL,
  .....
  PRIMARY KEY (`product_id`),
  KEY `fk_audit_detail_audit_header_idx` (`product_catalog_id`),
  CONSTRAINT `product_catalog_ibfk_1` FOREIGN KEY (`product_catalog_id`) REFERENCES `product_catalog` (`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

Changing a column from INT to BIGINT is an ONLINE ALTER. But in this case, it is not an ONLINE ALTER as the primary key is defined in this column. I started with a direct ALTER and later with pt-online-schema-change. Let’s have a look at how these tools react to this DDL change.

Direct Alter:

Considering the tables are close to 100GB in size together, a direct ALTER is not a good choice especially with a PXC cluster, and also on a standard deployment, it would block queries on metadata lock. But let’s see how the direct ALTER reacts here.  I will first alter the child table and then the parent table.

mysql> ALTER TABLE product_details MODIfY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL  ;
ERROR 1832 (HY000): Cannot change column 'product_catalog_id': used in a foreign key constraint 'product_details_ibfk_1'

It failed with Error 1832. Let’s try changing the column in the parent table first.

mysql> ALTER TABLE product_catalog MODIfY COLUMN catalog_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT  ;
ERROR 1833 (HY000): Cannot change column 'catalog_id': used in a foreign key constraint 'product_details_ibfk_1' of table 'DB255525.product_details'

This time, it is Error 1833. Both these errors state that it cannot change the column involved in a foreign key. The reason is that a foreign key is defined only between two identical columns. Changing any of those column data types would result in an error.

pt-online-schema-change:

It is always recommended to use pt-online-schema-change for DDL’s in PXC cluster deployments, provided additional disk space is available. You can refer to this blog post to know when to use this tool. pt-osc works by creating a new table with the required change in place, and copies data to the new table. The challenge comes in when there are child tables referring some column in this parent table. Presence of foreign keys complicates the job of pt-osc.

There are two ways the tool handles the foreign key constraints on the child table when the parent table is renamed as part of pt-osc. Below explains each of those cases.

alter-foreign-keys-method=auto

# pt-online-schema-change --user=root --password=root --host=172.23.0.2 --alter-foreign-keys-method=auto --alter "MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL" D=DB1,t=product_details --execute
....
....
2019-05-28T09:19:16 Dropping new table...
2019-05-28T09:19:16 Dropped new table OK.
`DB255525`.`product_details` was not altered.
Error altering new table `DB1`.`_product_details_new`: DBD::mysql::db do failed: Cannot change column 'product_catalog_id': used in a foreign key constraint '_product_details_ibfk_1' [for Statement "ALTER TABLE `DB255525`.`_product_details_new` MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL"] at /usr/bin/pt-online-schema-change line 9271.

It failed while renaming the table _product_details_new to product_details. The reason is that it would have BIGINT datatype in child table and INT datatype in the parent table if the rename succeeds – which is not allowed in MySQL. Foreign keys between different data types are not allowed.

alter-foreign-keys-method=rebuild_constraints

# pt-online-schema-change --user=root --password=root --host=172.23.0.2 --alter-foreign-keys-method=rebuild_constraints --alter "MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL" D=DB1,t=product_details --execute
.....
.....
Created new table DB1._product_details_new OK.
Altering new table...
2019-05-28T09:27:24 Dropping new table...
2019-05-28T09:27:25 Dropped new table OK.
`DB255525`.`product_details` was not altered.
Error altering new table `DB1`.`_product_details_new`: DBD::mysql::db do failed: Cannot change column 'product_catalog_id': used in a foreign key constraint '_product_details_ibfk_1' [for Statement "ALTER TABLE `DB255525`.`_product_details_new` MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL"] at /usr/bin/pt-online-schema-change line 9271.

In this case, the child table is rebuilt to point to the correct parent table using an ALTER, but it failed again for the same reason. So, none of the options like direct ALTER nor pt-online-schema-change are working for this particular change.

DDL Queries on Foreign Key Columns Workaround

Even disabling foreign key checks don’t work here, as that trick only works when it comes to data, not the schema changes. This has been reported independently under Percona Server as well as in PXC branches in JIRA. You can see more information about these here and here. There is one easy yet simple workaround that I would suggest. That is to drop the foreign key constraint on the child table, run the DDL on both the child and the parent tables, and finally redefine the foreign key constraint.

As you can see, the integrity constraint is compromised for the duration for this workaround. Be sure to keep the server in read-only mode and not allow any changes to these tables, as it might lead to inconsistent data between the parent and child tables.

Step #1:
mysql> ALTER TABLE product_details DROP FOREIGN KEY product_details_ibfk_1 ;
Query OK, 0 rows affected (0.95 sec)
Records: 0 Duplicates: 0 Warnings: 0
Step #2:
# pt-online-schema-change --user=root --password=XXXXXXX --host=172.23.0.2 --alter "MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL" D=DB1,t=product_details --execute
......
Successfully altered `DB1`.`product_details`.
Step #3:
# pt-online-schema-change --user=root --password=XXXXXXX --host=172.23.0.2 --alter "MODIFY COLUMN catalog_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT" D=DB1,t=product_catalog --execute
.......
Successfully altered `DB1`.`product_catalog`.
Step #4:
mysql> ALTER TABLE product_details ADD FOREIGN KEY product_details_ibfk_1 (product_catalog_id) REFERENCES `product_catalog`(`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ;
Query OK, 0 rows affected (1.55 sec)
Records: 0 Duplicates: 0 Warnings: 0

Summary:

Foreign keys can only be defined and operated between two identical columns. Due to this constraint, DDL queries on columns involved in foreign keys are still a problem in MySQL/PXC, especially when the tables are huge. This workaround, with little downtime for writes, is the only quick way to get this done without spending time on complex logic building and implementation that involves changes on both the DB and the application.

Photo by Vanessa Bucceri on Unsplash

May
29
2019
--

Announcing Percona Kubernetes Operators

kubernetes-operators

kubernetes-operatorsPercona announced the release of Percona Kubernetes Operator for XtraDB Cluster and Percona Kubernetes Operator for Percona Server for MongoDB. Kubernetes delivers a method to orchestrate containers, providing automated deployment, management, and scalability.

In today’s cloud-native world, the ability to easily and efficiently deploy new environments or scale existing environments is key to ongoing growth and development. With Kubernetes Operators, you can launch a new environment with no single point of failure in under 10 minutes. As needs change, Kubernetes Operators can reliably orchestrate scaling the environment to meet current requirements, adding or removing nodes quickly and efficiently. Kubernetes Operators also provide for self-healing of a failed node in a cluster environment.

One of the best features of the Percona Kubernetes Operators is that they provide a deployment configuration while meeting Percona best practices. When the Operator is used to create a new XtraDB or Percona Server for MongoDB node, you can rest assured that the new node will use the same configuration as other nodes created with that same Operator. This ensures consistent results and reliability.

The consistency and ease of deployment enable your developers to focus on writing code while your operations team focuses on building pipelines. The Operator takes care of the tedious tasks of deploying and maintaining your databases following Percona’s best practices for performance, reliability, and scalability.

Percona Kubernetes Operator for XtraDB Cluster

The Percona Kubernetes Operator for XtraDB Cluster provides a way to deploy, manage, or scale an XtraDB Cluster environment. Based on our best practices, this Operator delivers a solid and secure cluster environment that can be used for development, testing, or production.

Percona XtraDB Cluster includes ProxySQL for load balancing and Percona XtraBackup for MySQL to easily backup your database environment. The Operator adds Percona Monitoring and Management to provide you with deep visibility into the performance and usage of your cluster.

Percona Kubernetes Operator for Percona Server for MongoDB

The Percona Kubernetes Operator for Percona Server for MongoDB enables you to deploy, manage, and scale a Percona Server for MongoDB replica set. With the Operator, you are ensured of an environment that does not have a single point of failure and adheres to Percona’s best practices for use of Percona Server for MongoDB. New Percona Server for MongoDB nodes can be added as either data storage nodes or as arbiter nodes.

The Percona Kubernetes Operator for Percona Server for MongoDB also includes Percona Monitoring and Management so that you can easily view and analyze activity on your replica set. It also includes Percona Backup for MongoDB, providing both scheduled and on-demand backups of your replica set.

Percona Kubernetes Operators deliver a proven method to provide a reliable and secure environment for your users, whether they are developers, testers, or end users. With these Operators, you are assured of consistent results and properly managed environments, freeing you up to focus on other tasks.

May
28
2019
--

ProxySQL 2.0.4 and proxysql-admin tool Now Available

ProxySQL 1.4.14

ProxySQL 1.4.14

ProxySQL 2.0.4, released by ProxySQL, is now available for download in the Percona Repository along with Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

This release includes ProxySQL 2.0.4 which fixes many bugs and introduces a number of features and enhancements. The proxysql-admin tool has not changed since the previous release.

The ProxySQL 2.0.4 source and binary packages available from the Percona download page for ProxySQL include ProxySQL Admin – a tool developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 2.0.4 are available as well. You can download the original ProxySQL from GitHub. GitHub hosts the documentation in the wiki format.

ProxySQL is available under Open Source license GPLv3.

May
07
2019
--

ProxySQL 2.0.3 and updated proxysql-admin tool

ProxySQL 1.4.14

ProxySQL 1.4.14

ProxySQL 2.0.3, released by ProxySQL, is now available for download in the Percona Repository along with an updated version of Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL,  and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

The ProxySQL 2.0.3 source and binary packages available from the Percona download page for ProxySQL include ProxySQL Admin – a tool developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 2.0.3 are available as well. You can download the original ProxySQL from GitHub. GitHub hosts the documentation in the wiki format.

With ProxySQL 2.0.3, the proxysql-admin tool now uses the native ProxySQL support for Percona XtraDB Cluster and does not require custom bash scripts to keep track of PXC status.  As a result, proxysql_galera_checker and proxysql_node_monitor have been removed.

Improvements

  • The proxysql-admin tool is MySQL 8.0 compatible

Added Features

  • New option --use-ssl to use SSL for connections between ProxySQL and the backend database servers
  • New option --max-transactions-behind to determine the maximum number of writesets that can be queued before the node is SHUNNED to avoid stale reads. The default value is 100
  • New operation --update-cluster to update the cluster membership by adding server nodes as found. (Note that nodes are added but not removed).  The --writer-hg option may be used to specify which galera hostgroup to update. The --remove-all-servers option instructs to remove all servers from the mysql_servers table before updating the cluster.
  • Hostgroups can now be specified on the command-line: --writer-hg, --reader-hg, --backup-writer-hg, and --offline-hg.
    Previously, these host groups were only read from the configuration file.
  • The --enable and --update-cluster options used simultaneously have special meaning. If the cluster has not been enabled, then --enable is run.  If the cluster has already been enabled, then --update-cluster is run.
  • New command --is-enabled to see if a cluster has been enabled. This command checks for the existence of a row in the mysql_galera_hostgroups table.  The --writer-hg option may be used to specify the writer hostgroup used to search the mysql_galera_hostgroups table.
  • New command --status to display galera hostgroup information. This command lists all rows in the current mysql_galera_hostgroups table as well as all servers that belong to these hostgroups.  With the --writer-hg option, only the information for the galera hostgroup with that writer hostgroup is displayed.

Changed Features

  • Setting --node-check-interval now changes the ProxySQL global variable mysql-monitor_galera_healthcheck_interval
    Note that this is a global variable, not a per-cluster variable.
  • The option --write-node now takes only a single address as a parameter. In the singlewrite mode we only set the weight if --write-node specifies address:port.  A priority list of addresses is no longer accepted.
  • The option --writers-as-readers option now accepts a different set of values. Due to changes in the behavior of ProxySQL between version 1.4 and version 2.0 related to Galera support, the values of --writers-as-readers have been changed.  This option now accepts the following values: yes, no, and backup.
    yes: writers, backup-writers, and read-only nodes can act as readers.
    no: only read-only nodes can act as readers.
    backup: only backup-writers can act as readers.
  • The commands --syncusers, --sync-multi-cluster-users, --adduser, and --disable can now use the --writer-hg option.
  • The command --disable removes all users associated with the galera cluster hostgroups. Previously, this command only removed the users with the CLUSTER_APP_USERNAME.
  • The command --disable now accepts the --writer-hg option to disable the Galera cluster associated with that hostgroup overriding the value specified in the configuration file.

Removed Features

  • Asynchronous slave reader support has been removed: the --include-slaves option is not supported.
  • A list of nodes in the priority order is no longer supported. Only a single node is supported at this time.
  • Since the galera_proxysql_checker and galera_node_monitor scripts are no longer run in the scheduler, automatic cluster membership updates are not supported.
  • Checking the pxc_maint_mode variable is no longer supported
  • Using desynced nodes if no other nodes are available is no longer supported.
  • The server status is no longer maintained in the mysql_servers table.

Limitations

  • With --writers-as-readers=backup read-only nodes are not allowed.
    This a limitation of ProxySQL 2.0.  Note that backup is the default value of --writers-as-readers when --mode=singlewrite

ProxySQL is available under Open Source license GPLv3.

Apr
02
2019
--

Percona XtraDB Cluster Operator 0.3.0 Early Access Release Is Now Available

Percona XtraDB Cluster Operator

Percona announces the release of Percona XtraDB Cluster Operator 0.3.0 early access.

The Percona XtraDB Cluster Operator simplifies the deployment and management of Percona XtraDB Cluster in a Kubernetes or OpenShift environment. It extends the Kubernetes API with a new custom resource for deploying, configuring and managing the application through the whole life cycle.Percona XtraDB Cluster Operator

You can install the Percona XtraDB Cluster Operator on Kubernetes or OpenShift. While the operator does not support all the Percona XtraDB Cluster features in this early access release, instructions on how to install and configure it are already available along with the operator source code, hosted in our Github repository.

The Percona XtraDB Cluster Operator is an early access release. Percona doesn’t recommend it for production environments.

New features

Improvements

Fixed Bugs

  • CLOUD-148: Pod Disruption Budget code caused the wrong configuration to be applied for ProxySQL and had lack of multiple availability zones support.
  • CLOUD-138: The restore-backup.sh script was exiting with an error because its code was not taking into account images version numbers.
  • CLOUD-118: The backup recovery job was unable to start if Persistent Volume for backup and Persistent Volume for Pod-0 were placed in different availability zones.

Percona XtraDB Cluster is an open source, cost-effective and robust clustering solution for businesses. It integrates Percona Server for MySQL with the Galera replication library to produce a highly-available and scalable MySQL® cluster complete with synchronous multi-master replication, zero data loss and automatic node provisioning using Percona XtraBackup.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system.

Mar
25
2019
--

How to Perform Compatible Schema Changes in Percona XtraDB Cluster (Advanced Alternative)?

PXC schema changes options

PXC schema changes optionsIf you are using Galera replication, you know that schema changes may be a serious problem. With its current implementation, there is no way even a simple ALTER will be unobtrusive for live production traffic. It is a fact that with the default TOI alter method, Percona XtraDB Cluster (PXC) cluster suspends writes in order to execute the ALTER in the same order on all nodes.

For factual data structure changes, we have to adapt to the limitations, and either plan for a maintenance window, or use pt-online-schema-change, where interruptions should be very short. I suggest you be extra careful here, as normally you cannot kill an ongoing ALTER query in Galera cluster.

For schema compatible changes, that is, ones that cannot break ROW replication when the writer node and applier nodes have different metadata, we can consider using the Rolling Schema Update (RSU) method. An example of 100% replication-safe DDL is OPTIMIZE TABLE (aka noop-ALTER). However, the following are safe to consider too:

  • adding and removing secondary index,
  • renaming an index,
  • changing the ROW_FORMAT (for example enabling/disabling table compression),
  • changing the KEY_BLOCK_SIZE(compression property).

However, a lesser known fact is that even using the RSU method or pt-online-schema-change for the above may not save us from some unwanted disruptions.

RSU and Concurrent Queries

Let’s take a closer look at a very simple scenario with noop ALTER. We will set wsrep_OSU_method to RSU to avoid a cluster-wide stall. In fact, this mode turns off replication for the following DDL (and only for DDL), so you have to remember to repeat the same ALTER on every cluster member later.

For simplicity, let’s assume there is only one node used for writes. In the first client session, we change the method accordingly to prepare for DDL:

node1 > set wsrep_OSU_method=RSU;
Query OK, 0 rows affected (0.00 sec)
node1 > select @@wsrep_OSU_method,@@wsrep_on,@@wsrep_desync;
+--------------------+------------+----------------+
| @@wsrep_OSU_method | @@wsrep_on | @@wsrep_desync |
+--------------------+------------+----------------+
| RSU                |          1 |              0 |
+--------------------+------------+----------------+
1 row in set (0.00 sec)

(By the way, as seen above, the desync mode is not enabled yet, as it will be automatically enabled around the DDL query only, and disabled right after it finishes).

In a second client session, we start a long enough SELECT query:

node1 > select count(*) from db1.sbtest1 a join db1.sbtest1 b where a.id<10000;
...

And while it’s ongoing, let’s rebuild the table:

node1 > alter table db1.sbtest1 engine=innodb;
Query OK, 0 rows affected (0.98 sec)
Records: 0 Duplicates: 0 Warnings: 0

Surprisingly, immediately the client in the second session receives its SELECT failure:

ERROR 1213 (40001): WSREP detected deadlock/conflict and aborted the transaction. Try restarting the transaction

So, even a simple SELECT is aborted if it conflicts with the local, concurrent ALTER (RSU)… We can see more details in the error log:

2018-12-04T21:39:17.285108Z 0 [Note] WSREP: Member 0.0 (node1) desyncs itself from group
2018-12-04T21:39:17.285124Z 0 [Note] WSREP: Shifting SYNCED -> DONOR/DESYNCED (TO: 471796)
2018-12-04T21:39:17.305018Z 12 [Note] WSREP: Provider paused at 7bf59bb4-996d-11e8-b3b6-8ed02cd38513:471796 (30)
2018-12-04T21:39:17.324509Z 12 [Note] WSREP: --------- CONFLICT DETECTED --------
2018-12-04T21:39:17.324532Z 12 [Note] WSREP: cluster conflict due to high priority abort for threads:
2018-12-04T21:39:17.324535Z 12 [Note] WSREP: Winning thread:
THD: 12, mode: total order, state: executing, conflict: no conflict, seqno: -1
SQL: alter table db1.sbtest1 engine=innodb
2018-12-04T21:39:17.324537Z 12 [Note] WSREP: Victim thread:
THD: 11, mode: local, state: executing, conflict: no conflict, seqno: -1
SQL: select count(*) from db1.sbtest1 a join db1.sbtest1 b where a.id<10000
2018-12-04T21:39:17.324542Z 12 [Note] WSREP: MDL conflict db=db1 table=sbtest1 ticket=MDL_SHARED_READ solved by abort
2018-12-04T21:39:17.324544Z 12 [Note] WSREP: --------- CONFLICT DETECTED --------
2018-12-04T21:39:17.324545Z 12 [Note] WSREP: cluster conflict due to high priority abort for threads:
2018-12-04T21:39:17.324547Z 12 [Note] WSREP: Winning thread:
THD: 12, mode: total order, state: executing, conflict: no conflict, seqno: -1
SQL: alter table db1.sbtest1 engine=innodb
2018-12-04T21:39:17.324548Z 12 [Note] WSREP: Victim thread:
THD: 11, mode: local, state: executing, conflict: must abort, seqno: -1
SQL: select count(*) from db1.sbtest1 a join db1.sbtest1 b where a.id<10000
2018-12-04T21:39:18.517457Z 12 [Note] WSREP: resuming provider at 30
2018-12-04T21:39:18.517482Z 12 [Note] WSREP: Provider resumed.
2018-12-04T21:39:18.518310Z 0 [Note] WSREP: Member 0.0 (node1) resyncs itself to group
2018-12-04T21:39:18.518342Z 0 [Note] WSREP: Shifting DONOR/DESYNCED -> JOINED (TO: 471796)
2018-12-04T21:39:18.519077Z 0 [Note] WSREP: Member 0.0 (node1) synced with group.
2018-12-04T21:39:18.519099Z 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 471796)
2018-12-04T21:39:18.519119Z 2 [Note] WSREP: Synchronized with group, ready for connections
2018-12-04T21:39:18.519126Z 2 [Note] WSREP: Setting wsrep_ready to true

Another example – a simple sysbench test, during which I did noop ALTER in RSU mode:

# sysbench /usr/share/sysbench/oltp_read_only.lua --table-size=1000 --tables=8 --mysql-db=db1 --mysql-user=root --threads=8 --time=200 --report-interval=1 --events=0 --db-driver=mysql run
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 8
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 8 tps: 558.37 qps: 9004.30 (r/w/o: 7880.62/0.00/1123.68) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 8 tps: 579.01 qps: 9290.22 (r/w/o: 8130.20/0.00/1160.02) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 8 tps: 597.36 qps: 9528.89 (r/w/o: 8335.17/0.00/1193.72) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00
FATAL: mysql_stmt_store_result() returned error 1317 (Query execution was interrupted)
FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:432: SQL error, errno = 1317, state = '70100': Query execution was interrupted

So, SELECT queries are aborted to resolve MDL lock request that a DDL in RSU needs immediately. This of course applies to INSERT, UPDATE and DELETE as well. That’s quite an intrusive way to accomplish the goal…

“Manual RSU”

Let’s try a “manual RSU” workaround instead. In fact, we can achieve the same isolated DDL execution as in RSU, by putting a node in desync mode (to avoid flow control) and disabling replication for our session. That way, the ALTER will only be executed in that particular node.

Session 1:

node1 > set wsrep_OSU_method=TOI; set global wsrep_desync=1; set wsrep_on=0;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
node1 > select @@wsrep_OSU_method,@@wsrep_on,@@wsrep_desync;
+--------------------+------------+----------------+
| @@wsrep_OSU_method | @@wsrep_on | @@wsrep_desync |
+--------------------+------------+----------------+
| TOI                |          0 |              1 |
+--------------------+------------+----------------+
1 row in set (0.00 sec)

Session 2:

node1 > select count(*) from db1.sbtest1 a join db1.sbtest1 b where a.id<10000;
+-----------+
| count(*)  |
+-----------+
| 423680000 |
+-----------+
1 row in set (14.07 sec)

Session 1:

node1 > alter table db1.sbtest1 engine=innodb;
Query OK, 0 rows affected (13.52 sec)
Records: 0 Duplicates: 0 Warnings: 0

Session 3:

node1 > select id,command,time,state,info from information_schema.processlist where user="root";
+----+---------+------+---------------------------------+-----------------------------------------------------------------------------------------+
| id | command | time | state                           | info |
+----+---------+------+---------------------------------+-----------------------------------------------------------------------------------------+
| 11 | Query   | 9    | Sending data                    | select count(*) from db1.sbtest1 a join db1.sbtest1 b where a.id<10000 |
| 12 | Query   | 7    | Waiting for table metadata lock | alter table db1.sbtest1 engine=innodb |
| 17 | Query   | 0    | executing                       | select id,command,time,state,info from information_schema.processlist where user="root" |
+----+---------+------+---------------------------------+-----------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
node1 > select id,command,time,state,info from information_schema.processlist where user="root";
+----+---------+------+----------------+-----------------------------------------------------------------------------------------+
| id | command | time | state          | info |
+----+---------+------+----------------+-----------------------------------------------------------------------------------------+
| 11 | Sleep   | 14   |                | NULL |
| 12 | Query   | 13   | altering table | alter table db1.sbtest1 engine=innodb |
| 17 | Query   | 0    | executing      | select id,command,time,state,info from information_schema.processlist where user="root" |
+----+---------+------+----------------+-----------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

In this case, there was no interruption, the ALTER waited for it’s MDL lock request to succeed gracefully, and did it’s job when it became possible.

Remember, you have to execute the same commands on the rest of the nodes to make them consistent – even for noop-alter, it’s important to make the nodes consistent in terms of table size on disk.

Kill Problem

Another fact is that you cannot cancel or kill a DDL query executed in RSU or in TOI method:

node1 > kill query 12;
ERROR 1095 (HY000): You are not owner of thread 12

This may be an annoying problem when you need to unblock a node urgently. Fortunately, the workaround with wsrep_on=0 also allows to kill an ALTER without that restriction:

Session 1:

node1 > kill query 22;
Query OK, 0 rows affected (0.00 sec)

Session 2:

node1 > alter table db1.sbtest1 engine=innodb;
ERROR 1317 (70100): Query execution was interrupted

Summary

The RSU method may be more intrusive then you’d expect. For schema compatible changes, it is worth considering “manual RSU” with

set global wsrep_desync=1; set wsrep_on=0;

When using it though, please remember that wsrep_on applies to all types of writes, both DDL and DML, so be extra careful to set it back to 1 after the ALTER is done. So the procedure will look like this:

SET GLOBAL wsrep_desync=1;
SET wsrep_on=0;
ALTER ...  /* compatible schema change only! */
SET wsrep_on=1;
SET GLOBAL wsrep_desync=0;

Incidentally, as in my opinion the current RSU behavior is unnecessarily intrusive, I have filed this change suggestion: https://jira.percona.com/browse/PXC-2293


Photo by Pierre Bamin on Unsplash

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com