Hi, I’m very new to MemSQL and primarily have a RDBMS background. I’m running MemSQL with high_availability set to false, and would like to see performance based on different partitioning and replication schemes.
However, in trying to dynamically replicate and partition, I cannot find any documentation - as most replication documentations are about replicating to entire clusters (and not nodes), and most partitioning documents indicate to setting partitions during schema definition.
Could someone please advise on how it would be possible to do something like this, which can be done in PostgreSQL:
Thank you for your reply as the resources were indeed very helpful. However, I would still like to perform these tasks dynamically instead of during DDL. What would be the best practice to e.g. change the shard key for a relation?
We don’t currently support directly changing the shard key of a table, though we’re considering it for the future. The way to do it now is as follows. Suppose you have table t, sharded on column x, but you want it to be sharded on column y. You would do this:
create table t2(...., shard(y)); -- t2 otherwise has same schema as t
insert into t2 select * from t;
-- check t2 to make sure it has what you want in it
drop table t;
alter table t2 rename t;
This is an offline process, but can be pretty fast even for large tables.
Maybe. The KEY(…) definitions in your create table statement may already have created the indexes you need, depending on the table type and kind of key. If not, you’d need to run CREATE INDEX statements too, as appropriate.