Upgrade to SingleStoreDB 7. 3
On this page
Note
Please note the following before upgrading your cluster:
-
SingleStoreDB betas and release candidates cannot be upgraded unless explicitly stated in the release notes.
-
If any host in the cluster is near or at disk capacity, please increase available storage before upgrading.
Important Notes About Upgrading
This topic describes how to upgrade SingleStoreDB.
After you have finished upgrading, please see the Post-Upgrade Considerations section for additional information on behavioral changes that you should be aware of.
Upgrade Duration and Behavior
Anticipate a longer upgrade time for each node.
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.
Non-Sync Variables
By default, convert_
is set to TRUE
as of SingleStoreDB 7.
Verify Your Cluster is Ready for Upgrade
Warning
If upgrading from MemSQL 6.
If upgrading from MemSQL version 6.
If upgrading from MemSQL 6.
If upgrading from SingleStoreDB 7.
Prior to upgrading your cluster, it is recommended that you take a backup as a standard precautionary measure.
In addition, run the following SQL commands from the Master Aggregator to confirm that the following are true.
-
All leaf nodes ("leaves") are online.
SHOW LEAVES; -
All aggregators are online.
SHOW AGGREGATORS; -
There are no partitions with an
Orphan
role.SHOW CLUSTER STATUS; -
No rebalance or restore redundancy is necessary.
EXPLAIN REBALANCE PARTITIONS;EXPLAIN RESTORE REDUNDANCY;
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 the SingleStoreDB management tools or MemSQL Ops.
Upgrade Versions and Methods
The tables below depicts which versions of SingleStoreDB can be upgraded to SingleStoreDB 7.
-
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. |
✔ |
From 7. |
6. |
✔ |
From 6. |
6. |
✔ |
From 6. |
6. |
✘ |
✘ |
Upgrade via MemSQL Ops
Upgrade from |
Offline upgrade |
Online upgrade |
---|---|---|
7. |
✔ |
✘ |
6. |
✔ |
✘ |
6. |
✔ |
✘ |
6. |
✔ |
✘ |
Upgrade Your Cluster
Select an option below to upgrade your cluster.
Post-Upgrade Considerations
When upgrading to SingleStoreDB 7.
-
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 ofd
. -
You may want to set the
cardinality_
engine variable toestimation_ level '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_
engine variable can change the behavior of expressions in computed columns.conversion_ compatibility_ level Refer to the Data Type Conversion section of Data Types for more information. -
sp_
should be turned off if an application breaks post-upgrade due to a change in type conversion behavior.query_ dynamic_ param See the Example: Changes in Type Conversion Behavior for more information. -
Upgrading the cluster, with
json_
set toextract_ string_ collation auto
(default setting), changes the collation settings forJSON_
fromEXTRACT_ STRING json
toserver
.Refer to In-Depth Variable Definitions for information on json_
settings.extract_ string_ collation
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.
Security Change for Resource Pools
Between MemSQL 6.sync_
is enabled.
To ensure current users will be able to access pools immediately after upgrading to 7.USAGE
permissions to all existing and future resource pools if sync_
was enabled prior to upgrade (i.GRANT USAGE ON RESOURCE POOL '*' TO <user>@<host>
is run internally on upgrade to 7.REVOKE USAGE ON RESOURCE POOL '*' FROM <user>@<host>
is run.USAGE
permissions to specific resource pools for those users and any other new users created.
Many Existing Engine Variables are Now Sync Variables
The following engine variables from 6.
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.
Last modified: November 2, 2023