ApsaraDB for MongoDB:Parameter tuning recommendations

operationProfiling.mode

• Supported major versions: 3.0 or later

• Restart required: Yes

• Default value: off

• Function: Specifies the profiling level for the query profiler.

• Issues:

  • If this parameter is set to all or slowOp, the large volume of generated slow query logs can degrade instance performance and complicate analysis.

  • Some users may be confused by the appearance of a system.profile collection in their database if they forget to disable the query profiler.

  • Some users mistakenly believe that this parameter must be set to slowOp to generate slow query logs.

• Recommendations:

  Keep the default value. The slow query log provides similar analytical information without the performance overhead of the query profiler. Enable this feature only when necessary and disable it promptly after completing your analysis. For more information about the Database Profiler, see the official MongoDB documentation.

operationProfiling.slowOpThresholdMs

• Supported major versions: 3.0 or later

• Restart required: No

• Default value: 100

• Function: Defines the threshold, in milliseconds, for a query to be considered slow.

• Issues:

  • Setting the value too low can generate a large volume of slow query logs and audit logs, creating noise that complicates analysis.

  • Setting the value too high can prevent many slow queries from being logged, which hinders the analysis process.

• Recommendations: Adjust the threshold based on your workload. We recommend setting it to a value slightly higher than the average latency of your critical queries. For example:

  • For a latency-sensitive application where typical queries take about 30 ms, consider lowering the threshold to 50 to help analyze transient performance fluctuations.

  • For an analytics-heavy application where queries typically take 300 ms to 400 ms, consider raising the threshold to 500 ms to reduce log noise.

replication.oplogGlobalIdEnabled

• Supported major versions: 4.0 or later

• Restart required: Yes

• Default value: false

• This custom Alibaba Cloud parameter enables Global IDs (GIDs) in the oplog to support bidirectional synchronization with tools like Data Transmission Service (DTS) or mongoShake. GIDs prevent circular replication issues in these setups.

• Recommendations: Enable this parameter only when bidirectional synchronization is required. This change requires an instance restart, so we recommend applying it during off-peak hours.

replication.oplogSizeMB

• Supported major versions: 3.0 or later

• Restart required: No

• Default value: 10% of the instance's disk space. For example, if an instance has 500 GB of disk space, the initial oplogSizeMB is 51,200 (50 GB).

• Function: Specifies the maximum logical size of the oplog collection, which stores replication logs.

• Issues: A value that is too low can cause secondary nodes to fall behind and enter a RECOVERING state. It can also create gaps in log backups, which prevents point-in-time recovery.

• Recommendations: Keep the default value. Do not decrease it. Increase it only when necessary. Consider increasing the value for workloads with low data volume but a high rate of updates, which generates oplog entries quickly. A larger oplogSizeMB allows the oplog to cover a longer time window, preventing gaps. As a best practice, the oplog size should be large enough to retain at least one hour of oplog records.

setParameter.cursorTimeoutMillis

• Supported major versions: 3.0 or later

• Restart required: No

• Default value: 600000 (10 minutes)

• Function: Specifies the timeout in milliseconds for an idle cursor. MongoDB automatically cleans up any cursor that is idle for longer than this threshold.

• Issues: If you try to access a cursor that has expired and been cleaned up, the client receives an error in the following format:

  Message: "cursor id xxxxxxx not found"
  ErrorCode: CursorNotFound(43)

• Recommendations: We do not recommend increasing this value. To reduce the resource overhead of idle cursors, you can decrease the value (for example, to 300000). In all scenarios, your application should avoid creating long-lived idle cursors.

setParameter.flowControlTargetLagSeconds

• Supported major versions: 4.2 or later

• Restart required: No

• Default value: 10

• Function: Specifies the threshold that triggers the flow control mechanism. The purpose of flow control is to ensure that the majority commit point does not fall too far behind.

• Issues: Request latency increases significantly, and slow query logs similar to the following example appear. A durationMillis value that is nearly equal to flowControl.timeAcquiringMicros indicates that the flow control mechanism slowed the request.

  {
    "t": {
      "$date": "2024-04-25T13:28:45.840+08:00"
    },
    "s": "I",
    "c": "WRITE",
    "id": 51803,
    "ctx": "conn199253",
    "msg": "Slow query",
    "attr": {
      "type": "update",
      "ns": "xxx.xxxxx",
      "command": ...,
      "planSummary": "IDHACK",
      "totalOplogSlotDurationMicros": 61,
      "keysExamined": 1,
      "docsExamined": 1,
      "nMatched": 1,
      "nModified": 1,
      "nUpserted": 0,
      "keysInserted": 0,
      "keysDeleted": 0,
      "numYields": 0,
      "locks": ...,
      "flowControl": {
        "acquireCount": 1,
        "acquireWaitCount": 1,
        "timeAcquiringMicros": 959000
      },
      "readConcern": {
        "level": "local",
        "provenance": "implicitDefault"
      },
      "storage": {},
      "cpuNanos": 258845,
      "remote": "172.16.6.38:52368",
      "durationMillis": 959
    }
  }
  

• Recommendations: You can increase this value to make the flow control mechanism less sensitive. If requests are still frequently throttled after increasing the value, it indicates a potential performance bottleneck in replication. In this case, you need to perform further analysis and take other actions, such as upgrading the instance configuration or setting the write concern to majority.

setParameter.oplogFetcherUsesExhaust

• Supported major versions: 4.4 or later

• Restart required: Yes

• Default value: true

• Function: Specifies whether to enable stream replication. If this feature is disabled, replication reverts to the pull-based method of earlier versions. In this method, a secondary node requests a batch of oplog entries from its sync source, requiring a network round-trip for each batch.

• Issues: In some scenarios, the stream replication mechanism can introduce additional performance and network bandwidth overhead.

• Recommendation: We recommend that you do not change this setting. Stream replication can reduce replication latency in high-load and high-latency network environments. It can also reduce the risk of write loss if the primary node unexpectedly goes down when writeConcern is set to {w:1}, and reduce write latency for other writeConcern settings that rely on primary-secondary replication, such as {w:majority} or {w:>1}.

setParameter.maxTransactionLockRequestTimeoutMillis

• Supported major versions: 4.0 or later

• Restart required: No

• Default value: 5

• Function: Specifies the timeout, in milliseconds, for a transaction to acquire a lock. If an operation in the transaction cannot acquire the necessary lock within this period, the transaction automatically aborts.

• Issues: The following lock timeout error message appears in logs or is returned to the client. Modern drivers may automatically retry on TransientTransactionError, so the error might only be visible in the logs and not perceived by the client.

  Message: "Unable to acquire lock '{8442595743001781021: Database, 1525066715360699165}' within a max lock request timeout of '5ms' milliseconds."
  ErrorCode: LockTimeout(24)

• Recommendations: If your client frequently encounters this error, consider increasing this value. This can mitigate aborts from transient lock failures but may delay terminating transactions involved in a deadlock. If the problem persists, optimize your application logic instead of increasing the value further. For example, avoid concurrent modifications to the same document and review operations for long-running tasks (such as DDL or unoptimized queries) that could hold locks for an extended period.

setParameter.replWriterThreadCount

• Supported major versions: 3.2 or later

• Restart required: Yes

• Default value: 16

• Function: Specifies the maximum number of threads for parallel replication. The effective maximum number of threads is twice the number of CPU cores of the instance.

• Issues: In extreme cases, replication lag on secondary nodes increases continuously.

• Recommendations: We do not recommend changing this setting in most cases. In special circumstances, adjust this parameter only under the guidance of Alibaba Cloud support engineers.

setParameter.tcmallocAggressiveMemoryDecommit

• Supported major versions: 4.2 or later

• Restart required: No

• Default value: 0 (disables TCMalloc aggressive memory decommit)

• Function: MongoDB uses the TCMalloc memory allocator. This parameter controls TCMalloc's aggressive decommit policy, which proactively merges adjacent free memory blocks and returns them to the operating system.

• Issues:

  • A mongod node experiences an out-of-memory (OOM) error because memory cannot be reclaimed quickly enough due to high memory consumption from queries.

  • As the instance runs, heap memory fragmentation increases, leading to memory utilization that steadily climbs above 80%.

• Recommendations: We do not recommend changing this setting in most cases. If you encounter memory-related issues, consider adjusting this parameter during off-peak hours.

setParameter.transactionLifetimeLimitSeconds

• Supported major versions: 4.0 or later

• Restart required: No

• Default value: 60

• Function: Specifies the lifetime of a transaction, in seconds. If a transaction's total execution time exceeds this limit, it is marked as expired and aborted by a periodic background cleanup thread.

• Issues: The client receives an error in the following format:

  Message: "Aborting transaction with txnNumber xxx on session with lsid xxxxxxxxxx because it has been running for longer than 'transactionLifetimeLimitSeconds'"

• Recommendation: You can decrease this value (for example, to 30), but increasing it is not recommended. Long-running uncommitted transactions can place significant pressure on the cache of the WiredTiger storage engine. An overloaded cache often leads to more issues, such as database freezes, sharp increases in request latency, and maxed-out CPU utilization, which can degrade your services. Your application should avoid long-running transactions whenever possible. To resolve timeout issues, you should break down transactions into smaller parts so that they can be completed within the configured time limit. You also need to ensure that your queries are optimized and have proper index coverage for fast data access within transactions.

For more information about best practices for transactions, see Transactions and Read/Write Concern.

storage.oplogMinRetentionHours

• Supported major versions: 4.4 or later

• Restart required: No

• Default value: 0 (A value of 0 indicates that this parameter is disabled, and the oplog size is controlled entirely by the replication.oplogSizeMB parameter.)

• Function: Specifies the minimum retention period in hours for the oplog.

• Issues:

  • Setting the value too high can cause the oplog to consume excessive disk space.

  • Some users may forget they have set this parameter and become confused by fluctuations in the instance's disk space usage.

• Recommendations: For relatively stable workloads, keep the default value. For workloads that may experience significant fluctuations in write operations, we recommend setting this parameter to a floating-point number greater than 1.0. When setting this parameter, you must also evaluate the potential disk space consumption to avoid other issues caused by a full disk.

storage.wiredTiger.collectionConfig.blockCompressor

• Supported major versions: 3.0 or later

• Restart required: Yes

• Default value: snappy

• Function: Specifies the storage compression algorithm for collection data. This setting applies only to newly created tables and does not affect existing tables. Currently, the supported algorithms are no compression, snappy, zlib, and zstd. The zstd algorithm is supported only in versions 4.2 and later.

• Recommendations: Modify as needed. Different compression algorithms offer different performance. Some provide higher compression ratios but at the cost of greater CPU overhead during compression and decompression. The comparison between compression algorithms should be based on your own test results. If the instance is mainly used to store cold data, consider changing this parameter to zstd to achieve a higher compression ratio.

  Note

  If you want to use different compression algorithms for different collections, you need to use the explicit createCollection command with the relevant options. For more information, see the official MongoDB documentation.

setParameter.minSnapshotHistoryWindowInSeconds/setParameter.maxTargetSnapshotHistoryWindowInSeconds

• Supported major versions: 4.4 or later

• Restart required: No

• Default value: 300 (5 minutes)

• Function: Specifies the window size in seconds for which the WiredTiger storage engine retains snapshot history. A value of 0 disables the snapshot history window. This parameter is mainly used to support reads at a specific atClusterTime.

• Issues: This parameter can increase pressure on the WiredTiger cache, especially in scenarios with frequent updates to the same document.

• Recommendations: We do not recommend changing this setting in most cases.

  • If your application does not use the read atClusterTime feature, you can set this parameter to 0 to gain a small performance improvement.

  • If your application needs to read historical snapshot data older than 5 minutes, you can increase this value. However, be aware that this will lead to additional memory consumption and performance overhead.

  Note

  If the value of this parameter is low, and you specify a time that is too old when reading a historical snapshot, you will receive a SnapshotTooOld error.

rsconf.chainingAllowed

• Supported major versions: 4.0 or later

• Restart required: No

• Default value: true

• Function: Specifies whether to allow chained replication in a replica set.

• Issues:

  • Disabling chained replication may increase the load on the primary node, such as CPU utilization and network traffic.

  • Enabling chained replication can increase replication lag on secondary nodes.

• Recommendations:

  • For replica sets with four or fewer nodes: You can enable or disable chained replication based on your needs.

  • When the number of nodes is large (5 or more), you need to make a further trade-off between primary node load and instance performance only when writeConcern is configured as {w:majority}. Disabling chained replication helps improve write performance, but the corresponding load on the primary node also increases significantly.

setParameter.internalQueryMaxPushBytes/setParameter.internalQueryMaxAddToSetBytes

• Supported major versions: 4.2 or later

• Restart required: No

• Default value: 104,857,600 B (100 MB)

• Function: Limits the maximum memory usage for the $push and $addToSet operators.

• Symptom: A specific SQL statement that contains $push or $addToSet fails to execute and returns the following error message.

  "errMsg": "$push used too much memory and cannot spill to disk. Memory limit: 104857600... 

• Recommendations: We do not recommend changing this setting in most cases. If you encounter the error above when executing a specific query, you can consider increasing the value. However, be aware that setting this value too high may cause the mongod node to experience an out-of-memory (OOM) error.

setParameter.migrateCloneInsertionBatchSize

• Supported major versions: 4.0 or later

• Restart required: No

• Default value: 0 (limited by the 16 MB document size limit)

• Function: Specifies the maximum number of documents in a single batch during the cloning step of chunk migration.

• Issues: Chunk migration can cause performance fluctuations on the shard.

• Recommendations: We do not recommend changing this setting in most cases. If your sharded cluster instance experiences performance fluctuations during balancing due to chunk migration, consider setting this parameter to a fixed batch size.

setParameter.rangeDeleterBatchDelayMS

• Supported major versions: 4.0 or later

• Restart required: No

• Default value: 20

• Function: Specifies the interval for batch deletions during the cleanup step of chunk migration. This setting also affects the cleanupOrphaned command for cleaning up orphaned documents. The unit is milliseconds.

• Issues:

  • In some scenarios, asynchronous deletion of documents after chunk migration can cause a spike in CPU utilization.

  • Setting the value too high may cause documents to become orphaned because they are not deleted in time. It can also cause timeouts if too many documents need to be deleted, resulting in the following error log:

    Message: "OperationFailed: Data transfer error: ExceededTimeLimit: Failed to delete orphaned <db>.<collection> range [xxxxxx,xxxxx] :: caused by :: operation exceeded time limit"

• Recommendation: In general, no adjustment is required. If the CPU utilization of a sharded cluster instance spikes during balancing because of asynchronous document deletion, you can increase this parameter, for example, to 200.

setParameter.rangeDeleterBatchSize

• Supported major versions: 4.0 or later

• Restart required: No

• Default value: 0 (automatically chooses a reasonable batch size, typically 128)

• Function: Specifies the maximum number of documents in a single batch for asynchronous deletion during the cleanup step of chunk migration.

• Issues: In some scenarios, asynchronous deletion of documents after chunk migration can cause a spike in CPU utilization.

• Recommendations: We do not recommend changing this setting in most cases. If the CPU utilization of your sharded cluster instance spikes during balancing due to asynchronous document deletion, consider setting this parameter to a fixed batch size.

setParameter.receiveChunkWaitForRangeDeleterTimeoutMS

• Supported major versions: 4.4 or later

• Restart required: No

• Default value: 10000 (10 seconds)

• Function: Specifies the timeout, in milliseconds, for waiting for orphaned documents to be deleted before a chunk migration.

• Issues: While the balancer is running, you may see a timeout error log similar to the following:

  ExceededTimeLimit: Failed to delete orphaned <db.collection> range [{ <shard_key>: MinKey }, { <shard_key>: -9186000910690368367 }) :: caused by :: operation exceeded time limit

• Recommendations: We do not recommend changing this setting in most cases. If you encounter the error above, you can increase this value to allow the moveChunk operation to wait longer for the orphaned document deletion to complete, thereby avoiding such timeout errors.

setParameter.minSnapshotHistoryWindowInSeconds/setParameter.maxTargetSnapshotHistoryWindowInSeconds

• Supported major versions: 4.4 or later

• Restart required: No

• Default value: 300 (5 minutes)

• Function: Specifies the window size in seconds for which the WiredTiger storage engine retains snapshot history. A value of 0 disables the snapshot history window. This parameter is mainly used to support reads at a specific atClusterTime.

• Issues: This parameter can increase pressure on the WiredTiger cache, especially in scenarios with frequent updates to the same document.

• Recommendations: We do not recommend changing this setting in most cases.

  • If your application does not use the read atClusterTime feature, you can set this parameter to 0 to gain a small performance improvement.

  • If your application needs to read historical snapshot data older than 5 minutes, you can increase this value. However, be aware that this will lead to additional memory consumption and performance overhead.

  Note

  If the value of this parameter is low, and you specify a time that is too old when reading a historical snapshot, you will receive a SnapshotTooOld error.

rsconf.chainingAllowed

• Supported major versions: 4.0 or later

• Restart required: No

• Default value: true

• Function: Specifies whether to allow chained replication in a shard.

• Issues:

  • Disabling chained replication may increase the load on the primary node, such as CPU utilization and network traffic.

  • Enabling chained replication can increase replication lag on secondary nodes.

• Recommendations:

  • For shards with four or fewer nodes: You can enable or disable chained replication based on your needs.

  • If you have a large number of nodes (5 or more) and writeConcern is configured as {w:majority}, you must make a trade-off between the load on the primary node and instance performance. Disabling chained replication improves write performance, but it also significantly increases the load on the primary node.

setParameter.periodicNoopIntervalSecs

• Supported major versions: 4.2 or later

• Restart required: Yes

• Default value: 10

• Function: The interval in seconds for no-op (noop) writes.

• Issues: When you use Change Streams, shards with low write activity can become a bottleneck for mongos when aggregating changes. This can lead to a Change Stream delay of about 10 seconds.

• Recommendations: We do not recommend changing this setting in most cases. If you experience the delay described above when using Change Streams, consider reducing this parameter to 1. This increases the frequency of no-op writes and prevents Change Stream consumption delays caused by low write activity on a single shard.

setParameter.internalQueryMaxPushBytes/setParameter.internalQueryMaxAddToSetBytes

• Supported major versions: 4.2 or later

• Restart required: No

• Default value: 104,857,600 B (100 MB)

• Function: Limits the maximum memory usage for the $push and $addToSet operators.

• Symptom: A specific SQL statement that contains $push or $addToSet fails to execute and returns the following error message.

  "errMsg": "$push used too much memory and cannot spill to disk. Memory limit: 104857600... 

• Recommendations: We do not recommend changing this setting in most cases. If you encounter the error above when executing a specific query, you can consider increasing the value. However, be aware that setting this value too high may cause the mongod node to experience an out-of-memory (OOM) error.

operationProfiling.slowOpThresholdMs

• Supported major versions: 3.0 or later

• Restart required: No

• Default value: 100

• Function: Defines the threshold, in milliseconds, for a query to be considered slow.

• Issues:

  • Setting the value too low can generate a large volume of slow query logs and audit logs, creating noise that complicates analysis.

  • Setting the value too high can prevent many slow queries from being logged, which hinders the analysis process.

• Recommendations: Adjust the threshold based on your workload. We recommend setting it to a value slightly higher than the average latency of your critical queries. For example:

  • For a latency-sensitive application where typical queries take about 30 ms, consider lowering the threshold to 50 to help analyze transient performance fluctuations.

  • For an analytics-heavy application where queries typically take 300 ms to 400 ms, consider raising the threshold to 500 ms to reduce log noise.

setParameter.ShardingTaskExecutorPoolMaxConnecting

• Supported major versions: 3.6 or later

• Restart required:

  • For versions 3.6 and 4.0: Yes

  • For versions 4.2 and later: No

• Default value: 2

• Function: Specifies the maximum number of concurrent connections that the TaskExecutor connection pool on a mongos node can establish during initialization. It controls the rate at which connections are created from the mongos to shard nodes.

• Issues: If this value is set too high, creating many connections at once may cause a CPU utilization spike on the mongos node.

• Recommendations: We do not recommend changing this setting.

setParameter.ShardingTaskExecutorPoolMaxSize

• Supported major versions: 3.6 or later

• Restart required:

  • For versions 3.6 and 4.0: Yes

  • For versions 4.2 and later: No

• Default value: 2^64-1 (maximum value for a 64-bit integer)

• Function: Specifies the maximum number of connections in each TaskExecutor connection pool on a mongos node.

• Recommendations: No adjustment is needed. You can set this parameter to limit the connection pool size from a mongos to shards, but we do not recommend setting it too low. A value that is too small can cause requests on the mongos to be blocked when the connection pool is exhausted.

setParameter.ShardingTaskExecutorPoolMinSize

• Supported major versions: 3.6 or later

• Restart required:

  • For versions 3.6 and 4.0: Yes

  • For versions 4.2 and later: No

• Default value: 1

• Function: Specifies the minimum number of connections in each TaskExecutor connection pool on a mongos node.

• Issues: A sudden burst of requests can force the TaskExecutor connection pool on a mongos node to create many new connections at once, which can cause CPU utilization spikes and other issues.

• Recommendation: Set a reasonable value in the range of [10,50]. The specific value should depend on the topology of the shard instance (the number of shards and the number of nodes in each shard). Please note that mongos incurs a small resource overhead to maintain these idle connections to shards.

setParameter.cursorTimeoutMillis

• Supported major versions: 3.0 or later

• Restart required: No

• Default value: 600000 (10 minutes)

• Function: Specifies the expiration threshold for an idle cursor, in milliseconds. MongoDB automatically cleans up any cursor that is idle for longer than this threshold.

• Issues: If you try to access a cursor that has expired and been cleaned up, the client receives an error in the following format:

  Message: "cursor id xxxxxxx not found"
  ErrorCode: CursorNotFound(43)

• Recommendations: We do not recommend increasing this value. To reduce the resource overhead of idle cursors, you can decrease the value (for example, to 300000). In all scenarios, your application should avoid creating long-lived idle cursors.