scheduler lmm enable argument: Specify automatic low memory management settings (SQL administration API)

Use the scheduler lmm enable argument with the admin() or task() function to start automatic low memory management and to update low memory threshold settings.

Syntax


Element	Description	Key Considerations
`start_threshold_size`	The amount of free memory that you want the database server to maintain. If the amount of memory falls below the `start_threshold_size`, the server automatically frees memory and terminates applications.	The value can be expressed as either a percentage of the value of the SHMTOTAL configuration parameter or as a specific amount. If the value is less than 50, it is considered a percentage. The resulting value of the input parameter must be more than 5 MB and less than 95 MB. The default value is 5 MB. There must be a minimum 5 MB difference between the LMM START THRESHOLD and LMM STOP THRESHOLD values
`stop_threshold_size`	The amount of free memory that you want the database server to have, before the server stops automatically freeing memory and terminating applications.	The value can be expressed as either a percentage of the value of the SHMTOTAL configuration parameter or as a specific amount. If the value is less than 50, it is considered a percentage. The resulting value of the input parameter must be more than 10 MB and less than 100 MB. The value must also be at least 5 MB more than the LMM START THRESHOLD. The default value is 10 MB.
`minimum_amount_of_time`	The amount of time in seconds that defines a session as idle	The value must be between 1 and 86400. The default value is 300 seconds.

Usage

You use the scheduler lmm disable argument with the admin() or task() function to stop the current and subsequent low memory management processes in a primary or standard database server. When low memory management is triggered, the database server performs these tasks, in order:

The database server terminates sessions starting one at a time from the session with largest amount of idle time and continuing as necessary to the session with smallest amount of idle time that exceeds the amount specified in the LMM IDLE TIME setting. The server stops terminating sessions when the LMM STOP THRESHOLD is reached.
The database server terminates sessions starting with the session using the most memory and continuing as necessary to the session using the smallest amount of memory until the LMM STOP THRESHOLD is reached.
The database server performs memory reconfiguration by setting the VP_MEMORY_CACHE configuration parameter to 0 and running the onmode -F command to free unused shared memory segments.

When the low memory management operations are complete, the low memory manager returns to monitoring mode and restores the memory configuration of the database server by setting the VP_MEMORY_CACHE configuration parameter back to its original value.

The database server stores automatic low memory management settings in the ph_threshold table.

You can view low memory management settings and recent activity with the onstat -g lmm command.

Attention: If you enable automatic low memory management and configure the database server to use a percentage of the value specified in the SHMTOTAL configuration parameter for the start and stop thresholds, use caution when changing the value of the SHMTOTAL configuration parameter. Changing the value of the SHMTOTAL configuration parameter value can cause the configuration of automatic low memory management to become invalid, forcing HCL OneDB™ to use the default settings.

Example of setting low memory management threshold settings

The following example specifies that when the database server has 10 MB or less of free memory, the server will start automatic low memory management to stop applications and to free memory. The example also specifies that a session is considered idle if it has not run for 300 seconds, and the example specifies that the server will stop automatic low memory management when the server has 20 MB or more of free memory.

EXECUTE FUNCTION task("scheduler lmm enable",
	"LMM START THRESHOLD", "10MB",
	"LMM STOP THRESHOLD", "20MB",
	"LMM IDLE TIME", "300");

Example of the SHMTOTAL configuration parameter impacting low memory management threshold settings

Suppose you set the SHMTOTAL configuration parameter to 1000000 (1000 MB or 1 GB), the LMM START THRESHOLD to 2, and the LMM STOP THRESHOLD to 3. Because any value that is less than 50 is a percentage of the value of SHMTOTAL, the actual LMM START THRESHOLD is 20000 (20 MB) and the actual LMM STOP THRESHOLD is 30000 (30 MB).

The database server begins managing low memory when the remaining free memory is 20 MB or less and stop managing memory when the amount of free memory is 30 MB or greater.

Suppose you decide to change the value of the SHMTOTAL configuration parameter because you know now that you don't need as much memory and you want memory to be available to the operating system. You set the value of SHMTOTAL to 250000 (250 MB). This changes the actual LMM START THRESHOLD to 5000 (5 MB) and the LMM STOP THRESHOLD to 7500 (7.5 MB). The LMM STOP THRESHOLD is now invalid because there must be a minimum 5 MB difference between the LMM START THRESHOLD and LMM STOP THRESHOLD values. The LMM STOP THRESHOLD value must also be at least 10 MB.

You might have decided that a 10 MB difference is the right amount for your system. But at 5 MB, the database server could spend too much time spent on low memory management processes and this could cause performance problems.