How to make MySQL use less memory?

I'm trying to make a database update with Drush to a Drupal 7 site with just a few nodes and 85 modules... As the update dies almost every time is clear that, for a VPS with 512MB with it's containerized services (mysql, nginx, php-fpm, etc.), there isn't enough memory...

Everytime MySQL gets killed by the kernel as far I can understand from the logs:

Out of memory: Kill process 4310 (mysqld)

My question is, how can I configure MySQL service to avoid it's "assassination"? What are the parameters in the MySQL configuration file to lower the memory consumption of the mysqld process?

I'm in DEV so I don't mind if the process becomes slow. I just want to know what parameters I have to tweak to survive the update process without increasing the memory.

Thanks for your help.

6 Answers

Only you need:

[mysqld] performance_schema = off 

My config in a 512 Mb RAM at Vultr, with Fedora 29 and MariaDB. Using about 26% of RAM.

[mysqld] performance_schema = off key_buffer_size = 16M query_cache_size = 2M query_cache_limit = 1M tmp_table_size = 1M innodb_buffer_pool_size = 1M innodb_log_buffer_size = 1M max_connections = 25 sort_buffer_size = 512M read_buffer_size = 256K read_rnd_buffer_size = 512K join_buffer_size = 128K thread_stack = 196K 

In small VPSs, don't forget to have a swap enabled. In Vultr, for example, the default is no swap. Same in Digital Ocean.

This config working for me on VPS 512M, hope this helps...

[mysqld] performance_schema = off port = 3306 datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock key_buffer_size = 16M query_cache_size = 2M query-cache-limit = 1M tmp_table_size = 1M innodb_buffer_pool_size = 1M innodb_additional_mem_pool_size = 1M innodb_log_buffer_size = 1M max_connections = 25 sort_buffer_size = 512M read_buffer_size = 256K read_rnd_buffer_size = 512K join_buffer_size = 128K thread_stack = 196K binlog_cache_size = 0M # Disabling symbolic-links is recommended to prevent assorted security risks #symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory # !includedir /etc/my.cnf.d 

There are dozens of such parameters, you can find all of them with detailed description in the mysql server system variables documentation. In general, look for variables that have the word size in them. In particular, check out innodb_buffer_pool_size because the default value is 128MB and the recommended value on a dedicated server is 80% of the physical memory.

You have a VM with a minuscule 512MB of RAM? And you are running multiple things (Drupal, MySQL)? You will be lucky to get it to work at all. These setting may suffice to shrink MySQL enough to run:

key_buffer_size = 5M innodb_buffer_pool_size = 60M 

Meanwhile, do not increase any values in my.cnf. If my suggestions are not enough; let's see all of your my.cnf; there may be other things that can reasonably be decreased.

Which version of MySQL are you running?

I know that question is not related to docker, but in case of someone is using mysql with docker, I was able to run a mysql container in a very cheap server with 512 mb.

I used these docker-compose parameters based on @Hossein answer: mysqld --performance_schema=off

services: mysql57: image: mysql:5.7 command: mysqld --performance_schema=off container_name: mysql57 restart: always ports: - "3306:3306" volumes: - /opt/mysql_data:/var/lib/mysql environment: MYSQL_ROOT_PASSWORD: changeme MYSQL_USER: usr_acme MYSQL_PASSWORD: changeme MYSQL_DATABASE: db_acme TZ: America/Lima deploy: resources: limits: memory: 400M 

Before this, without mysqld --performance_schema=off, server got very slow and after some minutes, nobody could connect.

You can add more parameters in command in order to reduce the ram/cpu usage:

command: - "mysqld" - "--performance_schema=off" - "--query_cache_size=2M" - "--query_cache_limit=1M" - "--foo=bar"