Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

Query

Updated on 2024-06-03 GMT+08:00

instr_unique_sql_count

Parameter description: Specifies the maximum number of unique SQL records to be collected. The value 0 indicates that the function of collecting unique SQL information is disabled.

If the value is changed from a larger one to a smaller one, unique SQL statistics will be reset and re-collected (the standby node does not support this function). There is no impact if the value is changed from a smaller one to a larger one.

When the number of unique SQL records generated in the system (to view the statistics, query dbe_perf.statement or dbe_perf.summary_statement) is greater than the value of instr_unique_sql_count, the extra unique SQL records are not collected.

In the x86-based centralized deployment scenario, the hardware configuration specifications are 32U 256 GB memory. When the Benchmark SQL 5.0 tool is used to test performance, the performance fluctuates by about 3% by enabling or disabling this parameter.

This is a SIGHUP parameter. Set it based on instructions in Table 1.

Value range: an integer ranging from 0 to 2147483647

Default value: 200000

instr_unique_sql_combination_options

Parameter description: Specifies the configuration items of combining unique SQL statements of the same type. The value of this parameter consists of multiple configuration items separated by commas (,).

If this feature is enabled, the IDs of unique SQL statements of the same type are normalized, and the generated unique SQL strings are normalized.

Parameter type: string

Unit: none

Value range: See Table 1.

NOTICE:
  • When configuring the combination function, set the character string based on Table 1. Use commas (,) to separate multiple configuration items, for example, set instr_unique_sql_combination_options='in_clause';.
  • If this parameter is left blank, this function is disabled, for example, set instr_unique_sql_combination_options='';.
Table 1 Configuration items of the combination function

Configuration Item

Behavior Control

in_clause

Combines only fixed parameters and precompiled binding parameters in the IN clause of the SELECT IN() statement.

Example 1: select * from table where column in (1,2,3);

Unique SQL string after combination: select * from table where column in (1... n);

Example 2: select * from table where column in ($1,$2,$3);

Unique SQL string after combination: select * from table where column in (1... $n);

Example 3: select * from example_table where column in (1,2,$1,3,$2);

Unique SQL string after combination: select * from example_table where column in (1...n,$1...$n);

Example 4: select * from example_table where (column1, column2) in ((1, 'a'), (2, 'b'), (3, 'c'));

Unique SQL string after combination: select * from example_table where (column1, column2) in ((1...n));

Default value: 'in_clause'

Setting method: This is a USERSET parameter. Set it based on instructions in Table 1.

Setting suggestion: By default, this function is disabled during the upgrade and enabled when a new instance is delivered.

CAUTION:
  • If this feature is used, the unique SQL IDs of the query statements involved in combination are changed, which affects the SQL statements for which SQL patch has been created.
  • For values of the bigint, real, float4, blob, numeric, decimal, number, dec, or integer type, if in() contains a single parameter or multiple parameters, two different unique_sql_id values are generated.

instr_unique_sql_track_type

Parameter description: Specifies which SQL statements are recorded in Unique SQL.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: enumerated values
  • top: Only top-level SQL statements are recorded.
  • all: All SQL statements are recorded.

Default value: all

unique_sql_retention_time

Parameter description: Specifies the interval for cleaning the unique SQL hash table. The default value is 30 minutes.

This is a SIGHUP parameter. Set it based on instructions in Table 1.

Value range: an integer ranging from 1 to 3650. The unit is minute.

Default value: 30min

enable_instr_rt_percentile

Parameter description: Specifies whether to enable the function of calculating the response time of 80% and 95% SQL statements in the system.

This is a SIGHUP parameter. Set it based on instructions in Table 1.

Value range: Boolean

  • on indicates that the function is enabled.
  • off indicates that the function is disabled.

Default value: on

percentile

Parameter description: Specifies the percentage of SQL statements whose response time is to be calculated by the background calculation thread.

This is an INTERNAL parameter. Set it based on instructions in Table 1.

Value range: a string.

Default value: "80,95"

instr_rt_percentile_interval

Parameter description: Specifies the interval at which the background calculation thread calculates the SQL response time.

This is a SIGHUP parameter. Set it based on instructions in Table 1.

Value range: an integer ranging from 0 to 3600. The unit is s.

Default value: 10s

enable_instr_cpu_timer

Parameter description: Specifies whether to capture the CPU time consumed during SQL statement execution.

In the x86-based centralized deployment scenario, the hardware configuration specifications are 32U 256 GB memory. When the Benchmark SQL 5.0 tool is used to test performance, the performance fluctuates by about 3.5% by enabling or disabling this parameter.

This is a SIGHUP parameter. Set it based on instructions in Table 1.

Value range: Boolean

  • on indicates that the CPU time consumed during SQL statement execution is captured.
  • off indicates that the CPU time consumed during SQL statement execution is not captured.

Default value: on

enable_slow_query_log (Discarded)

Parameter description: Specifies whether to write the slow query information to the log file. This parameter is discarded in this version.

This is a SIGHUP parameter. Set it based on instructions in Table 1.

Value range: Boolean

  • on: indicates that slow query information needs to be written into log files.
  • off: indicates that slow query information does not need to be written into log files.

Default value: on

query_log_file (Discarded)

Parameter description: Specifies the name of a slow query log file on the server. If enable_slow_query_log is set to ON, slow query records are written into log files. Only the sysadmin user can access this parameter. Generally, log file names are generated in strftime mode. Therefore, the system time can be used to define log file names, which are implemented using the escape character %. This function has been discarded in this version.

This is a SIGHUP parameter. Set it based on instructions in Table 1.

NOTICE:

You are advised to use the escape character % to specify the log file names for efficient management of log files.

Value range: a string

Default value: slow_query_log-%Y-%m-%d_%H%M%S.log

query_log_directory (Discarded)

Parameter description: Specifies the directory for storing low query log files when enable_slow_query_log is set to on. Only the sysadmin user can access this parameter. It can be an absolute path or a relative path (relative to the data directory), which has been discarded in this version.

This is a POSTMASTER parameter. Set it based on instructions in Table 1.

NOTICE:

If query_log_directory is set to an invalid path, the cluster cannot be started.

NOTE:

Valid path: Users have read and write permissions on the path.

Invalid path: Users do not have read or write permission on the path.

Value range: a string

Default value: specified during installation.

asp_log_directory

Parameter description: Specifies the directory for storing ASP log files on the server when asp_flush_mode is set to all or file. The value can be an absolute path, or relative to the data directory. Only the sysadmin user can access this parameter.

This is a POSTMASTER parameter. Set it based on instructions in Table 1.

NOTICE:

If asp_log_directory is set to an invalid path, the cluster cannot be started.

NOTE:
  • Valid path: Users have read and write permissions on the path.
  • Invalid path: Users do not have read or write permission on the path.

Value range: a string

Default value: specified during installation.

perf_directory

Parameter description: Specifies the directory of the output file of the performance view dotting task. Only the sysadmin user can access this parameter. The value can be an absolute path, or relative to the data directory.

This is a POSTMASTER parameter. Set it based on instructions in Table 1.

NOTE:
  • Valid path: Users have read and write permissions on the path.
  • Invalid path: Users do not have read or write permission on the path.

Value range: a string

Default value: specified during installation.

enable_stmt_track

Parameter description: Specifies whether to enable the full/slow SQL statement feature.

In the x86-based centralized deployment scenario, the hardware configuration specifications are 32U 256 GB memory. When the Benchmark SQL 5.0 tool is used to test performance, the performance fluctuates by about 1.2% by enabling or disabling this parameter.

This is a SIGHUP parameter. Set it based on instructions in Table 1.

Value range: Boolean

  • on: Full/Slow SQL capture is enabled.
  • off: Full/Slow SQL capture is disabled.

Default value: on

track_stmt_parameter

Parameter description: After track_stmt_parameter is enabled, the executed statements recorded in statement_history are not normalized. The complete SQL statement information can be displayed to help the database administrator locate faults. For a simple query, the complete statement information is displayed. For a PBE statement, the complete statement information and information about each variable value are displayed. The format is "query string; parameters:$1=value1,$2=value2, ...". This parameter is used to display full SQL information for users and is not controlled by the track_activity_query_size parameter. When the SQL bypass logic is used for PBE statements, parameters are directly delivered to DNs. Therefore, the number of complete statements cannot be obtained by querying statement_history on CNs. In addition, DNs do not have query character strings. Therefore, complete statement information cannot be obtained by querying statement_history on DNs.

This is a SIGHUP parameter. Set it based on instructions in Table 2.

Value range: Boolean

  • on: The function of displaying complete SQL statement information is enabled.
  • off: The function of displaying complete SQL statement information is disabled.

Default value: off

track_stmt_session_slot

Parameter description: Specifies the maximum number of full/slow SQL statements that can be cached in a session. If the number of full/slow SQL statements exceeds this value, new statements will not be traced until the flush thread flushes the cached statements to the disk to reserve idle space.

Parameter type: integer.

Unit: none

Value range: 0 to 2147483647

Default value: 1000

Setting method: This is a SIGHUP parameter. Set it based on instructions in Table 1.

Setting suggestion: Retain the default value, that is, the maximum number of full SQL slots that can be reserved for each session. If the value of this parameter is too large, a large amount of memory is occupied. If the value of this parameter is too small, full SQL statements may be lost.

track_stmt_details_size

Parameter description: Specifies the maximum size of execution events that can be collected by a single statement.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: an integer ranging from 0 to 100000000. The unit is byte.

Default value: 4096

track_stmt_retention_time

Parameter description: Specifies the retention period of full/slow SQL statement records. This is a combination of parameters. This parameter is read every 60 seconds and records exceeding the retention period are deleted. Only the sysadmin user can access this parameter.

This is a SIGHUP parameter. Set it based on instructions in Table 1.

Value range: a string consisting of two parts in the format of 'full sql retention time, slow sql retention time'.

  • full sql retention time indicates the retention period of full SQL statements. The value ranges from 0 to 86400. The unit is second.
  • slow sql retention time indicates the retention period of slow SQL statements. The value ranges from 0 to 604800. The unit is second.

Default value: 3600,604800

track_stmt_stat_level

Parameter description: Determines the level of statement execution tracing.

Parameter type: character

Unit: none

Value range:

This parameter consists of two parts in the format of 'full sql stat level, slow sql stat level'.

  • The first part indicates the tracing level of full SQL statements. The value can be OFF, L0, L1, or L2.
  • The second part indicates the tracing level of slow SQL statements. The value can be OFF, L0, L1, or L2.
    NOTE:

    If the tracing level of full SQL statements is not OFF, the current SQL statement tracing level is a higher level (L2 > L1 > L0) of full and slow SQL statements. For details about the levels, see "System Catalogs and System Views > System Catalogs > STATEMENT_HISTORY > STATEMENT_HISTORY columns" in Developer Guide.

Default value: "OFF,L0"

Setting method: This is a USERSET parameter. Set it based on instructions in Table 1.

Setting suggestion: Retain the default value. If the full SQL tracing function is enabled, the performance is affected and a large amount of disk space may be occupied.

track_stmt_standby_chain_size

Parameter description: Specifies the maximum memory and disk space occupied by fast/slow SQL statement records on the standby node. This is a combination of parameters. Only the SysAdmin user can access the database.

This is a SIGHUP parameter. Set it based on instructions in Table 1.

Value range: a string

This parameter consists of four parts in the format of 'Full SQL memory size, Full SQL disk size, Slow SQL memory size, Slow SQL disk size'.

Full SQL and slow SQL statements are stored in different locations. Therefore, four additional values are used for control.

  • Full SQL memory size indicates the maximum memory space reserved for fast SQL statements. The value range is [16,1024], in MB.
  • Full SQL disk size indicates the maximum disk space occupied by reserved fast SQL statements. The value range is [512,1048576], in MB.
  • Slow SQL memory size indicates the maximum memory space reserved for slow SQL statements. The value range is [16,1024], in MB.
  • Slow SQL disk size indicates the maximum disk space reserved for slow SQL statements. The value range is [512,1048576], in MB.

The memory size cannot be greater than the disk size.

Default value: 32, 1024, 16, 512

track_stmt_flush_mode

Parameter description: Specifies the storage mode of full SQL statements.

Parameter type: character

Unit: none

Value range:

This parameter consists of two parts in the format of 'full sql flush mode, slow sql flush mode':

  • The first part indicates the full SQL tracing mode. The value can be MEMORY or FILE. If this parameter is set to MEMORY, full SQL statements are recorded in the memory. If this parameter is set to FILE, full SQL statements are recorded in disk files.
  • The second part indicates the slow SQL tracing mode. In the current version, the value can only be FILE. If this parameter is set to FILE, slow SQL statements are recorded in disk files.

Default value: "FILE,FILE"

Setting method: This is a SIGHUP parameter. Set it based on instructions in Table 1.

Setting suggestion: If this parameter is set to "MEMORY,FILE", the kernel supports full SQL statements, which occupies certain shared memory. The memory size is specified by the track_stmt_shm_size parameter.

track_stmt_shm_size

Parameter description: Specifies the size of the full SQL shared memory.

Parameter type: integer.

Unit: byte

Value range: 134217728 to 1073741824

Default value: 134217728

Setting method: This is a SIGHUP parameter. Set it based on instructions in Table 1.

concurrent_dml_mode

Parameter description: Specifies how to handle concurrent DML execution conflicts (lock conflicts) in a table where the function of updating distribution columns is enabled (that is, the table attribute enable_update_distkey is set to on).

This parameter takes effect only on DNs.

Parameter type: integer.

Unit: none

Value range: 0 and 1

  • 0: loose mode. That is, after a conflict occurs, the DML statement that obtains the lock later does not report an error.
  • 1: strict mode. If a conflict occurs, the DML statement that obtains the lock later terminates and reports an error.

Default value: 0

Setting method: This is a SIGHUP parameter. Set it based on instructions in Table 1.

Setting suggestion: Set this parameter based on the value range. If the DML statement that obtains the lock later does not report an error after a conflict is expected, set this parameter to 0. If the DML statement that obtains the lock later terminates and reports an error after a conflict is expected, set this parameter to 1.

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback