Reasons for the error “mysqld: out of memory”

You will have gone through the error called “500 server error” or “503 services unavailable”. What will your first step when you get MySQL errors? Of course! Your search will be on server logs and MySQL service logs.

You might get an error if the error occurred because of the issues in memory it will crash MySQL service.

Here’s the output of the error:

Code:

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)

From the statement “mysqld: out of memory” states that your server goes out of memory now. You would have assigned some memory allocation to the server at the beginning. It’s getting shortage now! Try to do a RAM upgrade or do the examination on server memory. In most cases, users won’t prefer to do a RAM upgrade cos, of its cost. If you even add some memory space, some queries might eat up your space in the server.

There won’t be enough space in MySQL service memory allocation. Better go for tweaking MySQL service for good results.

Let us look after the source for those errors one by one:

Queries and Tables are not optimized:

Overall, websites are created based on database driven and CMS software. You will add up other modes like third party application, plugins, themes, customization which have queries of very poor quality. This type of queries will lead you to get in MySQL crash just by giving memory hog, simply joining queries etc.

Sometimes, data deletions may have fragmented and simultaneously data size may grow rapidly. No use in both methods! You can fix this problem by taking necessary actions on the size of the database, slow queries and MySQL process.

Configuration in MySQL server:

Based on the parameter you configure the MySQL service gets advice for memory allocation.

Here’s the formula for memory usage in MySQL:

Code:

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)

Memory is calculated often based on the size of the temporary table, max connections, buffer sizes joining, sort, read, and size of the buffer pool. Now, we are going to perform MySQL service optimization which uses only 60% space from RAM.

Code:

[OK] Maximum reached memory usage: 2.0G (1.59% of installed RAM)
[OK] Maximum possible memory usage: 26.9G (21.45% of installed RAM)

By this, you can make sure that the MySQL service crash won’t happen due to optimal memory.

Process of resource consumption:

Memory consumption applications are third party applications, software backup, Coldfusion, PHP, Python and Java. Users would simply blame servers instead of these applications due to memory consumption. One application may crash the entire MySQL service so better instruct all the server applications to use only a given number of memory space.

RAM inadequate:

Once if you confirm that other reasons are not a major reason for memory allocation crash better go with the option for server which has insufficient memory size. There are more memory monitoring tools available in the market which helps you to ensure the time taken by the processes. Sometimes, normal traffic error without memory will give you a statement as insufficient memory.

Code:

      total used free shared buff/cache available

Mem:  3645  1781 720    171    1143      1625

Swap: 3839  618  3221

Inadequate memory can be sorted by upgrading RAM, modification instance, recreated container, migration in server etc.

So, we have listed four reasons for MySQL service crash and out of memory! Take everything as first step!

Reasons for the error “mysqld: out of memory”

More resources from kumkumsharma

Share this resource