SingleStoreDB v7.3

Upgrade to SingleStoreDB 7.3

Notice

SingleStoreDB betas and release candidates cannot be upgraded unless explicitly stated in the release notes.

Caution

As of SingleStoreDB 7.0, more disk space is required than with MemSQL 6.x for columnstore tables and the new replication mechanism, and introduces additional metadata for columnstore tables to improve performance for concurrent updates/deletes and selective filtering. Specifically:

An auto-incr value for every row has been introduced, which is persisted in blobs and requires approximately 8 bytes per columnstore row.
Database logs are now pre-allocated as of SingleStoreDB 7.0:
- Each system database requires an additional 32MB of disk space for pre-allocated log space. This equates to an additional 96MB per node.
- Each reference (ref) database requires two pre-allocated log files. The size of each log file is 64MB, for a total of 128MB of disk space per ref database per node. See the Sync Variables Lists for more information on changing the ref database log file size via the log_file_size_ref_dbs global variable.
- Each partition now requires two pre-allocated log files. The size of each log file is 256MB, for a total of 512MB of disk space for pre-allocated log space. See the Sync Variables Lists for more information on changing the partition log file size via the log_file_size_partitions global variable.
- Logs will require (snapshot_trigger_size * snapshots_to_keep) of space over time (approximately), or about 4GB per partition using the default values of these variables. Customers are recommended to have twice this amount of disk space available for long-term log storage. See the Sync Variables Lists for more information on snapshot_trigger_size and snapshots_to_keep.
- WARNING: Changing the log file size will not affect existing databases. Reducing the size of existing log files or allocating new ones will have random and severe performance impacts on your workload when new log files need to be allocated.
If any host in the cluster is near or at disk capacity, please increase available storage before upgrading to SingleStoreDB 7.3. Similarly, a newly deployed SingleStoreDB 7.3 cluster will consume more disk space than a MemSQL 6.8 cluster running the same workload.

This topic describes how to upgrade SingleStoreDB to 7.3. Please read the following information thoroughly before upgrading to SingleStoreDB 7.3.

If you want to upgrade to 7.3 from 6.0 or earlier, you must first upgrade to MemSQL 6.8 before upgrading to SingleStoreDB 7.3.

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

Important Notes About Upgrading

If upgrading from a version earlier than 7.0, please note the following.

Upgrade Duration and Behavior

Anticipate a longer upgrade time for each node. At the end of the upgrade process, anticipate a multi-minute delay while final checks are run that ensure the upgrade completed successfully and the cluster is ready for operation. If you connect to the cluster while these post-upgrade checks are running, you will receive an error that the upgrade is still in progress.

Compound Keys

In compound keys with the UNIQUE constraint, if the type of the first column of the key is specified as NOT NULL, that resulted in non-standard UNIQUE constraint behavior for any NULL-able columns in that same compound key. This behavior has been changed as of SingleStoreDB 7.0 to treat all columns the same, independent of position in the key, conforming to the SQL standard.

Delete Performance

SingleStoreDB 7.0 introduced row-level locking for columnstore update/delete so it can handle more concurrent queries, but for certain scenarios it may cause performance degradation (delete on wide table with json column). Set COLUMNSTORE_TABLE_LOCK_THRESHOLD to bypass row-level locking and obtain the previous behavior.

Leaf Nodes

In MemSQL 6.x, a leaf node can restart, come back online, and its databases can be queried when the Master Aggregator is offline. As of SingleStoreDB 7.0, a leaf node can restart and recover its databases, but it won’t bring them online unless the Master Aggregator is online. In all versions of SingleStoreDB, leaf nodes can safely be restarted when the Master Aggregator is online.

Plancache

Plans in the plancache are dependent upon the specific SingleStoreDB patch version, so when you upgrade to a new SingleStoreDB 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.

Replication

Replication is now synchronous as of SingleStoreDB 7.0 by default. Failovers in SingleStoreDB 7.3 will not occur unless a replica is in sync with its master. While you will never lose data on fail-overs, you could lose availability if your replicas are out of sync and a node goes down. Refer to Replication and Durability Concepts for more information.

`SCHEMA_BINDING` for Views

As of SingleStoreDB 7.0, SCHEMA_BINDING for views is off by default. Prior to SingleStoreDB 7.0, SCHEMA_BINDING for views was on by default. As a result, an error will no longer be issued when deleting a table under a view.

Note that this behavior only applies to views that are created after upgrading, and does not apply to views that were created prior to upgrading.

Snapshots

As of SingleStoreDB 7.0, the default value of snapshot_trigger_size has been increased from 256MB to 2GB. This will not be updated automatically on upgrade, and it is recommended that customers make this change post-upgrade to reduce snapshot disk I/O.

Sync Variables

As of SingleStoreDB 7.0, a large percentage of system variables have been changed to “sync” variables. This allows variables to be set on the Master Aggregator, which will then persist and be replicated to the other nodes in the cluster. This obviates the need to manually set these variables in a .cnf file. Use SHOW _SYNC VARIABLES to see the current set of sync variables.

As a consequence of this, when a variable has been changed to a sync variable and has different values on two nodes (for example, a sync variable on Node X has been set to value A, and this sync variable on Node Y has been set to value B), the upgrade will issue a warning, and these variables must be reconciled before they can become sync variables.

To do so, manually update the .cnf file and, using the above example, set the value of the sync variable to either A or B on both nodes. Next, run UPGRADE VARIABLES on the Master Aggregator to enable all the new sync variables. Refer to Engine Variables and update-config for more information.

Non-Sync Variables

By default, convert_nonunique_hash_to_skiplist is set to TRUE in SingleStoreDB 7.3. This means that any non-unique hash index will be recovered as a skiplist index, any newly created table will also have its non-unique hash indexes created as skiplists. For more information about this engine variable see the Non-Sync Variables List.

Verify Your Cluster is Ready for Upgrade

Warning

If upgrading from MemSQL 6.x, there must not be any disaster recovery (DR) databases present before upgrading to SingleStoreDB 7.3. If there are any DR databases in the cluster, the database must be dropped before upgrading, and re-replicated after the upgrade. If you choose to keep a DR database, you must stop replicating to it before the upgrade. Note that this DR database cannot be used for replication after the upgrade.

If upgrading from SingleStoreDB 7.x with DR clusters, SingleStore recommends that you upgrade your DR secondary cluster(s) one at a time, and then upgrade your primary cluster last so that replication will continue to work after each upgrade.

Prior to upgrading your cluster, it is recommended that you take a backup as a standard precautionary measure. See Back Up and Restore 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 an “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 SingleStoreDB using either SingleStoreDB management tools or MemSQL Ops.

Upgrade Versions and Methods

The tables below depicts which versions of SingleStoreDB can be upgraded to SingleStoreDB 7.3 and the method by which the cluster can be upgraded.

Offline upgrade: Your SingleStoreDB cluster will be shut down and restarted over the course of the upgrade
Online upgrade: Your SingleStoreDB cluster will not be shut down over the course of the upgrade

Upgrade via SingleStoreDB Toolbox

Upgrade from	Offline upgrade	Online upgrade
7.0	✔	From 7.0.16+
6.8	✔	From 6.8.19+
6.7	✔	From 6.7.27+
6.5	✘	✘

Upgrade via MemSQL Ops

Upgrade from	Offline upgrade	Online upgrade
7.0	✔	✘
6.8	✔	✘
6.7	✔	✘
6.5	✔	✘

Upgrade Your Cluster

Select an option below to upgrade your cluster.

Upgrade Using SingleStoreDB Toolbox

Step 1: Upgrade SingleStoreDB Toolbox

To upgrade to SingleStoreDB 7.3, you must have Toolbox 1.5.3 or later installed prior to the SingleStoreDB upgrade process. It is recommended that you use the latest version of Toolbox when upgrading your cluster.

Run the install command to either install Toolbox if it is not already installed, or upgrade an existing version of Toolbox to the latest version of the package.

RHEL/CentOS

sudo yum install singlestoredb-toolbox -y

Debian

sudo apt install singlestoredb-toolbox -y

Upgrade without Internet Access

If your cluster does not have internet access, use one of the following buttons to download either the latest RPM or Debian singlestoredb-toolbox package to a location accessible by your cluster:

singlestoredb-toolbox (.rpm)

singlestoredb-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/singlestoredb-toolbox.rpm -y

Debian

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

Now you are ready to upgrade SingleStoreDB.

Step 2: Upgrade SingleStoreDB

The simplest and preferred upgrade option is an offline cluster upgrade. It is the least error-prone and easiest to perform; 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.

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. See the instructions in the Online upgrade section for more details.

Select an option below to upgrade your cluster.

Option 1: Offline Upgrade

Note: Using this method to upgrade to SingleStoreDB 7.3 is referred to as an “offline” upgrade as your SingleStoreDB cluster will be shut down and restarted over the course of the upgrade. Do not shut down your cluster prior to starting the upgrade. If the cluster or individual nodes are offline when the upgrade is started, the upgrade will fail.

Run the following command to start an offline upgrade.

Confirm that the cluster can be upgraded. The cluster will not be upgraded when running this command.
```
sdb-deploy upgrade --precheck-only
```
Upgrade your cluster.
Note: sdb-deploy upgrade will perform a snapshot of all databases prior to upgrade.
```
sdb-deploy upgrade --version 7.3
```

If you do not specify a patch version, your cluster will be upgraded to the latest patch version of SingleStoreDB 7.3.

During the upgrade process, you will be prompted to enable synchronous replication on your existing databases, or to leave those databases using the previous asynchronous replication behavior.

Synchronous replication is enabled by default on all new 7.3 databases and provides an extra layer of resiliency in clusters with high availability enabled.

Note: You cannot downgrade from your current version.

If your cluster does not have internet access, use one of the following buttons to download either the latest RPM or Debian singlestoredb-server package to a location accessible by your cluster:

singlestoredb-server (.rpm)

singlestoredb-server (.deb)

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

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

RHEL/CentOS

sdb-deploy upgrade --file-path /path/to/singlestoredb-server.rpm

Debian

sdb-deploy upgrade --file-path /path/to/singlestoredb-server.deb

Refer to sdb-deploy upgrade for more information.

Once you have finished upgrading all of your nodes, see the Post-Upgrade Considerations section for any changes between versions that may impact your cluster.

Option 2: Online Upgrade

This upgrade method is referred to as an “online” upgrade as your SingleStoreDBcluster will not be shut down over the course of the upgrade. Nodes will be restarted in a specific sequence to ensure that DML-based workloads will still function. Do not shut down your cluster prior to starting the upgrade. If the cluster or individual nodes are offline when the upgrade is started, the upgrade will fail.

Notice

An online upgrade may fail if a long-running workload that writes to the database, or a workload that manipulates SingleStoreDB files (such as an automated backup or maintenance script), is running on the target cluster. SingleStore recommends performing an online upgrade only after these workloads have completed.

Toolbox 1.11.7 and later provide the option to retry a failed online upgrade. Should the online upgrade ultimately fail, an offline upgrade will be attempted. Note that the cluster will be shut down and restarted over the course of an offline upgrade.

Run the following command to start an online upgrade.

Confirm that the cluster can be upgraded. The cluster will not be upgraded when running this command.
```
sdb-deploy upgrade --precheck-only
```
Upgrade your cluster.
Note: sdb-deploy upgrade will perform a snapshot of all databases prior to upgrade.
```
sdb-deploy upgrade --online --version 7.3
```

By specifying --version 7.3, your cluster will be upgraded to the latest patch version of SingleStoreDB 7.3.

During the upgrade process, you will be prompted to enable synchronous replication on your existing databases. While asynchronous replication can still be chosen, the data replication mechanism as of SingleStoreDB 7.0 has been enhanced to the point where synchronous replication is now the recommended option.

Note: You cannot downgrade from your current version.

If your cluster does not have internet access, use one of the following buttons to download either the latest RPM or Debian singlestoredb-server package to a location accessible by your cluster:

singlestoredb-server (.rpm)

singlestoredb-server (.deb)

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

Run the sdb-deploy upgrade --online command and reference the appropriate package in the --file-path option. Running upgrade (as opposed to simply upgrading the package via the package manager) will perform an offline restart of all the nodes to make sure the cluster is using the new version.

RHEL/CentOS

sdb-deploy upgrade --online --file-path /path/to/singlestoredb-server.rpm

Debian

sdb-deploy upgrade --online --file-path /path/to/singlestoredb-server.deb

Refer to sdb-deploy upgrade for more information.

Once you have finished upgrading all of your nodes, see the Post-Upgrade Considerations section for any changes between versions that may impact your cluster.

Roll Back from a Failed Upgrade

Currently, SingleStoreDB does not support downgrading directly. Use the following steps to roll back to an earlier version of SingleStoreDB using the backup made at the beginning of this upgrade guide.

Note that a backup created from a given version of the SingleStoreDB engine can only be restored to the same engine version or later.

Make a backup of the cluster configuration.
```
sdb-deploy generate-cluster-file
```
Delete all of the nodes in the cluster.
```
sdb-admin delete-node --stop --all
```
Use the following command to roll back to an earlier version of the SingleStoreDB engine by removing the engine version(s) you do not want.
For example, if upgrading to SingleStoreDB 8.0 fails, remove 8.0.
```
sdb-deploy uninstall --version 8.0
```

Unregister all hosts in the cluster.

sdb-toolbox-config unregister-host --all

Recreate the cluster using the cluster configuration captured in the cluster file. Note that the cluster file may contain the engine version, so be sure to update the cluster file with the engine version you wish to restore.
```
sdb-deploy setup-cluster --cluster-file /path/to/cluster/file
```
Restore the cluster's data from the backup that was made earlier. Refer to Backing Up and Restoring Data for more information.

Upgrade using MemSQL Ops

Step 1: Upgrade MemSQL Ops

If you manage your cluster with MemSQL Ops, you must upgrade it to the latest 7.0 version of MemSQL Ops before you can upgrade to SingleStoreDB 7.3.

If you are running Ops 6.0 or later, you can upgrade Ops to the latest version by running the agent-upgrade command.

memsql-ops agent-upgrade

Upgrade without Internet Access

If your cluster does not have internet access, download the 7.0.6 version to your local machine. Then, run the agent-upgrade command using the --file-path option.

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

Now you are ready to upgrade SingleStoreDB.

Step 2: Upgrade SingleStoreDB

The currently supported upgrade option is an offline cluster upgrade. It requires downtime as all of the nodes in the cluster will be upgraded at the same time.

Offline Upgrade

Note: Upgrading to SingleStoreDB 7.3 is referred to as an “offline” upgrade as your SingleStoreDB cluster will be shut down and restarted over the course of the upgrade. Do not shut down your cluster prior to starting the upgrade. If the cluster or individual nodes are offline when the upgrade is started, the upgrade will fail.

Run the following command to start an offline upgrade.

memsql-ops memsql-upgrade --version 7.3

If you do not specify a patch version, your cluster will be upgraded to the latest patch version of SingleStoreDB 7.3.

During the upgrade process, prompted to enable synchronous replication on your existing databases, or to leave those databases using the previous asynchronous replication behavior.

Synchronous replication is enabled by default on all new 7.3 databases and provides an extra layer of resiliency in clusters with high availability enabled.

Note: You cannot downgrade from your current version.

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

For the latest version, the download link is: http://download.memsql.com/releases/latest/memsqlbin_amd64.tar.gz
For any other version, the download link format is: http://download.memsql.com/releases/version/X.Y.Z/memsqlbin_amd64.tar.gz

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

Refer to MEMSQL-UPGRADE for more information.

Once you have finished upgrading all of your nodes, see the Post-Upgrade Considerations section for any changes between versions that may impact your cluster.

Post-Upgrade Considerations

When upgrading to SingleStoreDB 7.3, 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 SingleStoreDB 7.3 may differ compared to a newly installed cluster on SingleStoreDB 7.3 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 SingleStoreDB 7.3 clusters but not automatically enabled on clusters upgraded from an earlier version to 7.3. In some of these cases, it is recommended to enable the new features, after appropriate testing.

Upgrades to 7.3

To reduce your total cost of ownership (TCO), you may be able store data in Universal Storage instead of rowstores. This is because rowstores store their data in RAM, which can be costly. Universal Storage now supports upserts, which were previously only supported in rowstores.
You may want to run the command REBALANCE ALL DATABASES. This command rebalances each database in the cluster, in alphabetical order of the database name. When a rebalance runs on a database d, it first considers the placement of the partitions of the other databases in the cluster before rebalancing the partitions of d.
You may want to set the cardinality_estimation_levelengine variable to '7.3'. This setting uses sampling and histograms together (when both are available) to improve selectivity estimation. The default setting is '7.1'.
Changing the value of the data_conversion_compatibility_level engine variable can change the behavior of expressions in computed columns. Refer to the Data Type Conversion section of Data Types for more information.
sp_query_dynamic_param should be turned off if an application breaks post-upgrade due to a change in type conversion behavior. See the Example: Changes in Type Conversion Behavior for more information.
Upgrading the cluster, with json_extract_string_collation set to auto (default setting), changes the collation settings for JSON_EXTRACT_STRING from json to server. Refer to In-Depth Variable Definitions for information on json_extract_string_collation settings.

Upgrades from 6.8 and Earlier to 7.0 and Later

Synchronous Replication On by Default

In previous versions of SingleStoreDB, in clusters with high availability enabled, replication between master partitions and replica partitions happened asynchronously. Starting with 7.0, synchronous replication is enabled by default for all new databases. And as noted in the upgrade section of this topic, you have the option to opt-out of synchronous replication for the existing databases in your cluster. For more information on synchronous replication, see Replication and Durability and CREATE DATABASE.

Security Change for Resource Pools

Between MemSQL 6.5 to 6.8, all users were able to access all resource pools. Access had to be controlled externally from the database, such as in a client application. Starting in 7.0, users can only access resource pools that they have explicit permission to access when sync_permissions is enabled. Otherwise, no permission checking is done for any user for any resource pool. Refer to Synchronizing Permissions Across Your Cluster for more information.

To ensure current users will be able to access pools immediately after upgrading to 7.3, all existing users will be granted USAGE permissions to all existing and future resource pools if sync_permissions was enabled prior to upgrade (i.e. for each user GRANT USAGE ON RESOURCE POOL '*' TO <user>@<host> is run internally on upgrade to 7.0 or later) unless REVOKE USAGE ON RESOURCE POOL '*' FROM <user>@<host> is run. You will then have to explicitly grant USAGE permissions to specific resource pools for those users and any other new users created. Refer to Set Resource Limits for more information.

Many Existing Engine Variables are Now Sync Variables

The following engine variables from 6.8 are now Sync Variables, which means you set the value on the master aggregator and the change is propagated across your cluster. You no longer have to set and manage these variables on each node in the cluster.

Global variables

auditlog_disk_sync
columnstore_disk_insert_threshold
columnstore_flush_bytes
columnstore_ingest_management_queue_timeout
columnstore_segment_rows
disk_plan_expiration_minutes
enable_columnstore_ingest_management
enable_disk_plan_expiration
explain_expression_limit
forward_aggregator_plan_hash
geo_query_info
geo_sphere_radius
internal_columnstore_window_minimum_blob_size
load_data_internal_compression
load_data_max_buffer_size
load_data_read_size
load_data_write_size
materialize_ctes
max_connect_errors
max_prepared_stmt_count
multi_insert_tuple_count
pipelines_batches_metadata_to_keep
pipelines_extractor_debug_logging
pipelines_kafka_version
pipelines_max_concurrent
pipelines_max_concurrent_batch_partitions
pipelines_max_errors_per_partition
pipelines_stderr_bufsize
plan_expiration_minutes
read_advanced_counters
replication_timeout_ms
snapshot_trigger_size
sync2_timeout
synchronize_database_timeout

Session Variables

character_set_server
collation_connection
collation_database
collation_server
enable_binary_protocol
enable_broadcast_left_join
enable_local_shuffle_group_by
enable_multipartition_queries
enable_skiplist_sampling_for_selectivity
explain_joinplan_costs
ignore_insert_into_computed_column
inlist_precision_limit
leaf_pushdown_default
leaf_pushdown_enable_rowcount
lock_wait_timeout
max_broadcast_tree_rowcount
max_subselect_aggregator_rowcount
optimize_constants
optimize_expressions_larger_than
optimize_huge_expressions
optimize_stmt_threshold
optimizer_warnings
report_mpl_optimizations
reshuffle_group_by_base_cost
sampling_estimates_for_complex_filters
sql_select_limit
statistics_warnings

See the List of Engine Variables for more information on these variables.

In this section:

Upgrade to SingleStoreDB 7.3

Notice

Caution

Important Notes About Upgrading

Upgrade Duration and Behavior

Compound Keys

Delete Performance

Leaf Nodes

Plancache

Replication

SCHEMA_BINDING for Views

Snapshots

Sync Variables

Non-Sync Variables

Verify Your Cluster is Ready for Upgrade

Warning

Upgrade Versions and Methods

Upgrade via SingleStoreDB Toolbox

Upgrade via MemSQL Ops

Upgrade Your Cluster

Upgrade Using SingleStoreDB Toolbox

Step 1: Upgrade SingleStoreDB Toolbox

Upgrade without Internet Access

Step 2: Upgrade SingleStoreDB

Option 1: Offline Upgrade

Option 2: Online Upgrade

Notice

Roll Back from a Failed Upgrade

Upgrade using MemSQL Ops

Step 1: Upgrade MemSQL Ops

Upgrade without Internet Access

Step 2: Upgrade SingleStoreDB

Offline Upgrade

Post-Upgrade Considerations

Upgrades to 7.3

Upgrades from 6.8 and Earlier to 7.0 and Later

Synchronous Replication On by Default

Security Change for Resource Pools

Many Existing Engine Variables are Now Sync Variables

Search results

`SCHEMA_BINDING` for Views