Miscellaneous Parameters
enable_default_ustore_table
Parameter description: Specifies whether to enable the Ustore by default. If this parameter is set to on, all created tables are Ustore tables.
Parameter type: Boolean.
- on indicates that the Ustore is enabled by default.
- off indicates that the Ustore is disabled by default.
Default value: on
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1. Note that the track_counts and track_activities parameters must be enabled when the Ustore table is used. Otherwise, space bloat may occur. In upgrade scenarios, the default value of this parameter preferentially ensures compatibility. That is, the default value after the upgrade is the same as that in the source version.
enable_ustore
Parameter description: Specifies whether to enable the Ustore. If this parameter is set to on, Ustore tables can be created. Note that the track_counts and track_activities parameters must be enabled when the Ustore table is used. Otherwise, space bloat may occur.
This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Value range: [off,on]
Default value: on
enable_segment_datafile_preallocate
Parameter description: Specifies whether to allocate disk space immediately during segment-page file name extension. If this parameter is set to on, the system preferentially uses fallocate to allocate disk space immediately during file expansion. If the system does not support fallocate, the system applies for disk space by writing zeros byte by byte. If this parameter is set to off, the system does not allocate disk space immediately during file expansion and hole files are created.
This is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: [off,on]
Default value: on
reserve_space_for_nullable_atts
Parameter description: Specifies whether to reserve space for the nullable attribute of an Ustore table. If this parameter is set to on, space is reserved for the nullable attribute of the Ustore table by default.
This is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: [off,on]
Default value: on
server_version
Parameter description: Specifies the server version number.
This is a fixed parameter of the INTERNAL type. It can be viewed but cannot be modified. This parameter is not recommended. To query the server version, use the opengauss_version() function.
Value range: a string.
Default value: 9.2.4
server_version_num
Parameter description: Specifies the server version number.
This is a fixed INTERNAL parameter. It can be viewed but cannot be modified.
Value range: an integer
Default value: 90204
block_size
Parameter description: Specifies the block size of the current database.
This is a fixed INTERNAL parameter. It can be viewed but cannot be modified.
Value: 8192
Default value: 8192
segment_size
Parameter description: Specifies the segment file size of the current database.
This is a fixed INTERNAL parameter. It can be viewed but cannot be modified.
Unit: 8 KB
Default value: 131072, that is, 1 GB.
max_index_keys
Parameter description: Specifies the maximum number of index keys supported by the current database.
This is a fixed INTERNAL parameter. It can be viewed but cannot be modified.
Default value: 32
integer_datetimes
Parameter description: Specifies whether the date and time are in the 64-bit integer format.
This is a fixed INTERNAL parameter. It can be viewed but cannot be modified.
Value range: Boolean
- on: yes.
- off: no.
Default value: on
lc_collate
Parameter description: Specifies the locale in which sorting of textual data is done.
This is a fixed INTERNAL parameter. It can be viewed but cannot be modified.
Default value: Determined by the configuration during the database installation and deployment.
lc_ctype
Parameter description: Specifies the locale that determines character classifications. For example, it specifies what a letter and its upper-case equivalent are.
This is a fixed INTERNAL parameter. It can be viewed but cannot be modified.
Default value: Determined by the configuration during the database installation and deployment.
max_identifier_length
Parameter description: Specifies the maximum identifier length.
This is a fixed INTERNAL parameter. It can be viewed but cannot be modified.
Value range: an integer.
Default value: 63
server_encoding
Parameter description: Specifies the database encoding (character set).
By default, gs_initdb will initialize the setting of this parameter based on the current system environment. You can also run the locale command to check the current configuration environment.
This is a fixed INTERNAL parameter. It can be viewed but cannot be modified.
Default value: determined by the current system environment when the database is created.
basebackup_timeout
Parameter description: Specifies the timeout interval for a connection that has no read or write operations after a backup transfer is complete.
When the gs_basebackup tool is used for transmission and a high compression rate is specified, the transmission of the tablespace may time out (the client needs to compress the transmitted data).
Value range: an integer ranging from 0 to INT_MAX. The unit is s. 0 indicates that archiving timeout is disabled.
Default value: 600s
datanode_heartbeat_interval
Parameter description: Specifies the interval at which heartbeat messages are sent between heartbeat threads. You are advised to set this parameter to a value no more than wal_receiver_timeout/2.
This is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 1000 to 60000. The unit is ms.
Default value: 1s
max_concurrent_autonomous_transactions
Parameter description: Specifies the maximum number of autonomous transaction connections, that is, the maximum number of concurrent autonomous transactions executed at the same time. If this parameter is set to 0, autonomous transactions cannot be executed.
Parameter type: integer.
Unit: none
Value range: 0–10000. The theoretical maximum value is 10000, and the actual maximum value is a dynamic value. The calculation formula is 262143 – job_queue_processes – autovacuum_max_workers – max_inner_tool_connections – max_connections – AUXILIARY_BACKENDS – AV_LAUNCHER_PROCS. The values of job_queue_processes, autovacuum_max_workers, max_inner_tool_connections, and max_connections depend on the settings of the corresponding GUC parameters. AUXILIARY_BACKENDS indicates the number of reserved auxiliary threads, which is fixed to 20. AV_LAUNCHER_PROCS indicates the number of reserved launcher threads for autovacuum, which is fixed to 2.
Default value: 200 (196-core CPU/1536 GB memory, 128-core CPU/1024 GB memory, 104-core CPU/1024 GB memory, 96-core CPU/1024 GB memory); 150 (96-core CPU/768 GB memory); 120 (80-core CPU/640 GB memory) 100 (64-core CPU/512 GB memory); 80 (60-core CPU/480 GB memory); 40 (32-core CPU/256 GB memory); 20 (16-core CPU/128 GB memory); 10 (8-core CPU/64 GB memory, 4-core CPU/32 GB memory, 4-core CPU/16 GB memory)
Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Suggestion: Set this parameter based on actual service requirements and hardware configurations. It is recommended that this parameter be set to a value less than or equal to 1/10 of max_connections. If you only increase the value of this parameter but do not adjust the memory parameters in the same proportion, the memory may be insufficient and the error message "memory is temporarily unavailable" is displayed when the service load is heavy.
If the value range of this parameter is changed during the upgrade and the value is changed before the commit operation, you need to change the value range to the value allowed before the upgrade if you roll back the upgrade. Otherwise, the database may fail to be started.
enable_seqscan_fusion
Parameter description: Specifies whether to enable SeqScan optimization.
This is a SUSET parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
- on indicates that SeqScan optimization is enabled.
- off indicates that SeqScan optimization is disabled.
Default value: off
This parameter can be used to optimize only the execution time of the SeqScan operator in the EXPLAIN ANALYZE statement.
cluster_run_mode
Parameter description: Specifies whether a DN belongs to the primary or standby database instance in the dual-database instance DR scenario. For a single database instance, use the default primary database instance.
This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Value range: enumerated values
- cluster_primary indicates the primary database instance.
- cluster_standby indicates the standby database instance.
Default value: cluster_primary
acceleration_with_compute_pool
Parameter description: Determines whether to use the computing resource pool for acceleration when an OBS is queried. (Due to specification changes, the current version no longer supports this feature. Do not use it.)
This is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
- on indicates that the query covering the OBS is accelerated based on the cost when the computing resource pool is available.
- off indicates that no query is accelerated using the computing resource pool.
Default value: off
dfs_partition_directory_length
Parameter description: Specifies the maximum directory name length for the partition directory of a table partitioned by VALUE in the HDFS.
This is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: 92 to 7999
Default value: 512
max_resource_package
Parameter description: Specifies the maximum number of threads that each DN can run concurrently on an acceleration database instance on the cloud.
This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Value range: 0 to 2147483647
Default value: 0
enable_gpi_auto_update
Parameter description: Determines whether global indexes are updated by default in partition DDL commands.
This is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
- The value on indicates that global indexes are updated regardless of whether the partition DDL commands contain the UPDATE GLOBAL INDEX clause.
- The value off indicates that global indexes are not updated unless the partition DDL commands contain the UPDATE GLOBAL INDEX clause.
Default value: off
enable_gsplsql_execopt
Parameter description: Determines whether to optimize the execution of stored procedure functions in Tuple mode.
- Statement mode: Use the perform or call procedure() function to call stored procedures, during which every SQL statement in the stored procedures is executed separately.
- Tuple mode: Use targetlist or trigger to call stored procedures. The calling environment is one tuple input in the execution state.
This is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
- on indicates that when a stored procedure function is cyclically executed in Tuple mode, the information that does not change during the initialization of the stored procedure is allocated and recorded to avoid invalid repeated calling during the execution.
- off indicates that the default calling logic is used and information is initialized in each loop.
Default value: on
multi_insert_min_rows
Parameter description: Specifies the minimum estimated number of rows to be inserted in batches. You can run explain (verbose on) to check whether the execution plan contains "Batch Insert."
Parameter type: integer.
Unit: none
Value range: –1 to 2147483647. –1 indicates that batch insertion is not used. If the value is greater than or equal to 0, the estimated number of rows to be inserted is greater than or equal to multi_insert_min_rows, and batch insertion is used for the INSERT SELECT statement to improve performance.
Default value: 1000
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: You are advised to use the default value or a value greater than 1000. If the number of inserted data rows is small, batch insertion may deteriorate the insertion performance.
- Specification
By default, the batch insertion rate of an Astore table can be improved by about 50%, and the batch insertion rate of a Ustore table can be improved by about 60%.
- Constraint
In addition to the multi_insert_min_rows parameter constraint, batch insertion has the following constraints:
- Statements other than INSERT SELECT are not supported.
- Tables that contain BEFORE and INSTEAD triggers are not supported because the data to be inserted may be changed.
- SQL statements cannot contain the volatile function because a new transaction is started each time a volatile function is executed. However, if batch insertion is used, the snapshot obtained by the volatile function may be different from that obtained by common insertion.
- The SQL statement cannot contain RETURNING.
- The UPSERT clause is not supported.
- Batch insertion cannot be used for unlogged tables, temporary tables, or global temporary tables. Otherwise, the performance may deteriorate.
- Tables containing auto increment columns are supported only when b_format_version is set to '5.7' or b_format_dev_version is set to 's2'.
Example:
-- Create tables and data.
create table t(id int, c1 varchar(20), c2 varchar(40), c3 varchar(50)) with (storage_type=astore);
create table t_tmp(id int, c1 varchar(20), c2 varchar(40), c3 varchar(50)) with (storage_type=astore);
insert into t_tmp values (generate_series(1, 1000), '1111111111111111111', '222222222222222222222', '333333333333333333333333333333333');
analyze t_tmp;
-- Insert 1000 rows of data for 1000 times. Batch insertion takes 1.2s while non-batch insertion takes 2.68s. Using batch insertion improves the performance by more than 50%.
set multi_insert_min_rows=-1;
DECLARE
start_time bigint;
end_time bigint;
BEGIN
start_time := dbe_utility.get_time();
for i in 1 .. 1000 loop
insert into t select * from t_tmp;
end loop;
end_time := dbe_utility.get_time();
dbe_output.print_line('Time consumed:' ||(end_time - start_time)/100);
END;
/
Time consumed: 2.68
set multi_insert_min_rows=0;
DECLARE
start_time bigint;
end_time bigint;
BEGIN
start_time := dbe_utility.get_time();
for i in 1 .. 1000 loop
insert into t select * from t_tmp;
end loop;
end_time := dbe_utility.get_time();
dbe_output.print_line('Time consumed:' ||(end_time - start_time)/100);
END;
/
Time consumed: 1.2
enable_force_smp
Parameter description: Specifies whether to forcibly enable SMP. When this parameter is enabled, the cost of starting the stream thread is empty by default. When the degree of parallelism is set and the operator supports parallelism, the parallel path is forcibly selected.
This is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
- on indicates that SMP is forcibly enabled.
- off indicates that SMP is not forcibly enabled.
Default value: off
- This parameter is valid only for operators that support SMP. For details, contact the administrator.
- In addition, the constraints on forcibly enabling SMP are as follows:
- The SMP feature improves the performance through operator parallelism and occupies more system resources, including CPU, memory, and I/O. It is used to save time at the cost of resources. By setting parallelism, SMP improves system performance in appropriate scenarios when resources are sufficient.
- If the scenarios are inappropriate (for example, the data volume is small) or resources are insufficient, the performance may deteriorate.
- This parameter is not supported in scenarios where SMP is not applicable.
enable_partrouting_optimization
Parameter description: Specifies whether to optimize the insertion of partitioned tables.
This is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
- on indicates that the insertion of partitioned tables is optimized. For the INSERT SELECT statement, if the SELECT statement contains a constant partition key, partition routing needs to be performed only once for the INSERT statement, improving performance.
- off indicates that the insertion of partitioned tables is not optimized. Before inserting each piece of data, you need to perform partition routing to determine the partitioned table to be inserted.
Default value: on
- Only the INSERT SELECT statement is supported.
- INSERT INTO ta SELECT FROM tb: For all partition key values inserted to table a, the column values in the corresponding SELECT result set must be constants. (This column is optional because the default values are constants.)
(1) INSERT INTO ta SELECT c,d FROM b WHERE tb.c='1' or SELECT '1' as c, d FROM tb: The result column tb.c is a constant.
(2) INSERT INTO ta SELECT c,d FROM b WHERE tb.c=func('1');: If func() is neither a volatile function nor a stable/immutable function that contains non-constant parameters, tb.c can be determined as a constant.
(3) If the column value is the return value of an aggregate function, for example, INSERT INTO ta SELECT count(c),d FROM b WHERE tb.c='1' group by c, d;, the count(c) cannot be determined as a constant.
- The UPSERT clause is not supported.
- Tables that contain BEFORE and INSTEAD triggers are not supported because the data to be inserted may be changed.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
