If you get an error message indicating that the limit set with the MME.ImdbMemoryLimit parameter has been reached, you must 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 resolution, you must 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 should typically do the following actions:
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 might want to have a policy or error-checking code to ensure that users and programs attempt to disconnect gracefully if they see this error.
2 If there are not enough temporary tables to free memory, drop some transient table indexes or transient tables if any exist.
3 If there are not enough temporary tables or transient tables to free enough memory, do the following actions:
a Drop one or more indexes on in-memory tables.
b Shut down the server.
c If there is nothing in memory that you can discard (for example, you have only normal in-memory tables, none of which have indexes, and all of which have valuable data), increase the value of the MME.ImdbMemoryLimit parameter slightly before restarting the server. This might 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 value of the MME.ImdbMemoryLimit parameter 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.
d Restart the server.
e Minimize the number of people using the system until you have time to address the long-term problem. Ensure that users do not create temporary tables or transient tables until the long-term problem is addressed.
Resolving the long term problem
After you solve the immediate problem and ensure that the server has at least some free memory, you can address the long term problems by using one or both of the following steps:
▪ Reduce the amount of data that is stored in in-memory tables. You can do this either by reducing the number or size of in-memory tables (including temporary tables and transient tables), or by reducing the number of indexes on in-memory tables.
If the problem was caused solely by heavy usage of temporary or transient tables, ensure that too many sessions do not 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, complete the following steps:
1 Create an empty disk-based table with the same structure (but a different name) as one of the in-memory tables.
2 Copy the information from the in-memory table to the disk-based table.
If you try to copy records of a large table to another table using a single SQL statement (INSERT INTO ...VALUES SELECT FROM), be aware 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) by using a simple stored procedure or application.
The disk-based table does not need indexes. The indexes should be re-created in the new table after the data has been successfully copied.
3 Drop the in-memory table.
4 Rename the disk-based table to the original name of the in-memory table that was dropped.
Notes
▪ You should set the value of the MME.ImdbMemoryLimit parameter to a slightly lower value than the maximum amount of memory that you 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 value of the MME.ImdbMemoryLimit parameter slightly and restart the server with enough free memory until you can address the long-term need.
▪ Use the MME.ImdbMemoryWarningPercentage parameter 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 might be to simply install more memory in the computer.