memory usage formula for uploads

[bmailhe]

I'm benchmarking backup upload to S3 but I can not figure out how settings affect the memory usage.
I try to tune the settings to optimize upload time and memory usage but I most of the time I got OOM error.
Can you explain how the memory usage can be calculated depending on the settings ?

[Slach]

how much memory do you have?
restrict upload_concurrency and download_concurrency

memory usage correlates with following formula
((S3_PART_SIZE (calculated from system.parts) / S3_PART_COUNT) * S3_CONCURRENCY) * (UPLOAD_CONCURRENCY ^ 2)

[bmailhe]

I have ~125Gib of RAM with ~12GiB free.
I try to upload a 5.1GiB backup file. When running the process takes 2516.55 MiB of memory with these settings :

general:
  remote_storage: s3
  max_file_size: 1099511627776
  disable_progress_bar: true
  backups_to_keep_local: 0
  backups_to_keep_remote: 0
  log_level: warn
  allow_empty_backups: false
  download_concurrency: 100
  upload_concurrency: 100
  restore_schema_on_cluster: ""
  upload_by_part: true
  download_by_part: true
  restore_database_mapping: {}
  retries_on_failure: 3
  upload_retries_pause: 100ms
  watch_interval: 1h
  full_interval: 24h
  watch_backup_name_template: shard{shard}-{type}-{time:20060102150405}
  retriesduration: 100ms
  watchduration: 1h0m0s
  fullduration: 24h0m0s
clickhouse:
  username: default
  password: ""
  host: localhost
  port: 9000
  disk_mapping: {}
  skip_tables:
    - system.*
    - default.*
    - full_orderbooks_v1.*
  timeout: 5m
  freeze_by_part: false
  freeze_by_part_where: ""
  use_embedded_backup_restore: false
  embedded_backup_disk: ""
  secure: false
  skip_verify: false
  sync_replicated_tables: false
  log_sql_queries: true
  config_dir: /etc/clickhouse-server/
  restart_command: systemctl restart clickhouse-server
  ignore_not_exists_error_during_freeze: true
  check_replicas_before_attach: true
  tls_key: ""
  tls_cert: ""
  tls_ca: ""
  debug: false
s3:
  bucket: "testbertrand"
  endpoint: "s3.us-west-000.backblazeb2.com"
  region: "us-west-000"
  acl: ""
  assume_role_arn: ""
  force_path_style: false
  path: ""
  disable_ssl: false
  compression_level: 5
  compression_format: gzip
  sse: ""
  disable_cert_verification: false
  use_custom_storage_class: false
  storage_class: STANDARD
  concurrency: 1
  part_size: 0
  max_parts_count: 10000
  allow_multipart_download: false
  debug: false

[bmailhe]

I'm confused as according to those settings, if I'm right, it should be 105MiB, while I observe the process takes 1477 MiB (RES memory from top cmd) 😕

S3_PART_SIZE=max_file_size / max_parts_count, between 5MB and 5Gb
S3_PART_SIZE=1099511627776/10000
S3_PART_SIZE=109951163

Memory usage = ((S3_PART_SIZE (calculated from system.parts) / S3_PART_COUNT) * S3_CONCURRENCY) * (UPLOAD_CONCURRENCY ^ 2)
Memory usage = 109951163/10000*1*100^2
Memory usage = 109951163 B (105MiB)

[Slach]

max_file_size: 1099511627776

Please remove it from general section, currently max_file_size calculates from system.parts
which clickhouse-backup version do you use?

download_concurrency: 100
upload_concurrency: 100

it means 100 parallel tables and 100 parallel parts inside tables = 1000 streams which will allocate buffers

for your use case

download_concurrency: 5
upload_concurrency: 5

is enough

compression_level: 5
compression_format: gzip

this could to affect CPU usage (clickhouse-sever will struggle to CPU with clickhouse-backup)
data in clickhouse already compressed
if you don't have enough CPU cores, you could use compression_format: tar

[bmailhe]

Version:         2.1.2
Git Commit:      376c8dd499c3f8c4a0f873670345c8b53ef5c44a
Build Date:      2022-10-23

[Slach]

did you apply recommendations from #571 (comment) ?

[bmailhe]

yes I applied it, it works fine for a small server configuration. Now I'm benchmarking with several different settings for a production server in order to optimize transfer and memory usage. Any advice is welcome ! :)

[bmailhe]

on my production server I can not saturate the upload bandwidth, it can reach 4Gbps but while uploading with clickhouse-backup, the best I got was <2000Mbps (trying with different settings). The files size is 3TB, the destination Backblaze. Any advice is welcome.

[Slach]

which is your source location datacenter? are you sure you have enough bandwidth from blackbaze side to your datacenter?

ok , need more context
how spread this 3TB?

could you share

SELECT database, table, count(), quantiles(0.5,0.99)(bytes_on_disk) 
FROM system.parts WHERE database!='system' GROUP BY database, table

and current config parameters

upload_concurrency
compression_format

and concurrency from s3 section?

[bmailhe]

Datacenter is OVH SAS in Quebec (QC) Canada (CA)
Backblaze endpoint is s3.us-west-000.backblazeb2.com

Tests made with rclone upload up to 2600Mbps

I have two tables to backup:

┌─table─────────────────┬─size─────┬────────rows─┬─engine───────────────────────┐
│ prod_db.snapshot_v1   │ 7.68 TiB │ 42732991888 │ ReplicatedMergeTree          │
│ prod_db.snapshot_v2   │ 3.12 TiB │ 32091088902 │ ReplicatedReplacingMergeTree │
└───────────────────────┴──────────┴─────────────┴──────────────────────────────┘

┌─database┬─table─────────┬─count()─┬─quantiles(0.5, 0.99)(bytes_on_disk)─┐
│ prod_db │ snapshot_v1   │    3729 │ [82864,103760322697.35974]          │
│ prod_db │ snapshot_v2   │     255 │ [235362444,141456943706.1201]       │
└─────────┴───────────────┴─────────┴─────────────────────────────────────┘

For parameters I have tried several configurations:

max_file_size	x	x	x	x
download_concurrency	50	5	25	25
upload_concurrency	50	5	25	25
upload_by_part	TRUE	TRUE	TRUE	TRUE
compression_level	1	1	1	1
compression_format	tar	tar	tar	tar
s3.concurrency	100	25	1	100
part_size	0	0	0	0
max_parts_count	20000	20000	20000	20000

[Slach]

Try following settings

general:
  upload_concurrency: 5
s3:
  concurrency: 5
  max_parts_count: 5000

[bmailhe]

with these settings I only have ~956Mbps

[Slach]

Do you have only 2 tables for backup?
snapshot_v1 and snapshot_v2 ?

[bmailhe]

on this database, yes only these 2 tables.

[Slach]

what about upload_concurrency: 10 ?

[bmailhe]

it works fine with upload_concurrency: 10 the next bottleneck are disk IO