#!/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 )
INNODB_BUFFER_LOGS=$(echo "$INNODB_BUFFER_POOL_SIZE/8192" | bc | cut -f 1 -d '.')
if (( $INNODB_BUFFER_LOGS < 2 )); then INNODB_BUFFER_LOGS=2; fi
INNODB_LOG_FILES_IN_GROUP=$( min $((INNODB_BUFFER_LOGS)) 100 )

DATADIR=`mysql -e 'show variables like "%datadir%"' | grep 'datadir' | awk -F " " '{print $2}'`
INNODB_LOG_GROUP_HOME_DIR=$DATADIR


if [ -f $INNODB_LOG_GROUP_HOME_DIR/ib_logfile0 ]; then
        LOGFILESIZE=$(stat --printf="%s" $INNODB_LOG_GROUP_HOME_DIR/ib_logfile0)
        if [ ! $LOGFILESIZE == 2147483648 ]; then rm -rf $INNODB_LOG_GROUP_HOME_DIR/ib_log*; else echo ""; fi;  else
        echo ""; fi
# The following is checking InnoDB read vs write operations. However this is data is not available on a MySQL restart. 
# Need to do some additional tests before these calculations can be used.
# IDBWrite=`mysql -e 'SHOW ENGINE INNODB STATUS \G' | grep 'Number of rows inserted ' | awk -F " " '{print $5}'`
# IDBWrite=${IDBWrite%?}
# if (( $IDBWrite < 1 )); then IDBWrite=1; fi
# IDBRead=`mysql -e 'SHOW ENGINE INNODB STATUS \G' | grep 'Number of rows inserted ' | awk -F " " '{print $11}'`
# if (( $IDBRead < 1 )); then IDBRead=2; fi

# IDBReadT=$(echo "scale=2; $IDBRead/$((IDBRead+IDBWrite))*64" | bc | cut -f 1 -d '.')
# IDBWriteT=$(echo "scale=2; $IDBWrite/$((IDBRead+IDBWrite))*64" | bc | cut -f 1 -d '.')

# if (( $IDBReadT < 8 )); then IDBReadT=8; fi
# if (( $IDBWriteT < 8 )); then IDBWriteT=8; fi

# IDBReadThreads=$( min $((IDBReadT)) 64 )
# IDBWriteThreads=$( min $((IDBWriteT)) 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_log_file_size            = 2G
innodb_log_files_in_group       = ${INNODB_LOG_FILES_IN_GROUP}
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

