/etc/hosts
192.168.1.14 ch14
192.168.1.15 ch15
192.168.1.16 ch16
zoo.cfg
[root@ch14 ~]# cat /home/i2/zookeeper/conf/zoo.cfg
tickTime=2000
initLimit=10
syncLimit=5
dataDir= /home/i2/zookeeper/data/zookeeper
dataLogDir= /home/i2/zookeeper/log/zookeeper
clientPort=2182
autopurge.purgeInterval=0
zookeeper.sasl.client=false
globalOutstandingLimit=200
server.14=ch14:2888:3888
server.15=ch15:2888:3888
server.16=ch16:2888:3888
clickhouse-server/config.xml
[root@ch14 ~]# cat /etc/clickhouse-server/config.xml
<?xml version="1.0"?>
<yandex>
<!--日志-->
<logger>
<level>warning</level>
<log>/data/clickhouse/logs/clickhouse.log</log>
<errorlog>/data/clickhouse/logs/error.log</errorlog>
<size>500M</size>
<count>5</count>
</logger>
<!--本地节点信息-->
<http_port>8123</http_port>
<tcp_port>9000</tcp_port>
<interserver_http_port>9009</interserver_http_port>
<interserver_http_host>ch14</interserver_http_host>
<!--本机域名或IP-->
<!--本地配置-->
<listen_host>ch14</listen_host>
<max_connections>2048</max_connections>
<keep_alive_timeout>3</keep_alive_timeout>
<max_concurrent_queries>64</max_concurrent_queries>
<uncompressed_cache_size>4294967296</uncompressed_cache_size>
<mark_cache_size>5368709120</mark_cache_size>
<path>/data/clickhouse/ch14/</path>
<tmp_path>/data/clickhouse/ch14/tmp/</tmp_path>
<users_config>/data/clickhouse/ch14/users.xml</users_config>
<default_profile>default</default_profile>
<query_log>
<database>system</database>
<table>query_log</table>
<partition_by>toMonday(event_date)</partition_by>
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>
<query_thread_log>
<database>system</database>
<table>query_thread_log</table>
<partition_by>toMonday(event_date)</partition_by>
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_thread_log>
<prometheus>
<endpoint>/metrics</endpoint>
<port>8001</port>
<metrics>true</metrics>
<events>true</events>
<asynchronous_metrics>true</asynchronous_metrics>
</prometheus>
<default_database>default</default_database>
<timezone>Asia/Shanghai</timezone>
<!--集群相关配置-->
<remote_servers incl="clickhouse_remote_servers" />
<zookeeper incl="zookeeper-servers" optional="true" />
<macros incl="macros" optional="true" />
<builtin_dictionaries_reload_interval>3600</builtin_dictionaries_reload_interval>
<max_session_timeout>3600</max_session_timeout>
<default_session_timeout>300</default_session_timeout>
<max_table_size_to_drop>0</max_table_size_to_drop>
<merge_tree>
<parts_to_delay_insert>300</parts_to_delay_insert>
<parts_to_throw_insert>600</parts_to_throw_insert>
<max_delay_to_insert>2</max_delay_to_insert>
</merge_tree>
<max_table_size_to_drop>0</max_table_size_to_drop>
<max_partition_size_to_drop>0</max_partition_size_to_drop>
<distributed_ddl>
<!-- Path in ZooKeeper to queue with DDL queries -->
<path>/clickhouse/task_queue/ddl</path>
</distributed_ddl>
<include_from>/data/clickhouse/ch14/metrika.xml</include_from>
</yandex>
metrika.xml
[root@ch14 ~]# cat /data/clickhouse/ch14/metrika.xml
<?xml version="1.0"?>
<yandex>
<clickhouse_remote_servers>
<perftest_3shards_1replicas>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>ch14</host>
<port>9000</port>
</replica>
</shard>
<shard>
<replica>
<internal_replication>true</internal_replication>
<host>ch15</host>
<port>9000</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>ch16</host>
<port>9000</port>
</replica>
</shard>
</perftest_3shards_1replicas>
<test_cluster_one_shards_three_replication>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>ch14</host>
<port>9000</port>
</replica>
<replica>
<host>ch15</host>
<port>9000</port>
</replica>
<replica>
<host>ch16</host>
<port>9000</port>
</replica>
</shard>
</test_cluster_one_shards_three_replication>
</clickhouse_remote_servers>
<!--zookeeper相关配置-->
<zookeeper-servers>
<node index="14">
<host>ch14</host>
<port>2182</port>
</node>
<node index="15">
<host>ch15</host>
<port>2182</port>
</node>
<node index="16">
<host>ch16</host>
<port>2182</port>
</node>
</zookeeper-servers>
<macros>
<replica>ch14</replica>
</macros>
<networks>
<ip>::/0</ip>
</networks>
<clickhouse_compression>
<case>
<min_part_size>10000000000</min_part_size>
<min_part_size_ratio>0.01</min_part_size_ratio>
<method>lz4</method>
</case>
</clickhouse_compression>
</yandex>
创建数据库
create database testdb on cluster perftest_3shards_1replicas
创建本地表
clickhouse-client -u default --port 9000 -hch16 --query="CREATE TABLE testdb.sbtest_local(EventDate DateTime,CounterID UInt32,UserID UInt32) ENGINE = MergeTree PARTITION BY toYYYYMM(EventDate)ORDER BY (CounterID, EventDate, intHash32(UserID))SETTINGS index_granularity = 8192;"
创建分区表
clickhouse-client -u default --port 9000 -hch14 --query="CREATE TABLE testdb.sbtest (EventDate DateTime,CounterID UInt32,UserID UInt32) ENGINE = Distributed(perftest_3shards_1replicas,testdb, sbtest_local, rand())"
insert.sh
[root@ch14 home]# cat insert.sh
#!/bin/bash
for((i=1;i<=100000000000;i++));
do
sleep 0.5
clickhouse-client -u default --port 9000 -hch14 --query="insert into testdb.sbtest_local VALUES (now(), 30000, 30000)"
clickhouse-client -u default --port 9000 -hch15 --query="insert into testdb.sbtest_local VALUES (now(), 30000, 30000)"
clickhouse-client -u default --port 9000 -hch16 --query="insert into testdb.sbtest_local VALUES (now(), 30000, 30000)"
done
统计分区表
clickhouse-client -u default --port 9000 -hch15 --query="select count(*) from testdb.sbtest"