ClickHouse集群探究 3shard_2replicas

1、准备三台物理主机分别命名为CentOSA/CentOSB/CentOSC
2、规划物理主机的集群规模和配置图

3、按照单机模式分别在CentOSA、CentOSB、CentOSC上安装ClickHouse的服务
4、安装完成后，所有节点拷贝/etc/init.d/clickhouse-server脚本在/etc/init.d/clickhouse-server-1
5、修改clickhouse-server-1脚本

#!/bin/sh
### BEGIN INIT INFO
# Provides:          clickhouse-server
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Required-Start:    $network
# Required-Stop:     $network
# Short-Description: Yandex clickhouse-server daemon
### END INIT INFO

CLICKHOUSE_USER=clickhouse
CLICKHOUSE_GROUP=${CLICKHOUSE_USER}
SHELL=/bin/bash
PROGRAM=clickhouse-server
CLICKHOUSE_GENERIC_PROGRAM=clickhouse
CLICKHOUSE_PROGRAM_ENV=""
EXTRACT_FROM_CONFIG=${CLICKHOUSE_GENERIC_PROGRAM}-extract-from-config
CLICKHOUSE_CONFDIR=/etc/$PROGRAM
CLICKHOUSE_LOGDIR=/var/log/clickhouse-server-1
CLICKHOUSE_LOGDIR_USER=root
CLICKHOUSE_DATADIR_OLD=/opt/clickhouse-1
CLICKHOUSE_DATADIR=/var/lib/clickhouse-1
if [ -d "/var/lock" ]; then
    LOCALSTATEDIR=/var/lock
else
    LOCALSTATEDIR=/run/lock
fi

if [ ! -d "$LOCALSTATEDIR" ]; then
    mkdir -p "$LOCALSTATEDIR"
fi

CLICKHOUSE_BINDIR=/usr/bin
CLICKHOUSE_CRONFILE=/etc/cron.d/clickhouse-server
CLICKHOUSE_CONFIG=$CLICKHOUSE_CONFDIR/config-1.xml
LOCKFILE=$LOCALSTATEDIR/$PROGRAM
RETVAL=0
CLICKHOUSE_PIDDIR=/var/run/$PROGRAM
CLICKHOUSE_PIDFILE="$CLICKHOUSE_PIDDIR/$PROGRAM-1.pid"
# CLICKHOUSE_STOP_TIMEOUT=60 # Disabled by default. Place to /etc/default/clickhouse if you need.

# Some systems lack "flock"
command -v flock >/dev/null && FLOCK=flock

# Override defaults from optional config file
test -f /etc/default/clickhouse && . /etc/default/clickhouse

# On x86_64, check for required instruction set.
if uname -mpi | grep -q 'x86_64'; then
    if ! grep -q 'sse4_2' /proc/cpuinfo; then
        # On KVM, cpuinfo could falsely not report SSE 4.2 support, so skip the check.
        if ! grep -q 'Common KVM processor' /proc/cpuinfo; then

            # Some other VMs also report wrong flags in cpuinfo.
            # Tricky way to test for instruction set:
            #  create temporary binary and run it;
            #  if it get caught illegal instruction signal,
            #  then required instruction set is not supported really.
            #
            # Generated this way:
            # gcc -xc -Os -static -nostdlib - <<< 'void _start() { __asm__("pcmpgtq %%xmm0, %%xmm1; mov $0x3c, %%rax; xor %%rdi, %%rdi; syscall":::"memory"); }' && strip -R .note.gnu.build-id -R .comment -R .eh_frame -s ./a.out && gzip -c -9 ./a.out | base64 -w0; echo

            if ! (echo -n 'H4sICAwAW1cCA2Eub3V0AKt39XFjYmRkgAEmBjsGEI+H0QHMd4CKGyCUAMUsGJiBJDNQNUiYlQEZOKDQclB9cnD9CmCSBYqJBRxQOvBpSQobGfqIAWn8FuYnPI4fsAGyPQz/87MeZtArziguKSpJTGLQK0mtKGGgGHADMSgoYH6AhTMPNHyE0NQzYuEzYzEXFr6CBPQDANAsXKTwAQAA' | base64 -d | gzip -d > /tmp/clickhouse_test_sse42 && chmod a+x /tmp/clickhouse_test_sse42 && /tmp/clickhouse_test_sse42); then
                echo 'Warning! SSE 4.2 instruction set is not supported'
                #exit 3
            fi
        fi
    fi
fi


SUPPORTED_COMMANDS="{start|stop|status|restart|forcestop|forcerestart|reload|condstart|condstop|condrestart|condreload|initdb}"
is_supported_command()
{
   
    echo "$SUPPORTED_COMMANDS" | grep -E "(\{|\|)$1(\||})" &> /dev/null
}


is_running()
{
   
    [ -r "$CLICKHOUSE_PIDFILE" ] && pgrep -s $(cat "$CLICKHOUSE_PIDFILE") 1> /dev/null 2> /dev/null
}


wait_for_done()
{
   
    timeout=$1
    attempts=0
    while is_running; do
        attempts=$(($attempts + 1))
        if [ -n "$timeout" ] && [ $attempts -gt $timeout ]; then
            return 1
        fi
        sleep 1
    done
}


die()
{
   
    echo $1 >&2
    exit 1
}


# Check that configuration file is Ok.
check_config()
{
   
    if [ -x "$CLICKHOUSE_BINDIR/$EXTRACT_FROM_CONFIG" ]; then
        su -s $SHELL ${CLICKHOUSE_USER} -c "$CLICKHOUSE_BINDIR/$EXTRACT_FROM_CONFIG --config-file=\"$CLICKHOUSE_CONFIG\" --key=path" >/dev/null || die "Configuration file ${CLICKHOUSE_CONFIG} doesn't parse successfully. Won't restart server. You may use forcerestart if you are sure.";
    fi
}


initdb()
{
   
    if [ -x "$CLICKHOUSE_BINDIR/$EXTRACT_FROM_CONFIG" ]; then
        CLICKHOUSE_DATADIR_FROM_CONFIG=$(su -s $SHELL ${
    CLICKHOUSE_USER} -c "$CLICKHOUSE_BINDIR/$EXTRACT_FROM_CONFIG --config-file=\"$CLICKHOUSE_CONFIG\" --key=path")
        if [ "(" "$?" -ne "0" ")" -o "(" -z "${CLICKHOUSE_DATADIR_FROM_CONFIG}" ")" ]; then
            die "Cannot obtain value of path from config file: ${CLICKHOUSE_CONFIG}";
        fi
        echo "Path to data directory in ${CLICKHOUSE_CONFIG}: ${CLICKHOUSE_DATADIR_FROM_CONFIG}"
    else
        CLICKHOUSE_DATADIR_FROM_CONFIG=$CLICKHOUSE_DATADIR
    fi

    if ! getent group ${CLICKHOUSE_USER} >/dev/null; then
        echo "Can't chown to non-existing user ${CLICKHOUSE_USER}"
        return
    fi
    if ! getent passwd ${CLICKHOUSE_GROUP} >/dev/null; then
        echo "Can't chown to non-existing group ${CLICKHOUSE_GROUP}"
        return
    fi

    if ! $(su -s $SHELL ${
    CLICKHOUSE_USER} -c "test -r ${CLICKHOUSE_CONFIG}"); then
        echo "Warning! clickhouse config [${CLICKHOUSE_CONFIG}] not readable by user [${CLICKHOUSE_USER}]"
    fi

    if ! $(su -s $SHELL ${
    CLICKHOUSE_USER} -c "test -O \"${CLICKHOUSE_DATADIR_FROM_CONFIG}\" && test -G \"${CLICKHOUSE_DATADIR_FROM_CONFIG}\""); then
        if [ $(dirname "${CLICKHOUSE_DATADIR_FROM_CONFIG}") = "/" ]; then
            echo "Directory ${CLICKHOUSE_DATADIR_FROM_CONFIG} seems too dangerous to chown."
        else
            if [ ! -e "${CLICKHOUSE_DATADIR_FROM_CONFIG}" ]; then
                echo "Creating directory ${CLICKHOUSE_DATADIR_FROM_CONFIG}"
                mkdir -p "${CLICKHOUSE_DATADIR_FROM_CONFIG}"
            fi

            echo "Changing owner of [${CLICKHOUSE_DATADIR_FROM_CONFIG}] to [${CLICKHOUSE_USER}:${CLICKHOUSE_GROUP}]"
            chown -R ${CLICKHOUSE_USER}:${CLICKHOUSE_GROUP} "${CLICKHOUSE_DATADIR_FROM_CONFIG}"
        fi
    fi

    if ! $(su -s $SHELL ${
    CLICKHOUSE_USER} -c "test -w ${CLICKHOUSE_LOGDIR}"); then
        echo "Changing owner of [${CLICKHOUSE_LOGDIR}/*] to [${CLICKHOUSE_USER}:${CLICKHOUSE_GROUP}]"
        chown -R ${CLICKHOUSE_USER}:${CLICKHOUSE_GROUP} ${CLICKHOUSE_LOGDIR}/*
        echo "Changing owner of [${CLICKHOUSE_LOGDIR}] to [${CLICKHOUSE_LOGDIR_USER}:${CLICKHOUSE_GROUP}]"
        chown ${CLICKHOUSE_LOGDIR_USER}:${CLICKHOUSE_GROUP} ${CLICKHOUSE_LOGDIR}
    fi
}


start()
{
   
    [ -x $CLICKHOUSE_BINDIR/$PROGRAM ] || exit 0
    local EXIT_STATUS
    EXIT_STATUS=0

    echo -n "Start $PROGRAM service: "

    if is_running; then
        echo -n "already running "
        EXIT_STATUS=1
    else
        ulimit -n 262144
        mkdir -p $CLICKHOUSE_PIDDIR
        chown -R $CLICKHOUSE_USER:$CLICKHOUSE_GROUP $CLICKHOUSE_PIDDIR
        initdb
        if ! is_running; then
            # Lock should not be held while running child process, so we release the lock. Note: obviously, there is race condition.
            # But clickhouse-server has protection from simultaneous runs with same data directory.
            su -s $SHELL ${CLICKHOUSE_USER} -c "$FLOCK -u 9; $CLICKHOUSE_PROGRAM_ENV exec -a \"$PROGRAM\" \"$CLICKHOUSE_BINDIR/$PROGRAM\" --daemon --pid-file=\"$CLICKHOUSE_PIDFILE\" --config-file=\"$CLICKHOUSE_CONFIG\""
            EXIT_STATUS=$?
            if [ $EXIT_STATUS -ne 0 ]; then
                break
            fi
        fi
    fi

    if [ $EXIT_STATUS -eq 0 ]; then
        attempts=0
        while ! is_running && [ $attempts -le ${CLICKHOUSE_START_TIMEOUT:=10} ]; do
            attempts=$(($attempts + 1))
            sleep 1
        done
        if is_running; then
            echo "DONE"
        else
            echo "UNKNOWN"
        fi
    else
        echo "FAILED"
    fi

    return $EXIT_STATUS
}


stop()
{
   
    #local EXIT_STATUS
    EXIT_STATUS=0

    if [ -f $CLICKHOUSE_PIDFILE ]; then

        echo -n "Stop $PROGRAM service: "

        kill -TERM $(cat "$CLICKHOUSE_PIDFILE")

        if ! wait_for_done ${CLICKHOUSE_STOP_TIMEOUT}; then
            EXIT_STATUS=2
            echo "TIMEOUT"
        else
            echo "DONE"
        fi

    fi
    return $EXIT_STATUS
}


restart()
{
   
    check_config
    if stop; then
        if start; then
            return 0
        fi
    fi
    return 1
}


forcestop()
{
   
    local EXIT_STATUS
    EXIT_STATUS=0

    echo -n "Stop forcefully $PROGRAM service: "

    kill -KILL $(cat "$CLICKHOUSE_PIDFILE")

    wait_for_done

    echo "DONE"
    return $EXIT_STATUS
}


service_or_func()
{
   
    if [ -x "/bin/systemctl" ] && [ -f /etc/systemd/system/clickhouse-server.service ] && [ -d /run/systemd/system ]; then
        service $PROGRAM $1
    else
        $1
    fi
}

forcerestart()
{
   
    forcestop
    # Should not use 'start' function if systemd active
    service_or_func start
}

use_cron()
{
   
    # 1. running systemd
    if [ -x "/bin/systemctl" ] && [ -f /etc/systemd/system/clickhouse-server.service ] && [ -d /run/systemd/system ]; then
        return 1
    fi
    # 2. disabled by config
    if [ -z "$CLICKHOUSE_CRONFILE" ]; then
        return 2
    fi
    return 0
}

enable_cron()
{
   
    use_cron && sed -i 's/^#*//' "$CLICKHOUSE_CRONFILE"
}


disable_cron()
{
   
    use_cron && sed -i 's/^#*/#/' "$CLICKHOUSE_CRONFILE"
}


is_cron_disabled()
{
   
    use_cron || return 0

    # Assumes that either no lines are commented or all lines are commented.
    # Also please note, that currently cron file for ClickHouse has only one line (but some time ago there was more).
    grep -q -E '^#' "$CLICKHOUSE_CRONFILE";
}


main()
{
   
    # See how we were called.
    EXIT_STATUS=0
    case "$1" in
    start)
        start && enable_cron
        ;;
    stop)
        # disable_cron returns false if cron disabled (with systemd) - not checking return status
        disable_cron
        stop
        ;;
    restart)
        restart && enable_cron
        ;;
    forcestop)
        disable_cron
        forcestop
        ;;
    forcerestart)
        forcerestart && enable_cron
        ;;
    reload)
        restart
        ;;
    condstart)
        is_running || service_or_func start
        ;;
    condstop)
        is_running && service_or_func stop
        ;;
    condrestart)
        is_running && service_or_func restart
        ;;
    condreload)
        is_running && service_or_func restart
        ;;
    initdb)
        initdb
        ;;
    enable_cron)
        enable_cron
        ;;
    disable_cron)
        disable_cron
        ;;
    *)
        echo "Usage: $0 $SUPPORTED_COMMANDS"
        exit 2
        ;;
    esac

    exit $EXIT_STATUS
}


status()
{
   
    if is_running; then
        echo "$PROGRAM service is running"
        exit 0
    else
        if is_cron_disabled; then
            echo "$PROGRAM service is stopped";
        else
            echo "$PROGRAM: process unexpectedly terminated"
        fi
        exit 3
    fi
}


# Running commands without need of locking
case "$1" in
status)
    status
    ;;
esac


(
    if $FLOCK -n 9; then
        main "$@"
    else
        echo "Init script is already running" && exit 1
    fi
) 9> $LOCKFILE

用户可以使用diff命令查看修改了那些配置

[root@CentOSX ~]# diff /etc/init.d/clickhouse-server /etc/init.d/clickhouse-server-1
19c19
< CLICKHOUSE_LOGDIR=/var/log/clickhouse-server
---
> CLICKHOUSE_LOGDIR=/var/log/clickhouse-server-1
21,22c21,22
< CLICKHOUSE_DATADIR_OLD=/opt/clickhouse
< CLICKHOUSE_DATADIR=/var/lib/clickhouse
---
> CLICKHOUSE_DATADIR_OLD=/opt/clickhouse-1
> CLICKHOUSE_DATADIR=/var/lib/clickhouse-1
35c35
< CLICKHOUSE_CONFIG=$CLICKHOUSE_CONFDIR/config.xml
---
> CLICKHOUSE_CONFIG=$CLICKHOUSE_CONFDIR/config-1.xml
39c39
< CLICKHOUSE_PIDFILE="$CLICKHOUSE_PIDDIR/$PROGRAM.pid"
---
> CLICKHOUSE_PIDFILE="$CLICKHOUSE_PIDDIR/$PROGRAM-1.pid"

6、三台机器都需要拷贝/etc/clickhouse-server/config.xml复制生成/etc/clickhouse-server/config-1.xml文件

• CentOSA-config.xml

<?xml version="1.0"?>
<!--
 NOTE: User and query level settings are set up in "users.xml" file.
-->
<yandex>
   <!-- The list of hosts allowed to use in URL-related storage engines and table functions.
   	If this section is not present in configuration, all hosts are allowed.
   -->
   <remote_url_allow_hosts>
   	<!-- Host should be specified exactly as in URL. The name is checked before DNS resolution.
   		Example: "yandex.ru", "yandex.ru." and "www.yandex.ru" are different hosts.
           		If port is explicitly specified in URL, the host:port is checked as a whole.
           		If host specified here without port, any port with this host allowed.
           		"yandex.ru" -> "yandex.ru:443", "yandex.ru:80" etc. is allowed, but "yandex.ru:80" -> only "yandex.ru:80" is allowed. 
   		If the host is specified as IP address, it is checked as specified in URL. Example: "[2a02:6b8:a::a]".
   		If there are redirects and support for redirects is enabled, every redirect (the Location field) is checked. 
   	-->

   	<!-- Regular expression can be specified. RE2 engine is used for regexps.
   		Regexps are not aligned: don't forget to add ^ and $. Also don't forget to escape dot (.) metacharacter
   		(forgetting to do so is a common source of error).
   	-->
   </remote_url_allow_hosts>

   <logger>
       <!-- Possible levels: https://github.com/pocoproject/poco/blob/develop/Foundation/include/Poco/Logger.h#L105 -->
       <level>trace</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> --> <!-- Default behavior is autodetection (log to console if not daemon mode and is tty) -->
   </logger>
   <!--display_name>production</display_name--> <!-- It is the name that will be shown in the client -->
   <http_port>8123</http_port>
   <tcp_port>9000</tcp_port>
   <!-- For HTTPS and SSL over native protocol. -->
   <!--
   <https_port>8443</https_port>
   <tcp_port_secure>9440</tcp_port_secure>
   -->

   <!-- Used with https_port and tcp_port_secure. Full ssl options list: https://github.com/ClickHouse-Extras/poco/blob/master/NetSSL_OpenSSL/include/Poco/Net/SSLManager.h#L71 -->
   <openSSL>
       <server> <!-- Used for https server AND secure tcp port -->
           <!-- openssl req -subj "/CN=localhost" -new -newkey rsa:2048 -days 365 -nodes -x509 -keyout /etc/clickhouse-server/server.key -out /etc/clickhouse-server/server.crt -->
           <certificateFile>/etc/clickhouse-server/server.crt</certificateFile>
           <privateKeyFile>/etc/clickhouse-server/server.key</privateKeyFile>
           <!-- openssl dhparam -out /etc/clickhouse-server/dhparam.pem 4096 -->
           <dhParamsFile>/etc/clickhouse-server/dhparam.pem</dhParamsFile>
           <verificationMode>none</verificationMode>
           <loadDefaultCAFile>true</loadDefaultCAFile>
           <cacheSessions>true</cacheSessions>
           <disableProtocols>sslv2,sslv3</disableProtocols>
           <preferServerCiphers>true</preferServerCiphers>
       </server>

       <client> <!-- Used for connecting to https dictionary source -->
           <loadDefaultCAFile>true</loadDefaultCAFile>
           <cacheSessions>true</cacheSessions>
           <disableProtocols>sslv2,sslv3</disableProtocols>
           <preferServerCiphers>true</preferServerCiphers>
           <!-- Use for self-signed: <verificationMode>none</verificationMode> -->
           <invalidCertificateHandler>
               <!-- Use for self-signed: <name>AcceptCertificateHandler</name> -->
               <name>RejectCertificateHandler</name>
           </invalidCertificateHandler>
       </client>
   </openSSL>

   <!-- Default root page on http[s] server. For example load UI from https://tabix.io/ when opening http://localhost:8123 -->
   <!--
   <http_server_default_response><![CDATA[<html ng-app="SMI2"><head><base href="http://ui.tabix.io/"></head><body><div ui-view="" class="content-ui"></div><script src="http://loader.tabix.io/master.js"></script></body></html>]]></http_server_default_response>
   -->

   <!-- Port for communication between replicas. Used for data exchange. -->
   <interserver_http_port>9009</interserver_http_port>

   <!-- Hostname that is used by other replicas to request this server.
        If not specified, than it is determined analoguous to 'hostname -f' command.
        This setting could be used to switch replication to another network interface.
     -->
   <!--
   <interserver_http_host>example.yandex.ru</interserver_http_host>
   -->

   <!-- Listen specified host. use :: (wildcard IPv6 address), if you want to accept connections both with IPv4 and IPv6 from everywhere. -->
   <!-- <listen_host>::</listen_host> -->
   <!-- Same for hosts with disabled ipv6: -->
   <!-- <listen_host>0.0.0.0</listen_host> -->

   <!-- Default values - try listen localhost on ipv4 and ipv6: -->
   <!--
   <listen_host>::1</listen_host>
   <listen_host>127.0.0.1</listen_host>
   -->
   <listen_host>CentOSA</listen_host>
   <!-- Don't exit if ipv6 or ipv4 unavailable, but listen_host with this protocol specified -->
   <!-- <listen_try>0</listen_try> -->

   <!-- Allow listen on same address:port -->
   <!-- <listen_reuse_port>0</listen_reuse_port> -->

   <!-- <listen_backlog>64</listen_backlog> -->

   <max_connections>4096</max_connections>
   <keep_alive_timeout>3</keep_alive_timeout>

   <!-- Maximum number of concurrent queries. -->
   <max_concurrent_queries>100</max_concurrent_queries>

   <!-- Set limit on number of open files (default: maximum). This setting makes sense on Mac OS X because getrlimit() fails to retrieve
        correct maximum value. -->
   <!-- <max_open_files>262144</max_open_files> -->

   <!-- Size of cache of uncompressed blocks of data, used in tables of MergeTree family.
        In bytes. Cache is single for server. Memory is allocated only on demand.
        Cache is used when 'use_uncompressed_cache' user setting turned on (off by default).
        Uncompressed cache is advantageous only for very short queries and in rare cases.
     -->
   <uncompressed_cache_size>8589934592</uncompressed_cache_size>

   <!-- Approximate size of mark cache, used in tables of MergeTree family.
        In bytes. Cache is single for server. Memory is allocated only on demand.
        You should not lower this value.
     -->
   <mark_cache_size>5368709120</mark_cache_size>


   <!-- Path to data directory, with trailing slash. -->
   <path>/var/lib/clickhouse/</path>

   <!-- Path to temporary data for processing hard queries. -->
   <tmp_path>/var/lib/clickhouse/tmp/</tmp_path>

   <!-- Directory with user provided files that are accessible by 'file' table function. -->
   <user_files_path>/var/lib/clickhouse/user_files/</user_files_path>

   <!-- Path to configuration file with users, access rights, profiles of settings, quotas. -->
   <users_config>users.xml</users_config>

   <!-- Default profile of settings. -->
   <default_profile>default</default_profile>

   <!-- System profile of settings. This settings are used by internal processes (Buffer storage, Distibuted DDL worker and so on). -->
   <!-- <system_profile>default</system_profile> -->

   <!-- Default database. -->
   <default_database>default</default_database>

   <!-- Server time zone could be set here.

        Time zone is used when converting between String and DateTime types,
         when printing DateTime in text formats and parsing DateTime from text,
         it is used in date and time related functions, if specific time zone was not passed as an argument.

        Time zone is specified as identifier from IANA time zone database, like UTC or Africa/Abidjan.
        If not specified, system time zone at server startup is used.

        Please note, that server could display time zone alias instead of specified name.
        Example: W-SU is an alias for Europe/Moscow and Zulu is an alias for UTC.
   -->
   <!-- <timezone>Europe/Moscow</timezone> -->

   <!-- You can specify umask here (see "man umask"). Server will apply it on startup.
        Number is always parsed as octal. Default umask is 027 (other users cannot read logs, data files, etc; group can only read).
   -->
   <!-- <umask>022</umask> -->

   <!-- Perform mlockall after startup to lower first queries latency
         and to prevent clickhouse executable from being paged out under high IO load.
        Enabling this option is recommended but will lead to increased startup time for up to a few seconds.
   -->
   <mlock_executable>false</mlock_executable>

   <!-- Configuration of clusters that could be used in Distributed tables.
        https://clickhouse.yandex/docs/en/table_engines/distributed/
     -->
   <remote_servers incl="clickhouse_remote_servers" >
     	<ch_3shards_2replicas>
   		<shard>
   		    <weight>1</weight>
   		    <internal_replication>true</internal_replication>
   		    <replica>
   		       <host>CentOSA</host>
   		       <port>9000</port>
   		    </replica>
   		    <replica>
   		       <host>CentOSB</host>
   		       <port>9001</port>
   		    </replica>
   		</shard>
   		<shard>
   		    <weight>1</weight>
   		    <internal_replication>true</internal_replication>
   		     <replica>
   		       <host>CentOSB</host>
   		       <port>9000</port>
   		    </replica>
   		    <replica>
   		       <host>CentOSC</host>
   		       <port>9001</port>
   		    </replica>
   		</shard>
   		<shard>
   		    <weight>1</weight>
   		    <internal_replication>true</internal_replication>
   		     <replica>
   		       <host>CentOSA</host>
   		       <port>9001</port>
   		    </replica>
   		    <replica>
   		       <host>CentOSC</host>
   		       <port>9000</port>
   		    </replica>
   		</shard>
   	</ch_3shards_2replicas>
   </remote_servers>


   <!-- If element has 'incl' attribute, then for it's value will be used corresponding substitution from another file.
        By default, path to file with substitutions is /etc/metrika.xml. It could be changed in config in 'include_from' element.
        Values for substitutions are specified in /yandex/name_of_substitution elements in that file.
     -->

   <!-- ZooKeeper is used to store metadata about replicas, when using Replicated tables.
        Optional. If you don't use replicated tables, you could omit that.

        See https://clickhouse.yandex/docs/en/table_engines/replication/
     -->

   <zookeeper >
   	<node index="1">
   		<host>CentOSA</host>
   		<port>2181</port>
   	</node>
       <node index="2">
               <host>CentOSB</host>
               <port>2181</port>
       </node>
       <node index="3">
               <host>CentOSC</host>
               <port>2181</port>
       </node>
   </zookeeper>

   <!-- Substitutions for parameters of replicated tables.
         Optional. If you don't use replicated tables, you could omit that.

        See https://clickhouse.yandex/docs/en/table_engines/replication/#creating-replicated-tables
     -->
   <macros >
   	<layer>01</layer>
       <shard>01</shard>
       <replica>CentOSA</replica>
   </macros>


   <!-- Reloading interval for embedded dictionaries, in seconds. Default: 3600. -->
   <builtin_dictionaries_reload_interval>3600</builtin_dictionaries_reload_interval>


   <!-- Maximum session timeout, in seconds. Default: 3600. -->
   <max_session_timeout>3600</max_session_timeout>

   <!-- Default session timeout, in seconds. Default: 60. -->
   <default_session_timeout>60</default_session_timeout>

   <!-- Sending data to Graphite for monitoring. Several sections can be defined. -->
   <!--
       interval - send every X second
       root_path - prefix for keys
       hostname_in_path - append hostname to root_path (default = true)
       metrics - send data from table system.metrics
       events - send data from table system.events
       asynchronous_metrics - send data from table system.asynchronous_metrics
   -->
   <!--
   <graphite>
       <host>localhost</host>
       <port>42000</port>
       <timeout>0.1</timeout>
       <interval>60</interval>
       <root_path>one_min</root_path>
       <hostname_in_path>true</hostname_in_path>

       <metrics>true</metrics>
       <events>true</events>
       <events_cumulative>false</events_cumulative>
       <asynchronous_metrics>true</asynchronous_metrics>
   </graphite>
   <graphite>
       <host>localhost</host>
       <port>42000</port>
       <timeout>0.1</timeout>
       <interval>1</interval>
       <root_path>one_sec</root_path>

       <metrics>true</metrics>
       <events>true</events>
       <events_cumulative>false</events_cumulative>
       <asynchronous_metrics>false</asynchronous_metrics>
   </graphite>
   -->

   <!-- Serve endpoint fot Prometheus monitoring. -->
   <!--
       endpoint - mertics path (relative to root, statring with "/")
       port - port to setup server. If not defined or 0 than http_port used
       metrics - send data from table system.metrics
       events - send data from table system.events
       asynchronous_metrics - send data from table system.asynchronous_metrics
   -->
   <!--
   <prometheus>
       <endpoint>/metrics</endpoint>
       <port>9363</port>

       <metrics>true</metrics>
       <events>true</events>
       <asynchronous_metrics>true</asynchronous_metrics>
   </prometheus>
   -->

   <!-- Query log. Used only for queries with setting log_queries = 1. -->
   <query_log>
       <!-- What table to insert data. If table is not exist, it will be created.
            When query log structure is changed after system update,
             then old table will be renamed and new table will be created automatically.
       -->
       <database>system</database>
       <table>query_log</table>
       <!--
           PARTITION BY expr https://clickhouse.yandex/docs/en/table_engines/custom_partitioning_key/
           Example:
               event_date
               t