Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 405
Beto Aveiga Avatar asked Oct 22 '16 06:10

Beto Aveiga


People also ask

Why does MySQL use so much memory?

Memory Allocation in MySQLMemory plays a significant resource for speed and efficiency when handling concurrent transactions and running big queries. Each thread in MySQL demands memory which is used to manage client connections, and these threads share the same base memory.

Which MySQL case eats all memory?

First of all, there are 3 major cases when MySQL will crash due to running out of memory: MySQL tries to allocate more memory than available because we specifically told it to do so. For example: you did not set innodb_buffer_pool_size correctly. This is very easy to fix.


2 Answers

Only you need:

[mysqld]
performance_schema = off
like image 172
Hossein Avatar answered Sep 18 '22 20:09

Hossein


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.

like image 22
Arvy Avatar answered Sep 18 '22 20:09

Arvy