Skip to content

Instantly share code, notes, and snippets.

@tkroman

tkroman/c1.xml

Last active November 20, 2018 23:17
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save tkroman/07b422075a5aa7023e173c59f7bcd89a to your computer and use it in GitHub Desktop.
Save tkroman/07b422075a5aa7023e173c59f7bcd89a to your computer and use it in GitHub Desktop.
Download ZIP
fuckfuckfuck
Raw
c1.xml
<yandex>
<logger>
<level>debug</level>
<log>/var/log/clickhouse-server/clickhouse-server.log</log>
<errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
<size>1000M</size>
<count>10</count>
<console>1</console>
</logger>
<http_port>8123</http_port>
<tcp_port>9000</tcp_port>
<interserver_http_port>9009</interserver_http_port>
<interserver_http_host>host.docker.internal</interserver_http_host>
<max_connections>4096</max_connections>
<keep_alive_timeout>3</keep_alive_timeout>
<max_concurrent_queries>100</max_concurrent_queries>
<max_open_files>262144</max_open_files>
<uncompressed_cache_size>8589934592</uncompressed_cache_size>
<mark_cache_size>5368709120</mark_cache_size>
<path>/var/lib/clickhouse/</path>
<tmp_path>/var/lib/clickhouse/tmp/</tmp_path>
<user_files_path>/var/lib/clickhouse/user_files/</user_files_path>
<users_config>users.xml</users_config>
<default_profile>default</default_profile>
<default_database>default</default_database>
<remote_servers>
<level0>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>host.docker.internal</host>
<port>9000</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>host.docker.internal</host>
<port>9001</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>host.docker.internal</host>
<port>9002</port>
</replica>
</shard>
</level0>
<cross>
<shard>
<internal_replication>true</internal_replication>
<replica>
<default_database>db0</default_database>
<host>host.docker.internal</host>
<port>9000</port>
</replica>
<replica>
<default_database>db1</default_database>
<host>host.docker.internal</host>
<port>9001</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<default_database>db0</default_database>
<host>host.docker.internal</host>
<port>9001</port>
</replica>
<replica>
<default_database>db1</default_database>
<host>host.docker.internal</host>
<port>9002</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<default_database>db0</default_database>
<host>host.docker.internal</host>
<port>9002</port>
</replica>
<replica>
<default_database>db1</default_database>
<host>host.docker.internal</host>
<port>9000</port>
</replica>
</shard>
</cross>
</remote_servers>
<builtin_dictionaries_reload_interval>3600</builtin_dictionaries_reload_interval>
<max_session_timeout>3600</max_session_timeout>
<default_session_timeout>60</default_session_timeout>
<query_log>
<database>system</database>
<table>query_log</table>
<partition_by>toYYYYMM(event_date)</partition_by>
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>
<dictionaries_config>*_dictionary.xml</dictionaries_config>
<distributed_ddl>
<path>/clickhouse/task_queue/ddl</path>
</distributed_ddl>
<format_schema_path>/var/lib/clickhouse/format_schemas/</format_schema_path>
<listen_host>0.0.0.0</listen_host>
<listen_try>1</listen_try>
<zookeeper>
<node index="1">
<host>host.docker.internal</host>
<port>2181</port>
</node>
</zookeeper>
</yandex>
Raw
c2.xml
<yandex>
<logger>
<level>debug</level>
<log>/var/log/clickhouse-server/clickhouse-server.log</log>
<errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
<size>1000M</size>
<count>10</count>
<console>1</console>
</logger>
<http_port>8124</http_port>
<tcp_port>9001</tcp_port>
<interserver_http_port>9010</interserver_http_port>
<interserver_http_host>host.docker.internal</interserver_http_host>
<max_connections>4096</max_connections>
<keep_alive_timeout>3</keep_alive_timeout>
<max_concurrent_queries>100</max_concurrent_queries>
<max_open_files>262144</max_open_files>
<uncompressed_cache_size>8589934592</uncompressed_cache_size>
<mark_cache_size>5368709120</mark_cache_size>
<path>/var/lib/clickhouse/</path>
<tmp_path>/var/lib/clickhouse/tmp/</tmp_path>
<user_files_path>/var/lib/clickhouse/user_files/</user_files_path>
<users_config>users.xml</users_config>
<default_profile>default</default_profile>
<default_database>default</default_database>
<remote_servers>
<level0>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>host.docker.internal</host>
<port>9000</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>host.docker.internal</host>
<port>9001</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>host.docker.internal</host>
<port>9002</port>
</replica>
</shard>
</level0>
<cross>
<shard>
<internal_replication>true</internal_replication>
<replica>
<default_database>db0</default_database>
<host>host.docker.internal</host>
<port>9000</port>
</replica>
<replica>
<default_database>db1</default_database>
<host>host.docker.internal</host>
<port>9001</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<default_database>db0</default_database>
<host>host.docker.internal</host>
<port>9001</port>
</replica>
<replica>
<default_database>db1</default_database>
<host>host.docker.internal</host>
<port>9002</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<default_database>db0</default_database>
<host>host.docker.internal</host>
<port>9002</port>
</replica>
<replica>
<default_database>db1</default_database>
<host>host.docker.internal</host>
<port>9000</port>
</replica>
</shard>
</cross>
</remote_servers>
<builtin_dictionaries_reload_interval>3600</builtin_dictionaries_reload_interval>
<max_session_timeout>3600</max_session_timeout>
<default_session_timeout>60</default_session_timeout>
<query_log>
<database>system</database>
<table>query_log</table>
<partition_by>toYYYYMM(event_date)</partition_by>
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>
<dictionaries_config>*_dictionary.xml</dictionaries_config>
<distributed_ddl>
<path>/clickhouse/task_queue/ddl</path>
</distributed_ddl>
<format_schema_path>/var/lib/clickhouse/format_schemas/</format_schema_path>
<listen_host>0.0.0.0</listen_host>
<listen_try>1</listen_try>
<zookeeper>
<node index="1">
<host>host.docker.internal</host>
<port>2181</port>
</node>
</zookeeper>
</yandex>
Raw
c3.xml
<yandex>
<logger>
<level>debug</level>
<log>/var/log/clickhouse-server/clickhouse-server.log</log>
<errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
<size>1000M</size>
<count>10</count>
<console>1</console>
</logger>
<http_port>8125</http_port>
<tcp_port>9002</tcp_port>
<interserver_http_port>9011</interserver_http_port>
<interserver_http_host>host.docker.internal</interserver_http_host>
<max_connections>4096</max_connections>
<keep_alive_timeout>3</keep_alive_timeout>
<max_concurrent_queries>100</max_concurrent_queries>
<max_open_files>262144</max_open_files>
<uncompressed_cache_size>8589934592</uncompressed_cache_size>
<mark_cache_size>5368709120</mark_cache_size>
<path>/var/lib/clickhouse/</path>
<tmp_path>/var/lib/clickhouse/tmp/</tmp_path>
<user_files_path>/var/lib/clickhouse/user_files/</user_files_path>
<users_config>users.xml</users_config>
<default_profile>default</default_profile>
<default_database>default</default_database>
<remote_servers>
<level0>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>host.docker.internal</host>
<port>9000</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>host.docker.internal</host>
<port>9001</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>host.docker.internal</host>
<port>9002</port>
</replica>
</shard>
</level0>
<cross>
<shard>
<internal_replication>true</internal_replication>
<replica>
<default_database>db0</default_database>
<host>host.docker.internal</host>
<port>9000</port>
</replica>
<replica>
<default_database>db1</default_database>
<host>host.docker.internal</host>
<port>9001</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<default_database>db0</default_database>
<host>host.docker.internal</host>
<port>9001</port>
</replica>
<replica>
<default_database>db1</default_database>
<host>host.docker.internal</host>
<port>9002</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<default_database>db0</default_database>
<host>host.docker.internal</host>
<port>9002</port>
</replica>
<replica>
<default_database>db1</default_database>
<host>host.docker.internal</host>
<port>9000</port>
</replica>
</shard>
</cross>
</remote_servers>
<builtin_dictionaries_reload_interval>3600</builtin_dictionaries_reload_interval>
<max_session_timeout>3600</max_session_timeout>
<default_session_timeout>60</default_session_timeout>
<query_log>
<database>system</database>
<table>query_log</table>
<partition_by>toYYYYMM(event_date)</partition_by>
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>
<dictionaries_config>*_dictionary.xml</dictionaries_config>
<distributed_ddl>
<path>/clickhouse/task_queue/ddl</path>
</distributed_ddl>
<format_schema_path>/var/lib/clickhouse/format_schemas/</format_schema_path>
<listen_host>0.0.0.0</listen_host>
<listen_try>1</listen_try>
<zookeeper>
<node index="1">
<host>host.docker.internal</host>
<port>2181</port>
</node>
</zookeeper>
</yandex>
Raw
how_to.md
  1. mkdir chtest
  2. save c{1,2,3}.xml into chtest/
  3. save docker_related_config.xml into chtest/
  4. ./start.sh
  5. ./tables.sh
  6. ./just_run.sh
  7. ./stop.sh
Raw
just_run.sh
curl localhost:8123 -d "insert into write.x values (1,1),(2,2),(3,3)"
curl localhost:8123 -d "insert into write.y values (1,10),(2,20),(3,30)"
curl localhost:8123/\?distributed_product_mode=local -d "select x,y as xy, yy from x all inner join (select x,y as yy from y) using x"
Raw
relevant_cluster_config.xml
<remote_servers>
<level0>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>host.docker.internal</host>
<port>9000</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>host.docker.internal</host>
<port>9001</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>host.docker.internal</host>
<port>9002</port>
</replica>
</shard>
</level0>
<cross>
<shard>
<internal_replication>true</internal_replication>
<replica>
<default_database>db0</default_database>
<host>host.docker.internal</host>
<port>9000</port>
</replica>
<replica>
<default_database>db1</default_database>
<host>host.docker.internal</host>
<port>9001</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<default_database>db0</default_database>
<host>host.docker.internal</host>
<port>9001</port>
</replica>
<replica>
<default_database>db1</default_database>
<host>host.docker.internal</host>
<port>9002</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<default_database>db0</default_database>
<host>host.docker.internal</host>
<port>9002</port>
</replica>
<replica>
<default_database>db1</default_database>
<host>host.docker.internal</host>
<port>9000</port>
</replica>
</shard>
</cross>
</remote_servers>
Raw
start.sh
#!/bin/bash
docker run -d --name zk -p2181:2181 zookeeper
sleep 10
docker run -d --name c1 --ulimit nofile=262144:262144 -p8123:8123 -p9000:9000 -p9009:9009 -v ~/chtest0/c1.xml:/etc/clickhouse-server/config.xml -v ~/chtest0/docker_related_config.xml:/etc/clickhouse-server/config.d/docker_related_config.xml yandex/clickhouse-server:18.12.14
docker run -d --name c2 --ulimit nofile=262144:262144 -p8124:8124 -p9001:9001 -p9010:9010 -v ~/chtest0/c2.xml:/etc/clickhouse-server/config.xml -v ~/chtest0/docker_related_config.xml:/etc/clickhouse-server/config.d/docker_related_config.xml yandex/clickhouse-server:18.12.14
docker run -d --name c3 --ulimit nofile=262144:262144 -p8125:8125 -p9002:9002 -p9011:9011 -v ~/chtest0/c3.xml:/etc/clickhouse-server/config.xml -v ~/chtest0/docker_related_config.xml:/etc/clickhouse-server/config.d/docker_related_config.xml yandex/clickhouse-server:18.12.14
Raw
stop.sh
#!/bin/bash
docker stop c1 c2 c3 zk
docker rm c1 c2 c3 zk
Raw
tables.sh
#!/bin/bash
curl localhost:8123 -d "create database db0"
curl localhost:8123 -d "create database db1"
curl localhost:8123 -d "create database write"
curl localhost:8124 -d "create database db0"
curl localhost:8124 -d "create database db1"
curl localhost:8124 -d "create database write"
curl localhost:8125 -d "create database db0"
curl localhost:8125 -d "create database db1"
curl localhost:8125 -d "create database write"
curl localhost:8123 -d "create table db0.x_(x UInt8, y UInt8) engine = ReplicatedMergeTree('/c/0/x', '0') partition by tuple() order by tuple()"
curl localhost:8124 -d "create table db1.x_(x UInt8, y UInt8) engine = ReplicatedMergeTree('/c/0/x', '1') partition by tuple() order by tuple()"
curl localhost:8124 -d "create table db0.x_(x UInt8, y UInt8) engine = ReplicatedMergeTree('/c/1/x', '0') partition by tuple() order by tuple()"
curl localhost:8125 -d "create table db1.x_(x UInt8, y UInt8) engine = ReplicatedMergeTree('/c/1/x', '1') partition by tuple() order by tuple()"
curl localhost:8125 -d "create table db0.x_(x UInt8, y UInt8) engine = ReplicatedMergeTree('/c/2/x', '0') partition by tuple() order by tuple()"
curl localhost:8123 -d "create table db1.x_(x UInt8, y UInt8) engine = ReplicatedMergeTree('/c/2/x', '1') partition by tuple() order by tuple()"
curl localhost:8123 -d "create table x(x UInt8, y UInt8) engine = Distributed(level0, db0, x_, x)"
curl localhost:8123 -d "create table write.x(x UInt8, y UInt8) engine = Distributed(cross, '', x_, x)"
curl localhost:8124 -d "create table x(x UInt8, y UInt8) engine = Distributed(level0, db0, x_, x)"
curl localhost:8124 -d "create table write.x(x UInt8, y UInt8) engine = Distributed(cross, '', x_, x)"
curl localhost:8125 -d "create table x(x UInt8, y UInt8) engine = Distributed(level0, db0, x_, x)"
curl localhost:8125 -d "create table write.x(x UInt8, y UInt8) engine = Distributed(cross, '', x_, x)"
curl localhost:8123 -d "create table db0.y_(x UInt8, y UInt8) engine = ReplicatedMergeTree('/c/0/y', '0') partition by tuple() order by tuple()"
curl localhost:8124 -d "create table db1.y_(x UInt8, y UInt8) engine = ReplicatedMergeTree('/c/0/y', '1') partition by tuple() order by tuple()"
curl localhost:8124 -d "create table db0.y_(x UInt8, y UInt8) engine = ReplicatedMergeTree('/c/1/y', '0') partition by tuple() order by tuple()"
curl localhost:8125 -d "create table db1.y_(x UInt8, y UInt8) engine = ReplicatedMergeTree('/c/1/y', '1') partition by tuple() order by tuple()"
curl localhost:8125 -d "create table db0.y_(x UInt8, y UInt8) engine = ReplicatedMergeTree('/c/2/y', '0') partition by tuple() order by tuple()"
curl localhost:8123 -d "create table db1.y_(x UInt8, y UInt8) engine = ReplicatedMergeTree('/c/2/y', '1') partition by tuple() order by tuple()"
curl localhost:8123 -d "create table y(x UInt8, y UInt8) engine = Distributed(level0, db0, y_, x)"
curl localhost:8123 -d "create table write.y(x UInt8, y UInt8) engine = Distributed(cross, '', y_, x)"
curl localhost:8124 -d "create table y(x UInt8, y UInt8) engine = Distributed(level0, db0, y_, x)"
curl localhost:8124 -d "create table write.y(x UInt8, y UInt8) engine = Distributed(cross, '', y_, x)"
curl localhost:8125 -d "create table y(x UInt8, y UInt8) engine = Distributed(level0, db0, y_, x)"
curl localhost:8125 -d "create table write.y(x UInt8, y UInt8) engine = Distributed(cross, '', y_, x)"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment