Recommended settings for mysql with less than 3gb ram

v3
#1

I’m using a basic ee configuration Ubuntu 16.04 with redis caching. I was noticing occasional mysql crashes, and researching on this site it seems easyengine default configuration is for a server with at least 3gb of ram. This site mentions that when setting up ee customizes for server resources, but this doesn’t seem to be the case for mysql.

I’ve seen recommendations like use mysqltuner but the suggestions can be hard to understand I’m not real knowledgeable about mysql. I just went ahead and increased my vps to 3gb but my site doesn’t get that many hits and it feels like a waste. Could someone just give some easy copy and paste my.cnf settings for a stock ee config for example with 512mb, 1gb, and 2gb ram configurations?

#2

This

TL;DR

innodb_buffer_pool_size = 70% of RAM key_buffer_size = 20% of RAM

The 20%/70% assumes you have at least 4GB of RAM. If you have a tiny antique, or a tiny VM, then those percentages are too high.

max_connections, thread_stack Each “thread” takes some amount of RAM. This used to be about 200KB; 100 threads would be 20MB, not a signifcant size. If you have max_connections = 1000, then you are talking about 200MB, maybe more. Having that many connections probably implies other issues that should be addressed.

and this

TL;DR

innodb_buffer_pool_size = 1G # (adjust value here, 50%-70% of total RAM) innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0 innodb_flush_method = O_DIRECT

#3

Thanks. From what I could gather on my 2GB ram vps, I’ve upped innodb_buffer_pool_size to 1G, set query_cache_type to OFF and key_buffer_size to 10M sense I updated all my databases to InnoDB already. If anyone has any other suggestions let me know. I’m thinking I’ve got to do some more tuning mysqltuner says my max MySQL memory is 1.9G and dangerously high.

#4

Could you paste what’s reported by mysqltuner?

Anyway what’s your website exactly? WordPress or Forum?

#5