23

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.

Improve this question

7 Answers 7

Reset to default
46

Only you need:

[mysqld]
performance_schema = off
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

this single one made the difference for me, reducing from 175M to 37M of actual memory used.
this is the best answer in the whole internet ever !
in which file is this parameter?
file is in /etc/mysql/my.cnf
Mine went from 390M to 181M.
|
31

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
tmp_table_size = 1M
innodb_buffer_pool_size = 1M
innodb_log_buffer_size = 1M
max_connections = 25
sort_buffer_size = 512K
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.

Improve this answer

3 Comments

query_cache_size, query_cache_limit were removed in MySQL 8.
512M sort buffer?!?
Hey, fixed! 512K
4

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.

Improve this answer

3 Comments

80% is too much for a tiny VM.
I did not say he should set it t 80%, I pointed out this particular setting gecause it is generally set to a high level because the recomjended setting is 80%.
Thanks. innodb_buffer_pool_size is the most important here. I couldn't get it to work with my config at first because MySQL was inside a docker container and my settings for the container were wrong. Now the container is reading the my.cnf file and everything works as expected.
4

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 http://fedoraproject.org/wiki/Systemd

[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
Improve this answer

2 Comments

Shouldn't query-cache-limit be query_cache_limit ?
You probably meant sort_buffer_size = 512K instead of sort_buffer_size = 512M.
3

How I Reduced MySQL 9 Memory Usage to Just ~130MB with Simple Configurations

If you're struggling with MySQL's high memory usage and need a lightweight configuration, I managed to drop MySQL 9's memory consumption from 512MB (default) to just 130MB with these tweaks. Perfect for low-demand environments like development or small servers.

My Configuration (With Default Values for Reference)

[mysqld]
# General
performance_schema=off          # Default: on
skip-name-resolve               # Default: off
event_scheduler=off             # Default: on

# Memory and Buffers
innodb_buffer_pool_size=16M     # Default: 128M
innodb_log_buffer_size=4M       # Default: 16M
read_rnd_buffer_size=256K       # Default: 256K
read_buffer_size=512K           # Default: 128K
key_buffer_size=4M              # Default: 8M
tmp_table_size=16M              # Default: 16M
max_heap_table_size=16M         # Default: 16M

# Connections
max_connections=10              # Default: 151
thread_stack=192K               # Default: 256K
max_allowed_packet=1M           # Default: 16M
sort_buffer_size=128K           # Default: 2M
join_buffer_size=256K           # Default: 256K

# Cache and Tables
table_open_cache=64             # Default: 2000

# Logs
log_error_verbosity=1           # Default: 3
max_binlog_size=1M              # Default: 1G
skip-log-bin                    # Default: log-bin enabled

# Open Files
open_files_limit=64             # Default: 1024

Key Tweaks That Worked for Me

  • Disabled Unnecessary Features Turned off performance_schema and event_scheduler to save memory used for monitoring and scheduling.
  • Reduced Buffer Sizes Adjusted innodb_buffer_pool_size to 16MB (from 128MB) and optimized per-connection buffers like read_buffer_size and sort_buffer_size.
  • Limited Connections Set max_connections to 10. Each connection consumes memory, so keeping this low was crucial.
  • Disabled Binary Logs Using skip-log-bin saved additional memory and storage, ideal for non-production setups.

The Result: From 512MB to Just 130MB This configuration drastically reduced MySQL's memory footprint without compromising functionality for small workloads. It’s perfect for lightweight use cases like development environments or single-purpose servers.

Improve this answer

1 Comment

This is excellent! Saved me from hours of troubleshooting! Thanks
1

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?

Improve this answer

1 Comment

Thanks Rick. MySQL wasn't reading my "my.cnf" file, that made me doubt about what I have to tweak in order to make it work. MySQL was inside a docker container and the config wasn't right. MySQL could work perfectly with low memory consumption in a DEV environment.
0

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"
Improve this answer

1 Comment

Docker also allows you to mount a custom my.cnf config, I highly recommend to do it. More readable and handy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.