ClickHouse 资源隔离

<yandex>
    <profiles>
        <profile1></profile1>
        <profile2></profile2>
    </profiles>
    <quotas>
        <quotas1></quotas1>
        <quotas2></quotas2>
    </quotas>
    <users>
        <username1>
            <profile>profile1</profile>
            <quotas>quotas2</quotas>
        </username1>
        <username2></username2>
    </users>
</yandex>

- min：最小值约束，在设置相应参数的时候，取值不能小于该阈值；
- max：最大值约束，在设置相应参数的时候，取值不能大于该阈值；
- readonly：只读约束，该参数值不允许被修改。

1：max_memory_usage：在单个ClickHouse服务进程中，运行一次查询限制使用的最大内存用量，默认值为10G；
2：max_memory_usage_for_user：在单个ClickHouse服务进程中，以用户为单位进行统计，单个用户在运行查询时，限制使用的最大内存用量，默认值为0，即不做限制；
3：max_memory_usage_for_all_queries：在单个ClickHouse服务进程中，所有运行的查询累加在一起，限制使用的最大内存用量，默认为0不做限制；
4：max_partitions_per_insert_block：在单次INSERT写入的时候，限制创建的最大分区个数，默认值为100个。如果超出这个阈值数目，将会得到异常；
5:max_rows_to_group_by：在执行GROUP BY聚合查询的时候，限制去重后的聚合KEY的最大个数，默认值为0，即不做限制。当超过阈值数量的时候，其处理方式由group_by_overflow_mode参数决定；
6:group_by_overflow_mode：当max_rows_to_group_by熔断规则触发的时候，有三种处理形式: 
throw抛出异常，此乃默认值；
break立即停止查询，并返回当前部分的数据；
any仅以当前已存在的聚合KEY，继续完成聚合查询；
7:max_bytes_before_external_group_by：在执行GROUP BY聚合查询的时候，限制使用的最大内存用量，默认值为0，即不做限制。当超过阈值数量的时候，聚合查询将会进一步借用本地磁盘。

<!-- Settings profiles -->
<profiles>
    <!-- Default settings -->
    <default>
        <!-- The maximum number of threads when running a single query. -->
        <max_threads>8</max_threads>
    </default>

    <!-- Settings for quries from the user interface -->
    <web>
        <max_rows_to_read>1000000000</max_rows_to_read>
        <max_bytes_to_read>100000000000</max_bytes_to_read>

        <max_rows_to_group_by>1000000</max_rows_to_group_by>
        <group_by_overflow_mode>any</group_by_overflow_mode>

        <max_rows_to_sort>1000000</max_rows_to_sort>
        <max_bytes_to_sort>1000000000</max_bytes_to_sort>

        <max_result_rows>100000</max_result_rows>
        <max_result_bytes>100000000</max_result_bytes>
        <result_overflow_mode>break</result_overflow_mode>

        <max_execution_time>600</max_execution_time>
        <min_execution_speed>1000000</min_execution_speed>
        <timeout_before_checking_execution_speed>15</timeout_before_checking_execution_speed>

        <max_columns_to_read>25</max_columns_to_read>
        <max_temporary_columns>100</max_temporary_columns>
        <max_temporary_non_const_columns>50</max_temporary_non_const_columns>

        <max_subquery_depth>2</max_subquery_depth>
        <max_pipeline_depth>25</max_pipeline_depth>
        <max_ast_depth>50</max_ast_depth>
        <max_ast_elements>100</max_ast_elements>

        <readonly>1</readonly>
    </web>
    <vip></vip>
    <high></high>
    <mid></mid>
    <low></low>
    <readonly></readonly>
</profiles>

<profiles>
    <default>
        <max_memory_usage>10000000000</max_memory_usage>
        <use_uncompressed_cache>0</use_uncompressed_cache>
        <force_index_by_date>0</force_index_by_date>
        <load_balancing>random</load_balancing>
        <constraints>
            <max_memory_usage>
                <min>100000</min>
                <max>20000</max>
            </max_memory_usage>
            <force_index_by_date>
                <readonly/>
            </force_index_by_date>
        </constraints>
    </default>
</profiles>

ALTER SETTINGS PROFILE [IF EXISTS] name [ON CLUSTER cluster_name]
    [RENAME TO new_name]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | INHERIT 'profile_name'] [,...]

<default>：配额规则名。
<interval>：配置时间间隔，每个时间内的资源消耗限制。
<duration>：时间周期，单位秒。duration表示累计的时间周期，单位为秒，达到该时间周期后，清除所有收集的值，接下来的周期，将重新开始计算，当服务重启时，也会清除所有的值，重新开始新的周期。
<queries>：时间周期内允许的请求总数，0表示不限制。
<errors>：时间周期内允许的异常总数，0表示不限制
<result_rows>：时间周期内允许返回的行数，0表示不限制。
<read_rows>：时间周期内允许在分布式查询中，远端节点读取的数据行数，0表示不限制。
<execution_time>：时间周期内允许执行的查询时间，单位是秒，0表示不限制。
上面示例中的配置，属性值均为0，所以资源配额不做任何限制。现在继续声明另外一组配额：

<!-- Quotas -->
<quotas>
    <!-- Quota name. -->
    <default>
        <statbox>
            <!-- Restrictions for a time period. You can set many intervals with different restrictions. -->
            <interval>
                <!-- Length of the interval. -->
                <duration>3600</duration>
                <queries>1000</queries>
                <errors>100</errors>
                <result_rows>1000000000</result_rows>
                <read_rows>100000000000</read_rows>
                <execution_time>900</execution_time>
            </interval>
            <interval>
                <duration>86400</duration>
                <queries>10000</queries>
                <errors>1000</errors>
                <result_rows>5000000000</result_rows>
                <read_rows>500000000000</read_rows>
                <execution_time>7200</execution_time>
            </interval>
        </statbox>
    </default>

ALTER QUOTA [IF EXISTS] name [ON CLUSTER cluster_name]
    [RENAME TO new_name]
    [KEYED BY {'none' | 'user name' | 'ip address' | 'client key' | 'client key or user name' | 'client key or ip address'}]
    [FOR [RANDOMIZED] INTERVAL number {SECOND | MINUTE | HOUR | DAY | WEEK | MONTH | QUARTER | YEAR}
        {MAX { {QUERIES | ERRORS | RESULT ROWS | RESULT BYTES | READ ROWS | READ BYTES | EXECUTION TIME} = number } [,...] |
        NO LIMITS | TRACKING ONLY} [,...]]
    [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]

<users>
    <!-- If user name was not specified, 'default' user is used. -->
    <user_name>
        <password></password>
        <!-- Or -->
        <password_sha256_hex></password_sha256_hex>

        <access_management>0|1</access_management>

        <networks incl="networks" replace="replace">
        </networks>

        <profile>profile_name</profile>

        <quota>default</quota>

        <databases>
            <database_name>
                <table_name>
                    <filter>expression</filter>
                <table_name>
            </database_name>
        </databases>
    </user_name>
    <!-- Other users settings -->
</users>

ALTER USER [IF EXISTS] name [ON CLUSTER cluster_name]
    [RENAME TO new_name]
    [IDENTIFIED [WITH {PLAINTEXT_PASSWORD|SHA256_PASSWORD|DOUBLE_SHA1_PASSWORD}] BY {'password'|'hash'}]
    [[ADD|DROP] HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [DEFAULT ROLE role [,...] | ALL | ALL EXCEPT role [,...] ]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]

# http://hadoop.apache.org/zookeeper/docs/current/zookeeperAdmin.html

# The number of milliseconds of each tick
tickTime=2000
# The number of ticks that the initial
# synchronization phase can take
# This value is not quite motivated
initLimit=300
# The number of ticks that can pass between
# sending a request and getting an acknowledgement
syncLimit=10

maxClientCnxns=2000

# It is the maximum value that client may request and the server will accept.
# It is Ok to have high maxSessionTimeout on server to allow clients to work with high session timeout if they want.
# But we request session timeout of 30 seconds by default (you can change it with session_timeout_ms in ClickHouse config).
maxSessionTimeout=60000000
# the directory where the snapshot is stored.
dataDir=/opt/zookeeper/{{ cluster['name'] }}/data
# Place the dataLogDir to a separate physical disc for better performance
dataLogDir=/opt/zookeeper/{{ cluster['name'] }}/logs

autopurge.snapRetainCount=10
autopurge.purgeInterval=1


# To avoid seeks ZooKeeper allocates space in the transaction log file in
# blocks of preAllocSize kilobytes. The default block size is 64M. One reason
# for changing the size of the blocks is to reduce the block size if snapshots
# are taken more often. (Also, see snapCount).
preAllocSize=131072

# Clients can submit requests faster than ZooKeeper can process them,
# especially if there are a lot of clients. To prevent ZooKeeper from running
# out of memory due to queued requests, ZooKeeper will throttle clients so that
# there is no more than globalOutstandingLimit outstanding requests in the
# system. The default limit is 1,000.ZooKeeper logs transactions to a
# transaction log. After snapCount transactions are written to a log file a
# snapshot is started and a new transaction log file is started. The default
# snapCount is 10,000.
snapCount=3000000

# If this option is defined, requests will be will logged to a trace file named
# traceFile.year.month.day.
#traceFile=

# Leader accepts client connections. Default value is "yes". The leader machine
# coordinates updates. For higher update throughput at thes slight expense of
# read throughput the leader can be configured to not accept clients and focus
# on coordination.
leaderServes=yes

standaloneEnabled=false
dynamicConfigFile=/etc/zookeeper-{{ cluster['name'] }}/conf/zoo.cfg.dynamic

NAME=zookeeper-{{ cluster['name'] }}
ZOOCFGDIR=/etc/$NAME/conf

# TODO this is really ugly
# How to find out, which jars are needed?
# seems, that log4j requires the log4j.properties file to be in the classpath
CLASSPATH="$ZOOCFGDIR:/usr/build/classes:/usr/build/lib/*.jar:/usr/share/zookeeper/zookeeper-3.5.1-metrika.jar:/usr/share/zookeeper/slf4j-log4j12-1.7.5.jar:/usr/share/zookeeper/slf4j-api-1.7.5.jar:/usr/share/zookeeper/servlet-api-2.5-20081211.jar:/usr/share/zookeeper/netty-3.7.0.Final.jar:/usr/share/zookeeper/log4j-1.2.16.jar:/usr/share/zookeeper/jline-2.11.jar:/usr/share/zookeeper/jetty-util-6.1.26.jar:/usr/share/zookeeper/jetty-6.1.26.jar:/usr/share/zookeeper/javacc.jar:/usr/share/zookeeper/jackson-mapper-asl-1.9.11.jar:/usr/share/zookeeper/jackson-core-asl-1.9.11.jar:/usr/share/zookeeper/commons-cli-1.2.jar:/usr/src/java/lib/*.jar:/usr/etc/zookeeper"

ZOOCFG="$ZOOCFGDIR/zoo.cfg"
ZOO_LOG_DIR=/var/log/$NAME
USER=zookeeper
GROUP=zookeeper
PIDDIR=/var/run/$NAME
PIDFILE=$PIDDIR/$NAME.pid
SCRIPTNAME=/etc/init.d/$NAME
JAVA=/usr/bin/java
ZOOMAIN="org.apache.zookeeper.server.quorum.QuorumPeerMain"
ZOO_LOG4J_PROP="INFO,ROLLINGFILE"
JMXLOCALONLY=false
JAVA_OPTS="-Xms{{ cluster.get('xms','128M') }} \
    -Xmx{{ cluster.get('xmx','1G') }} \
    -Xloggc:/var/log/$NAME/zookeeper-gc.log \
    -XX:+UseGCLogFileRotation \
    -XX:NumberOfGCLogFiles=16 \
    -XX:GCLogFileSize=16M \
    -verbose:gc \
    -XX:+PrintGCTimeStamps \
    -XX:+PrintGCDateStamps \
    -XX:+PrintGCDetails
    -XX:+PrintTenuringDistribution \
    -XX:+PrintGCApplicationStoppedTime \
    -XX:+PrintGCApplicationConcurrentTime \
    -XX:+PrintSafepointStatistics \
    -XX:+UseParNewGC \
    -XX:+UseConcMarkSweepGC \
-XX:+CMSParallelRemarkEnabled"

<?xml version="1.0"?>
<yandex>
    <profiles>
        <default>
            <max_memory_usage>100000000</max_memory_usage>
            <use_uncompressed_cache>0</use_uncompressed_cache>
            <load_balancing>random</load_balancing>
        </default>
        <readonly>
            <readonly>1</readonly>
        </readonly>
        <readwrite>
            <constraints>
                <max_memory_usage>
                    <readonly/>
                </max_memory_usage>
                <force_index_by_date>
                    <readonly/>
                </force_index_by_date>
            </constraints>
        </readwrite>
    </profiles>
    <users>
        <default>
            <password></password>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
        </default>
        <!-- 只读 -->
        <readonly>
            <password_double_sha1_hex></password_double_sha1_hex>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>readonly</profile>
            <quota>default</quota>
        </zhoujy_ro>
        <!-- 读写 -->
        <rw>
            <password_double_sha1_hex></password_double_sha1_hex>
            <networks incl="networks" replace="replace">
                <ip></ip>
            </networks>
            <profile>readwrite</profile>
            <quota>default</quota>
        </rw>
        <!--从指定IP访问的管理账号 -->
        <admin>
            <password_double_sha1_hex></password_double_sha1_hex>
            <networks incl="networks" replace="replace">
                <ip></ip>
                <ip></ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
        </admin>
        <!--从指定IP访问指定数据库 -->
        <db>
            <password_double_sha1_hex></password_double_sha1_hex>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
                <ip></ip>
                <ip></ip>
            </networks>
            <profile>readwrite</profile>
            <quota>default</quota>
            <allow_databases>
                <database>test</database>
            </allow_databases>
        </db>
        <!--从指定IP访问指定数据库表的记录 -->
        <tb>
            <password_double_sha1_hex></password_double_sha1_hex>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
                <ip>127.0.0.1</ip>
                <ip></ip>
            </networks>
            <profile>readonly</profile>
            <quota>default</quota>
            <allow_databases>
                <database>test</database>
            </allow_databases>
            <databases>
                <test>
                    <xx>
                        <filter>id >= 500 </filter>
                    </xx>
                </test>
            </databases>
        </tb>
    </users>
    <quotas>
        <default>
            <interval>
                <duration>3600</duration>
                <queries>0</queries>
                <errors>0</errors>
                <result_rows>0</result_rows>
                <read_rows>0</read_rows>
                <execution_time>0</execution_time>
            </interval>
        </default>
    </quotas>
</yandex>