“mysqld: out of memory” – 4 major reasons why you get this error
Database errors can present themselves in the form of ‘503 Service Unavailable’ or ‘500 Server Error’ in websites.
As part of our Outsourced Tech Support services for web hosts, database errors are a commonly resolved issue for customers who report website issues.
To know the exact cause for the MySQL error, we examine the MySQL service error logs and the server logs.
If the MySQL service crashed due to memory issues, the logs would show these information:
101011 2:41:17 [ERROR] mysqld: Out of memory (Needed 116817914 bytes) 101011 2:41:17 [ERROR] mysqld: Out of memory (Needed 98689909 bytes) 101011 2:41:17 [ERROR] mysqld: Out of memory (Needed 92789259 bytes)
Today, we’ll see what causes ‘mysqld: out of memory’ errors and how we fix those.
‘mysqld: out of memory’ – How we tackle this error
The ‘Out of memory’ error is a pretty straight forward message that shows that the server is running short of memory.
The memory allocated to MySQL service isn’t enough to handle the process requirements. And the easiest way out, is to examine server memory and upgrade the RAM.
But RAM upgrade is not always the ideal solution, nor a cost-effective one. If the query or process is a memory-hogging one, it can eventually eat up the added memory too, and lead to errors in no time.
At times, the memory allocation to the MySQL service may not be adequate. It is possible to tweak the MySQL service and allot the optimal memory for it.
When Bobcares’ Dedicated Support Services perform server management, we consider all the possibilities and manage them one by one, before initiating a RAM upgrade.
‘mysqld: out of memory’ errors – How we prevent them
When a MySQL based website or application gives out memory error, it shows that MySQL does not have enough memory to store the results of the query executed.
Today, we’ll see the causes for ‘mysqld: out of memory’ that we’ve come across, and how we deal with them.
1. Unoptimized tables and queries
Most websites are built over CMS software and are database driven. Customization, plugins, third party apps, all these can involve complex code or poor quality queries.
These software can involve complicated sort and join queries, which can hog up all memory and cause MySQL to crash. Finding the sources of memory leaks is the first step.
Over time, the databases can grow in size and also fragments can form in them due to data deletions. Both these factors lead to memory wastage.
By examining the MySQL processes, slow queries and database sizes, we pinpoint the problem makers and take actions to fix them.
2. MySQL server configuration
The memory allocated to MySQL service depends on the various parameters configured in it. An approximate formula for MySQL memory usage is:
Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)
The buffer pool size, read, sort and join buffer sizes, max connections, temporary table size, all of them together determine the memory used.
In the servers we manage, we optimize the MySQL service to use the optimal memory, which does not exceed 60% of overall available RAM. This is done in an iterative manner, with tweaks done till the ideal value is reached.
[OK] Maximum reached memory usage: 2.0G (1.59% of installed RAM) [OK] Maximum possible memory usage: 26.9G (21.45% of installed RAM)
Allotting the optimal memory to MySQL service helps to ensure that MySQL does not crash due to insufficient memory and it is given the ideal resources that it needs.
3. Resource consuming processes
At times, there can be applications such as Java, Python, PHP, Coldfusion, backup software, 3rd party applications, etc. that consume too much memory.
There can be users who abuse the server resources, if not constrained well. Such processes and users can gobble up the entire server memory and crash services like MySQL.
To arrest a single process from abusing the server resources, we tweak all server applications to use not more than a fixed share of available memory. Setting user account limits is another precautionary measure.
4. Inadequate RAM
When every other aspect is examined and ruled out as not the reason, we can conclude that the server is not having sufficient memory for executing all the processes in it.
With the help of memory monitoring tools, we monitor the memory usage of various processes over time. Constant ‘Out of memory’ errors, even with normal traffic, indicate insufficient memory.
total used free shared buff/cache available Mem: 3645 1781 720 171 1143 1625 Swap: 3839 618 3221
If the memory is found to be inadequate, we initiate RAM upgrade, instance modification, container recreation, server migration, etc. as appropriate to the server scenario that we handle.
Database errors are a commonly encountered issue in web servers. Today we saw why “mysqld: out of memory” error happens in websites and how our Dedicated Hosting Engineers prevent it.