CODEWITHSUNDEEP
mysqlmemoryout-of-memory
Beto Aveiga
Beto Aveiga

Reputation: 3690

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.

Upvotes: 22

Views: 24161

Answers (7)

Henrique Van Klaveren
Henrique Van Klaveren

Reputation: 1760

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.

Upvotes: 2

Arvy
Arvy

Reputation: 1212

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.

Upvotes: 29

JRichardsz
JRichardsz

Reputation: 16564

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"

Upvotes: 0

Hossein
Hossein

Reputation: 4559

Only you need:

[mysqld]
performance_schema = off

Upvotes: 42

waryat
waryat

Reputation: 41

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

Upvotes: 4

Rick James
Rick James

Reputation: 142518

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?

Upvotes: 1

Shadow
Shadow

Reputation: 34284

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.

Upvotes: 4

Related Questions