The in-memory database memory consumption is controlled by the following three configuration parameters in the [MME] section of the solid.ini file:
▪ImdbMemoryLimit
▪ImdbMemoryLowPercentage
▪ImdbMemoryWarningPercentage
Additionally, the process memory consumption is controlled by the following four configuration parameters in the [SRV] section of the solid.ini file:
▪ProcessMemoryLimit
▪ProcessMemoryLowPercentage
▪ProcessMemoryWarningPercentage
▪ProcessMemoryCheckInterval
The violations of the in-memory database memory and process limits are logged in the solmsg.out log file. Every time the memory limit defined with the ImdbMemoryLimit and ProcessMemoryLimit parameters is crossed, a system event is posted. These system events are described in solidDB® SQL Guide.
The in-memory database main memory usage differs from the standard solidDB®. The in-memory database resides in its own memory pool.
The solidDB® main memory engine provides commands and configuration parameters to help you monitor and control memory consumption of the in-memory database and the server process. These commands and parameters focus on the server’s in-memory database feature, not the server as a whole.
MME.ImdbMemoryLimit
The MME.ImdbMemoryLimit parameter specifies the maximum amount of virtual memory that can be allocated to in-memory tables (including temporary tables and transient tables) and the indexes on those in-memory tables.
The default value for MME.ImdbMemoryLimit is 0, which means “no limit”. You should not use the default value; instead, set the parameter to a value that will ensure that the in-memory data will fit entirely within physical memory. Consider also the following factors:
▪The amount of physical memory in the computer.
▪The amount of memory used by the operating system.
▪The amount of memory used by solidDB® (the program itself).
▪The amount of memory set aside for the solidDB® server’s cache (the IndexFile.CacheSize solid.ini configuration parameter).
▪The amount of memory required by the connections, transactions and statements running concurrently in the server. The more concurrent connections and active statements there are in the server, the more working memory the server requires. Typically, you should allocate at least 0.5 MB of memory for each client connection in the server.
▪The memory used by other processes (programs and data) that are running in the computer.
When 100% of the memory specified by MME.ImdbMemoryLimit is reached, the server will prohibit UPDATE operations on in-memory tables. Before the limit is reached, the server will prohibit creation of new in-memory tables and INSERT operations on those tables.
For example:
[MME] ImdbMemoryLimit=1000MB
MME.ImdbMemoryLowPercentage
The MME.ImdbMemoryLowPercentage parameter sets a “low water mark” for the amount of virtual memory that can be allocated to in-memory tables. The limit is expressed as a percentage of the MME.ImdbMemoryLimit parameter value.
When the server has consumed the percentage of memory specified with MME.ImdbMemoryLowPercentage, the server will start to limit activities in order to prevent memory consumption from continuing to grow. For example, if MME.ImdbMemoryLimit is 1000 megabytes and MME.ImdbMemoryLowPercentage is 90%, the server will start limiting activities if the memory allocated to the in-memory tables exceeds 900 megabytes. Specifically, the server will:
▪Prohibit further creation of in-memory tables (including temporary tables and transient tables) and indexes on in-memory tables.
▪Prohibit INSERTs into in-memory tables.
When the limit set with MME.ImdbMemoryLimit itself is reached, the server will also prohibit UPDATE operations on records in in-memory tables.
Valid values for MME.ImdbMemoryLowPercentage range between 60-99 (percent). MME.ImdbMemoryWarningPercentage: The MME.ImdbMemoryWarningPercentage parameter sets a limit at which a system even is given to warn you that the maximum amount of virtual memory that can be allocated to in-memory tables is being reached.
The warning limit is expressed as a percentage of the MME.ImdbMemoryLimit parameter value. When the MME.ImdbMemoryWarningPercentage limit is exceeded, a system event is given.
Troubleshooting MME.ImdbMemoryLimit
If you get an error message indicating that the limit set with MME.ImdbMemoryLimit has been reached, you need to take action immediately.
You must address both the immediate problems and the long term problems. The immediate problems are to prevent users from experiencing serious errors, and to free up some memory before shutting down the server so that your system is not out of memory when you restart the server. For long term, you need to ensure that you will not run out of memory in the future as tables expand.
Resolving the immediate problem
To address the immediate problem, you typically need do the following:
1 Notify users that they should disconnect from the server. This will accomplish two things: it will minimize the number of users who will be impacted if the situation deteriorates. Also, if any of the users who disconnect were using temporary tables, disconnecting will free up memory. You may wish to have a policy or error-checking code to ensure that users and/or programs will attempt to disconnect gracefully if they see this error.
2 If there were not enough temporary tables to free memory, drop some transient table indexes or transient tables if any exist.
If there were not enough temporary tables and transient tables to free enough memory, do the following:
3 Drop one or more indexes on in-memory tables.
4 Shut down the server.
5 If there was absolutely nothing in memory that you could discard (for example, you had only normal in-memory tables, none of which had indexes, and all of which had valuable data), increase the MME.ImdbMemoryLimit slightly before restarting the server. This may force the server to start paging virtual memory which will greatly reduce performance, but it will allow you to continue using the server and address the long-term problems. If you previously set the ImdbMemoryLimit a little bit lower than the maximum, you will be able to raise it slightly now without forcing the system to start paging virtual memory.
6 Restart the server.
7 Minimize the number of people using the system until you have had time to address the long-term problem. Ensure that users do not create temporary tables or transient tables until the long-term problem has been addressed.
Resolving the long term problem
After you have solved the immediate problem and have ensured that the server has at least some free memory, you are ready to address the long term problems.
For long term, reduce the amount of data stored in in-memory tables. The ways to do this are to reduce the number or size of in-memory tables (including temporary tables and transient tables), or reduce the number of indexes on in-memory tables.
▪If the problem was caused solely by heavy usage of temporary or transient tables, ensure that not too many sessions create too many large temporary or transient tables at the same time.
▪If the problem was caused by using too much memory for normal in-memory tables, and if you cannot increase the amount of memory available to the server, move one or more tables out of main memory and onto the disk.
To move a table from memory to disk, do the following:
1 Create an empty disk-based table with the same structure (but a different name) as one of the tables in memory.
2 Copy the information from the in-memory table to an intermediate disk-based table.
3 If you try to copy records of a large table to another table using a single SQL statement (INSERT INTO ...VALUES SELECT FROM), keep in mind that the entire operation occurs in one transaction. Such an operation is efficient only if the entire amount of data fits in the cache memory of the server. If transaction size outgrows the cache size, the performance degrades significantly. Therefore, you should copy data of a large table to another table in smaller transactions (for example, few thousands of rows per transaction) using a simple stored procedure or application.
Note The intermediate table does not need indices. The indices should be re-created in the new table after the data has been successfully copied.
4 Drop the in-memory table.
5 Rename the disk-based table to have the original name of the dropped in-memory table.
Notes
▪You should set the MME.ImdbMemoryLimit to a slightly lower value than the maximum you really have available. If you run out of memory and have no unnecessary in-memory tables or indexes that you can get rid of, you can increase the MME.ImdbMemoryLimit slightly, restart the server with enough free memory that you can address the long-term need.
▪Use the MME.ImdbMemoryWarningPercentage to warn you about increasing memory consumption.
▪Not all situations require you to reduce the number of in-memory tables. In some cases, the most practical solution may be to simply install more memory in the computer.
Use the configuration parameters in the Srv section to control the maximum amount of virtual memory that can be allocated to the in-memory database process.
Srv.ProcessMemoryLimit
The Srv.ProcessMemoryLimit parameter specifies the maximum amount of virtual memory that can be allocated to the in-memory database process.
The factory value for Srv.ProcessMemoryLimit is 0; there is no process memory limit. If you use the parameter, set it to a value that will ensure that the in-memory database process will fit entirely within physical memory. The following factors impact the amount of memory needed:
▪The amount of physical memory in the computer.
▪The amount of memory used by the operating system.
▪The amount of memory used by in-memory tables (including temporary tables and transient tables) and the indexes on those in-memory tables.
▪The amount of memory set aside for the solidDB® server’s cache (the IndexFile.CacheSize parameter).
▪The amount of memory required by the connections, transactions and statements running concurrently in the server. The more concurrent connections and active statements there are in the server, the more working memory the server requires. Typically, you should allocate at least 0.5 MB of memory for each client connection in the server.
▪The memory used by other processes (programs and data) that are running in the computer.
When the limit is reached, that is, when the in-memory database process uses up 100% of the memory specified by Srv.ProcessMemoryLimit, the server will accept ADMIN COMMANDs only. You can use the Srv.ProcessMemoryWarningPercentage and Srv.ProcessMemoryLowPercentage parameters to warn you about increasing process memory consumption.
Notes
▪The Srv.ProcessMemoryLimit and Srv.ProcessMemoryCheckInterval parameters are interlinked; if the ProcessMemoryCheckInterval parameter is set to 0, the ProcessMemoryLimit parameter is not effective, that is, there is no process memory limit.
▪You should not set the Srv.ProcessMemoryLimit parameter when using SMA. If you need to limit the memory the SMA server uses, use the SharedMemoryAccess.MaxSharedMemorySize parameter.
Srv.ProcessMemoryLowPercentage
The Srv.ProcessMemoryLowPercentage parameter sets a warning limit for the total process size. The limit is expressed as percentage of the Srv.ProcessMemoryLimit parameter value.
Prior to exceeding the limit, you have exceeded the warning limit defined with the ProcessMemoryWarningPercentage parameter and received a warning in the solmsg.out log file. When the Srv.ProcessMemoryLowPercentage limit is exceeded, a system event is given.
The limit set with Srv.ProcessMemoryLowPercentage must be higher than the Srv.ProcessMemoryWarningPercentage limit. For example, if the Srv.ProcessMemoryWarningPercentage is set to 82, the Srv.ProcessMemoryLowPercentage value must be at least 83.
Srv.ProcessMemoryWarningPercentage
The Srv.ProcessMemoryWarningPercentage parameter sets the first warning limit for the total process size. The warning limit is expressed as percentage of the Srv.ProcessMemoryLimit parameter value.
When the Srv.ProcessMemoryWarningPercentage limit is exceeded, a system event is given in the solmsg.out log file.
The limit set with Srv.ProcessMemoryWarningPercentage must be lower than the Srv.ProcessMemoryLowPercentage limit.
Srv.ProcessMemoryCheckInterval
The Srv.ProcessMemoryCheckInterval parameter defines the interval for checking the process size limits. The interval is given in milliseconds.
The minimum non-zero value for Srv.ProcessMemoryCheckInterval is 1000 (ms). Only values 0, 1000, or above 1000 (1 second) are allowed. If the given value is above 0 but below 1000, an error message is given.
The factory value is 0, that is, the process size checking is disabled.
The Srv.ProcessMemoryLimit and Srv.ProcessMemoryCheckInterval parameters are interlinked; if the ProcessMemoryCheckInterval parameter is set to 0, the ProcessMemoryLimit parameter is not effective, that is, there is no process memory limit.