How Can We Help?
I have spent some time to get the configuration right and figuring out what is causing memory issues.
I have used MySQLTuner and Percona Toolkit to find the values, and various websites and searches on google:
MySQLTuner https://github.com/major/MySQLTuner-perl
Percona Toolkit:
Download: https://www.percona.com/software/database-tools/percona-toolkit
Pt-Stalk: https://www.percona.com/blog/2013/01/03/percona-toolkit-by-example-pt-stalk/
This is my initial running mysql conf.
[mysqld]
# I am assuming you have 500M of memory spare for your data
innodb_buffer_pool_size=500M
# This option is ignored by modern OS, no need to set it
#innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
# let innodb handle concurrency, comment this or it may create a bottleneck
#innodb_thread_concurrency=2
# Add a bigger transaction log, assuming you have 100MB extra disk for it
innodb_log_file_size = 8M
# The following assumes that durability is not important, but it may provide
# a huge boost in perfomarnace
#innodb_flush_log_at_trx_commit = 2
# the following lines are duplicated, remove them:
#innodb_buffer_pool_size=2M
#innodb_additional_mem_pool_size=500K
#innodb_log_buffer_size=500K
#innodb_thread_concurrency=2
#
# * Performance Metrics
#
query_cache_limit = 1M
query_cache_type = 0
query_cache_size = 0 #16M
table_definition_cache = -1 # should make it auto with -1
#
# * Fine Tuning
#
key_buffer = 16M
read_buffer = 60K
sort_buffer = 1M
innodb_buffer_pool_size = 64M
tmp_table = 16M
tmp_table_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
max_heap_table_size = 32M
join_buffer_size = 512K
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
max_connections = 50
#
# * Performance schema
#
performance_schema = on
#
# * Logging
#
# Enable the slow query log to see queries with especially long duration
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 5
log_slow_rate_limit = 1
#log_slow_verbosity = query_plan
log-queries-not-using-indexes
[mariadb]
wait_timeout=20
Also to check the configuration on how much memory it will consume, I use this script:
#!/bin/sh
# you might want to add some user authentication here
mysql -e "show variables; show status" | awk '
{
VAR[$1]=$2
}
END {
MAX_CONN = VAR["max_connections"]
MAX_USED_CONN = VAR["Max_used_connections"]
BASE_MEM=VAR["key_buffer_size"] + VAR["query_cache_size"] + VAR["innodb_buffer_pool_size"] + VAR["innodb_additional_mem_pool_size"] + VAR["innodb_log_buffer_size"]
MEM_PER_CONN=VAR["read_buffer_size"] + VAR["read_rnd_buffer_size"] + VAR["sort_buffer_size"] + VAR["join_buffer_size"] + VAR["binlog_cache_size"] + VAR["thread_stack"] + VAR["t$
MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN
MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONN
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "key_buffer_size", VAR["key_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "query_cache_size", VAR["query_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_buffer_pool_size", VAR["innodb_buffer_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_additional_mem_pool_size", VAR["innodb_additional_mem_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_log_buffer_size", VAR["innodb_log_buffer_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "BASE MEMORY", BASE_MEM/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "sort_buffer_size", VAR["sort_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_buffer_size", VAR["read_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_rnd_buffer_size", VAR["read_rnd_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "join_buffer_size", VAR["join_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "thread_stack", VAR["thread_stack"]/1048576
printf "| %40s | %15.3f MB |\n", "binlog_cache_size", VAR["binlog_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "tmp_table_size", VAR["tmp_table_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "MEMORY PER CONNECTION", MEM_PER_CONN/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %18d |\n", "Max_used_connections", MAX_USED_CONN
printf "| %40s | %18d |\n", "max_connections", MAX_CONN
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", MEM_TOTAL_MIN/1048576
printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", MEM_TOTAL_MAX/1048576
printf "+------------------------------------------+--------------------+\n"
}'
