yexinru/doc-exports
1
0
Fork 0
forked from docs/doc-exports
Code Pull Requests Activity
Files
sgode-patch-2
doc-exports/docs/dws/dev/dws_04_0970.html
luhuayi 177cd61a57 DWS DEVG 910.211 version 
Reviewed-by: Pruthi, Vineet <vineet.pruthi@t-systems.com>
Co-authored-by: luhuayi <luhuayi@huawei.com>
Co-committed-by: luhuayi <luhuayi@huawei.com>
2025-05-05 07:44:03 +00:00

201 lines
35 KiB
HTML
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

<a name="EN-US_TOPIC_0000002052813794"></a><a name="EN-US_TOPIC_0000002052813794"></a>
<h1 class="topictitle1">Tuning Database Parameters</h1>
<div id="body8662426"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_a97493f9219434bd59f20069dd68d470e">To ensure high performance of the database, you are advised to configure GUC parameters based on available resources and the actual workloads. This section describes some of the common parameters and the recommended configurations for them. For more details, see <a href="dws_04_0885.html">Configuring GUC Parameters</a>.</p>
<div class="section" id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_section1788074910303"><h4 class="sectiontitle">Parameters Related to Database Memory</h4>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_table10983115018312" frame="border" border="1" rules="all"><caption><b>Table 1 </b>Parameters related to database memory</caption><thead align="left"><tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row2984185011319"><th align="left" class="cellrowborder" valign="top" width="20%" id="mcps1.3.2.2.2.4.1.1"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p982132512342">GUC Parameter</p>
</th>
<th align="left" class="cellrowborder" valign="top" width="35%" id="mcps1.3.2.2.2.4.1.2"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p8984145003111">Description</p>
</th>
<th align="left" class="cellrowborder" valign="top" width="45%" id="mcps1.3.2.2.2.4.1.3"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p199844505315">Configuration Suggestion</p>
</th>
</tr>
</thead>
<tbody><tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row1984450153117"><td class="cellrowborder" valign="top" width="20%" headers="mcps1.3.2.2.2.4.1.1 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p13984115073112">max_process_memory</p>
</td>
<td class="cellrowborder" valign="top" width="35%" headers="mcps1.3.2.2.2.4.1.2 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p8985175093114">Specifies the maximum physical memory available to a single CN/DN.</p>
<p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p983819123610"></p>
<p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p139946316306"></p>
<p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_aab35f3b5765c45d5bef7391ef5ea5e17"></p>
</td>
<td class="cellrowborder" valign="top" width="45%" headers="mcps1.3.2.2.2.4.1.3 "><ul id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_ul3173165403010"><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_li717345415305">On DNs, the value of this parameter is determined based on the server's physical memory and the number of DNs deployed on a single node. Parameter value = (Physical memory – <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b41378826463949">vm.min_free_kbytes</strong>) x 0.8/(n + Number of primary DNs). This parameter aims to ensure system reliability, preventing node OOM caused by increasing memory usage. <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b58733722063949">vm.min_free_kbytes</strong> indicates OS memory reserved for kernels to receive and send data. Its value is at least 5% of the total memory. That is, <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b105075877663949">max_process_memory</strong> = Physical memory x 0.8/ (n + Number of primary DNs). If the cluster scale (number of nodes in the cluster) is smaller than 256, n=1; if the cluster scale is larger than 256 and smaller than 512, n=2; if the cluster scale is larger than 512, n=3.</li><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_li51731654163013">Set this parameter on CNs to the same value as that on DNs.</li><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_li917318544306">RAM is the maximum memory allocated to the cluster.</li></ul>
</td>
</tr>
<tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row298555016317"><td class="cellrowborder" valign="top" width="20%" headers="mcps1.3.2.2.2.4.1.1 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_af50c448cdee0492b8ea1339e07f6008e">shared_buffers</p>
</td>
<td class="cellrowborder" valign="top" width="35%" headers="mcps1.3.2.2.2.4.1.2 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_p647414481114">Specifies the size of the shared memory used by GaussDB(DWS). If the value of this parameter is increased, GaussDB(DWS) requires more System V shared memory than the default system setting.</p>
</td>
<td class="cellrowborder" valign="top" width="45%" headers="mcps1.3.2.2.2.4.1.3 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_a65c5d2025fc74e82a32cf6335d2a88b1">It is recommended that <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b42954880663949">shared_buffers</strong> be set to a value less than 40% of the memory. Set it to a large value for row-store tables and a small value for column-store tables. Set this parameter to a large value for row storage and a small value for column storage. For column-store tables: shared_buffers = (Memory of a single server/Number of DNs on the single server) x 0.4 x 0.25</p>
<p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_aa01ec1333ed54e1a9bdd9bf07ac8b8f6">If you want to increase the value of <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b44312660263949">shared_buffers</strong>, you also need to increase the value of <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b69003058063949">checkpoint_segments</strong>, because a longer period of time is required to write a large amount of new or changed data.</p>
</td>
</tr>
<tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row198518508312"><td class="cellrowborder" valign="top" width="20%" headers="mcps1.3.2.2.2.4.1.1 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p271816570361">cstore_buffers</p>
</td>
<td class="cellrowborder" valign="top" width="35%" headers="mcps1.3.2.2.2.4.1.2 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_a89455377567f45b1ae13510f4a6cefc5">Specifies the size of the shared buffer used by column-store tables and column-store tables (ORC, Parquet, and CarbonData) of OBS and HDFS foreign tables.</p>
</td>
<td class="cellrowborder" valign="top" width="45%" headers="mcps1.3.2.2.2.4.1.3 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p10291741144815">Column-store tables use the shared buffer specified by <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b128454414063949">cstore_buffers</strong> instead of that specified by <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b47868291563949">shared_buffers</strong>. When column-store tables are mainly used, reduce the value of <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b113192502163949">shared_buffers</strong> and increase that of <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b119490966063949">cstore_buffers</strong>.</p>
<p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p112929419481">Use <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b96037192863949">cstore_buffers</strong> to specify the cache of ORC, Parquet, or CarbonData metadata and data for OBS or HDFS foreign tables. The metadata cache size should be 1/4 of <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b140057275363949">cstore_buffers</strong> and not exceed 2 GB. The remaining cache is shared by column-store data and foreign table column-store data.</p>
</td>
</tr>
<tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row09851050193117"><td class="cellrowborder" valign="top" width="20%" headers="mcps1.3.2.2.2.4.1.1 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p139852050173118">work_mem</p>
</td>
<td class="cellrowborder" valign="top" width="35%" headers="mcps1.3.2.2.2.4.1.2 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p646720245413">Specifies the size of the memory used by internal sequential operations and the Hash table before data is written into temporary disk files.</p>
</td>
<td class="cellrowborder" valign="top" width="45%" headers="mcps1.3.2.2.2.4.1.3 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p141014434233">The default value is 512 MB for small-scale memory (<strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b2139218183918">max_process_memory</strong> is less than 30 GB) and 2 GB for large-scale memory (<strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b134392893819">max_process_memory</strong> is greater than or equal to 30 GB).</p>
<p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_a8b8407fcfb944313aeb9b56f2f9f11e0">When the specified physical memory is insufficient, <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b84961367263949">work_mem</strong> determines whether to write additional operator calculation data into temporary tables based on query characteristics and concurrency. This reduces performance by five to ten times and increases query response times from seconds to minutes.</p>
<ul id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_u0456f9a7b18e44819c3d75bfed41ea3e"><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_l900e71d92c3f46f7b91245262fa19bdd">In complex serial query scenarios, each query requires five to ten associated operations. Set <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b61450776463949">work_mem</strong> using the following formula: <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b204398106263949">work_mem</strong> = 50% of the memory/10.</li><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_lb81821a583784d8292b4d84664570d3c">In simple serial query scenarios, each query requires two to five associated operations. Set <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b20667594263949">work_mem</strong> using the following formula: <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b169483235363949">work_mem</strong> = 50% of the memory/5.</li><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_laf62dbc2eec942028034e5eea840ce4b">For concurrent queries, use the formula: <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b41871604963949">work_mem</strong> = <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b118587073163949">work_mem</strong> in serialized scenario/Number of concurrent SQL statements.</li></ul>
</td>
</tr>
<tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row19986195023113"><td class="cellrowborder" valign="top" width="20%" headers="mcps1.3.2.2.2.4.1.1 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p1398617506312">maintenance_work_mem</p>
</td>
<td class="cellrowborder" valign="top" width="35%" headers="mcps1.3.2.2.2.4.1.2 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p1489144113535">Specifies the maximum size of memory used for maintenance operations, involving <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b170656331463949">VACUUM</strong>, <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b15804242763949">CREATE INDEX</strong>, and <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b45484812363949">ALTER TABLE ADD FOREIGN KEY</strong>.</p>
</td>
<td class="cellrowborder" valign="top" width="45%" headers="mcps1.3.2.2.2.4.1.3 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_p8476134811111">If you set this parameter to the value of <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b11024589263949">work_mem</strong>, database dump files can be cleaned up and restored more efficiently. In a database session, only one maintenance operation can be performed at a time. Maintenance is usually performed when there are not much sessions.</p>
<p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_p4476164818118">When the automatic cleanup process is running, up to <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b167195384563949">autovacuum_max_workers</strong> times of the memory will be allocated. In this case, set <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b191875051763949">maintenance_work_mem</strong> to a value greater than or equal to that of <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b187361941763949">work_mem</strong>.</p>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="section" id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_section2093943519404"><h4 class="sectiontitle">Parameters Related to Queue Concurrency in Databases</h4>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_table537403917416" frame="border" border="1" rules="all"><thead align="left"><tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row113751639114110"><th align="left" class="cellrowborder" valign="top" width="20%" id="mcps1.3.3.2.1.4.1.1"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p193751239154112">GUC Parameter</p>
</th>
<th align="left" class="cellrowborder" valign="top" width="35%" id="mcps1.3.3.2.1.4.1.2"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p13375103914411">Description</p>
</th>
<th align="left" class="cellrowborder" valign="top" width="45%" id="mcps1.3.3.2.1.4.1.3"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p2375163914116">Configuration Suggestion</p>
</th>
</tr>
</thead>
<tbody><tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row1137518396416"><td class="cellrowborder" valign="top" width="20%" headers="mcps1.3.3.2.1.4.1.1 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p02381742114115">max_active_statements (global concurrent queue)</p>
</td>
<td class="cellrowborder" valign="top" width="35%" headers="mcps1.3.3.2.1.4.1.2 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p6237194215419">Controls the maximum number of concurrent jobs on a single CN.</p>
</td>
<td class="cellrowborder" valign="top" width="45%" headers="mcps1.3.3.2.1.4.1.3 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p1430712574513">All common users' jobs are subject to this threshold, regardless of their complexity. When the number of concurrent jobs reaches the specified threshold, the excess jobs have to wait in a queue. Administrator's jobs are exempt from this limit.</p>
<p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_p154741148111119">Set the value of this parameter based on system resources, such as CPU, I/O, and memory resources, to ensure that the system resources can be fully utilized and the system will not be crashed due to excessive concurrent jobs.</p>
</td>
</tr>
<tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row737516398419"><td class="cellrowborder" valign="top" width="20%" headers="mcps1.3.3.2.1.4.1.1 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p1512619810423">parctl_min_cost (local concurrent queue)</p>
</td>
<td class="cellrowborder" valign="top" width="35%" headers="mcps1.3.3.2.1.4.1.2 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p1323434214111">Controls the maximum number of concurrent jobs within the same resource pool on a single CN.</p>
</td>
<td class="cellrowborder" valign="top" width="45%" headers="mcps1.3.3.2.1.4.1.3 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p38425228533">The number of concurrent complex jobs are controlled based on their cost.</p>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="note" id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_n05b5631ceb244406ac30002e22487648"><img src="public_sys-resources/note_3.0-en-us.png"><span class="notetitle"> </span><div class="notebody"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p13795153417512">When tuning the <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b182113171393">max_active_statements</strong> parameter (global concurrent queue), pay attention to the following:</p>
<ul id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_ul05591046102719"><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_li1655964614270">If <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b16922312063949">max_active_statements</strong> is set to <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b61469049963949">-1</strong>, which indicates that global concurrency is not limited, users may be disconnected in a high concurrency scenario.</li><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_li16559154611275">In a point query scenario, set <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b194722466363949">max_active_statements</strong> to <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b40206459763949">100</strong>.</li><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_li95591546142719">In an analytical query scenario, set <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b38138626863949">max_active_statements</strong> to the number of CPU cores divided by the number of DNs. Generally, its value ranges from 5 to 8.</li></ul>
</div></div>
<div class="section" id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_section3503133803817"><h4 class="sectiontitle">Database Communication Parameters</h4><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p12505191163919">By default, nodes in a database cluster communicate using the TCP proxy communication library.</p>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_table12291469384" frame="border" border="1" rules="all"><caption><b>Table 2 </b>Database communication parameters</caption><thead align="left"><tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row529124615382"><th align="left" class="cellrowborder" valign="top" width="20%" id="mcps1.3.5.3.2.4.1.1"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p16972185317389">GUC Parameter</p>
</th>
<th align="left" class="cellrowborder" valign="top" width="35%" id="mcps1.3.5.3.2.4.1.2"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p119721053123818">Description</p>
</th>
<th align="left" class="cellrowborder" valign="top" width="45%" id="mcps1.3.5.3.2.4.1.3"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p6972115353811">Configuration Suggestion</p>
</th>
</tr>
</thead>
<tbody><tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row1530184643811"><td class="cellrowborder" valign="top" width="20%" headers="mcps1.3.5.3.2.4.1.1 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p6301046123810">comm_quota_size</p>
</td>
<td class="cellrowborder" valign="top" width="35%" headers="mcps1.3.5.3.2.4.1.2 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p730114623817"><strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b73222950263949">comm_quota_size</strong> controls the size of data transmitted every time in each flow channel. Its default value is <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b192466534863949">1M</strong>.</p>
</td>
<td class="cellrowborder" valign="top" width="45%" headers="mcps1.3.5.3.2.4.1.3 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_a3cf9e37ee1df4ccba156f2e391b80d2a">In a high concurrency scenario, you can increase its value to improve communication performance, but doing so consumes more memory. Optimize this parameter as needed. If you query the <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b66053034563949">pg_total_memory_detail</strong> view of a DN and find that the memory used by the communication layer has reached the threshold of <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b178979855963949">comm_usable_memory</strong>, set <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b9098111063949">comm_quota_size</strong> to a small value, such as <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b36964810063949">512K</strong>.</p>
</td>
</tr>
<tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row130164693815"><td class="cellrowborder" valign="top" width="20%" headers="mcps1.3.5.3.2.4.1.1 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p430146153817">comm_usable_memory</p>
</td>
<td class="cellrowborder" valign="top" width="35%" headers="mcps1.3.5.3.2.4.1.2 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p163014616388"><strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b204878811663949">comm_usable_memory</strong> controls the memory on a DN that can be used for database communication.</p>
</td>
<td class="cellrowborder" valign="top" width="45%" headers="mcps1.3.5.3.2.4.1.3 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p130104613812">The value of this parameter is only used for memory flow control. The default flow control value is 1 MB. If the memory usage exceeds half of the parameter value, the flow control value will be automatically changed to 0.5 MB. If only 20% of the memory specified by the parameter is available, the flow control value will be changed to the allowed minimum, 8 KB.</p>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="section" id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_section0292820125714"><h4 class="sectiontitle">Database Connection Parameters</h4>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_table1693942185715" frame="border" border="1" rules="all"><caption><b>Table 3 </b>Database connection parameters</caption><thead align="left"><tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row1169454295717"><th align="left" class="cellrowborder" valign="top" width="20%" id="mcps1.3.6.2.2.4.1.1"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p17694642135714">GUC Parameter</p>
</th>
<th align="left" class="cellrowborder" valign="top" width="35%" id="mcps1.3.6.2.2.4.1.2"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p206942423575">Description</p>
</th>
<th align="left" class="cellrowborder" valign="top" width="45%" id="mcps1.3.6.2.2.4.1.3"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p136946426579">Configuration Suggestion</p>
</th>
</tr>
</thead>
<tbody><tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row869414219574"><td class="cellrowborder" valign="top" width="20%" headers="mcps1.3.6.2.2.4.1.1 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p897515895712">max_connections</p>
</td>
<td class="cellrowborder" valign="top" width="35%" headers="mcps1.3.6.2.2.4.1.2 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_p1747724831111">Specifies the maximum number of concurrent connections to the database. This parameter affects the concurrent processing capability of the cluster.</p>
</td>
<td class="cellrowborder" valign="top" width="45%" headers="mcps1.3.6.2.2.4.1.3 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_p16477104881120">Retain the default value of this parameter on CNs. Set this parameter on DNs to a value calculated using this formula: Number of CNs x Value of this parameter on a CN.</p>
<p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_p20477148171115">If the value of this parameter is increased, GaussDB(DWS) may require more System V shared memory or semaphore, which may exceed the default maximum value of the OS. In this case, modify the value as needed.</p>
</td>
</tr>
<tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row8694104235719"><td class="cellrowborder" valign="top" width="20%" headers="mcps1.3.6.2.2.4.1.1 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p1146518395814">max_prepared_transactions</p>
</td>
<td class="cellrowborder" valign="top" width="35%" headers="mcps1.3.6.2.2.4.1.2 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_p12478648181115">Specifies the maximum number of transactions that can stay in the <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b107098663663949">prepared</strong> state simultaneously. If the value of this parameter is increased, GaussDB(DWS) requires more System V shared memory than the default system setting.</p>
</td>
<td class="cellrowborder" valign="top" width="45%" headers="mcps1.3.6.2.2.4.1.3 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p968216349572">The value of <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b189443601863949">max_connections</strong> is related to <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b164424360963949">max_prepared_transactions</strong>. Before configuring <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b82420614263949">max_connections</strong>, ensure that the value of <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b159035156163949">max_prepared_transactions</strong> is greater than or equal to that of <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b202815052663949">max_connections</strong>. In this way, each session has a prepared transaction in the waiting state.</p>
</td>
</tr>
<tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row3639174215917"><td class="cellrowborder" valign="top" width="20%" headers="mcps1.3.6.2.2.4.1.1 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p116111194416">session_timeout</p>
</td>
<td class="cellrowborder" valign="top" width="35%" headers="mcps1.3.6.2.2.4.1.2 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p10864192011417">Specifies the maximum duration a database connection can stay idle before it is automatically disconnected.</p>
</td>
<td class="cellrowborder" valign="top" width="45%" headers="mcps1.3.6.2.2.4.1.3 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p184140354428">The value can be an integer in the range 0 to 86400. The minimum unit is second (s). The value <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b190145125963949">0</strong> disables this timeout mechanism. Generally, you are advised not to set this parameter to <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b178253896063949">0</strong>.</p>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="section" id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_section1468154217598"><h4 class="sectiontitle">Other Performance-related Parameters</h4>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_table111141149105914" frame="border" border="1" rules="all"><caption><b>Table 4 </b>Other performance-related parameters</caption><thead align="left"><tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row17114204965910"><th align="left" class="cellrowborder" valign="top" width="20%" id="mcps1.3.7.2.2.4.1.1"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p0114649125910">GUC Parameter</p>
</th>
<th align="left" class="cellrowborder" valign="top" width="30%" id="mcps1.3.7.2.2.4.1.2"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p8114144912594">Description</p>
</th>
<th align="left" class="cellrowborder" valign="top" width="50%" id="mcps1.3.7.2.2.4.1.3"><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p18114849125915">Configuration Suggestion</p>
</th>
</tr>
</thead>
<tbody><tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row8115349185917"><td class="cellrowborder" valign="top" width="20%" headers="mcps1.3.7.2.2.4.1.1 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p362833114416">enable_dynamic_workload</p>
</td>
<td class="cellrowborder" valign="top" width="30%" headers="mcps1.3.7.2.2.4.1.2 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p61081154181612">Specifies whether to enable dynamic load management.</p>
<p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p2269153313413">Dynamic load management refers to the automatic queue control of complex queries based on user loads in a database. This fine-tunes system parameters without manual adjustment.</p>
</td>
<td class="cellrowborder" valign="top" width="50%" headers="mcps1.3.7.2.2.4.1.3 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_p1547319482113">This parameter is enabled by default. Notes:</p>
<ul id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_ul191071342186"><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_li9477912101817">Simple query jobs (which are estimated to require less than 32 MB memory) and non-DML statements (statements other than <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b191625767063949">INSERT</strong>, <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b114675605663949">UPDATE</strong>, <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b16260316963949">DELETE</strong>, and <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b95100157163949">SELECT</strong>) have no adaptive load restrictions. Control the upper memory limits for them on a single CN using <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b195407270963949">max_active_statements</strong>.</li><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_li74778127186">In adaptive load scenarios, the value cannot be increased. If you increase it, memory cannot be controlled for certain statements, such as statements that have not been analyzed.</li><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_li215084151814">Reduce concurrency in the following scenarios, because high concurrency may lead to uncontrollable memory usage.<ul id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_ul4473114810117"><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_li18473184818111">A single tuple occupies excessive memory, for example, a base table contains a column more than 1 MB wide.</li><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_li9473148101111">A query is fully pushed down.</li><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_li4473548161118">A statement occupies a large amount of memory on the CN, for example, a statement that cannot be pushed down or a cursor withholding statement.</li><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_li20473548101117">An execution plan creates a hash table based on the hash join operator, and the table has many duplicate values and occupies a large amount of memory.</li><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_li2473348101118">UDFs are used, which occupy a large amount of memory.</li></ul>
</li><li id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_li114001942774">When configuring this parameter, you can set <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b75374384963949">query_dop</strong> to <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b22219321763949">0</strong> (adaptive). In this case, the system dynamically selects the optimal degree of parallelism (DOP) for each query based on resource usage and the execution plan. The <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b18018045163949">enable_dynamic_workload</strong> parameter supports the dynamic memory allocation.</li></ul>
</td>
</tr>
<tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row1670814264019"><td class="cellrowborder" valign="top" width="20%" headers="mcps1.3.7.2.2.4.1.1 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p10887192943">bulk_write_ring_size</p>
</td>
<td class="cellrowborder" valign="top" width="30%" headers="mcps1.3.7.2.2.4.1.2 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p99326819420">Specifies the size of a ring buffer used for parallel data import.</p>
</td>
<td class="cellrowborder" valign="top" width="50%" headers="mcps1.3.7.2.2.4.1.3 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p13986402013">This parameter affects the database import performance. You are advised to increase the value of this parameter on DNs when a large amount of data is to be imported.</p>
<p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_ad69fdc72c7284d399eb23b7c5e7f56aa">The default value is <strong id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_b6159630363949">2GB</strong>.</p>
</td>
</tr>
<tr id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_row10115193410013"><td class="cellrowborder" valign="top" width="20%" headers="mcps1.3.7.2.2.4.1.1 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p343983710215">data_replicate_buffer_size</p>
</td>
<td class="cellrowborder" valign="top" width="30%" headers="mcps1.3.7.2.2.4.1.2 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_p347834871114">Specifies the memory used by queues when the sender sends data pages to the receiver.</p>
</td>
<td class="cellrowborder" valign="top" width="50%" headers="mcps1.3.7.2.2.4.1.3 "><p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_p1450510314581">The value of this parameter affects the buffer size for data replication between the primary and standby servers.</p>
<p id="EN-US_TOPIC_0000002052813794__en-us_topic_0000001188482248_en-us_topic_0000001211922921_p174780485110">The default value is 16 MB for a CN and 128 MB for a DN. If the server memory is 256 GB, you can increase the value to 512 MB.</p>
</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="dws_04_0414.html">System Optimization</a></div>
</div>
</div>
View Git Blame Copy Permalink