Upgrading MemSQL to 6.8 min read


Info

This topic does not apply to MemSQL Helios.

This topic describes how to upgrade MemSQL to 6.8. These steps can be used to upgrade from 5.8 to 6.8.x (e.g. 6.8.9). It is recommended that you upgrade to the latest patch version of MemSQL 6.8.

After you have finished upgrading, please see the Post-Upgrade Considerations section for additional information on behavioral changes in MemSQL 6.8 that you should be aware of.

Warning

If you are using replication between two 5.x clusters, and choose to upgrade one to 6.0 or later, you will break replication between your clusters. See Replication Compatibility Between Different Cluster Versions for more information.

Info

Plans in the plancache are dependent upon the specific MemSQL patch version, so when you upgrade to a new MemSQL version, all previously compiled plans will be invalidated.

This means that any queries run against the upgraded cluster will force a one time plan compilation, which results in slower query times the first time those queries are run. After the plans have been recompiled, they will be stored again in the plancache and query latency will return to nominal values.

Verify your cluster is ready for upgrade

Prior to upgrading your cluster, it is recommended that you take a backup as a standard precautionary measure. See Backing Up and Restoring Data.

In addition, from the master aggregator, run the following commands:

SHOW LEAVES;
SHOW AGGREGATORS;
SHOW CLUSTER STATUS;
EXPLAIN RESTORE REDUNDANCY;
EXPLAIN REBALANCE PARTITIONS;

With the output of these commands, confirm that the following are true:

  • All leaves are online

  • All aggregators are online

  • There are no partitions with ‘Orphan’ role

  • No Rebalance or restore redundancy is necessary

After you have backed up your data and verified your cluster is ready, you are ready to upgrade your cluster to the latest version of MemSQL using either the MemSQL management tools or MemSQL Ops.

Info

You can only use MemSQL tools with MemSQL 6.7 or later. If you are upgrading from MemSQL 6.5 or earlier, or have an existing cluster managed by MemSQL Ops, please see the instructions for using MemSQL Ops to perform the upgrade process.

Step 1: Upgrade MemSQL tools

While not strictly required, it is recommended that you upgrade to the latest version of memsql-toolbox as part of your upgrade process.

The install command will either install memsql-toolbox if it is not already installed, or upgrade an existing version of memsql-toolbox to the latest version.
RHEL/CentOS

sudo yum install memsql-toolbox -y

Debian

sudo apt install memsql-toolbox -y

Upgrade without Internet access

If your cluster does not have internet access, download either the RPM or Debian memsql-toolbox package to your local machine using one of the following buttons:



memsql-toolbox (rpm) memsql-toolbox (deb)

Then, run the install command for the installed package manager and specify the path to the package.
RHEL/CentOS

sudo yum install /path/to/memsql-toolbox.rpm -y

Debian

sudo apt install /path/to/memsql-toolbox.deb -y

Now you are ready to upgrade MemSQL.

Step 2: Upgrade MemSQL

The simplest and preferred upgrade option is an offline cluster upgrade. It is the least error prone and easiest to execute; however, it requires downtime as all of the nodes in the cluster will be upgraded at the same time, shutting down the entire cluster for the duration of the upgrade.

Offline upgrade

Execute the following command to start an offline upgrade:

memsql-deploy upgrade [--version <VERSION>]

If you do not specify a version, you will be upgraded to the latest patch version of MemSQL 6.8.
Note: You cannot downgrade from your current version.

If your cluster does not have internet access, download either the RPM or Debian memsql-server package to your local machine using one of the following buttons:



memsql-server (rpm) memsql-server (deb)

The memsql-server package contains both the MemSQL binary and the low-level management tool, memsqlctl.

Run the memsql-deploy upgrade command and reference the appropriate package in the --file-path argument. Running upgrade (as opposed to simply upgrading the package via the package manager) will do an offline restart of all the nodes to make sure the cluster is using the new version.
RHEL/CentOS

memsql-deploy upgrade --file-path /path/to/memsql-server.rpm

Debian

memsql-deploy upgrade --file-path /path/to/memsql-server.deb

For more information on the upgrade command, see the MemSQL tools reference documentation.

Once you have finished upgrading all of your nodes, see Post-upgrade considerations for any changes between versions that may impact your cluster.

Online upgrade

If the cluster is running with High Availability, you also have the option to perform an incremental online cluster upgrade, which maintains cluster availability throughout the upgrade process. This section will be updated soon with the required steps.

Step 1: Upgrade MemSQL Ops

If you are upgrading from Ops 6.0 or later, you can upgrade Ops to the latest version by running the agent-upgrade command without specifying a version number.

$ memsql-ops agent-upgrade

If you are currently running MemSQL Ops 5.7 or older, you must first upgrade Ops to 5.8 before proceeding.

$ memsql-ops agent-upgrade --version 5.8.4

Once you have upgraded to Ops 5.8, you can proceed to upgrade to the latest Ops 6.7 version. You must specify a version number when upgrading from Ops 5.8 to Ops 6.0 or later. If you omit the --version flag, you will not be able to successfully upgrade your Ops agents.

$ memsql-ops agent-upgrade --version 6.7.5

Upgrade without Internet access

If your cluster does not have internet access, find the latest available MemSQL Ops version number at http://versions.memsql.com/memsql-ops/latest-v6 and use the payload URL to download it to your local machine.

Then, run the agent-upgrade command using the --file-path argument.

sudo memsql-ops agent-upgrade --file-path /path/to/memsql-ops-XYZ.tar.gz

Now you are ready to upgrade MemSQL.

Step 2: Upgrade MemSQL

The simplest and preferred upgrade option is an offline cluster upgrade. It is the least error prone and easiest to execute; however, it requires downtime as all of the nodes in the cluster will be upgraded at the same time, shutting down the entire cluster for the duration of the upgrade.

Info

Online upgrade from MemSQL 5.x to 6.x is not supported at this time; however, you can perform a manual online upgrade between 6.x minor versions, or between 6.x.y patch versions.

Offline upgrade

Execute the following command to start an offline upgrade:

$ memsql-ops memsql-upgrade [--version <VERSION>]

If you do not specify a version, you will be upgraded to the latest patch version of MemSQL 6.8.

Note: You cannot downgrade from your current version.

If your cluster does not have internet access, download the MemSQL binary from the following locations before running the memsql-upgrade command with the --file-path argument:

$ sudo memsql-ops memsql-upgrade --file-path /path/to/memsqlbin_amd64.tar.gz

For more information on the memsql-upgrade command, see the CLI reference documentation.

Once you have finished upgrading all of your nodes, see Post-upgrade considerations for any changes between versions that may impact your cluster.

Online upgrade

If the cluster is running with High Availability, you also have the option to perform an incremental online cluster upgrade, which maintains cluster availability throughout the upgrade process.

In this procedure, you will upgrade individual nodes in the cluster while other nodes in the cluster are running. It is very important that you follow the upgrade sequence exactly as described in this section: leaf nodes first, then child aggregator nodes, and finally the master aggregator. Unless you require zero downtime, the offline upgrade process is simpler and less error prone.

Info

You must be running MemSQL Ops 6.5 or later to perform an online upgrade with these instructions. Also, it is strongly recommended to avoid running CREATE DATABASE, DROP DATABASE, or ALTER TABLE during the upgrade procedure.

Part 1: Prepare for upgrade

  1. Log into any node and ensure your cluster is running with High Availability (Redundancy Level 2). The redundancy_level variable should be 2.

    SHOW VARIABLES LIKE 'redundancy_level';
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | redundancy_level | 2     |
    +------------------+-------+
    
  2. Ensure your cluster is healthy, and in particular ensure that all nodes are online and there is both a master and slave for each partition; otherwise, you will be unable to keep the cluster available throughout the upgrade procedure. Run SHOW CLUSTER STATUS to retrieve the current state of your cluster.

  3. (Recommended but optional) Prior to upgrading, you may run SNAPSHOT DATABASE on any or all databases, which compacts the transaction logs, reducing database recovery time on startup.

    SNAPSHOT DATABASE database;
    
  4. On the master aggregator, disable auto-attach and leaf failure detection:

    SET GLOBAL auto_attach = OFF;
    SET GLOBAL leaf_failure_detection = OFF;
    
  5. Now you must disable aggregator_failure_detection. If you are upgrading from MemSQL 5.8 or earlier, the easiest way to do that is by running the memsql-upgrade-config command from any aggregator, as shown below.

    $ memsql-ops memsql-list -q -r aggregator master | xargs -L 1 memsql-ops memsql-update-config --key aggregator_failure_detection --value off --set-global
    

    If you are upgrading from MemSQL 6.0 or later, the aggregator_failure_detection variable is a sync variable, so any changes are propagated across the cluster and do not require running the memsql-upgrade-config command. Disable detection by setting the aggregator_failure_detection to OFF on the master aggregator.

    SET GLOBAL aggregator_failure_detection = OFF;
    

Part 2: Upgrade leaf nodes

Assume we have the following cluster configuration, with 8 leaf nodes in running in high availability mode (i.e. redundancy_level set to 2):

image

The cluster has two availability groups:

  • Leaves L1 - L4 are in Availability Group 1
  • Leaves L5 - L8 are in Availability Group 2

There are four pairs of leaf nodes:

  • L1 and L5
  • L2 and L6
  • L3 and L7
  • L4 and L8

The simplest way to upgrade the leaf nodes is to upgrade all leaves in Availability Group 1, then all leaves in Availability Group 2. The simple instructions for this method are below, and more complex instructions for alternative options are discussed later in the section Upgrading leaf nodes in parallel.

Upgrade all leaves in Availability Group 1 by following these steps for each leaf node. You can upgrade them all at the same time, which is fastest, but you can also upgrade one or a few at a time if desired.

  1. Run SHOW LEAVES to list the host IP and port values for your leaves.

  2. Copy the IP address for a leaf in Availability Group 1, and if a port other than the default port (i.e. 3306) is used, copy the port number as well.

  3. Run DETACH LEAF from the Master Aggregator to detach the leaf from the cluster using the IP address and optional port value from the previous step.

    memsql> DETACH LEAF 'host'[:port];
    
  4. List out all of the nodes running MemSQL with memsql-list. Then, select the detached leaf and copy its ID from the output.

    $ memsql-ops memsql-list
    
  5. Stop MemSQL on the leaf node.

    $ memsql-ops memsql-stop MEMSQL_ID
    
  6. Upgrade the leaf using the memsql-upgrade command. This process will upgrade the node and restart it automatically. Specifying a version number is optional.

    $ memsql-ops memsql-upgrade --skip-snapshot --memsql-id MEMSQL_ID --no-prompt [--version VERSION]
    

    Wait for the MemSQL node to start and recover. You can check whether it is recovering by running SHOW DATABASES EXTENDED; and seeing if any row has State = 'recovering'.

  7. Re-attach the leaf back to the cluster by running ATTACH LEAF ... NO REBALANCE from the Master Aggregator.

    ATTACH LEAF 'host'[:port] NO REBALANCE;
    

    Wait for the attach to complete and the leaf to move to the online status. This may take some time. All leaf nodes must be attached and online before proceeding with the upgrade procedure, i.e. the next RESTORE REDUNDANCY or REBALANCE PARTITIONS command.

  8. Perform steps one through seven for the rest of the leaf nodes in Availability Group 1. As an alterative workflow, you can also detach all leaves, upgrade MemSQL on all of the nodes, and then re-attach the leaves as separate steps, if you wish.

  9. Run RESTORE REDUNDANCY on every database.

    RESTORE REDUNDANCY ON database;
    
  10. Perform the same steps for all of the leaf nodes in Availability Group 2.

  11. Run RESTORE REDUNDANCY on every database after all leaves are re-attached.

    RESTORE REDUNDANCY ON database;
    
  12. After you have finished upgrading all the nodes in Availability Groups 1 and 2, run REBALANCE PARTITIONS on every database.

    REBALANCE PARTITIONS ON database;
    

Part 3: Upgrade child aggregator nodes

  1. List out all of the nodes running MemSQL with memsql-list. Then, select the child aggregator you want to upgrade and copy its ID from the output.

    $ memsql-ops memsql-list
    
  2. Stop MemSQL on the child aggregator.

    $ memsql-ops memsql-stop MEMSQL_ID
    
  3. Upgrade the node using the memsql-upgrade command. This process will upgrade it and restart it automatically. Specifying a version number is optional.

    $ memsql-ops memsql-upgrade --skip-snapshot --memsql-id MEMSQL_ID --no-prompt [--version VERSION]
    

Wait for the MemSQL node to start and recover. You can check whether it is recovering by running SHOW DATABASES EXTENDED; and seeing if any row has State = 'recovering'.

You may upgrade child aggregators in parallel as desired. Keep enough aggregators online at any given time to continue running client queries smoothly.

Info

When upgrading a child aggregator, you will be unable to run queries against that node. Furthermore, it will not see any operations take effect until it is upgraded, so to minimize risks, avoid running DDL operations such as ALTER TABLE while upgrading any of your aggregators.

Part 4: Upgrade the master aggregator node

  1. List out all of the nodes running MemSQL with memsql-list. Then, select the master aggregator and copy its ID from the output.

    $ memsql-ops memsql-list
    
  2. Stop MemSQL on the master aggregator.

    $ memsql-ops memsql-stop MEMSQL_ID
    
  3. Upgrade the node using the memsql-upgrade command. This process will upgrade it and restart it automatically. Specifying a version number is optional.

    $ memsql-ops memsql-upgrade --skip-snapshot --memsql-id MEMSQL_ID --no-prompt [--version VERSION]
    

Wait for the MemSQL node to start and recover. You can check whether it is recovering by running SHOW DATABASES EXTENDED; and seeing if any row has State = 'recovering'.

Info

When upgrading the master aggregator, you will be unable to run queries against that node. This means you will be unable to run DDL operations, DML operations against reference tables, as well as database-level and cluster-level operations.

Part 5: Re-enable auto cluster operations

Restore the auto-cluster operations settings to their initial state.

  1. On the master aggregator, re-enable auto-attach and leaf failure detection:

    SET GLOBAL auto_attach = ON;
    SET GLOBAL leaf_failure_detection = ON;
    SET GLOBAL aggregator_failure_detection = ON;
    

Upgrading leaf nodes in parallel

To keep the cluster online, at least one leaf in each leaf pair must be online at any given time, so you must upgrade the two nodes in a leaf pair at different times. But upgrading nodes in different leaf pairs may be done in parallel.

The simplest and generally fastest procedure to achieve this is to upgrade all the leaves in one availability group, then the other availability group, as described above. But you can also upgrade leaf nodes in a different order - you do not have to upgrade all nodes in one availability group before the other - as long as you ensure at least one leaf node in each pair is online at all times.

You may split all leaf nodes into multiple groups of leaf nodes, and upgrade group 1, then group 2, and so on, as long as the two leaves in each leaf pair are in different groups. Run RESTORE REDUNDANCY on every database in after each group of upgrades, and run REBALANCE PARTITIONS on every database at the end.

Post-upgrade considerations

When upgrading from 6.5 or earlier to 6.8, you should be aware of the following changes to system behavior or default configuration settings. The behavior of a cluster upgraded from an earlier version to MemSQL 6.8 may differ compared to a newly installed cluster on MemSQL 6.8 as described below. Most of the changes fall into two categories:

  • In some versions, the default value for a configuration variable was changed compared to previous versions, but clusters upgraded from earlier versions retain their previous setting, both if it was set to a specific value or if it was not explicitly set and hence using the previous default. In some of these cases, it is recommended to update your configuration to the new default if you were previously using the old default, after appropriate testing.
  • Some new features are automatically enabled by default on newly installed MemSQL 6.8 clusters but not automatically enabled on clusters upgraded from an earlier version to 6.8. In some of these cases, it is recommended to enable the new features, after appropriate testing.

If you are upgrading past one or more major or minor versions, read all sections applicable to the version you are upgrading from. For example, if upgrading from 6.0 to 6.8, both the “Upgrades from 6.5 and earlier” and the “Upgrades from 6.0 and earlier” sections are applicable.

Upgrades from 5.8 and earlier to 6.0 and later

Automatic statistics

MemSQL 6.0 introduced a new automatic statistics feature for columnstore tables. By default, automatic statistics is enabled on tables created on version 6.0 or later, but remains disabled on existing tables created on version 5.8 or earlier prior to the upgrade. In most cases, we recommend enabling autostats on existing columnstore tables created prior to the upgrade using ALTER TABLE table_name ENABLE AUTOSTATS. See autostats support upon upgrading MemSQL.

Increased columnstore_segment_rows default

columnar_segment_rows, which is now an alias for columnstore_segment_rows in 6.0 or later, keeps the old default value of 102,400 after upgrading minimize the performance impact on any existing workloads; however, if 6.0 or later is installed on a new cluster, the default value will be 1,024,000. If you want to change the columnstore_segment_rows value to the new default for 6.0 or later, set it as a global variable using the instructions here.

Alert

Changing the default columnstore_segment_rows value may have significant performance impact on the workload. It is advisable to test on a staging environment before applying changes to the columnstore_segment_rows setting. For more information about columnstore_segment_rows, see Advanced Columnstore Configuration Options.

Upgrades from 6.0 and earlier to 6.5 and later

Advanced histograms and cardinality_estimation_level

MemSQL 6.5 introduced new, more advanced histograms, but old histograms from 6.0 and earlier are still supported. When upgrading from MemSQL 6.0 to 6.5 or later, there is a new engine variable introduced in 6.5, cardinality_estimation_level, which allows you to choose whether to use newer, more advanced histograms and algorithms to perform cardinality estimation, or use the previous histogram format. The default setting on newly installed 6.7 or later clusters is 6.5, which uses the new histograms. The default setting on clusters upgraded from 6.0 or earlier to 6.7 or later is 6.0, which uses the old histograms to minimize performance impact on any existing workloads.

You can change the setting cardinality_estimation_level by running SET GLOBAL cardinality_estimation_level = '6.5' on the master aggregator. (Changes will be synchronized to the other nodes in the cluster, and restarting memsql nodes is not required.) After changing the setting, you should re-run ANALYZE TABLE on all previously analyzed tables to collect up-to-date statistics.

AUTO_INCREMENT behavior changes

When upgrading to MemSQL 6.5 or later, the behavior for AUTO_INCREMENT has changed. Specifically, a reserved set of values are now managed by each aggregator, which are then encoded with the aggregator’s ID whenever the aggregator performs an insert into a table that uses AUTO_INCREMENT. This range of values jumps each time the aggregator restarts. Having each aggregator manage its own set of AUTO_INCREMENT values prevents collisions after the aggregator is restarted.

The first observable result of these changes is that after upgrading to 6.5 or later, the start value for the next insert post-upgrade will be much higher than the last value set before the upgrade to align with the new AUTO_INCREMENT behavior. For more information on these changes, see AUTO_INCREMENT behavior in the 6.8 version of CREATE TABLE.

Upgrades from 6.5 and earlier to 6.7 and later

Default DDL timeout update

In MemSQL 6.7, the default value of default_distributed_ddl_timeout was changed from the 6.5 default value 18446744073709551615 to the 6.7 default value 180000. The new default applies to clusters newly installed on MemSQL 6.7 or later, but clusters upgraded from MemSQL 6.5 to 6.7 retain the previous value.

These values are in milliseconds. The MemSQL 6.5 default value is effectively infinite, so the DDL lock will never timeout. With the MemSQL 6.7 default value, the lock will timeout in three minutes.

After upgrading to MemSQL 6.7 or later, it is recommended that you set the default_distributed_ddl_timeout value to 180000 to match the new default value. To update this variable, run SET GLOBAL default_distributed_ddl_timeout = default on the master aggregator (the change is automatically synchronized to other nodes in the cluster).

Upgrades from 6.7 and earlier to 6.8 and later

interpreter_mode set to interpret_first by default

In previous versions of MemSQL, interpreter_mode was set to llvm, which meant queries were compiled first before execution. In MemSQL 6.5, a new interpreter_mode, interpret_first, was available as an experimental setting. This setting allowed you to start interpreting queries for execution while asychronously compiling them in the background for later use. For complex queries, this approach can reduce overall query execution time during the first execution of the query. In MemSQL 6.8 or later, this mode is now production ready and enabled by default; however, if you are upgrading from an earlier version of MemSQL, the interpreter_mode is still set to llvm and must be changed by setting the interpreter_mode session variable.

Was this article useful?