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’ Support Engineers 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.

Conclusion

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 Support Engineers prevent it.