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.
These instructions are for MemSQL clusters managed by MemSQL Ops. For MemSQL clusters without MemSQL Ops, see Upgrading MemSQL without Ops instead.
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.
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
- Check your current MemSQL Ops version by running the following:
$ memsql-ops version
- 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
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
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.
Upgrade MemSQL Ops to 6.0 by running the
$ 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 ```
- 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>]
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.
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.
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-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.
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.