20_clickhouse,硬件管理与优化(cpu,内存,网络,存储,操作系统配置),profile管理，Quotas设置，约束管理，查询权限，用户管理配置等

傷城~ 2022-12-28 12:34 35阅读 0赞

26.运维管理与优化  
26.1.硬件管理与优化  
26.1.1.CPU  
26.1.2.内存  
26.1.3.网络  
26.1.4.存储  
26.2.操作系统的配置  
26.2.1.CPU频率调整策略  
26.2.2.关闭透明大页  
26.2.3.禁用swap文件  
26.2.4.内核分配策略  
26.3.彻底理解ClickHouse的配置文件  
26.3.1.用户设置  
26.3.2.重复设置项的处理  
26.3.3.预处理文件  
26.3.4.ZooKeeper的关键优化点  
26.3.5.服务监控  
26.3.5.1.系统表  
26.3.5.2.查询日志  
26.4.用户管理  
26.4.1.profile管理  
26.4.2.Quotas设置  
26.4.3.约束管理  
26.4.4.查询权限  
26.4.5.用户管理配置

## 26.运维管理与优化 ##

### 26.1.硬件管理与优化 ###

#### 26.1.1.CPU ####

ClickHouse的并行数据处理机制，使得其能利用所有可用的硬件资源。在选择CPU处理器时，应选择更多核心数而不是更高频率的处理器。

例如：频率为2600MHz的16核心处理器比3600MHz的8核心的处理器的效率更高。  
建议使用Turbo Boost和超线程技术，在高工作负载情况下能显著提高性能。

#### 26.1.2.内存 ####

ClickHouse的服务本身需要的RAM很少，但是ClickHouse需要内存来处理查询，建议至少使用4GB内存。  
RAM的容量取决于：

*  查询的复杂性。
 *  查询处理的数据量。
 *  根据GROUP BY、DISTINCT、JOIN和其他操作产生的临时数据大小估算所需的RAM容量。

对于少量数据，例如压缩后在200GB以下，使用数据量一样多的内存。  
对于大量数据，以及交互式查询，热数据集缓存在操作系统的cache中，建议配置尽可能大的内存，例如128GB的内存性能明显比64GB高很多。

如果内存不足， ClickHouse支持使用外部存储器处理数据，如外部聚合和外部排序等，当数据超过设置的阈值时使用外部存储器处理数据。

#### 26.1.3.网络 ####

推荐使用10GB或更高带宽的网络。  
ClickHouse在数据处理过程（数据导入、中间数据存储等）中会充分利用网络带宽。网络带宽对于处理具有大量中间数据的分布式查询至关重要。  
网络带宽会影响复制的过程。

#### 26.1.4.存储 ####

综合考虑安全性、性能和预算。  
如果使用RAID0，则需考虑使用副本机制。  
预算足够，使用SSD，然后HDD。  
RAID10的性能最佳，兼顾了安全性和性能，但是成本高，50%的磁盘利用率。  
主机的磁盘超过4块，可以考虑使用RAID6、RAID50、RAID5等。

优先使用具有多个本地磁盘的服务器，而不是具有附加磁盘架的服务器。

考虑压缩率、副本、磁盘存储容量、未来的数据增长估算存储资源。  
数据量：对数据进行采样，计算每行的平均大小，根据总行数估算数据量。  
数据压缩系数：ClickHouse的数据通常被压缩5倍左右，根据原始数据大小和ClickHouse存储的表的数据目录占用空间进行比较。  
副本：每份副本存储完整的数据。

### 26.2.操作系统的配置 ###

#### 26.2.1.CPU频率调整策略 ####

建议将CPU的电源管理策略调整为performance，即将CPU频率固定工作在其支持的最高运行频率上， 不动态调节，可以获取到最大的性能。  
查看系统支持的策略：

# cat /sys/devices/system/cpu/cpu0/cpufreq/scaling_available_governors
    performance powersave

查看生效的策略：

# cat /sys/devices/system/cpu/cpu0/cpufreq/scaling_governor
    powersave

设置：

echo 'performance' | sudo tee /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor

#### 26.2.2.关闭透明大页 ####

开启透明大页，操作系统的内存分配活动需要各种内存锁，直接影响程序的内存访问性能，对于ClickHouse而言，透明大页会严重干扰内存分配器的工作，从而导致性能显著下降。

echo never > /sys/kernel/mm/transparent_hugepage/defrag
    echo never > /sys/kernel/mm/transparent_hugepage/enabled
    
    echo 'echo never > /sys/kernel/mm/transparent_hugepage/defrag'>> /etc/rc.local
    echo 'echo never > /sys/kernel/mm/transparent_hugepage/enabled'>> /etc/rc.local

#### 26.2.3.禁用swap文件 ####

避免将数据调度到swap上，影响性能。

dd if=/dev/zero of=/home/swap bs=1024 count=1048576
    swapon /dev/dm-1
    # swapon
    NAME TYPE SIZE USED PRIO
    /dev/dm-1 partition 16G 0B -3
    
    swapoff   /dev/dm-1

#### 26.2.4.内核分配策略 ####

overcommit\_memory是一个内核对内存分配的一种策略。具体可见/proc/sys/vm/overcommit\_memory下的值。  
overcommit\_memory取值有三种分别为0,1,2

overcommit\_memory=0，表示内核将检查是否有足够的可用内存供应用进程使用；如果有足够的可用内存，内存申请允许；否则，内存申请失败，并把错误返回给应用进程。  
overcommit\_memory=1，表示内核允许分配所有的物理内存，而不管当前的内存状态如何。  
overcommit\_memory=2，表示内核允许分配超过所有物理内存和交换空间总和的内存。

### 26.3.彻底理解ClickHouse的配置文件 ###

ClickHouse支持多文件的配置管理，主配置文件为/etc/clickhouse-server/config.xml，其他的配置文件必须位于/etc/clickhouse-server/config.d目录中。

所有配置文件必须为XML格式，每个文件具有相同的根元素，通常为.

**配置替换**  
配置的元素可以指定incl属性，这个属性指定了一个”substitutions (替换)”。默认情况下，这个”替换”来自配置文件/etc/metrika.xml的元素，可以通过服务器的配置选项include\_from修改”替换”文件的路径。当指定了”替换”，将使用替换文件中相应元素的内容作为值。“替换”的值是引用”替换”文件的根节点（yarndex）下的子元素。如果incl中指定的”替换”不存在，则在服务器中记录日志，如果不想记录日志，则为元素指定optional="true"属性即可。  
例如，”替换”文件/etc/metrika.xml中的部分内容：

ClickHouse的config.xml配置文件默认引用了这个文件的"替换"：zookeeper-servers，它是/etc/metrika.xml中根节点yandex下的子元素。如下：

#### 26.3.1.用户设置 ####

在config.xml文件默认指定了一个单独的用户管理相关的配置文件，用于管理用户的权限、profiles、quotas等。这个配置文件由users\_config元素指定文件路径，默认为users.xml。如果省略users\_config元素，则直接在config.xml中指定用户管理相关的配置。

<users_config>users.xml</users_config>

可以将用户配置拆分为多个配置文件，类似config.xml和config.d。用户配置的目录默认为users.d，目录名称的规则为：user\_config设置的文件名去掉.xml后缀，并拼接上.d后缀。例如，如果user\_config设置为abc.xml，则用户配置目录为：abc.d。  
如下示例，为用户创建了一个单独的配置文件：

$ cat /etc/clickhouse-server/users.d/alice.xml
    <yandex>
        <users>
          <alice>
              <profile>default</profile>
              <networks>
                  <ip>::/0</ip>
              </networks>
              <password>123</password>
              <quota>default</quota>
          </alice>
        </users>
    </yandex>

#### 26.3.2.重复设置项的处理 ####

主配置文件的有些设置会被其他配置文件中的设置覆盖。  
可以为这些配置文件的元素指定replace和remove属性。  
如果replace和remove属性都不指定， ClickHouse将以递归的方式合并元素的内容，替换掉重复子元素的值。  
如果指定了replace属性， 则使用当前的元素配置替换从其他配置文件引用的元素内容。  
如果指定了remove属性， 则表示删除该元素。  
例如下面的配置，它会替换networks元素（由incl指定）的整个配置，而保留当前定义的设置：

#### 26.3.3.预处理文件 ####

ClickHouse服务在启动时，会生成每类配置文件的预处理文件，这些文件包含了所有已完成的替换和重写，是ClickHouse最终应用的配置。  
ClickHouse会跟踪配置文件的更改，能动态加载变更的用户和集群设置。这意味着用户可以在线动态修改集群、用户等相关配置，而无需重新启动服务。  
预处理文件路径为/var/lib/clickhouse/preprocessed\_configs，使用/etc/clickhouse-server/preprocessed软链接指向这个目录。

# ll /etc/clickhouse-server/preprocessed
    lrwxrwxrwx 1 root root 41 3月   4 09:44 /etc/clickhouse-server/preprocessed -> /var/lib/clickhouse//preprocessed_configs
    # ls /etc/clickhouse-server/preprocessed/
    abc_dictionary.xml  config.xml  users.xml

#### 26.3.4.ZooKeeper的关键优化点 ####

**1、ZooKeeper节点数3个以上，奇数台。**

**2、与ClickHouse集群独立部署**  
ZooKeeper对延迟非常敏感，ClickHouse可能会利用所有可用的系统资源。

**3、配置snapshot文件清理策略**

autopurge.purgeInterval=1
    
    autopurge.snapRetainCount=10

autopurge.purgeInterval：开启清理事务日志和快照文件的功能，单位是小时。默认是0，表示不开启自动清理功能。  
autopurge.snapRetainCount ： 指定了需要保留的文件数目。默认是保留3个。

**4、限制snapshot数量**  
snapCount=3000000

每snapCount次事务日志输出后，触发一次快照(snapshot)。 ZooKeeper会生成一个snapshot文件和事务日志文件。 默认是100000。

**5、log和data数据分磁盘存储**  
dataDir：存储快照文件snapshot的目录。默认情况下，事务日志也会存储在这里。  
dataLogDir：事务日志输出目录。尽量给事务日志的输出配置单独的磁盘或是挂载点，这将极大的提升ZK性能。

**6、ZooKeeper的磁盘建议使用SSD**  
如果数据在TB级别以上，且复制表的数量比较多，超过100个，建议使用SSD磁盘。提升ZooKeeper的响应速度，避免ClickHouse副本间数据同步的延迟。

**7、调整JVM大小**  
ZooKeeper的JVM内存默认是根据操作系统本身内存大小的一个百分比预先分配的，所以这不是我们所需要的。

在./bin/zkEnv.sh文件中，有如下配置项：

if [ -f "$ZOOCFGDIR/java.env" ]
    then
        . "$ZOOCFGDIR/java.env"
    fi

我们在./conf/java.env文件中配置JVM的内存，增加如下配置：

export JAVA_HOME=/usr/local/java/jdk1.8.0_151
    export JVMFLAGS="-Xms1024m -Xmx2048m $JVMFLAGS"

修改完成使用jmap -heap $pid来验证内存修改情况。

**8、其它配置**  
**tickTime=2000**  
ZK中的一个时间单元。ZK中所有时间都是以这个时间单元为基础，进行整数倍配置的。例如，session的最小超时时间是2\*tickTime。 默认值2000，单位毫秒。

**initLimit=10**  
Follower在启动过程中，会从Leader同步所有最新数据，然后确定自己能够对外服务的起始状态。Leader允许F在 initLimit 时间内完成这个工作。通常情况下，我们不用太在意这个参数的设置。如果ZK集群的数据量确实很大了，F在启动的时候，从Leader上同步数据的时间也会相应变长，因此在这种情况下，有必要适当调大这个参数了。  
initLimit=30000

**syncLimit=5**  
在运行过程中，Leader负责与ZK集群中所有机器进行通信，例如通过一些心跳检测机制，来检测机器的存活状态。如果L发出心跳包在syncLimit之后，还没有从F那里收到响应，那么就认为这个F已经不在线了。注意：不要把这个参数设置得过大，否则可能会掩盖一些问题。 建议设置为10。

**maxClientCnxns ：**  
单个客户端与单台服务器之间的连接数的限制，是ip级别的，默认是60，如果设置为0，那么表明不作任何限制。请注意这个限制的使用范围，仅仅是单台客户端机器与单台ZK服务器之间的连接数限制，不是针对指定客户端IP，也不是ZK集群的连接数限制，也不是单台ZK对所有客户端的连接数限制。  
建议设置为2000

**maxSessionTimeout=60000000**  
Session超时时间限制，如果客户端设置的超时时间不在这个范围，那么会被强制设置为最大或最小时间。默认的Session超时时间是在2 \* tickTime ~ 20 \* tickTime 这个范围

preAllocSize=131072  
预先开辟磁盘空间，用于后续写入事务日志。默认是64M，每个事务日志大小就是64M。如果ZK的快照频率较大的话，建议适当减小这个参数。单位kb。

**配置：**

tickTime=2000
    
    initLimit=30000
    syncLimit=10
    
    maxClientCnxns=2000
    maxSessionTimeout=60000000
    
    dataDir=/opt/zookeeper/{
       { cluster['name'] }}/data
    dataLogDir=/opt/zookeeper/{
       { cluster['name'] }}/logs
    
    autopurge.purgeInterval=1
    autopurge.snapRetainCount=10
    
    snapCount=3000000
    
    preAllocSize=131072

**9、建ClickHouse表配置元数据压缩**  
建表的时候设置use\_minimalistic\_part\_header\_in\_zookeeper=1， 则ZooKeeper中会存储较少的数据，支持副本的表使用单个znode紧凑地存储数据片段的头信息。如果表包含很多列， 则此存储方法将大大减少Zookeeper中存储的数据量。

#### 26.3.5.服务监控 ####

ClickHouse内置了自我状态检测的功能， 可根据系统表、查询日志等监控服务器计算资源的不同度量以及查询处理的通用统计信息。

##### 26.3.5.1.系统表 #####

ClickHouse提供了system.metrics、system.events和system.asynchronous\_metrics表收集服务器的不同的运行度量。  
（1）、system.metrics  
这张表的数据实时更新， 用于查看正在运行的查询或当前副本的延迟信息等。  
包含的列有：  
•metric(String) ： 度量名称。  
•value(Int64) ：指标值。  
•description (String) ：度量描述。

**示例:**

select * from system.metrics;
    
    ┌─metric───────────────────────────────────────┬────value─┬─description─────────────────────────────────────────────┐
    │ Query                                        │        1 │ Number of executing queries                             │      
    │ Merge                                        │        0 │ Number of executing background merges                   │     
    │ PartMutation                                 │        0 │ Number of mutations (ALTER DELETE/UPDATE)               │
    │ GlobalThread                                 │       65 │ Number of threads in global thread pool.                │                   
    │ GlobalThreadActive                           │       51 │ Number of threads in global thread pool running a task. │
    └──────────────────────────────────────────────┴──────────┴─────────────────────────────────────────────────────────┘

**（2）、system.events**  
用于收集系统中发生的事件数的信息。例如，可以查询自ClickHouse服务启动以来已处理的SELECT 查询数。  
列信息如下：  
**•event (String)** ： 事件名称。  
**•value (UInt64)** ： 发生的事件数。  
**•description (String)** ： 事件描述。

**示例：**

clickhouse1 :) select * from system.events LIMIT 5;
    
    SELECT *
    FROM system.events
    LIMIT 5
    
    ┌─event───────────────────────┬───value─┬─description────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
    │ Query                       │       7 │ Number of queries to be interpreted and potentially executed. Does not include queries that failed to parse or were rejected due to AST size limits, quota limits or limits on the number of simultaneously running queries. May include internal queries initiated by ClickHouse itself. Does not count subqueries. │
    │ SelectQuery                 │       7 │ Same as Query, but only for SELECT queries.                                                                                                                                                                                                                │
    │ QueryTimeMicroseconds       │  140616 │ Total time of all queries.                                                                                                                                                                                                                                 │
    │ SelectQueryTimeMicroseconds │  140616 │ Total time of SELECT queries.                                                                                                                                                                                                                              │
    │ FileOpen                    │ 3645730 │ Number of files opened.                                                                                                                                                                                                                                    │
    └─────────────────────────────┴─────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
    
    5 rows in set. Elapsed: 0.002 sec. 
    
    clickhouse1 :)

**（3）、system.asynchronous\_metrics**  
用于实时监控后台定期计算的指标。例如，正在使用的RAM大小。

列信息如下：  
**•metric (String)** ： 事件名称。  
**•value (Float64)** ：指标值。

**例如：**

clickhouse1 :) SELECT * FROM system.asynchronous_metrics;
    
    SELECT *
    FROM system.asynchronous_metrics
    
    ┌─metric───────────────────────────────────┬──────value─┐
    │ CPUFrequencyMHz_0                        │   2591.504 │
    │ jemalloc.arenas.all.pmuzzy               │          0 │
    │ jemalloc.arenas.all.pdirty               │       1311 │
    │ jemalloc.background_thread.run_intervals │          0 │
    │ jemalloc.background_thread.num_runs      │          0 │
    │ jemalloc.retained                        │  894418944 │
    │ jemalloc.mapped                          │  136331264 │
    │ jemalloc.metadata                        │   13635904 │
    │ jemalloc.resident                        │  126943232 │
    │ jemalloc.allocated                       │  102341072 │
    │ jemalloc.epoch                           │       1298 │
    │ NumberOfTables                           │         73 │
    │ jemalloc.active                          │  109989888 │
    │ NumberOfDatabases                        │          3 │
    │ MaxPartCountForPartition                 │          7 │
    │ jemalloc.background_thread.num_threads   │          0 │
    │ ReplicasSumQueueSize                     │          0 │
    │ ReplicasMaxMergesInQueue                 │          0 │
    │ MemoryShared                             │ 4238995456 │
    │ MemoryCode                               │  432128000 │
    │ ReplicasMaxAbsoluteDelay                 │          0 │
    │ ReplicasMaxQueueSize                     │          0 │
    │ jemalloc.arenas.all.muzzy_purged         │          0 │
    │ MemoryVirtual                            │ 6097547264 │
    │ MarkCacheBytes                           │          0 │
    │ Uptime                                   │      77887 │
    │ jemalloc.arenas.all.dirty_purged         │  379880169 │
    │ ReplicasMaxRelativeDelay                 │          0 │
    │ MemoryResident                           │ 4455157760 │
    │ ReplicasMaxInsertsInQueue                │          0 │
    │ jemalloc.metadata_thp                    │          0 │
    │ UncompressedCacheCells                   │          0 │
    │ CompiledExpressionCacheCount             │          0 │
    │ ReplicasSumMergesInQueue                 │          0 │
    │ UncompressedCacheBytes                   │          0 │
    │ ReplicasSumInsertsInQueue                │          0 │
    │ MarkCacheFiles                           │          0 │
    │ MemoryDataAndStack                       │ 1824006144 │
    │ jemalloc.arenas.all.pactive              │      26853 │
    └──────────────────────────────────────────┴────────────┘
    
    39 rows in set. Elapsed: 0.049 sec. 
    
    clickhouse1 :)

###### 26.3.5.2.查询日志 ######

**query\_log**  
记录所有查询语句的开始时间、耗时、用户、资源占用等信息，包括SELECT、SET、ALTER等语句。  
在ClickHouse的主配置文件config.xml配置了query\_log日志的表、分区、数据刷新间隔等信息。

<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>

ClickHouse默认不会收集查询的日志， 可通过设置log\_queries = 1开启查询日志的功能。

set log_queries = 1;
    show tables;
    
    SELECT * from `system`.query_log ql limit 1;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RvdG8xMjk3NDg4NTA0_size_16_color_FFFFFF_t_70_pic_center]

**trace\_log**  
存储query profilers收集的堆栈跟踪日志。  
堆栈日志根据计时器类型分为REAL和CPU，分别表示实际时钟计时器和CPU时钟计时器。  
计时器相关的两个设置：

query_profiler_real_time_period_ns：设置query profilers的实际计时器的周期，单位为纳秒，默认为1000000000（1秒）
    query_profiler_cpu_time_period_ns：设置query profilers的CPU计时器的周期，单位为纳秒，默认为1000000000（1秒）。

在ClickHouse的主配置文件config.xml配置了trace\_log日志的表、分区、数据刷新间隔等信息。

<trace_log>
        <database>system</database>
        <table>trace_log</table>
    
        <partition_by>toYYYYMM(event_date)</partition_by>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </trace_log>

**示例：**

SELECT * FROM system.trace_log ORDER BY event_date ASC LIMIT 2;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RvdG8xMjk3NDg4NTA0_size_16_color_FFFFFF_t_70_pic_center 1]

**query\_thread\_log**  
查询线程日志， 记录查询执行的所有线程的信息。  
在ClickHouse的主配置文件config.xml配置了query\_thread\_log日志的表、分区、数据刷新间隔等信息。

<query_thread_log>
        <database>system</database>
        <table>query_thread_log</table>
        <partition_by>toYYYYMM(event_date)</partition_by>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </query_thread_log>

ClickHouse默认不会收集查询线程的日志， 可通过设置log\_query\_threads= 1开启查询日志的功能。

set log_query_threads = 1;
    
    select * from system.query_thread_log limit 1;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RvdG8xMjk3NDg4NTA0_size_16_color_FFFFFF_t_70_pic_center 2]

**part\_log**  
用户监控MergeTree引擎表中针对数据片段所有操作（创建、删除、合并、下载等）的信息。

part\_log默认是关闭， 在ClickHouse的主配置文件使用如下配置开启数据片段日志。

<part_log>
        <database>system</database>
        <table>part_log</table>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </part_log>

**text\_log**  
text\_log用于记录服务器的常规日志，但是以结构化和高效的方式存储在表中。  
text\_log默认是关闭的， 在ClickHouse的主配置文件使用如下配置开启日志。

<text_log>
        <database>system</database>
        <table>text_log</table>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </text_log>

**metric\_log**  
用于存储来自表system.metrics和system.events指标值的历史记录。collect\_interval\_milliseconds用于设置采集的时间间隔。metric\_log默认是关闭的， 在ClickHouse的主配置文件使用如下配置开启日志。

<metric_log>
        <database>system</database>
        <table>metric_log</table>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
        <collect_interval_milliseconds>1000</collect_interval_milliseconds>
    </metric_log>

### 26.4.用户管理 ###

#### 26.4.1.profile管理 ####

profile是一组设置的集合，类似于角色的概念，每个用户都有一个profile。

可以有两种方式使用profile：  
在会话中应用profile的所有设置，例如：SET profile = ‘web’ 。  
在users.xml的users部分为某个用户指定profile。  
**profiles在users.xml配置文件的profiles标签下配置：**

<profiles>
        
        <default>
            
            <max_threads>8</max_threads>
        </default>
    
        
        <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>
    </profiles>

上面的配置指定了两个profile：default和web。默认的default不能省略，它包含默认的设置，在服务器启动时应用。web是常规的profile，可以使用SET语句或HTTP查询中的URL参数进行设置。

profile设置可相互继承。如果使用继承，在配置文件中列出的其他设置之前， 先指定一个或多个profile。如果一个设置在不同的profile都定义了，则使用最新的设置。

#### 26.4.2.Quotas设置 ####

Quotas用于在一段时间内跟踪资源的使用情况或限制资源的使用。quotas在user.xml配置文件的quotas标签下配置，在users标签下分配给用户。  
注意：quota用于限制一组查询，并且将所有远程服务器上的分布式查询处理的资源纳入限制范围，而不是限制单个查询。  
**quotas的配置示例1:**

<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>

默认情况下，quota只跟踪一个小时内的资源使用情况，并不会限制资源的使用。每个时间间隔内的资源消耗在每次请求之后输出到服务器日志。

**Quota配置示例2：**

上面配置了一个名称为statbox的quota，在每小时（3600秒）和每24小时（86400秒）设置了对资源的限制。间隔结束时，将清除所有收集的值，在接下来的下一个时间区间，quota将重新开始计算。

可用于quota限制的资源如下：  
**queries** ： 请求总数。  
**errors** ： 抛出异常的总数。  
**result\_rows** : 作为结果给出的总行数。  
**read\_rows** : 在所有远程服务器上，从表中读取用于运行查询的源总行数。  
**execution\_time** : 查询执行的总耗时，单位为秒。如果在至少一个时间间隔内超过了限制，则会引发异常，异常信息包括限制的类型、时间间隔以及新时间间隔的开始时间。

对于分布式查询处理， 资源的累积量存储在请求服务器上，因此，如果用户转到另一个服务器，则这个服务器的quota将重新开始累积。重启服务器后，quota将重置。

#### 26.4.3.约束管理 ####

在users.xml配置文件的profiles部分定义对设置的约束，可以禁止用户使用SET语句更改某些设置。  
约束的定义模板如下：

<profiles>
      <profile_name>
        <constraints>
          <setting_name_1>
            <min>lower_boundary</min>
          </setting_name_1>
          <setting_name_2>
            <max>upper_boundary</max>
          </setting_name_2>
          <setting_name_3>
            <min>lower_boundary</min>
            <max>upper_boundary</max>
          </setting_name_3>
          <setting_name_4>
            <readonly/>
          </setting_name_4>
        </constraints>
      </profile_name>
    </profiles>

如果用户尝试违法冲突，则会引发异常。  
支持三种类型的约束：max、min和readonly。  
max和min约束使用数值指定约束的上限和下限，这两个约束可以组合使用。 readonly约束指定了用户无法更改相应的设置。  
使用示例：

上面的约束配置限制了max\_memory\_usage的范围在20000000000和5000000000之间，force\_index\_by\_date设置为只读。  
使用下面的语句修改配置将引发异常：

SET max_memory_usage=20000000001;
    SET max_memory_usage=4999999999;
    SET force_index_by_date=1;

**异常信息如下：**

Code: 452, e.displayText() = DB::Exception: Setting max_memory_usage should not be greater than 20000000000.
    Code: 452, e.displayText() = DB::Exception: Setting max_memory_usage should not be less than 5000000000.
    Code: 452, e.displayText() = DB::Exception: Setting force_index_by_date should not be changed.

**Note**：在default的profile中定义的约束为默认的约束，这些约束会限制所有的用户，除非用户在自己的profile中显式覆盖这些设置。

force\_index\_by\_date，如果表的主键包含日期列，则查询条件必须包含该该列。如果表的主键不包含日期列，则无需包含日期列。

仅适用于MergeTree引擎。

#### 26.4.4.查询权限 ####

**ClickHouse中的查询可分为如下几种类型：**  
**读取数据查询**：SELECT、SHOW、DESCRIBE、EXISTS。  
**写数据查询**：INSERT、OPTIMIZE。  
**更改设置查询**：SET、USE。  
**DDL查询**：CREATE、ALTER、RENAME、ATTACH、DETACH、 DROP、TRUNCATE。  
KILL QUERY

**下面的设置可用户配置用户的查询权限：**  
**readonly** ：限制读取、写入和更改三类查询的权限。  
**allow\_ddl**：限制DDL查询的权限。  
KILL QUERY不受任何设置的限制。

**1. readonly**  
用于限制读取、写入和更改三类查询的权限，默认值为0。  
**readonly的取值如下：**  
**0** ： 允许执行所有查询。  
**1** ： 仅允许读取数据的查询。  
**2** ： 允许读取数据和更改设置。  
在设置readonly=1后，用户将无法在当前会话中更改readonly和allow\_ddl的设置。  
在HTTP请求中使用GET方法时将自动设置readonly=1。如果在http请求中修改数据，则必须使用POST方法。

设置readonly=1将禁止用户更改所有的设置。如果要禁止用户修改某些特定的设置，可以在users.xml配置文件中配置profiles的约束。

**2. allow\_ddl**  
用于配置是否允许DDL操作的权限，默认值为1。  
可设置的值如下：  
**0** ： 不允许DDL查询。  
**1** ： 允许DDL查询。  
如果当前会话的allow\_ddl=0，则无法执行： SET allow\_ddl=0。

#### 26.4.5.用户管理配置 ####

xml文件的users标签的结构如下：

<users>
        
        <user_name>
            <password></password>
            
            <password_sha256_hex></password_sha256_hex>
    
            <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>
    
           <allow_databases>
               <database>test</database>
            </allow_databases>
            <allow_dictionaries>
               <dictionary>test</dictionary>
            </allow_dictionaries>
        </user_name>
        
        
    </users>

在上面的配置中，定义了一个user\_name的用户，该用户的所有其他配置都通过该标签的子标签配置。下面详细介绍每个具体子标签的配置信息。  
**（1）、user\_name/password**  
密码可以使用明文、SHA256或SHA1指定。

明文形式的密码通过password标签指定。  
**例如:**

<password>qwerty</password>

密码可以为空。

SHA256散列密码通过password\_sha256\_hex标签指定。  
示例：

<password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex>

可通过如下shell命令生成SHA256散列：

PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'

命令的输出如下：

pmpjaK2V
    21c4185b8155e532ca5a1eb0d4ca74ecde83eddee4ca55af393007f7c29f7eb7

第一行是明文的密码，第二行对应的SHA256加密散列。  
SHA1散列密码通过password\_double\_sha1\_hex标签指定。  
SHA1密码是为了兼容MySQL客户端。  
使用示例：

<password_double_sha1_hex>08b4a0f1de6ad37da17359e592c8d74788a83eb0</password_double_sha1_hex>

可通过如下shell命令生成SHA1散列：

命令的输出如下：

KCXM95It
    de4bbcb9bbb35e3e497fcbebafc0f04b3dcef383

第一行是明文的密码，第二行对应的SHA1加密散列。

**（2）、user\_name/networks**  
networks用于配置网络列表，只有网络列表范围内的用户可以连接到ClickHouse。  
列表中的元素可以使用如下形式定义：  
： IP地址或带掩码的IP地址。  
例如：

213.180.204.3, 10.0.0.1/8, 10.0.0.1/255.255.255.0, 2a02:6b8::3, 2a02:6b8::3/64, 2a02:6b8::3/ffff:ffff:ffff:ffff::

： 主机名。  
例如：

example01.host.ru

<host\_regexp> ： 主机名的正则表达式。  
**例如：**

^example\d\d-\d\d-\d\.host\.ru$

使用这种方式指定网络列表，强烈建议regexp以$结尾。  
示例1：用户可以从任意网络访问，指定：

**示例2：仅限本地网络访问，指定：**

**（3）、user\_name/profile**  
profile用于给用户分配一个配置好的profile，这个profile是在users.xml配置文件的profiles标签下配置的。  
**（4）、user\_name/quota**  
用于给用户分配quota，这里quota是在users.xml配置文件的quotas标签下配置的。quota用于在一定时间内跟踪或限制资源的使用。

**（5）、user\_name/databases**  
用于限制当前用户的SELECT查询返回的行，可实现基本的行级安全性。

**示例：**  
以下的配置将限制用户user1只能查询table1表中id=1000的行，其他记录对用户不可见，就好像表中不存在其他行一样。

使用这种方式需要注意WHERE子句的查询谓词不会下推，即禁用了WHERE移动到PREWHERE的优化。

**（6）、user\_name/allow\_databases**  
用于指定用户可以访问的数据库列表。默认情况下，用户可以访问所有数据库。  
Note： 用户对system数据库的访问始终是有权限的，因为用户需要根据此数据库处理查询。  
配置示例，配置用户可访问test1和test2数据库：

<allow_databases>
      <database>test1</database>
      <database>test2</database>
    </allow_databases>

**（7）、user\_name/allow\_dictionaries**  
配置可以访问的字典列表。默认情况下，用户可访问所有字典。  
示例， 配置用户可访问test1和test2字典：

<allow_dictionaries>
      <dictionary>test1</dictionary>
      <dictionary>test2</dictionary>
    </allow_dictionaries>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RvdG8xMjk3NDg4NTA0_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/a8b6873979d8460898edec1c949535c7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RvdG8xMjk3NDg4NTA0_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/ef9ba594056b40febc695f62cc19d4b7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RvdG8xMjk3NDg4NTA0_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/80819bbe854a4c8195272663b15f0278.png