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 want 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:
▪Drop one or more indexes on in-memory tables.
▪Shut down the server.
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.
Restart the server.
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.
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.
The intermediate table does not need indices. The indices 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 have the original name of the dropped in-memory table.
▪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.