We run a MySQL dump backup every 4 hours, and today noticed:
Apr 19 20:09:30 mysql1 kernel: Out of memory: Kill process 3046 (mysqld) score 970 or sacrifice child
Apr 19 20:09:30 mysql1 kernel: Killed process 3046, UID 27, (mysqld) total-vm:12617880kB, anon-rss:7932424kB, file-rss:24kB
Apr 19 20:09:30 mysql1 mysqld_safe: Number of processes running now: 0
Apr 19 20:09:30 mysql1 mysqld_safe: mysqld restarted
See the entire log at:
https://gist.github.com/nodesocket/daf77fb923b4bf6bef1f
The server is running 4 cpus and 16GB of memory with SSD disks. I thought the OS would swap instead of just crashing mysqld completely.
I suppose upgrading the server to 32GB of memory, would probably help, but wondering if we can optimize any settings in our my.cnf before we commit more physical resources and money.
------------------------------------
my.cnf
-------------------------------------
[mysqld]
datadir=/mysql/data
socket=/mysql/mysqld.sock
symbolic-links=0
default-storage-engine=InnoDB
slow_query_log=1
long_query_time=2
slow_query_log_file=/var/log/mysql_slow.log
expire_logs_days=30
max_connections=50
bind-address=XXX.XXX.XXX.XXXX
port=3306
max_allowed_packet=4M
net_retry_count=5
max_connect_errors=100
wait_timeout=14400
connect_timeout=10
open_files_limit=65535
innodb_open_files=65535
key_buffer_size=256M
innodb_buffer_pool_size=4096M
innodb_log_buffer_size=4M
group_concat_max_len=16k
max_sort_length=16k
max_length_for_sort_data=16k
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
innodb_thread_concurrency=8
thread_concurrency=8
thread_cache_size=128
thread_stack=1M
read_buffer_size=1M
join_buffer_size=1M
sort_buffer_size=1M
read_rnd_buffer_size=1M
table_open_cache=32768
table_definition_cache=65535
tmp_table_size=33554432
max_heap_table_size=33554432
innodb_log_file_size=1024M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=0
log-warnings
innodb_flush_neighbors=0
user=mysql
old_passwords=0
[mysqld_safe]
syslog
pid-file=/var/run/mysqld/mysqld.pid