#!/usr/bin/bash
#
# Automatic tuning of mysqld parameters, this script is (typically) invoked by
# a systemd unit drop-in for mysqld specifying an ExecStartPre
#
# TODO: Try to detect hardware raid (or BBU more to the point?)
function min { echo $(($1<$2?$1:$2)); };
function max { echo $(($1>$2?$1:$2)); };

# Remove any legacy systemd LimitNOFILE autotune attempts
rm -f /etc/systemd/system/mysqld.service.d/autotune.conf

# Get the mysqld unit's LimitNOFILE since we can't tune that dynamically
SYSTEMD_LimitNOFILE=$(systemctl show -p LimitNOFILE mysqld | grep -Pio '(?<=^LimitNOFILE=)(\d+)$')
if [ -z "$SYSTEMD_LimitNOFILE" ]; then
  SYSTEMD_LimitNOFILE=$(ulimit -H -n)
  echo -e "\033[1;31mWARNING:\033[0m Unable to read LimitNOFILE for mysqld.service from systemd assuming ulimit -H value of $SYSTEMD_LimitNOFILE" >&2
fi

NPROC=$(nproc)
MEMMB=$(
cat /proc/meminfo <(ulimit -m) <(ulimit -v) \
 | awk '/^[0-9]+$/{print int($1/1024)}; /^MemTotal:/{print int($2/1024)}' \
 | sort -n \
 | head -1
)
CORES=$(grep -c ^processor /proc/cpuinfo)
THREADS=$((CORES*20/26))

if [ $MEMMB -le $((32*1024)) ]; then
        INNODB_BUFFER_POOL_SIZE=$(echo "$MEMMB*0.36" | bc | cut -f 1 -d '.')                        
else
        INNODB_BUFFER_POOL_SIZE=$(echo "$MEMMB*0.40" | bc | cut -f 1 -d '.')
fi
INNODB_BUFFER_POOL_INSTANCE=$(echo "$INNODB_BUFFER_POOL_SIZE/1024" | bc | cut -f 1 -d '.')
INNODB_BUFFER_POOL_INSTANCES=$( min $((INNODB_BUFFER_POOL_INSTANCE)) 64 )

# MyISAM Key Buffer
KEY_BUFFER_SIZE=$( min 2048 $((MEMMB/16)) )

# Definition cache should scale fine
TABLE_DEFINITION_CACHE=$( min 16384 $((MEMMB/2)) )

# Open cache scalabity is a problem, don't set it too high...
TABLE_OPEN_CACHE=$( min 16384 $((MEMMB/2)) )

# Set the open_files_limit based on the connections + table_open_cache
# Note that TEMP tables generally use at least TWO file descriptors the numbers
# below are file-descriptor counts NOT temp table count
TEMP_TABLES_BASE=$( max 65536 $((MEMMB*4)) )
TEMP_TABLES_CONN=$(( MAX_CONNECTIONS * 8 ))
OPEN_FILES_LIMIT=$(( 20 + MAX_CONNECTIONS + $((TABLE_OPEN_CACHE*3)) + TEMP_TABLES_BASE + TEMP_TABLES_CONN ))

# But capped by the systemd NOFile ulimit setting
OPEN_FILES_LIMIT=$( min $OPEN_FILES_LIMIT $((SYSTEMD_LimitNOFILE-256)) )

#MAX_CONNECTIONS_CPU=$((96*NPROC))
MAX_CONNECTIONS_RAM=$((MEMMB/64))
MAX_CONNECTIONS=$( min $((MAX_CONNECTIONS_RAM)) 1024)

THREAD_CACHE_SIZE=$(echo "$MAX_CONNECTIONS/10+24" | bc | cut -f 1 -d '.')

cat <<-EOD >/etc/mysql/conf.d/40-autotune.cnf
;
; * DO NOT EDIT * DO NOT EDIT * DO NOT EDIT *
;
;       Automatic tuning by $0
;
; * DO NOT EDIT * DO NOT EDIT * DO NOT EDIT *
;
; Manual edits should be made in 50-tuning.conf which will override the
; values in this file as this file will be overwritten every time mysqld
; is restarted
;
[mysqld]
# Server Max Memory             = ${MEMMB}
open_files_limit                = ${OPEN_FILES_LIMIT}

table_definition_cache          = ${TABLE_DEFINITION_CACHE}
table_open_cache                = ${TABLE_OPEN_CACHE}

key_buffer_size                 = ${KEY_BUFFER_SIZE}M

net_buffer_length               = 31744
net_retry_count                 = 100
net_read_timeout                = 3600
net_write_timeout               = 3600
max_allowed_packet              = 1024M


; Query cache size capped for performance and preference given to small queries
query_prealloc_size             = 65535
query_alloc_block_size          = 131072

; InnoDB prevent semaphore time lock crash
innodb_adaptive_hash_index      = 0

; InnoDB performance tweaks
innodb_print_all_deadlocks      = 1
innodb_buffer_pool_size         = ${INNODB_BUFFER_POOL_SIZE}M
innodb_buffer_pool_instances    = ${INNODB_BUFFER_POOL_INSTANCES}
innodb_log_group_home_dir       = ${INNODB_LOG_GROUP_HOME_DIR}
innodb_redo_log_capacity        = 2G
innodb_log_buffer_size          = 16M
innodb_flush_log_at_trx_commit  = 2
innodb_sort_buffer_size         = 2M
innodb_fast_shutdown            = 1
innodb_read_io_threads          = 48
innodb_write_io_threads         = 16
innodb_thread_concurrency       = ${THREADS}
thread_cache_size               = ${THREAD_CACHE_SIZE}
binlog_cache_size               = 2M
binlog_stmt_cache_size          = 2M

; Buffers trimmed for performance 
sort_buffer_size                = 256K
myisam_sort_buffer_size         = 64M
read_rnd_buffer_size            = 256K
read_buffer_size                = 256K
join_buffer_size                = 256K


; Max connections capped for performance
max_connections              = ${MAX_CONNECTIONS}

EOD
