Hash-Mod sharded backups / restore of replicated tables

[mskwon]

Hi,

As I understand it, the way to back up a replicated table is to take the back up from a single replica. To restore this replicated table, the schema must be restored on all of the replicas and then the data must be restored on a single replica - after which the replication will take over to restore all replicas.

I was wondering what you thought of adding a flag which will make clickhouse-backup query system.replicas and then perform a hash of the table name and then doing a mod with the number of active replicas for the table. If the resulting number is the same as the index of the current replica in a sorted list of active replicas for the table, then this table is selected for backup.

I was thinking that this might be nice as then the backup and the restore could be distributed among all of the replicas, all of the unreplicated tables would also be covered (since N%1 == 0). It would also be the case that the schema-only restore of a replica could be replaced with just a full restore. This behavior could be selected with a flag --sharded-backup perhaps.

While it is true that this strategy might not work so well in the case of a network partition, I'd assume that backups will more often than not occur when the installation is in a good state.

[Slach]

root reason why we restore only in one replica in shard is ATTACH PART doesn't apply part deduplication mechanism for ReplicatedMergeTree and concurrent ATTACH PART for the same data will make race condition between ATTACH_PART replication events and local ATTACH PART SQL query

when one replica push ATTACH_PART event to system.replication_queue and second replica can't make ATTACH PART cause part already attached in other replica

your idea, looks good
but not clear for me howto detect current replica index?
could you provide SQL query?

as i see
replicas_is_active in system.replicas added only 2 years ago and will not work older than 21.x clickhouse-server version

maybe better use system.clusters, but replica name shall equal with hostName(), but it not guaranteed

SELECT

[mskwon]

I've made an attempt here - it is kind of heavy.. Please give me a heads up what you think

[UnamedRus]

query system.replicas and then perform a hash of the table name and then doing a mod with the number of active replicas for the table.

I don't like idea of doing this on table level, because tables tends to be very different in size, quite often people have 1 big table and rest is small.

Better will be do that on partition level, but it more complex.

[mskwon]

I see, based on consistency concerns from Slach, the current implementation includes multiple different modes for the sharding of the backup operation (individual tables, individual databases, first active replica only). I'd like to keep these modes of operation as I can definitely see how splitting the backup of a database across multiple replicas could cause issues in a star schema situation (missing dimension row for a corresponding fact table entry?).

This current commit is sort of big - would it be alright if the first iteration only includes the table / database / first active replica sharded operation mode and then for a later PR (also linked with this issue) will implement the "partition" sharded operation mode?

FWIW, I agree with you - our clickhouse setup has wide tables without star schema and the tables don't have some sort of informal foreign key / primary key relationship between each other. Our test backups did show the exact backup duration skew you were describing - as a handful of tables are much bigger than our average.