Getting a MySQL error: out of memory? We break down what this error means, and steps you can take it resolve it.
MySQL Error: Out of Memory
Error: "101011 8:12:27 [ERROR] mysqld: Out of memory (Needed 219113937 bytes)"
The "out of memory" error is raised by the MySQL server when it encounters a memory shortage. In short, the MySQL server doesn’t have enough buffer and cache memory to perform SQL queries or hold the result sets returned by the SQL queries.
Whenever you set up a MySQL server, it allocates a considerable amount of memory from the host RAM to facilitate reading, joining, and sorting buffers, temporary tables, and client connections. Hence, database administrators should ensure that the above memory areas don't exceed the available system memory which crashes the MySQL service with the “out of memory” error.
MySQL Error: Out of Memory Solutions
Configure the maximum MySQL server memory usage.
In general, the MySQL server starts on a virtual machine with 512MB of RAM. It utilizes the memory for its caches and buffers such as innodb_buffer_pool, key_buffer, query_cache, sort, read, join and binlog cache, etc. Since every SQL connection keeps separate cache areas for reading, joining and sorting operations, total cache memory will be calculated by multiplying the total cache size by the count of permitted SQL connections. Hence the total MySQL server memory consumption can be calculated as the following:
Total MySQL Memory Consumption = innodb_buffer_pool_size +
innodb_additional_mem_pool_size+ innodb_log_buffer_size + tmp_table_size +
(max_connections*(sort_buffer_size+read_buffer_size+join_buffer_size+binlog
_cache_size))
All these parameters are defined in the MySQL configuration file as shown in the following figure:
Increasing the innodb_buffer_pool_size
Usually, the cached InnoDB data is stored in the InnoDB buffer pool memory area. Furthermore, it assists in holding multiple rows returned from high-volume read operations — so the size of the InnoDB buffer pool has a big impact on MySQL server performance. The ‘innodb_buffer_pool_size’ system variable is used to define the optimal InnoDB buffer size. It is recommended to use up to 75% of the system memory for the InnoDB buffer pool.
The innodb_buffer_pool_size variable can be set dynamically while the MySQL server is running. In addition, the InnoDB buffer size changes in chunks. It is defined by the 'innodb_buffer_pool_chunk_size' system variable. By default, that value is set to 128Mb.
So, the innodb_buffer_pool_size has to be a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. Whenever the defined InnoDB buffer size is not multiple, MySQL will automatically round it to the nearest multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.
Let’s set the innodb_buffer_pool_size system variable to 4GB and the number of buffer pool instances to 8.
mysqld --innodb-buffer-pool-size=4G --innodb-buffer-pool-instances=8
We can inspect whether these values have been set properly with a select statement as shown in the following:
SELECT @@innodb_buffer_pool_size/1024/1024/1024
Output:
+-------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+-------------------------------------------+
| 4.000000000000 |
+-------------------------------------------+
This guarantees that the MySQL InnoDB buffer pool size will not exceed the available system memory and crash due to the memory shortage.
In the same way, most of the mentioned parameters can be increased or decreased accordingly until the overall MySQL memory consumption value hits below 60% of system RAM. It would be an incremental task where you set some values to each of these system parameters and check what percent of system RAM has been consumed by MySQL service and repeat until it meets the optimal memory consumption.
Allocate a fixed memory for third-party applications, processes from available memory.
In most cases, MySQL is not the only service that consumes system memory. The system might run other applications including backup applications, third-party software, monitoring tools, docker containers, etc., which eat up system memory excessively. Hence, it is important to implement constraints on how much memory each process/application should consume from available memory. Every application should be assigned to a fixed maximum memory that it shouldn’t exceed. This will guarantee that such processes will not eat up the entire server memory and crash the MySQL service.
Optimize the database tables and queries
Databases have a very dynamic nature where the data insertion, update and deletion occur frequently. With the growth of data inside MySQL databases, memory consumption will be increased. Furthermore, the fragments can occur due to the data deletions over time. These fragments altogether might occupy a considerable amount of system memory that is abandoned — and should be monitored in regular intervals and optimized. MySQL memory leaks can negatively affect memory consumption, leading you to a MySQL out of memory error.
These memory leaks might occur due to the complex joins and sorts implemented in the application’s database logic. It will eat up all the available memory and eventually, MySQL will crash. Therefore, database logic needs to be reviewed closely and optimized. In addition, whenever new plugins, libraries and third-party apps are introduced, developers should be careful and alert to the complexity and optimizations of MySQL queries.
Setting user account limits
In some scenarios, clients who connect to the server might abuse its resources. It can be a startup of unwanted applications/processes, and keep them open up forever without terminating or opening up several client connections to the MySQL database for longer periods which is not necessary. These might waste a considerable amount of system memory, and will eventually cause the MySQL error: 'out of memory' due to memory shortage.Proper constraints should be implemented against user accounts to avoid this issue.
That might mean restricting access to certain applications, or authorizing users with a minimum set of permissions. These measures will keep your server’s memory consumption in the green area, ensuring enough memory is available to use with MySQL.
Expand the available memory (RAM)
After trying out all the preceding solutions, you may still end up with the MySQL ‘out of memory error. It concludes that your server doesn’t have enough RAM to feed all the processes and applications, even with the normal traffic. Hence, a RAM upgrade is mandatory. All the above solutions are low cost, but an upgrade might be costly.
SingleStoreDB
SingleStoreDB is a real-time, distributed SQL database that unifies transactions and analytics in a single engine to drive low-latency access to large datasets, simplifying the development of fast, modern enterprise applications.
Built for developers and architects, SingleStoreDB delivers 10-100 millisecond performance on complex queries — all while ensuring your business can effortlessly scale.
SingleStoreDB is MySQL wire compatible and offers the familiar syntax of SQL, but is based on modern underlying technology that allows infinitely higher speed and scale versus MySQL. This is one of the many reasons that SingleStore is the #1 top-rated relational database on TrustRadius.
For more information on how SingleStore is related and can turbocharge your MySQL, visit our MySQL page.
Resources:
