In this article, we will learn how to move a table to the another device. First, follow the steps which is given below.
Create directory on new device for ClickHouse data.
mkdir /mnt/disk_1/clickhouse
Change ownership of created directory to ClickHouse user.
chown -R clickhouse:clickhouse /mnt/disk_1/clickhouse
Create a special storage policy that includes both old and new disks.
vi /etc/clickhouse-server/config.d/storage.xml
Now fill the file with the following parameters.
<yandex>
<storage_configuration>
<disks>
<!--
default disk is special, it always
exists even if not explicitly
configured here, but you can't change
it's path here (you should use <path>
on top level config instead)
-->
<default>
<!--
You can reserve some amount of free space
on any disk (including default) by adding
keep_free_space_bytes tag
-->
</default>
<disk_1> <!-- disk name -->
<path>/mnt/disk_1/clickhouse/</path>
</disk_1>
</disks>
<policies>
<move_from_default_to_disk_1> <!-- name for new storage policy -->
<volumes>
<default>
<disk>default</disk>
<max_data_part_size_bytes>10000000</max_data_part_size_bytes>
</default>
<disk_1_vol> <!-- name of volume -->
<!--
we have only one disk in that volume
and we reference here the name of disk
as configured above in <disks> section
-->
<disk>disk_1</disk>
</disk_1_vol>
</volumes>
<move_factor>0.99</move_factor>
</move_from_default_to_disk_1>
</policies>
</storage_configuration>
</yandex>
Now, connect to ClickHouse Server, then update the storage_policy setting of tables to the new policy.
ALTER TABLE trips MODIFY SETTING storage_policy='move_from_default_to_disk_1';
Check the current disk status.
Check current disk information.
SELECT
disk_name,
path,
sum(rows),
sum(bytes_on_disk),
uniq(partition),
count()
FROM system.parts
WHERE (table = 'trips') AND active
GROUP BY
disk_name,
path
ORDER BY
disk_name ASC,
path ASC
Query id: 645ca093-d63b-4a42-9038-991b94c756fa
┌─disk_name─┬─path────────────────────────────────────────────────────────────────────────────────┬─sum(rows)─┬─sum(bytes_on_disk)─┬─uniq(partition)─┬─count()─┐
│ disk_1 │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201507_1_1_0/ │ 192866 │ 16024134 │ 1 │ 1 │
│ disk_1 │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201507_4_4_0/ │ 192775 │ 16018221 │ 1 │ 1 │
│ disk_1 │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201508_2_2_0/ │ 556737 │ 45966120 │ 1 │ 1 │
│ disk_1 │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201508_5_5_0/ │ 556491 │ 45967817 │ 1 │ 1 │
│ disk_1 │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201509_3_3_0/ │ 250229 │ 20809969 │ 1 │ 1 │
│ disk_1 │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201509_6_6_0/ │ 250559 │ 20845804 │ 1 │ 1 │
└───────────┴─────────────────────────────────────────────────────────────────────────────────────┴───────────┴────────────────────┴─────────────────┴─────────┘
6 rows in set. Elapsed: 0.005 sec.
To change the device, please remove ‘default’ disk from new storage policy.
vi /etc/clickhouse-server/config.d/storage.xml
Edit file
<yandex>
<storage_configuration>
<disks>
<!--
default disk is special, it always
exists even if not explicitly
configured here, but you can't change
it's path here (you should use <path>
on top level config instead)
-->
<default>
<!--
You can reserve some amount of free space
on any disk (including default) by adding
keep_free_space_bytes tag
-->
</default>
<disk_1> <!-- disk name -->
<path>/mnt/disk_1/clickhouse/</path>
</disk_1>
</disks>
<policies>
<move_from_default_to_disk_1> <!-- name for new storage policy -->
<volumes>
<disk_1_vol> <!-- name of volume -->
<!--
we have only one disk in that volume
and we reference here the name of disk
as configured above in <disks> section
-->
<disk>disk_1</disk>
</disk_1_vol>
</volumes>
<move_factor>0.99</move_factor>
</move_from_default_to_disk_1>
</policies>
</storage_configuration>
</yandex>
To enable changes please restart ClickHouse Server
systemctl restart clickhouse-server
Done!
Make sure that the storage policy uses the right disks.
SELECT * FROM system.storage_policies WHERE policy_name = 'move_from_default_to_disk_1' Query id: 46c7b42e-16bd-44a3-a9c4-0be5cea4b981 ┌─policy_name─────────────────┬─volume_name─┬─volume_priority─┬─disks──────┬─volume_type─┬─max_data_part_size─┬─move_factor─┬─prefer_not_to_merge─┐ │ move_from_default_to_disk_1 │ disk_1_vol │ 1 │ ['disk_1'] │ JBOD │ 0 │ 0.99 │ 0 │ └─────────────────────────────┴─────────────┴─────────────────┴────────────┴─────────────┴────────────────────┴─────────────┴─────────────────────┘ 1 row in set. Elapsed: 0.002 sec.
root@clickhouse01:/mnt/disk_1/clickhouse/store/ac1# pwd /mnt/disk_1/clickhouse/store/ac1 root@clickhouse01:/mnt/disk_1/clickhouse/store/ac1# du -sh /mnt/disk_1/clickhouse/store/ac1 160M /mnt/disk_1/clickhouse/store/ac1