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:

  • Increase sqm_cmd_cache_size if the Replication Server has a large total SQM cache.
    Total SQM cache = sqm_cache_size (in pages) * sqm_page_size (in blocks) * block_size (in kilobytes)
  • Decrease sqm_max_cmd_in_block if command size or table row size is large.
  • Increase sqm_max_cmd_in_block if block_size is large.

 

 

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

 

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:

  • Min: 0
  • Max: 2047

Valid Range for 64-Bit:

  • Min: 0
  • Max: 2147483647

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.

 

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply