This blog is in continuation to our blog on Counter Data Analysis, and is the third and final blog in the Replication Server Performance Tuning series. |
This is the last in our three-blog series on tuning performance for SAP Replication Server. Now that we’ve identified some problems through counter data analysis, we can adjust some memory-related configurations.
You can use counter categories (see previous blog,
Counter Data Analysis) to configure memory use in Replication Server, which will help you tune performance and eliminate bottlenecks.
In this blog, I’ll talk about the following memory-related configurations:
- EXEC (LTL)
- CAPTURE (CI)
- SQT
- DIST
- DSI/S
- SQM
- SPQ
- MEMORY CONTROL
EXEC Module (used in LTL mode)
First, let’s take a look at some configurations for the
flow control time duration setting. We'll focus on the following counters here:
- exec_nrm_request_limit
- exec_sqm_write_request_limit
- exec_max_cache_size
exec_nrm_request_limit
After you set
nrm_thread to
ON, use the
exec_nrm_request_limit parameter with
configure replication server or
alter connection to specify the total amount of memory available to the RepAgent Executor thread for the message queue the NRM threads.
Default values:
- 32-bit: 1,048,576 bytes (1MB)
- 64-bit: 8,388,608 bytes (8MB)
- Maximum: 2,147,483,647 bytes (2GB)
- Minimum: 16,384 bytes (16KB)
If the total amount of memory used by commands on the message queue is larger than what you specify with
exec_nrm_request_limit, the RepAgent Executor thread sleeps, and waits for memory to become available. As the NRM thread processes commands on the message queue, it frees the memory for the RepAgent Executor thread.
For example, to set
exec_sqm_nrm_request_limt to 1GB for the connection to the pubs2 database in the SYDNEY_DS data server, enter:
alter connection to SYDNEY_DS.pubs2set exec_nrm_request_limit to ‘1073741824’
exec_sqm_write_request_limit
The
exec_sqm_write_request_limit database configuration parameter controls and specifies the amount of memory available to the LTI or RepAgent executor thread to queue up outstanding write requests. In short, it controls the number of outstanding bytes of memory.
Once the limit is reached, the RepAgent executor thread sleeps until some of the outstanding writes have been completed. Therefore, you should set this parameter at the connection level using
alter connection.
- Default: 1 MB
- Minimum: 16 KB
- Maximum: 2 GB
exec_max_cache_size
Use
exec_max_cache_size to specify the amount of memory to allocate for the Executor command cache. If the cache doesn’t have enough memory, replication slows down, or stops completely.
To avoid performance degradation, you can either add more memory to the cache with
exec_max_cache_size, or disable table metadata reduction in your Replication Agent.
You can set values from 0 to 2,147,483,647 bytes for a 32-bit Replication Server, and 0 to 2,251,799,813,685,247 bytes for a 64-bit Replication Server. The default is 1,048,576 bytes for both 32-bit and 64-bit Replication Servers.
For example:
To set the Executor command cache size to 2,097,152 bytes at the Server level for all primary database connections to Replication Server, enter:
configure replication server
set exec_max_cache_size to '2097152'
To set this counter at the Connection level for a specific primary database connection, enter:
alter connection to dataserver_name.database_name
set exec_max_cache_size to '2097152'
Replication Server always uses the connection level setting if there are settings at both levels. You do not need to restart Replication Server for the change to take effect.
CI
Next, let's look at configurations for the CI mode. We'll focus on the following counters here:
- ci_pool_size
- ci_package_size
- ci_max_cmds
- cap_sqm_write_request_limit
ci_pool_size
This is a stream replication parameter that specifies the number of packages in a stream replication buffer pool. When stream replication begins, the stream replication buffer pool pre-allocates the number of stream replication packages in the pool as specified by
ci_pool_size. The package is fetched from this pool when needed.
- Value: 10 to 10,000
- Default: 50
ci_package_size
This is a stream replication parameter that specifies the size of a stream replication package. Each stream replication package in the stream replication buffer pool shares the same size specified by
ci_package_size.
- Value: 2,048 (bytes) to 104,857,600 (bytes)
- Default: 1,048,576 (bytes)
ci_max_cmds
This is a stream replication parameter that specifies the maximum number of commands that can be added to a stream replication package.
- Value: 1 to 1,000,000
- Default: 80
cap_sqm_write_request_limit
Use this configuration to specify the number of concurrent parsers in the Capture. In a Capture, multiple parsers process different stream replication packages concurrently. Increasing the number of parsers improves the throughput of the Capture.
- Value: 1 to 20
- Default: 2
SQT
In this section, let's look over the configurations for the SQT setting. We'll focus on the following counters here:
- sqt_max_prs_size
- sqt_max_cache_size
sqt_max_prs_size
Maximum memory consumed by commands that are unpacked by the transaction profiling processes for HVAR and RTL, measured in bytes.
For 32-bit Replication Server:
- Default: 2,147,483,647 (2GB)
- Minimum: 0
- Maximum: 2,147,483,647
For 64-bit Replication Server:
- Default: 2,147,483,647 (2GB)
- Minimum: 0
- Maximum: 2,251,799,813,685,247
Use the
configure replication server command to set the parameter at the server level for all connections or use
alter connection at the database level for individual connections. The default value at the database level is 0. If you retain the database-level default or reset to the default, Replication Server uses the value set at the server level.
sqt_max_cache_size
Use
sqt_max_cache_size for sizing the SQT cache to the maximum SQT cache memory, in bytes.
For 32-bit Replication Server:
- Default: 1,048,576
- Minimum: 0
- Maximum: 2,147,483,647
For 64-bit Replication Server:
- Default: 20,971,520
- Minimum: 0
- Maximum: 2,251,799,813,685,247
DIST
Next, we'll look at the configurations for the DIST setting. We'll focus on the following counters here:
- dist_sqt_max_cache_size
- md_sqm_write_request_limit
dist_sqt_max_cache_size
The maximum SQT cache size for the inbound queue in bytes. The default, 0, means the current setting of the
sqt_max_cache_size parameter is being used as the maximum cache size for the connection.
For 32-bit Replication Server:
- Minimum: 0
- Maximum: 2,147,483,647
For 64-bit Replication Server:
- Minimum: 0
- Maximum: 2,251,799,813,685,247
md_sqm_write_request_limit
This configuration sets the amount of memory available to the Distributor for messages waiting to be written to the outbound queue.
- Default: 1MB
- Minimum: 16K
- Maximum: 2G
DSI/S
In this section, let's look at the the configuration for the DSI/S setting. We'll focus on the following counters here:
- dsi_sqt_max_cache_size
- dsi_cdb_max_size (DSI/E)
- dsi_compile_max_cmds (DSI/E)
dsi_sqt_max_cache_size
Maximum SQT (Stable Queue Transaction) interface cache size for the outbound queue in bytes. The default, 0, means the current setting of the
sqt_max_cache_size parameter is being used as the maximum cache size for the connection.
For 32-bit Replication Server:
- Minimum: 0
- Maximum: 2GB (2,147,483,648 bytes)
For 64-bit Replication Server:
- Minimum: 0
- Maximum: 2 petabytes (2,251,799,813,685,247 bytes)
dsi_cdb_max_size
Maximum net-change in the database size that Replication Server can generate for HVAR or RTL, measured in megabytes.
- Default: 1024
- Minimum: 0
- Maximum: 2,147,483,647
In HVAR, Replication Server uses
dsi_cdb_max_size as a threshold to:
- Detect large transactions that are then replicated using the continuous replication mode
- Stop grouping small, compile-ready transactions into a group requiring a net-change database that is larger than dsi_cdb_max_size
In RTL, Replication Server uses
dsi_cdb_max_size to flush large transaction groups incrementally using full incremental compilation.
dsi_compile_max_cmds
Specifies, in number of commands, the maximum size of a group of transactions. When HVAR or RTL reaches the maximum group size for the current group that it is compiling, HVAR or RTL starts a new group.
If there is no more data to read, and even if the group does not reach the maximum number of commands, HVAR or RTL completes grouping the current set of transactions into the current group.
To set this parameter, use
configure replication server for server-level or
alter connection for database-level.
- Minimum: 100
- Default: 10,000
You must set
dsi_compile_enable to
ON to use
dsi_compile_max_cmds.
SQM
Next, let's look over the counters for the SQM setting. We'll focus on the following counters here:
- cap_sqm_write_request_limit
- sqm_cache_size
- sqm_page_size
- sqm_cmd_cache_size
- sqm_max_cmd_in_block
- block_size to 'value' with shutdown
cap_sqm_write_request_limit
Use this configuration to set the number of concurrent parsers in the Capture. In a Capture, multiple parsers process different stream replication packages concurrently. Increasing the number of parsers improves the throughput of the Capture.
- Value: 1 to 20
- Default: 2
sqm_cache_size
Sets server-wide stable queue cache size by specifying the number of pages in cache where size of a page is specified by
sqm_page_size. Enclose the number of pages in single quotes or double quotes.
- Range: 1 to 4096 pages
- Default: 16 pages
sqm_page_size
Sets the server-wide stable queue page size by specifying the number of 16KB blocks in a page. Configuring the page size also sets the I/O size of SAP Replication Server. For example, setting page size to 4 instructs Replication Server to write to the stable queue in 64K blocks.
Enclose page sizes in single quotes or double quotes.
- Range: 1 to 64 pages
- Default: 4 pages
sqm_cmd_cache_size
Use the SQM command cache to store unpacked data from the Executor thread that the Distributor thread can retrieve directly, and therefore improve replication performance.
Use the
sqm_cmd_cache_size and
sqm_max_cmd_in_block parameters to set the the SQM command cache memory configuration. You can configure
cmd_direct_replicate,
sqm_cmd_cache_size and
sqm_max_cmd_in_block in the same command or separately.
Guidelines for Setting SQM Command Cache Memory Configuration
The SQM command cache memory configuration settings depend on the total amount of memory available to Replication Server, the number of inbound queues, and the transaction profile, which depends on the command size. When setting the SQM command cache memory configuration:
Example 1
To set the configuration for all connections and queues for a 64-bit Replication Server:
configure replication server
set cmd_direct_replicate to 'on' set sqm_cmd_cache_size to '40971520'
set sqm_max_cmd_in_block to '640'
go
Example 2
To set the configuration for the connection to the pdb1primary database in the TOKYO_DS data server and for inbound queue number 2 for a 32-bit Replication Server:
alter connection to TOKYO_DS.pdb1 set cmd_direct_replicate to 'on'
go
alter queue 2, 1, set sqm_cmd_cache_size to '2048576'
set sqm_max_cmd_in_block to '640'
go
sqm_max_cmd_in_block
Specifies, in each SQM block, the maximum number of entries with which the parsed data can associate.
- Default: 320
- Minimum: 0
- Maximum: 4096
Set the value of
sqm_max_cmd_in_block to the number of entries in the SQM block. Depending on the data profile, each block has a different number of entries because the block size is fixed, and the message size is unpredictable. If you set a value that is too large, there is memory waste. If you set a value that is too small, replication performance is compromised.
Replication Server ignores any value you set for
sqm_max_cmd_in_block if
cmd_direct_replicate or
sqm_cache_enable is off.
block_size to 'value' with shutdown
Specifies the queue block size which is the number of bytes in a contiguous block of memory used by stable queue structures.
- Valid values: 16KB, 32KB, 64KB, 128KB, or 256KB
- Default: 16KB
When you execute the command to change the block size, Replication Server shuts down. You must include the
with shutdown clause after specifying the block size in versions prior to Replication Server 15.6. In version 15.6 and later, the
with shutdown clause is optional; you need not restart Replication Server for the change in queue block size to take effect. You should change this parameter only with the
configure replication server command. Doing otherwise corrupts the queues.
SPQ
Next, take a look at the counter for the SQM setting. We'll focus on the following counter here:
spq_cache_size
A stream replication parameter that sets the SPQ cache size, which is a memory pool for caching en-queue data before it is written on disk and reading the de-queue data beforehand.
- Value: 1 KB to 100 MB
- Default: 10 MB
Memory control
Finally, let's look at the counters for the memory control setting. We'll focus on the following counters here:
- memory_limit
- memory_control
memory_limit
Changes to the
memory_limit configuration parameter impacts performance on 32-bit Replication Server and 64-bit Replication Server.
This is the maximum total memory the Replication Server can use, in megabytes. Values for several other configuration parameters are directly related to the amount of memory available from the memory pool indicated by memory_limit. These include:
fstr_cachesize,md_source_memory_pool, queue_dump_buffer_size,sqt_max_cache_size, sre_reserve, and sts_cachesize.
Valid Range for 32-Bit:
Valid Range for 64-Bit:
memory_control
The server-level configuration parameter,
memory_control, manages the memory control behavior of threads. Valid values for
memory_control are
enable (default) or
disable. In this way, Replication Server controls the memory consumption and does not shut down due to memory issues.
Use
configure replication server to alter the default values for the new configuration parameters.
Use
admin config to view the default or existing values.
Use
admin who to provide information on the memory control behavior of the thread:
State |
Description |
Controlling Mem |
The thread is executing memory control. |
Sleeping For Mem |
The thread is sleeping until memory is available. |
