页面历史

调优参数的配置可以根据实际环境进行调整

<?xml version="1.0"?>
<clickhouse>
    <logger>
<!-- smartbi begin 日志级别从trace调整成error减少无用日志输出-->
        <level>error</level>
<!-- smartbi end  -->
        <log>/var/log/clickhouse-server/clickhouse-server.log</log>
        <errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
<!-- smartbi begin 配置日志文件大小从1000M 修改成100M 防止日志过大无法下载和打开-->  
        <size>100M</size>
<!-- smartbi end  -->
        <count>10</count>
    </logger>
        <header>
            <name>Access-Control-Allow-Origin</name>
            <value>*</value>
        </header>
        <header>
            <name>Access-Control-Allow-Headers</name>
            <value>origin, x-requested-with</value>
        </header>
        <header>
            <name>Access-Control-Allow-Methods</name>
            <value>POST, GET, OPTIONS</value>
        </header>
        <header>
            <name>Access-Control-Max-Age</name>
            <value>86400</value>
        </header>
    <http_port>8123</http_port>
    <tcp_port>9000</tcp_port>
    <mysql_port>9004</mysql_port>
    <postgresql_port>9005</postgresql_port>
    <interserver_http_port>9009</interserver_http_port>
    <listen_host>0.0.0.0</listen_host>
    <max_connections>4096</max_connections>

<!-- smartbi begin 关闭连接之前等待传入请求的秒数， 默认为3秒，调整为300，解决大数据了插入时 broken write问题-->
    <keep_alive_timeout>3</keep_alive_timeout>
<!-- smartbi end  -->
    <grpc>
        <enable_ssl>false</enable_ssl>
        <ssl_cert_file>/path/to/ssl_cert_file</ssl_cert_file>
        <ssl_key_file>/path/to/ssl_key_file</ssl_key_file>
        <ssl_require_client_auth>false</ssl_require_client_auth>
        <ssl_ca_cert_file>/path/to/ssl_ca_cert_file</ssl_ca_cert_file>
        <transport_compression_type>none</transport_compression_type>
        <transport_compression_level>0</transport_compression_level>
        <max_send_message_size>-1</max_send_message_size>
        <max_receive_message_size>-1</max_receive_message_size>
        <verbose_logs>false</verbose_logs>
    </grpc>
    <openSSL>
        <server> 
            <certificateFile>/etc/clickhouse-server/server.crt</certificateFile>
            <privateKeyFile>/etc/clickhouse-server/server.key</privateKeyFile>
            <verificationMode>none</verificationMode>
            <loadDefaultCAFile>true</loadDefaultCAFile>
            <cacheSessions>true</cacheSessions>
            <disableProtocols>sslv2,sslv3</disableProtocols>
            <preferServerCiphers>true</preferServerCiphers>
        </server>
        <client>
            <loadDefaultCAFile>true</loadDefaultCAFile>
            <cacheSessions>true</cacheSessions>
            <disableProtocols>sslv2,sslv3</disableProtocols>
            <preferServerCiphers>true</preferServerCiphers>
            <invalidCertificateHandler>
                <name>RejectCertificateHandler</name>
            </invalidCertificateHandler>
        </client>
    </openSSL>

<!-- smartbi begin 最大并发处理的请求数(包含 select,insert 等)，默认100 调整到300 可以根据机器性能和并发情况再调大-->
    <max_concurrent_queries>300</max_concurrent_queries>
<!-- smartbi end  -->

    <max_server_memory_usage>0</max_server_memory_usage>
    <max_thread_pool_size>10000</max_thread_pool_size>
    <max_server_memory_usage_to_ram_ratio>0.9</max_server_memory_usage_to_ram_ratio>
    <total_memory_profiler_step>4194304</total_memory_profiler_step>
    <total_memory_tracker_sample_probability>0</total_memory_tracker_sample_probability>
    <uncompressed_cache_size>8589934592</uncompressed_cache_size>
    <mark_cache_size>5368709120</mark_cache_size>
    <mmap_cache_size>1000</mmap_cache_size>
    <compiled_expression_cache_size>134217728</compiled_expression_cache_size>
    <compiled_expression_cache_elements_size>10000</compiled_expression_cache_elements_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>
    <ldap_servers>
    </ldap_servers>
    <user_directories>
        <users_xml>
            <path>users.xml</path>
        </users_xml>
        <local_directory>
            <path>/var/lib/clickhouse/access/</path>
        </local_directory>
    </user_directories>
    <default_profile>default</default_profile>
    <custom_settings_prefixes></custom_settings_prefixes>
    <default_database>default</default_database>
<!-- smartbi begin 配置时区-->
     <timezone>Asia/Shanghai</timezone>
<!-- smartbi end  -->  
<!-- smartbi begin 解决删除副本表立马重建会报错的问题 ,默认是480s，调整为0-->
    <database_atomic_delay_before_drop_table_sec>0</database_atomic_delay_before_drop_table_sec>
<!-- smartbi end  -->
    <mlock_executable>true</mlock_executable>
    <remap_executable>false</remap_executable>
    <![CDATA[
         Uncomment below in order to use JDBC table engine and function.

         To install and run JDBC bridge in background:
         * [Debian/Ubuntu]
           export MVN_URL=https://repo1.maven.org/maven2/ru/yandex/clickhouse/clickhouse-jdbc-bridge
           export PKG_VER=$(curl -sL $MVN_URL/maven-metadata.xml | grep '<release>' | sed -e 's|.*>\(.*\)<.*|\1|')
           wget https://github.com/ClickHouse/clickhouse-jdbc-bridge/releases/download/v$PKG_VER/clickhouse-jdbc-bridge_$PKG_VER-1_all.deb
           apt install --no-install-recommends -f ./clickhouse-jdbc-bridge_$PKG_VER-1_all.deb
           clickhouse-jdbc-bridge &

         * [CentOS/RHEL]
           export MVN_URL=https://repo1.maven.org/maven2/ru/yandex/clickhouse/clickhouse-jdbc-bridge
           export PKG_VER=$(curl -sL $MVN_URL/maven-metadata.xml | grep '<release>' | sed -e 's|.*>\(.*\)<.*|\1|')
           wget https://github.com/ClickHouse/clickhouse-jdbc-bridge/releases/download/v$PKG_VER/clickhouse-jdbc-bridge-$PKG_VER-1.noarch.rpm
           yum localinstall -y clickhouse-jdbc-bridge-$PKG_VER-1.noarch.rpm
           clickhouse-jdbc-bridge &

         Please refer to https://github.com/ClickHouse/clickhouse-jdbc-bridge#usage for more information.
    ]]>
    <remote_servers>
    </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>
<!-- smartbi begin 建议增加TTL 只保留30天之内数据，减少系统空间占用-->
<ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
<!-- smartbi end  -->
    </query_log>

    <trace_log>
        <database>system</database>
        <table>trace_log</table>
        <partition_by>toYYYYMM(event_date)</partition_by>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
<!-- smartbi begin 建议增加TTL 只保留30天之内数据，减少系统空间占用-->
<ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
<!-- smartbi end  -->
    </trace_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>
<!-- smartbi begin 建议增加TTL 只保留30天之内数据，减少系统空间占用-->
<ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
<!-- smartbi end  -->
    </query_thread_log>

    <query_views_log>
        <database>system</database>
        <table>query_views_log</table>
        <partition_by>toYYYYMM(event_date)</partition_by>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
<!-- smartbi begin 建议增加TTL 只保留30天之内数据，减少系统空间占用-->
<ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
<!-- smartbi end  -->
    </query_views_log>

    <part_log>
        <database>system</database>
        <table>part_log</table>
        <partition_by>toYYYYMM(event_date)</partition_by>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
<!-- smartbi begin 建议增加TTL 只保留30天之内数据，减少系统空间占用-->
<ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
<!-- smartbi end  -->
    </part_log>

    <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>
    <asynchronous_metric_log>
        <database>system</database>
        <table>asynchronous_metric_log</table>
        <flush_interval_milliseconds>7000</flush_interval_milliseconds>
    </asynchronous_metric_log>

    <opentelemetry_span_log>
        <engine>
            engine MergeTree
            partition by toYYYYMM(finish_date)
            order by (finish_date, finish_time_us, trace_id)
        </engine>
        <database>system</database>
        <table>opentelemetry_span_log</table>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </opentelemetry_span_log>

    <crash_log>
        <database>system</database>
        <table>crash_log</table>

        <partition_by />
        <flush_interval_milliseconds>1000</flush_interval_milliseconds>
    </crash_log>

    <session_log>
        <database>system</database>
        <table>session_log</table>

        <partition_by>toYYYYMM(event_date)</partition_by>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </session_log>

    <top_level_domains_lists>
    </top_level_domains_lists>
    <dictionaries_config>*_dictionary.xml</dictionaries_config>
    <user_defined_executable_functions_config>*_function.xml</user_defined_executable_functions_config>

    <encryption_codecs>
    </encryption_codecs>
    <distributed_ddl>
        <path>/clickhouse/task_queue/ddl</path>
    </distributed_ddl>

<!-- smartbi begin merge_tree调优配置-->
<merge_tree>
    <inactive_parts_to_throw_insert>0</inactive_parts_to_throw_insert>
    <inactive_parts_to_delay_insert>0</inactive_parts_to_delay_insert>
    <non_replicated_deduplication_window>0</non_replicated_deduplication_window>
    <execute_merges_on_single_replica_time_threshold>0</execute_merges_on_single_replica_time_threshold>
    <try_fetch_recompressed_part_timeout>7200</try_fetch_recompressed_part_timeout>
    <replicated_fetches_http_connection_timeout>1</replicated_fetches_http_connection_timeout>
    <replicated_fetches_http_send_timeout>0</replicated_fetches_http_send_timeout>
    <replicated_fetches_http_receive_timeout>0</replicated_fetches_http_receive_timeout>
    <max_partitions_to_read>-1</max_partitions_to_read>
    <allow_nullable_key>FALSE</allow_nullable_key>
    <min_bytes_to_rebalance_partition_over_jbod>0</min_bytes_to_rebalance_partition_over_jbod>
    <min_marks_to_honor_max_concurrent_queries>0</min_marks_to_honor_max_concurrent_queries>
    <detach_old_local_parts_when_cloning_replica>TRUE</detach_old_local_parts_when_cloning_replica>
    <always_fetch_merged_part>TRUE</always_fetch_merged_part>
    <max_suspicious_broken_parts>10000</max_suspicious_broken_parts>
    <parts_to_throw_insert>300</parts_to_throw_insert>
    <parts_to_delay_insert>150</parts_to_delay_insert>
    <max_delay_to_insert>256</max_delay_to_insert>
    <max_parts_in_total>100000</max_parts_in_total>
    <replicated_deduplication_window>100</replicated_deduplication_window>
    <replicated_deduplication_window_seconds>604800</replicated_deduplication_window_seconds>
    <max_replicated_logs_to_keep>1000</max_replicated_logs_to_keep>
    <min_replicated_logs_to_keep>10</min_replicated_logs_to_keep>
    <prefer_fetch_merged_part_time_threshold>3600</prefer_fetch_merged_part_time_threshold>
    <prefer_fetch_merged_part_size_threshold>10737418240</prefer_fetch_merged_part_size_threshold>
    <max_files_to_modify_in_alter_columns>75</max_files_to_modify_in_alter_columns>
    <max_files_to_remove_in_alter_columns>50</max_files_to_remove_in_alter_columns>
    <replicated_max_ratio_of_wrong_parts>0.5</replicated_max_ratio_of_wrong_parts>
    <replicated_max_parallel_fetches_for_host>15</replicated_max_parallel_fetches_for_host>
    <replicated_can_become_leader>TRUE</replicated_can_become_leader>
    <zookeeper_session_expiration_check_period>1</zookeeper_session_expiration_check_period>
    <old_parts_lifetime>480</old_parts_lifetime>
    <max_bytes_to_merge_at_max_space_in_pool>161061273600</max_bytes_to_merge_at_max_space_in_pool>
    <max_bytes_to_merge_at_min_space_in_pool>1048576</max_bytes_to_merge_at_min_space_in_pool>
    <merge_max_block_size>8192</merge_max_block_size>
    <max_part_loading_threads>16</max_part_loading_threads>
</merge_tree>
<!-- smartbi end  -->

    <graphite_rollup_example>
        <pattern>
            <regexp>click_cost</regexp>
            <function>any</function>
            <retention>
                <age>0</age>
                <precision>3600</precision>
            </retention>
            <retention>
                <age>86400</age>
                <precision>60</precision>
            </retention>
        </pattern>
        <default>
            <function>max</function>
            <retention>
                <age>0</age>
                <precision>60</precision>
            </retention>
            <retention>
                <age>3600</age>
                <precision>300</precision>
            </retention>
            <retention>
                <age>86400</age>
                <precision>3600</precision>
            </retention>
        </default>
    </graphite_rollup_example>

    <format_schema_path>/var/lib/clickhouse/format_schemas/</format_schema_path>
    <query_masking_rules>
        <rule>
            <name>hide encrypt/decrypt arguments</name>
            <regexp>((?:aes_)?(?:encrypt|decrypt)(?:_mysql)?)\s*\(\s*(?:'(?:\\'|.)+'|.*?)\s*\)</regexp>
            <replace>\1(???)</replace>
        </rule>
    </query_masking_rules>

    <send_crash_reports>
        <enabled>false</enabled>
        <anonymize>false</anonymize>
        <endpoint>https://6f33034cfe684dd7a3ab9875e57b1c8d@o388870.ingest.sentry.io/5226277</endpoint>
    </send_crash_reports>
</clickhouse>

调优参数的配置可以根据实际环境进行调整

<?xml version="1.0"?>
<clickhouse>
    <profiles>
        <default>
        <!-- smartbi begin 数据接收和发送时间，默认180s,适量增大方式插入数据时出现 Broken pipe-->
        <http_send_timeout>3600</http_send_timeout>
        <http_receive_timeout>3600</http_receive_timeout>
        <max_query_size>26214400</max_query_size>
        <max_ast_elements>10000000</max_ast_elements>
        <max_expanded_ast_elements>10000000</max_expanded_ast_elements>        <!-- 其他user.xml调优参数添加在此处即可 -->
        <!-- smartbi end  →
            <load_balancing>random</load_balancing>
        </default>
        <readonly>
            <readonly>1</readonly>
        </readonly>
    </profiles>
    <users><!--用户配置-->
        <default> <!--用户名-->
            <password></password> <!--置登录密码 免密码登入，支持明文、SHA256加密和double_sha1加密三种形式-->
            <networks>
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
        </default>
    </users>
    <quotas><!--限制资源被过度使用，当使用的资源数量达到阈值时，那么正在进行的操作会被自动中断-->
        <default><!--自定义名称，全局唯一-->
            <interval><!--配置时间间隔，每个时间内的资源消耗限制-->
                <duration>3600</duration><!--表示累积的时间周期，单位是秒-->
                <queries>0</queries><!--表示在周期内允许执行的查询次数，0表示不限制-->
                <errors>0</errors><!--表示在周期内允许发生异常的次数，0表示不限制-->
                <result_rows>0</result_rows> <!--表示在周期内允许查询返回的结果行数，0表示不限制-->
                <read_rows>0</read_rows><!--表示在周期内在分布式查询中，允许远端节点读取的数据行数，0表示不限制。-->
                <execution_time>0</execution_time><!--表示周期内允许执行的查询时间，单位是秒，0表示不限制。-->
            </interval>
        </default>
    </quotas>
</clickhouse>