45

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.

Bilesh Ganguly
  • 3,792
  • 3
  • 36
  • 58
Raghav
  • 2,128
  • 5
  • 27
  • 46
  • How are you defining memory consumed ? There are lots of MySQL settings that control memory size. – Frederick Cheung May 20 '12 at 20:04
  • I am no mysql admin, but I see that on the system, nothing else is running except the OS itself and mysql server, and more than 500M is consumed... – Raghav May 20 '12 at 20:16
  • Optimize your queries! Look at the slow query log to see the exact queries. Probably a lot of RAM is wasted in temporary tables – Maxim Krizhanovsky May 20 '12 at 20:25

5 Answers5

126

As of MySQL 8.0.30:

Edit your /etc/mysql/my.cnf file and add the following:

[mysqld]
performance_schema = 0

Restart your mysql server and happiness should ensue.

To verify that the configuration change has been loaded correctly, start a new mysql session (e.g. mysql -u root -p) and run the following:

SHOW VARIABLES LIKE '%perf%';

You should see the following line at the top:

| performance_schema  | OFF   |

It should read OFF. If it reads ON, your config was not properly loaded for some reason.

Mahn
  • 16,261
  • 16
  • 62
  • 78
  • 3
    Cut mine from 400MB to 25MB. – Isaac Oct 18 '13 at 11:14
  • 6
    This is very helpful for low memory servers. Thanks ! FYI, show_compatibility_56 = 1 is removed MySQL 8.0.1 onwards https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html – Abhishek Agarwal Jun 09 '18 at 17:43
  • FYI These two are set by default on AWS Aurora RDS 5.7 – Ryan Shillington Mar 27 '19 at 12:51
  • 1
    Did not require `show_compatibility_56 = 1` on MySQL 8.0.19 for me to go from 400MB to 95MB. – Snake Mar 20 '20 at 01:51
  • 9
    On my system (Ubuntu 20.04, mysql 8.0.22), I found the config file at `/etc/mysql/my.cnf` and had to add a line `[mysqld]` before `performance_schema = 0`. Reduced memory usage from ~350MB to 130MB. – Erik Koopmans Dec 19 '20 at 12:45
  • This should be, at least, part of the accepted answer. One simple parameter to remove all not strictly necessary RAM usage. – freesoul Dec 03 '22 at 20:32
  • 3
    This answer is missing an explanation of what exactly `performance_schema` is and what the implications of disabling it are. – Hashim Aziz Feb 02 '23 at 20:20
43

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
Pang
  • 9,564
  • 146
  • 81
  • 122
user1391670
  • 476
  • 5
  • 6
  • 6
    See also Mahn's answer, which is tremendously more radical and efficient. – Denis de Bernardy Oct 27 '13 at 18:28
  • I went for 1.5G to 0.5G. Thanks a lot – Wistar Dec 21 '15 at 19:15
  • 1
    This answer combined with Mahn's decreased the usage from 21% of RAM on t2.micro to 0.2%!!! It went from 200M to 2M – Ricardo Gonçalves Dec 30 '15 at 20:23
  • 5
    key_buffer was giving me errors on mysql 5.7. I'm using key_buffer_size instead – Ivan Monteiro Oct 09 '19 at 23:01
  • 3
    running MySQL 8.0.17. Apart from replacing ```key_buffer``` with ```key_buffer_size``` as Ivan suggests, I had to remove the ```query_cache_size``` and ```query_cache_limit``` lines. MySQL docs say "The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. " – mimo31 Jul 31 '20 at 21:12
3

Just to add to the other answer. I recently had this problem myself with the Amazon micro instance (not Ubuntu). The my.cnf file is almost empty so what I did was this:

cp /etc/my.cnf /etc/my.cnf.orig
cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

Edit my.cnf and enable the innodb lines if applicable. Restart mysqld.

Also the micro instance has no swap, that might be a problem..

SWAPFILE=/mnt/swapfile.swap
dd if=/dev/zero of=$SWAPFILE bs=1M count=512
mkswap $SWAPFILE
swapon $SWAPFILE

Then in /etc/rc.local add:

swapon /mnt/swapfile.swap

To save memory in ruby you might want to use ruby enterprise:

bash < <(curl -s https://raw.github.com/wayneeseguin/rvm/master/binscripts/rvm-installer)
gpasswd -a root rvm
source /etc/profile.d/rvm.sh
rvm get head
rvm reload
rvm install ree
rvm --default use ree
EivinGS
  • 41
  • 2
  • Thanks for tips on my.cfg. I am currently using a ruby built from source. REE page says that it would bring down memory usage by about 30%. However, another question that i couldnt find answer to, is rails 3.2.2 going to work fine with ruby 1.8.7 (REE is based/compatible with ruby 1.8.7)... – Raghav May 25 '12 at 05:42
  • When installing rails with gem after installing ree you get 3.2.3. – EivinGS May 26 '12 at 13:48
  • Is the swap working when doing swapon /mnt/swapfile.swap but not starting at boot? If your rc.local file has this at the top: #!/bin/sh -e, you can try to remove the -e. – EivinGS May 26 '12 at 13:52
  • 2
    Enabling a swap file is the single most valuable response to my months long trouble with Amazon micro. This helped tremendously compared to fiddling with my.cnf which frankly makes no difference with only 600KB RAM. – Slawa Sep 10 '13 at 23:30
1

I have a server with only 500mb ram and found that mysql started using a lot of ram as my tables got larger. After playing with a bunch of the settings, what reduced memory usage for me was to convert all my tables to MyISAM. If you dont need the features of innodb converting tables to MyISAM helps quite a bit. You can convert tables like this :

ALTER TABLE test.mytable ENGINE=MyISAM;

After this change I found that memory usage decreased by 20%. To get a further reduction in memory usage you can convert ALL of your tables to MyISAM and then turn off innodb support in mysql altogether. This reduced my memory usage by 50%.

You can do this by adding :

[mysqld]
default_storage_engine=myisam
innodb=OFF

and then restarting mysql.

Rockvole
  • 4,422
  • 3
  • 21
  • 27
  • 3
    I would advise against MyISAM based on the number of unclean shutdowns I've seen that caused data corruption. See Reliability section "MyISAM offers no data integrity - Hardware failures, unclean shutdowns and canceled operations can cause the data to become corrupt." https://stackoverflow.com/questions/20148/myisam-versus-innodb – Feczo Mar 21 '19 at 06:41
  • Yeah, if MyISAM works for you, you should consider NoSQL. You clearly don't need ACID transactions. – Ryan Shillington Mar 27 '19 at 11:52
0

Configure Swapfile
 sudo fallocate -l 2G /swapfile
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile
sudo cp /etc/fstab /etc/fstab.bak
sudo nano /etc/fstab

Add the following line at the end, save and close:
 /swapfile swap swap defaults 0 0

sudo sysctl vm.swappiness=10 sudo vi /etc/sysctl.conf

Add the following line at the end, save and close: vm.swappiness=10

Configure PHP
 sudo nano /opt/bitnami/php/etc/memory.conf

; Bitnami memory configuration for PHP-FPM
;
; Note: This will be modified on server size changes
pm.max_children=10
pm.start_servers=2
pm.min_spare_servers=2
pm.max_spare_servers=10
pm.max_requests=5000

Configure MariaDB (MySQL)
 sudo nano /opt/bitnami/mariadb/conf/bitnami/memory.conf

[mysqld]
#wait_timeout = 120
long_query_time = 1
query_cache_limit=2M
query_cache_type=1
query_cache_size=8M
innodb_buffer_pool_size=16M
#innodb_log_file_size=128M
#innodb_flush_method=O_DIRECT
tmp_table_size=16M
max_connections = 100
max_user_connections = 250
key_buffer_size=8M

sudo /opt/bitnami/ctlscript.sh restart

Scotch Design
  • 1,016
  • 9
  • 10