You are viewing an older version of this section. View current production version.

Upgrading MemSQL to 6.0 min read


MemSQL Ops has been deprecated

Please follow this guide to learn how to migrate to MemSQL tools.

This topic describes how to upgrade MemSQL to 6.0 from MemSQL 4.0 and later. To upgrade to the latest patch version of MemSQL 6.0 (e.g. 6.0.8 to 6.0.26), see Upgrading between MemSQL minor versions.

It is recommended that you upgrade to the latest patch version of MemSQL 6.0.

If you want to upgrade MemSQL from 3.2 and earlier, you must first upgrade to MemSQL 5.8, following the instructions at Upgrading MemSQL from 3.2 and earlier, and then upgrade from MemSQL 5.8 to MemSQL 6.0.

Warning

These instructions are for MemSQL clusters managed by MemSQL Ops. For MemSQL clusters without MemSQL Ops, see Upgrading MemSQL without Ops instead.

Warning

If you are using replication between two 5.x clusters, and choose to upgrade one to 6.0, 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 MemSQL 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.

Prior to upgrading

Taking a backup is recommended as a standard precautionary measure. See Backing Up and Restoring Data.

Step 1: Verify your cluster is ready for upgrade

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

Step 2: Upgrade MemSQL Ops

  1. Check your current MemSQL Ops version by running the following:
$ memsql-ops version
  1. If your current MemSQL Ops version is 5.7 or earlier, you must first upgrade MemSQL Ops to 5.8, prior to upgrading MemSQL Ops to 6.0. If your current MemSQL Ops version is 5.8, you can skip this step.
$ memsql-ops agent-upgrade --version 5.8.2


Warning

If you attempt to upgrade MemSQL Ops 5.7 or earlier to MemSQL Ops 6.0 or later without first upgrading to MemSQL Ops 5.8, the upgrade will fail after five minutes with the error Expected AGENT_UPGRATE_V6_GATE_FLAG to exist. Please first upgrade to MemSQL Ops 5.8. in the memsql-ops-upgrade-errors.log file.

  1. Find the latest available MemSQL Ops 6.0 version number at http://versions.memsql.com/memsql-ops/latest-v6. Look for the version number 6.0.x, like 6.0.10.

  2. Upgrade MemSQL Ops to 6.0 by running the agent-upgrade command.

$ memsql-ops agent-upgrade --version VERSION
`VERSION` should be the 6.0.x version number from the previous step. If you do not put in a 6.0.x version number, the default upgrade will be to the latest 5.8.x version available.

If your cluster does not have internet access, you must use the `--file-path` argument and point to a MemSQL Ops binary, which can be downloaded from the payload URL listed in Step 3.
```bash
$ sudo memsql-ops agent-upgrade --file-path memsql-ops-X.Y.Z.tar.gz
```
  1. Verify that MemSQL Ops is the correct version.
$ memsql-ops version

Step 3: Upgrade MemSQL

Note: This upgrade method is referred to as an “offline” upgrade because your existing MemSQL cluster will be shut down and restarted over the course of the upgrade. You do not need to shut down your cluster prior to starting the upgrade.

Once you have upgraded MemSQL Ops, upgrade the MemSQL cluster with an offline cluster upgrade. This will upgrade all nodes at the same time, shutting down the entire cluster for the duration of the upgrade.

Execute the following command to start an offline upgrade:

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

MemSQL Ops must be upgraded prior to upgrading the engine. If you are running Ops 5.x and attempt to run memsql-ops memsql-upgrade without specifying a version argument, it will upgrade to the latest 5.8 version. If you are running Ops 5.x and you specify a 6.0 version in the memsql-upgrade arguments, it will result in an error like one of the following:

  • Could not get the latest binary of MemSQL with version 6.0.6: There are no MEMSQL files available.
  • Could not retrieve information for commit hash <hash>: Version hash <hash> does not exist.

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

Info

Online upgrade from 5.8 to 6.0 is not supported at this time.

Step 4: Post-upgrade considerations

By default, automatic statistics for columnstore tables will be disabled on existing tables created prior to the upgrade, and enabled on tables created after 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.

Also, columnar_segment_rows, which is now an alias for columnstore_segment_rows in 6.0, keeps the old default value of 102,400 after upgrading to 6.0 to minimize performance impact on any existing workloads; however, if 6.0 is installed from scratch, 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, run the following memsql-update-config command:

$ memsql-ops memsql-update-config --all --key columnstore_segment_rows --value 1024000 --set-global

This will write the columnstore_segment_rows = 1024000 key-value pair to all memsql.cnf files in the cluster and will run SET GLOBAL columnstore_segment_rows = 1024000 on all nodes in the cluster to enable the change at runtime without restarting any nodes.

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.