1

We have a t2.medium instance on AWS' RDS platform (4gb, 2xCpu). We're using MariaDB 10.1.19 with a single database, ~120 tables and ~2gb of data in total.

After running for about a week we get this error, at first only some requests, then on the heavy requests and then it starts affecting more queries. Eventually resulting in a reboot.

Php (PDO) error:

General error: 5 Out of memory (Needed 262680 bytes) 

Mysql (from error.log)

2017-06-14 10:55:29 47531169896192 [ERROR] mysqld: Out of memory (Needed 262680 bytes)

This shows the 'freeable memory' on the instance (the big spike being when we rebooted).

Freeable Memory

We're were using default mysql config. We ran mysqltuner it mentioned:

Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
join_buffer_size (> 512.0K, or always use indexes with joins)
thread_cache_size (start at 4)
performance_schema = ON enable PFS
innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=1G) if possible.
innodb_buffer_pool_instances(=2)

So we've since tried increasing the join_buffer_size (to 512k) and increased the key_buffer_size (to 16M) but didn't seem to make any difference. Not sure if the error is the result of the OS having no ram available, or the mysql process itself.

Any suggestions? (Thanks)

3
  • mysqltuner seems to give almost universally sketchy advice, and this is no exception. You're short of memory, so make some buffers bigger so they'll demand even more memory... seems legit (not). Any idea what kind of query triggers the error? Commented Jun 14, 2017 at 15:40
  • Hi @Michael-sqlbot it occurs first on queries which have many heavy joins, a few of these we were able to optimize to get to run without the error. Do you think decreasing the innodb_buffer_pool_size would help?
    – Edd Turtle
    Commented Jun 15, 2017 at 8:09
  • It would likely help this specific problem, but at a cost of somewhat lower performance overall. Are you familiar with EXPLAIN SELECT ...? I assume so, since you said "optimize," but... you know, just checking. :) Commented Jun 15, 2017 at 11:31

1 Answer 1

1

Usually the quick fix is to decrease the biggest memory usage -- which should be innodb_buffer_pool_size. What is that currently set to? Do you have other applications running on the same VM? How much room are they taking?

On a 4GB 'machine', I don't recomment innodb_buffer_pool_size be higher than 1500M, but less if the server is not dedicated to MySQL.

4
  • Thanks Rick, we lowered it 2.2G, and the problem occurs but much less frequently, so that seems to be the problem. We'll lower it further and monitor. Why the defaults on AWS aren't less, I've no idea.
    – Edd Turtle
    Commented Jun 30, 2017 at 11:23
  • What was the default on AWS? If it is larger than 2G for a 4G VM, file a bug report. Meanwhile, I suggest that 2.2G is dangerously high. Swapping is very bad for MySQL; no swap space is deadly.
    – Rick James
    Commented Jun 30, 2017 at 16:28
  • Yep, just checked, the default is {DBInstanceClassMemory*3/4} (so 3G)
    – Edd Turtle
    Commented Jul 5, 2017 at 10:15
  • DBInstanceClassMemory is the RAM size adjusted for OS, etc, so it is not really 4GB. See, for example, forums.aws.amazon.com/thread.jspa?threadID=116126 .
    – Rick James
    Commented Jul 5, 2017 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.