Reducing memory consumption of mysql on ubuntu@aws micro instance

Question

I have recently started on a PoC project wherein we are developing a small web app. The initial setup is done on a micro instance from AWS. We are on rails+mysql stack.

After installing/running MySQL, I see that about 500+ MB RAM has been consumed already; leaving quite less for rest of the systems (micro instances have barely 620 MB RAM).

Our app is fairly simple at this stage. Can I do something to reduce the memory consumed by MySQL server?

Appreciate the help.

Answer 1

In your /etc/my.cnf file:

performance_schema = 0 

And restart MySQL. This should chop memory usage dramatically if you previously had it on.

---

Edit: For MySQL versions between 5.7.8 and 8.0.1 (not required from 8.0.1 onwards), the above is not enough to free your memory of performance schema data:

As of MySQL 5.7.8, even when the Performance Schema is disabled, it continues to populate the global_variables, session_variables, global_status, and session_status tables.

(source)

To prevent this behaviour, set show_compatibility_56 to 1 in addition to performance_schema. That is to say, your my.cnf changes should look like:

performance_schema = 0 show_compatibility_56 = 1 

Answer 2

Change this setting in the MySQL configuration file (my.cnf)

key_buffer              = 8M  max_connections         = 30 # Limit connections query_cache_size        = 8M # try 4m if not enough  query_cache_limit       = 512K thread_stack            = 128K