Mysql – Reducing memory consumption of thesql on ubuntu@aws micro instance

amazon ec2amazon-web-servicesmemory-managementMySQLmysql-management

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.

Best Answer

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

Related Solutions

Mysql – Install MySQL on Ubuntu without a password prompt

sudo debconf-set-selections <<< 'mysql-server mysql-server/root_password password your_password'
sudo debconf-set-selections <<< 'mysql-server mysql-server/root_password_again password your_password'
sudo apt-get -y install mysql-server

For specific versions, such as mysql-server-5.6, you'll need to specify the version in like this:

sudo debconf-set-selections <<< 'mysql-server-5.6 mysql-server/root_password password your_password'
sudo debconf-set-selections <<< 'mysql-server-5.6 mysql-server/root_password_again password your_password'
sudo apt-get -y install mysql-server-5.6

For mysql-community-server, the keys are slightly different:

sudo debconf-set-selections <<< 'mysql-community-server mysql-community-server/root-pass password your_password'
sudo debconf-set-selections <<< 'mysql-community-server mysql-community-server/re-root-pass password your_password'
sudo apt-get -y install mysql-community-server

Replace your_password with the desired root password. (it seems your_password can also be left blank for a blank root password.)

If your shell doesn't support here-strings (zsh, ksh93 and bash support them), use:

echo ... | sudo debconf-set-selections

R – “UNPROTECTED PRIVATE KEY FILE!” Error using SSH into Amazon EC2 Instance (AWS)

The problem is having wrong mod on the file.

Easily solved by executing -

chmod 400 mykey.pem

Taken from Amazon's instructions -

Your key file must not be publicly viewable for SSH to work. Use this command if needed: chmod 400 mykey.pem

400 protects it by making it read only and only for the owner.

Best Answer

Related Solutions

Mysql – Install MySQL on Ubuntu without a password prompt

R – “UNPROTECTED PRIVATE KEY FILE!” Error using SSH into Amazon EC2 Instance (AWS)

Related Topic