Reducing memory usage of mysql server in ec2 linux instance

sql-mem-usage.md

Reducing sql memory usage

Note: This link was followed for this answer.

This is primarily meant for low memory servers only. Before we go ahead with this, it is important to understand what exactly is mysql performance_schema.

This MySQL link defines performance_schema as follows:

The MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level. The Performance Schema has these characteristics:

• The Performance Schema provides a way to inspect internal execution of the server at runtime. It is implemented using the PERFORMANCE_SCHEMA storage engine and the performance_schema database. The Performance Schema focuses primarily on performance data. This differs from INFORMATION_SCHEMA, which serves for inspection of metadata.

• The Performance Schema monitors server events. An “event” is anything the server does that takes time and has been instrumented so that timing information can be collected. In general, an event could be a function call, a wait for the operating system, a stage of an SQL statement execution such as parsing or sorting, or an entire statement or group of statements. Event collection provides access to information about synchronization calls (such as for mutexes) file and table I/O, table locks, and so forth for the server and for several storage engines.

Note: You can also follow This link for a simpler explanation.

Procedure

• First disable any services which use the mysql server. For example, If using gunicorn or wsgi, disable them using sudo systemctl stop gunicorn

• Now edit the my.cnf file located in /etc/mysql/ using any editor, (for nano editor, use sudo nano <file_path>) and add the lines

  performance_schema = 0
  show_compatibility_56 = 1
  

• Now restart the mysql server using sudo /etc/init.d/mysql restart

• To check the memory usage use free --mega

Make sure that [mysqld] is present inside the my.cnf file, else it causes This error.