ClickHouse調優（一）基本調優 · Clickhouse學習筆記

<div id="article_content" class="article_content clearfix"> <link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/kdoc_html_views-1a98987dfd.css"> <link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/ck_htmledit_views-25cebea3f9.css"> <div id="content_views" class="htmledit_views"> <h1><a name="t0"></a>1、執行計劃調優</h1> <p>        在clickhuse20.6版本之前要查看SQL語句的執行計劃需要設置<a href="https://so.csdn.net/so/search?q=%E6%97%A5%E5%BF%97%E7%BA%A7%E5%88%AB&spm=1001.2101.3001.7020" target="_blank" class="hl hl-1" data-report-click="{"spm":"1001.2101.3001.7020","dest":"https://so.csdn.net/so/search?q=%E6%97%A5%E5%BF%97%E7%BA%A7%E5%88%AB&spm=1001.2101.3001.7020","extra":"{\"searchword\":\"日志級別\"}"}" data-tit="日志級別" data-pretit="日志級別">日志級別</a>為trace才能可以看到并且只能真正執行sql，在執行日志里面查看。在20.6版本引入了原生的執行計劃的語法。在20.6.3版本成為正式版本的功能。</p> <h2><a name="t1"></a>1.1、基本語法</h2> <pre data-index="0"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">EXPLAIN [AST <span class="hljs-operator">|</span> SYNTAX <span class="hljs-operator">|</span> PLAN <span class="hljs-operator">|</span> PIPELINE] [setting <span class="hljs-operator">=</span> <span class="hljs-keyword">value</span>, ...] </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">select</span> ...[FORMAT ...]</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>        PLAN：用于查看執行計劃，默認值。</p> <ul><li>header：打印計劃中各個步驟的head說明，默認關閉，默認值為 0；</li><li>description：打印計劃中各個步驟的描述，默認開啟，默認值為1；</li><li>actions：打印計劃中各個步驟的詳細信息，默認關閉，默認值為0。</li></ul> <p>        <a href="https://so.csdn.net/so/search?q=AST&spm=1001.2101.3001.7020" target="_blank" class="hl hl-1" data-report-click="{"spm":"1001.2101.3001.7020","dest":"https://so.csdn.net/so/search?q=AST&spm=1001.2101.3001.7020","extra":"{\"searchword\":\"AST\"}"}" data-tit="AST" data-pretit="ast">AST</a>：用于查看語法樹；</p> <p>        SYNTAX：用于優化語法；</p> <p>        PIPELINE：用于查看PIPELINE計劃。</p> <ul><li>header：打印計劃中各個步驟的head說明，默認關閉；</li><li>graph：用DOT圖形語言描述管道圖，默認關閉，需要查看相關的圖形需要配置grapgviz查看；</li><li>actions：如果開啟了graph，默認開啟。</li></ul> <p>        注：PLAN和PIPELINE還可以進行額外的顯示設置，如上setting。</p> <h2><a name="t2"></a>1.2、案例實操</h2> <h3><a name="t3"></a>1.2.1、新版本使用EXPLAIN</h3> <p>        可以使用20.6以上版本，例如我現使用的為21.7.3.14。或者直接在官網的在線demo，選擇高版本進行測試。</p> <p>        官網在線測試鏈接：<a href="https://play.clickhouse.com/?file=welcome" title="ClickHouse Playground">ClickHouse Playground</a></p> <p>        1、查看PLAN</p> <pre data-index="1"><code class="language-sql hljs"><ol class="hljs-ln" style="width:1355px"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">-- plan 是默認的，可省略</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">explain plan <span class="hljs-keyword">select</span> database,<span class="hljs-keyword">table</span>,<span class="hljs-built_in">count</span>(<span class="hljs-number">1</span>) cnt <span class="hljs-keyword">from</span> system.parts <span class="hljs-keyword">where</span> database <span class="hljs-keyword">in</span> (<span class="hljs-string">'datasets'</span>,<span class="hljs-string">'system'</span>) <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> database,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">table</span> <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> database,cnt <span class="hljs-keyword">desc</span> limit <span class="hljs-number">2</span> <span class="hljs-keyword">by</span> database;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">explain header<span class="hljs-operator">=</span><span class="hljs-number">1</span>,actions<span class="hljs-operator">=</span><span class="hljs-number">1</span>,description<span class="hljs-operator">=</span><span class="hljs-number">1</span> <span class="hljs-keyword">select</span> database,<span class="hljs-keyword">table</span>,<span class="hljs-built_in">count</span>(<span class="hljs-number">1</span>) cnt <span class="hljs-keyword">from</span> system.parts <span class="hljs-keyword">where</span> database <span class="hljs-keyword">in</span> (<span class="hljs-string">'datasets'</span>,<span class="hljs-string">'system'</span>) <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> database,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">table</span> <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> database,cnt <span class="hljs-keyword">desc</span> limit <span class="hljs-number">2</span> <span class="hljs-keyword">by</span> database;</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p style="text-align:center;"><img alt="" height="549" src="https://img-blog.csdnimg.cn/f62432c9c61842d0ae53f72738ede70b.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWXVhbl9DU0RG,size_20,color_FFFFFF,t_70,g_se,x_16" width="941"></p> <p>         2、AST<a href="https://so.csdn.net/so/search?q=%E8%AF%AD%E6%B3%95%E6%A0%91&spm=1001.2101.3001.7020" target="_blank" class="hl hl-1" data-report-click="{"spm":"1001.2101.3001.7020","dest":"https://so.csdn.net/so/search?q=%E8%AF%AD%E6%B3%95%E6%A0%91&spm=1001.2101.3001.7020","extra":"{\"searchword\":\"語法樹\"}"}" data-tit="語法樹" data-pretit="語法樹">語法樹</a>（更深究才會使用到）</p> <pre data-index="2"><code class="language-sql hljs">explain AST <span class="hljs-keyword">SELECT</span> number <span class="hljs-keyword">from</span> system.numbers limit <span class="hljs-number">10</span>;</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p style="text-align:center;"><img alt="" height="349" src="https://img-blog.csdnimg.cn/1cb6121f2afd4dff88ac31f48de245c7.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWXVhbl9DU0RG,size_15,color_FFFFFF,t_70,g_se,x_16" width="473"></p> <p>         3、SYNTAX語法優化</p> <pre data-index="3"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">-- 初次查詢</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">select</span> number <span class="hljs-operator">=</span><span class="hljs-number">1</span>? <span class="hljs-string">'hello'</span>:(number<span class="hljs-operator">=</span><span class="hljs-number">2</span>?<span class="hljs-string">'world'</span>:<span class="hljs-string">'abcdef'</span>) <span class="hljs-keyword">from</span> numbers(<span class="hljs-number">10</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">-- 查看語法優化</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">EXPLAIN SYNTAX <span class="hljs-keyword">SELECT</span> number <span class="hljs-operator">=</span><span class="hljs-number">1</span>? <span class="hljs-string">'hello'</span>:(number<span class="hljs-operator">=</span><span class="hljs-number">2</span>?<span class="hljs-string">'world'</span>:<span class="hljs-string">'abcdef'</span>) <span class="hljs-keyword">from</span> numbers(<span class="hljs-number">10</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">-- 開啟三元運算符優化</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">set</span> optimize_if_chain_to_multiif <span class="hljs-operator">=</span> <span class="hljs-number">1</span>;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">-- 再次查看語法優化</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="11"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">EXPLAIN SYNTAX <span class="hljs-keyword">SELECT</span> number <span class="hljs-operator">=</span><span class="hljs-number">1</span>? <span class="hljs-string">'hello'</span>:(number<span class="hljs-operator">=</span><span class="hljs-number">2</span>?<span class="hljs-string">'world'</span>:<span class="hljs-string">'abcdef'</span>) <span class="hljs-keyword">from</span> numbers(<span class="hljs-number">10</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="12"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="13"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">-- 返回優化后的語句</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="14"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">SELECT</span> multiIf(number <span class="hljs-operator">=</span> <span class="hljs-number">1</span>, <span class="hljs-string">'hello'</span>, number <span class="hljs-operator">=</span> <span class="hljs-number">2</span>, <span class="hljs-string">'world'</span>, <span class="hljs-string">'abcdef'</span>) <span class="hljs-keyword">FROM</span> numbers(<span class="hljs-number">10</span>)</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p style="text-align:center;"><img alt="" height="571" src="https://img-blog.csdnimg.cn/7b66e881850d44dc96a1a6b7762a8624.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWXVhbl9DU0RG,size_20,color_FFFFFF,t_70,g_se,x_16" width="707"></p> <p>         4、PIPELINE</p> <p>        可以查看每個步驟使用的并行度：</p> <pre data-index="4"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">EXPLAIN PIPELINE <span class="hljs-keyword">SELECT</span> <span class="hljs-built_in">sum</span>(number) <span class="hljs-keyword">from</span> numbers_mt(<span class="hljs-number">10000</span>) <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> number <span class="hljs-operator">%</span> <span class="hljs-number">20</span>;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">EXPLAIN PIPELINE header<span class="hljs-operator">=</span><span class="hljs-number">1</span>,graph<span class="hljs-operator">=</span><span class="hljs-number">1</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-built_in">sum</span>(number) <span class="hljs-keyword">from</span> numbers_mt(<span class="hljs-number">10000</span>) <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> number <span class="hljs-operator">%</span> <span class="hljs-number">20</span>;</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p style="text-align:center;"><img alt="" height="323" src="https://img-blog.csdnimg.cn/faa0c22cf12c403ea54655a1f370d64b.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWXVhbl9DU0RG,size_20,color_FFFFFF,t_70,g_se,x_16" width="641"></p> <p>         <strong><span style="color:#fe2c24;">在這里最常用的是SYNTAX和PIPELINE，效果顯而易見。</span></strong></p> <h3><a name="t4"></a>1.2.2、老版本查看執行計劃</h3> <pre data-index="5"><code class="language-bash hljs">clickhouse-client -h 主機名 --send_logs_level=trace <<< <span class="hljs-string">"sql"</span> > /dev/null</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>        其中，send_logs_level參數指定日志等級為trace，<<<將SQL語句重定向至clickhouse-client進行查詢，>/dev/numm將查詢結果重定向到空設備吞掉，以便觀察日志。</p> <p>        <span style="color:#fe2c24;">注意：</span></p> <ol><li><span style="color:#0d0016;">通過將ClickHouse的服務日志，設置到DEBUG或者TRACE級別，才可以變相實現EXPLAIN查詢的作用。</span></li><li><span style="color:#0d0016;">需要真正的執行SQL查詢，CH才能打印計劃日志，所以如果表的數據量很大，最好借助LIMIT子句，減小查詢返回的數據量。</span></li></ol> <h1><a name="t5"></a>2、建表優化</h1> <h2><a name="t6"></a>2.1、數據類型</h2> <h3><a name="t7"></a>2.1.1、時間字段的類型</h3> <p>        建表時能用數值型或日期時間型表示的字段能不用字符串就不要用字符串，全String類型在以Hive為中心的數倉建設中常見，但ClickHouse環境不應受此影響。</p> <p>        雖然ClickHouse底層將DateTime存儲為時間戳Long類型，但不建議存儲Long類型，因為<span style="color:#fe2c24;">DateTime不需要經過函數轉換處理，執行效率高、可讀性好</span>。</p> <h3><a name="t8"></a>2.1.2、控制存儲類型</h3> <p>        官網已經指出Nullable類型幾乎總是會拖累性能，<span style="color:#fe2c24;">因為存儲Nullable列時需要創建一個額外的文件來存儲NULL的標記，并且Nullable列無法被索引</span>。因為除非極特殊情況，應直接使用字段默認值表示空，或者自行指定一個在業務中無意義的值。</p> <p>        例如：</p> <pre data-index="6"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> test_null(x Int8,y Nullable(Int8)) ENGINE TinyLog;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> test_null <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">1</span>,<span class="hljs-keyword">NULL</span>),(<span class="hljs-number">2</span>,<span class="hljs-number">3</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">SELECT</span> x<span class="hljs-operator">+</span>y <span class="hljs-keyword">FROM</span> test_null;</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>        查看存儲的文件：</p> <p style="text-align:center;"><img alt="" height="159" src="https://img-blog.csdnimg.cn/5084fa778aaf46ff8d91411b0058158c.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWXVhbl9DU0RG,size_11,color_FFFFFF,t_70,g_se,x_16" width="360"></p> <h2><a name="t9"></a>2.2、分區和索引</h2> <p>        分區粒度根據業務特點決定，不宜過粗或過細。一般選擇按天分區，也可以指定為Tuple()，<strong>以單表一億數據為例，分區大小控制在10-30個為最佳。</strong></p> <p>        必須指定索引列，ClickHouse中的索引列即排序列，通過order by指定，一般在查詢條件中經常被用來充當篩選條件的屬性被納入進來：可以是單一維度，也可以是組合維度的索引；<span style="color:#fe2c24;">通常需要滿足高級列在前、查詢頻率大的在前原則；還有基數特別大的不適合做索引列，如用戶表的userid字段；通常篩選后的數據滿足在百萬以內為最佳。</span></p> <h2><a name="t10"></a><span style="color:#0d0016;">2.3、表參數</span></h2> <p><span style="color:#0d0016;">        Index_granularity是用來通知索引粒度的，默認是8192，如非必須不建議調整。</span></p> <p><span style="color:#0d0016;">        如果表中不是必須保留全量歷史數據，建議制定TTL（生存時間值）可以免去手動過期歷史數據的麻煩，TTL也可以通過alter table語句隨時修改。</span></p> <h2><a name="t11"></a><span style="color:#0d0016;">2.4、寫入和刪除優化</span></h2> <p>        1、盡量不要執行單條或小批量刪除和插入操作，這樣會產生小分區文件，給后臺Merge任務帶來巨大壓力。</p> <p>        2、不要一次寫入太多分區，或數據寫入太快，數據寫入太快會導致Merge速度跟不上而報錯，一般建議每秒鐘發起2-3次寫入操作（例如flink的并行度），每次操作寫入2W~5W條數據（依服務器性能而定）。</p> <h2><a name="t12"></a>2.5、常見配置</h2> <p>        配置項主要在config.xml（對應服務端的配置）或users.xml（用戶使用的配置）中，基本上都在users.xml里。目錄在/etc/clickhouse-server。</p> <p>        1、config.xml的配置項（<span style="color:#fe2c24;">不能被覆蓋，即在客戶端設置的參數無法覆蓋</span>）：</p> <p>        <a href="https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings/" title="Server Settings | ClickHouse Documentation">Server Settings | ClickHouse Documentation</a></p> <p>        2、users.xml的配置項：</p> <p>        <a href="https://clickhouse.com/docs/en/operations/settings/" title="Introduction | ClickHouse Documentation">Introduction | ClickHouse Documentation</a></p> <p>        </p> <p style="text-align:center;"><img alt="" height="842" src="https://img-blog.csdnimg.cn/0a5c2d15ba914a0d833eb7e8570cb119.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWXVhbl9DU0RG,size_9,color_FFFFFF,t_70,g_se,x_16" width="295"></p> <h3><a name="t13"></a>2.5.1、CPU資源</h3> <div class="table-box"><table align="center" border="1" cellpadding="1" cellspacing="1" style="width:700px;"><thead><tr><th style="text-align:center;">配置</th><th style="text-align:center;">描述</th></tr></thead><tbody><tr><td>background_pool_size</td><td>后臺線程池的大小，merge線程就是在該線程池中執行，該線程池不僅僅是給merge線程用的，默認值16，允許的前提下建議改成<span style="color:#fe2c24;">cpu個數的2倍（線程數）</span>。</td></tr><tr><td> <p id="background_schedule_pool_size">background_schedule_pool_size</p> </td><td>執行后臺任務（復制表、Kafka流、DNS緩存更新）的線程數。默認128，建議改成<span style="color:#fe2c24;">cpu個數的2倍（線程數）</span>。</td></tr><tr><td>background_distributed_schedule_pool_size</td><td>設置為分布式發送執行后臺任務的線程數，默認16，建議改成<span style="color:#fe2c24;">CPU個數的2倍（線程數）</span></td></tr><tr><td>max_concurrent_queries</td><td>最大并發處理的請求數（包含select,insert等），默認值100，推薦<span style="color:#fe2c24;">150（不夠再加）~300</span>。</td></tr><tr><td>max_threads</td><td>設置單個查詢所能使用的最大cpu個數，默認是cpu核數</td></tr></tbody></table></div> <h3><a name="t14"></a>2.5.2、內存資源</h3> <div class="table-box"><table border="1" cellpadding="1" cellspacing="1" style="width:700px;"><thead><tr><th style="text-align:center;">配置</th><th style="text-align:center;">描述</th></tr></thead><tbody><tr><td>max_memory_usage</td><td>此參數在users.xml中,表示單次Query占用內存最大值，該值可以設置的比較大，這樣可以提升集群查詢的上限。保留一點給OS，比如<span style="color:#fe2c24;">128G內存的機器，設置為100GB。</span></td></tr><tr><td>max_bytes_before_external_group_by</td><td>一般按照max_memory_usage的一半設置內存，當group使用內存超過閾值后會刷新到磁盤進行。因為clickhouse聚合分兩個階段：查詢并及建立中間數據、合并中間數據，<span style="color:#fe2c24;">結合上一項，建議50GB。</span></td></tr><tr><td>max_bytes_before_external_sort</td><td>當order by已使用max_bytes_before_external_sort內存就進行溢寫磁盤(基于磁盤排序)，如果不設置該值，那么當內存不夠時直接拋錯，設置了該值order by可以正常完成，但是速度相對存內存來說肯定要慢點(實測慢的非常多，無法接受)。</td></tr><tr><td>max_table_size_to_drop</td><td>此參數在config.xml中，應用于需要刪除表或分區的情況，默認是50GB，意思是如果刪除50GB以上的分區表會失敗。<span style="color:#fe2c24;">建議修改為0</span>，這樣不管多大的分區表都可以刪除。</td></tr></tbody></table></div> <h3><a name="t15"></a>2.5.3、存儲</h3> <p>        ClickHouse不支持設置多數據目錄，為了提升數據io性能，可以掛載虛擬券組，一個券組綁定多塊物理磁盤提升讀寫性能，多數據查詢場景SSD會比普通機械硬盤快2-3倍。</p> <p></p> </div><div data-report-view="{"mod":"1585297308_001","spm":"1001.2101.3001.6548","dest":"https://blog.csdn.net/Yuan_CSDF/article/details/122289287","extend1":"pc","ab":"new"}"><div></div></div> </div>