s3_disk_cache

s3_disk_cache.sql

aarch64


ClickHouse client version 22.4.5.8.
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.4.5 revision 54455.

<clickhouse>
    <storage_configuration>
        <disks>
            <s3>
              <type>s3</type>
              <endpoint>https://s3.us-east-1.amazonaws.com/t..../test_table/</endpoint>
              <access_key_id>....</access_key_id>
        	    <secret_access_key>....</secret_access_key>
              <region>us-east-1</region>
              <connect_timeout_ms>10000</connect_timeout_ms>
              <request_timeout_ms>5000</request_timeout_ms>
              <retry_attempts>10</retry_attempts>
              <single_read_retries>4</single_read_retries>
              <metadata_path>/var/lib/clickhouse/disks/s3/</metadata_path>
              <cache_enabled>true</cache_enabled>
              <cache_path>/var/lib/clickhouse/disks/s3/cache/</cache_path>
              <skip_access_check>false</skip_access_check>
             </s3>
         </disks>
         <policies>
            <s3_cold>
               <volumes>
                 <main>
                    <disk>default</disk>
                 </main>
               <external>
                  <disk>s3</disk>
	          <prefer_not_to_merge>true</prefer_not_to_merge>
               </external>
                </volumes>
            <move_factor>0.2</move_factor>
            </s3_cold>
     </policies>

    </storage_configuration>
</clickhouse>



CREATE TABLE default.test_s3_3
(
    `D` Date,
    `A` Int64
)
ENGINE = MergeTree
PARTITION BY D
ORDER BY A
SETTINGS storage_policy = 's3_cold';

insert into default.test_s3_3(D,A) select toDate('2022-04-30'), number from numbers(10000000);
insert into default.test_s3_3(D,A) select toDate('2022-05-10'), number from numbers(10000000);

select name, table, formatReadableSize(bytes_on_disk), rows,disk_name from system.parts where table =  'test_s3_3' and active;

┌─name─────────────┬─table─────┬─formatReadableSize(bytes_on_disk)─┬─────rows─┬─disk_name─┐
│ 20220430_1_10_2  │ test_s3_3 │ 38.34 MiB                         │ 10000000 │ default   │
│ 20220510_11_20_2 │ test_s3_3 │ 38.34 MiB                         │ 10000000 │ default   │
└──────────────────┴───────────┴───────────────────────────────────┴──────────┴───────────┘

┌─changed─┬─name────────────────────────────────────────┬─value─┐
│       0 │ enable_filesystem_cache                     │ 1     │
│       0 │ enable_filesystem_cache_on_write_operations │ 0     │
└─────────┴─────────────────────────────────────────────┴───────┘



select uniq(A) from test_s3_3;
1 rows in set. Elapsed: 0.136 sec. Processed 20.00 million rows, 160.00 MB (147.39 million rows/s., 1.18 GB/s.)

select uniq(A) from test_s3_3;
1 rows in set. Elapsed: 0.122 sec. Processed 20.00 million rows, 160.00 MB (163.59 million rows/s., 1.31 GB/s.)

select uniq(A) from test_s3_3;
1 rows in set. Elapsed: 0.098 sec. Processed 20.00 million rows, 160.00 MB (203.23 million rows/s., 1.63 GB/s.)

select uniq(A) from test_s3_3;
1 rows in set. Elapsed: 0.102 sec. Processed 20.00 million rows, 160.00 MB (197.02 million rows/s., 1.58 GB/s.)


alter table test_s3_3 move partition '20220510' to disk 's3';
alter table test_s3_3 move partition '20220430' to disk 's3';

select name, table, formatReadableSize(bytes_on_disk), rows,disk_name from system.parts where table =  'test_s3_3' and active;
┌─name─────────────┬─table─────┬─formatReadableSize(bytes_on_disk)─┬─────rows─┬─disk_name─┐
│ 20220430_1_10_2  │ test_s3_3 │ 38.34 MiB                         │ 10000000 │ s3        │
│ 20220510_11_20_2 │ test_s3_3 │ 38.34 MiB                         │ 10000000 │ s3        │
└──────────────────┴───────────┴───────────────────────────────────┴──────────┴───────────┘


select uniq(A) from test_s3_3;
1 rows in set. Elapsed: 5.540 sec. Processed 20.00 million rows, 160.00 MB (3.61 million rows/s., 28.88 MB/s.)
1 rows in set. Elapsed: 0.779 sec. Processed 20.00 million rows, 160.00 MB (25.69 million rows/s., 205.49 MB/s.)
1 rows in set. Elapsed: 1.021 sec. Processed 20.00 million rows, 160.00 MB (19.60 million rows/s., 156.78 MB/s.)
1 rows in set. Elapsed: 1.141 sec. Processed 20.00 million rows, 160.00 MB (17.53 million rows/s., 140.20 MB/s.)
1 rows in set. Elapsed: 4.712 sec. Processed 20.00 million rows, 160.00 MB (4.24 million rows/s., 33.96 MB/s.)
1 rows in set. Elapsed: 6.091 sec. Processed 20.00 million rows, 160.00 MB (3.28 million rows/s., 26.27 MB/s.)
1 rows in set. Elapsed: 5.270 sec. Processed 20.00 million rows, 160.00 MB (3.80 million rows/s., 30.36 MB/s.)


select * from system.filesystem_cache;

0 rows in set. Elapsed: 0.002 sec.


set enable_filesystem_cache=0;
1 rows in set. Elapsed: 0.910 sec. Processed 20.00 million rows, 160.00 MB (21.99 million rows/s., 175.92 MB/s.)
1 rows in set. Elapsed: 3.827 sec. Processed 20.00 million rows, 160.00 MB (5.23 million rows/s., 41.81 MB/s.)


SELECT * FROM system.remote_data_paths
┌─disk_name─┬─path──────────────────────────┬─cache_base_path─┬─local_path────────────────────────────────────────────────────────────────────────────────────┬─remote_path─────────────────────────────────┬─cache_paths─┐
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220430_1_10_2/primary.idx                    │ test_table/kkwfamxcmhwsiwfsofgcpaypxzzbfpxn │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220430_1_10_2/D.mrk2                         │ test_table/hnznummrtwzzigxoyknzongbtzacmcqm │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220430_1_10_2/D.bin                          │ test_table/swdoeorguiafpdbuoyrzpgkuovifabwv │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220430_1_10_2/A.bin                          │ test_table/eiicrolfnkhpecljikgxvuooxmyhpmbd │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220430_1_10_2/minmax_D.idx                   │ test_table/sgwrbjcsbuwzopibexndyxldbrmkeaql │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220430_1_10_2/A.mrk2                         │ test_table/vkivsqexxciwbfkhgzssgrhxavokyreo │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220430_1_10_2/checksums.txt                  │ test_table/irugrrivwvrqiuecdcobuuifbnwnxyye │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220430_1_10_2/partition.dat                  │ test_table/hkjoxqnlljstbpcevvyvhxioogybmniu │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220430_1_10_2/default_compression_codec.txt  │ test_table/ljfwizpmfjirpjubwrlsnlcxrojommry │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220430_1_10_2/columns.txt                    │ test_table/wywqxfkicbkewmpdblvrifxjlbhqlsrv │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220430_1_10_2/count.txt                      │ test_table/efylagbqooarrcvmvbepyettsvnnsggx │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220510_11_20_2/primary.idx                   │ test_table/jmcpplgfsdhczkyupbauzwzaqczmnncz │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220510_11_20_2/D.mrk2                        │ test_table/odhzwkekrhcbbhepzyjzwrxxnrmvozsw │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220510_11_20_2/D.bin                         │ test_table/vwjxusguieijzsntupmkfmfgqglslnqp │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220510_11_20_2/A.bin                         │ test_table/dhmhytzioylnqujfierqpgtwatimudvg │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220510_11_20_2/minmax_D.idx                  │ test_table/bojitknabbtjlzeblyewrexkifolcxtd │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220510_11_20_2/A.mrk2                        │ test_table/ncmotzcidgihuyemkvfqflkcgavptbhx │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220510_11_20_2/checksums.txt                 │ test_table/mhnvgnerwmgecknvbrbkdbyjrfbflwwe │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220510_11_20_2/partition.dat                 │ test_table/tnfkcnokzuzkzmvaipicbcqlwshlbmzn │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220510_11_20_2/default_compression_codec.txt │ test_table/wrifyecwckdweypmmsmgluqpkluojnak │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220510_11_20_2/columns.txt                   │ test_table/yadodstfnculbuallyogtbkcvqrbbbsl │ []          │
│ s3        │ /var/lib/clickhouse/disks/s3/ │                 │ store/b5c/b5c8aa5e-9cd3-4bfe-9292-f13ba821d3be/20220510_11_20_2/count.txt                     │ test_table/rxvfjxffzoithbbruuleaupfiqwaehwo │ []          │
└───────────┴───────────────────────────────┴─────────────────┴───────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────────────────────────────────┴─────────────┘