Tuning DB. Configuración inicial para MySql o Postgres.

tuningdb.sh

#!/bin/bash -       
#title			:tuningdb.sh
#description	:This script makes an initial calculation of the recommended values ​​for the tuning of DB.
#author			:celtha
#date			:20150919
#version 		:0.5    
#usage			:bash tuningdb.sh
#notes 			:N.A
#==============================================================================

##Tunning Mysql / Postgres
#

#FUNCTIONS
confirmation(){
   read -p "Pulsa [Enter] para continuar..."
}
get_mem()
{
	v_memory=`free -m|grep -i mem|awk '{print $2}'`
	echo $v_memory
}
get_effective_cache_size()
{
	v_memo=$1
	v_buffer=`cat /proc/meminfo|grep -w Buffers:|awk '{print $2}'`
	v_cached=`cat /proc/meminfo|grep -w Cached:|awk '{print $2}'`
	v_cached=`echo "$v_cached/1024"|bc`
	v_buffer=`echo "$v_buffer/1024"|bc`
	v_cache=`echo "$v_cached+$v_buffer"|bc`
	v_cached_res=`echo "$v_memo+$v_cache"|bc`
	echo $v_cached_res

}

get_maintenance_work_mem()
{
	v_maintenance=`echo "($1/1024)*50"|bc`
	echo $v_maintenance

}

#MAIN
echo "Que motor de BBDD vamos a ajustar..."
echo "Mysql / Postgres"
echo ""
echo -n "Indica la opcion: "
read VAR
echo --------------------------------
echo --------------------------------

VAR=`echo $VAR | tr [:upper:] [:lower:]`

case $VAR in
	mysql )
		mem=$(get_mem)
	  	echo "Calculado sobre InnoDB..."
	  	confirmation
	  	clear
	  	echo ""
	  	echo "---DEFAULT-STORAGE-ENGINE---"
		echo "Valor fijo sobre documentacion:"
		echo "default-storage-engine = innodb"
		read -p "Engine por defecto [innodb]: " default_storage_engine
		default_storage_engine=${default_storage_engine:-innodb}
		echo $default_storage_engine
		#La variable mysql es con guion "-" no con subguion "_"
		echo ""
		clear
		echo ""
	  	echo "---INNODB_BUFFER_POOL_SIZE---"
	  	echo "Valor inicial de 4096M..."
	  	echo "Tras un periodo de funcionamiento debemos ajustar con el valor de la siguiente query.."
	  	echo "SELECT CEILING(SUM(data_length+index_length)/POWER(1024,2)) RIBPS FROM information_schema.tables WHERE engine='InnoDB';"
	  	read -p "Innodb_buffer_pool_size en MB [4096]: " innodb_buffer_pool_size
		innodb_buffer_pool_size=${innodb_buffer_pool_size:-4096}
		echo $innodb_buffer_pool_size
		echo ""
		clear
		echo ""
		echo "---INNODB_FILE_PER_TABLE---"
		echo "Valor fijo sobre documentacion:"
		echo "innodb_file_per_table"
		innodb_file_per_table="innodb_file_per_table"
		confirmation
		echo ""
		clear
		echo ""
		echo "---INNODB_FLUSH_METHOD---"
		echo "Valor fijo sobre documentacion:"
		echo "innodb_flush_method=O_DIRECT"
		echo "En caso de problemas de rendimiento y dependiendo del HW, podemos probar con: O_DSYNC"
		innodb_flush_method="O_DIRECT"
		confirmation
		echo ""
		clear
		echo ""
		echo "---INNODB_THREAD_CONCURRENCY---"
		echo "Por defecto viene establecido sin limite '0'"
		echo "Como valor maximo tomaremos 8"
		echo "Para evitar un exceso de carga se puede calcular con:"
		echo "(2*Numero de cores)+Numero de discos"
		v_num_cores=`cat /proc/cpuinfo|grep -i process|wc -l`
		v_num_disk=`lsblk -l|grep disk|wc -l`
		v_total=`echo "(2*$v_num_cores)+$v_num_disk"|bc`
		echo "(2*$v_num_cores)+$v_num_disk = $v_total"
		if [ $v_total -lt 8 ]; then
			v_result=$v_total
		else v_result=8
		fi
		read -p "Thread concurrency [$v_result]: " v_res
		v_res=${v_res:-$v_result}
		echo $v_res
		echo ""
		clear
		echo ""
		echo "---INNODB_LOG_FILE_SIZE---"
		echo "Segun la documentacion deberia tener espacio para almacenar una hora de logs."
		echo "Cuando tengamos la bbdd recibiendo datos podemos calcular el volumen:"
		echo "---Calculo---"
		echo "Para valor1 y valor2 :"
		echo "	---> mysql> pager grep sequence| awk '{print $5}'"
		echo "	---> SHOW ENGINE INNODB STATUS\G SELECT SLEEP(60); SHOW ENGINE INNODB STATUS\G;"
		echo "Para valor3 :"
		echo "	---> mysql> SHOW GLOBAL VARIABLES LIKE '%innodb_log_files_in_group%';"
		echo "Resultado :"
		echo "	---> (((valor1-valor2)*60)/1024/1024)/valor3"
		echo "---Fin calculo---"
		echo "Iniciamos con 1Gb ..."
		read -p "Innodb_log_file_size en MB [1024]: " innodb_log_file_size
		innodb_log_file_size=${innodb_log_file_size:-1024}
		echo $innodb_log_file_size
		echo ""
		clear
		echo ""
		echo "---INNODB_LOG_BUFFER_SIZE---"
		echo "Valor maximo debe ser el 25% de Innodb_log_file_size"
		echo "Pudiendo bajarse si no se necesita."
		innodb_log_buffer_size=`echo "($innodb_log_file_size*25)/100"|bc`
		read -p "Innodb_log_buffer_size en MB [$innodb_log_buffer_size]: " v_innodb_log_buffer_size
		v_innodb_log_buffer_size=${v_innodb_log_buffer_size:-$innodb_log_buffer_size}
		echo $v_innodb_log_buffer_size
		echo ""
		clear
		echo ""
		echo "---INNODB_FLUSH_LOG_AT_TRX_COMMIT---"
		echo "Valores: 0, 1, 2 "
		echo "0: Una vez por segundo el log_buffer se graba en el fichero de registro y se vuelca a disco"
		echo "	---> Riesgo: Perdida del ultimo segundo si cae mysql / Rendimiento: Alto"
		echo "1: Con cada commit el log_buffer se graba en el fichero de registro y se vuelca a disco"
		echo "	---> Riesgo: Bajo / Rendimiento: Bajo"
		echo "2: Con cada commit el log_buffer se graba en el fichero de registro y cada segundo se vuelca a disco"
		echo "	---> Riesgo: Perdida del ultimo segundo si cae OS. / Rendimiento: Medio"
		read -p "Innodb_flush_log_at_trx_commit [0]: " innodb_flush_log_at_trx_commit
		innodb_flush_log_at_trx_commit=${innodb_flush_log_at_trx_commit:-0}
		echo $innodb_flush_log_at_trx_commit
		echo ""
		clear
		echo ""
		echo "Definiendo configuracion de Cache y Buffers..."
	  	confirmation
	  	###Cache ordenada
	  	echo "---QUERY_CACHE_TYPE---"
		read -p "Activamos 1 o desactivamos 0, la QueryCache: [1]: " query_cache_type
		query_cache_type=${query_cache_type:-1}
		echo $query_cache_type
		echo ""
		clear
	  	echo ""
	  	omitir_query_cache=0
	  	if [ $query_cache_type -eq 1 ]; then 
	  		echo "---QUERY_CACHE_SYZE---"
	  		read -p "Tamaño de la QueryCache en Mb: [256]: " query_cache_size
			query_cache_size=${query_cache_size:-256}
			echo $query_cache_size
			echo ""
			clear
	  		echo ""
	  		echo "---QUERY_CACHE_LIMIT---"
	  		read -p "Limite em Mb de las queries para entrar en la QueryCache: [256]: " query_cache_limit
			query_cache_limit=${query_cache_limit:-256}
			echo $query_cache_limit
			echo ""
			clear
	  		echo ""
	  	else
	  		omitir_query_cache=1
	  	fi
	  	v_temp_buffer=`echo "$mem/1024"|bc`
		
		echo "---JOIN_BUFFER_SIZE---"
		echo "Tamano del buffer para joins...."
		read -p "join_buffer_size, 1Mb por cada Gb de Ram: [$v_temp_buffer]: " join_buffer_size
		join_buffer_size=${join_buffer_size:-$v_temp_buffer}
		echo $join_buffer_size
 		echo ""
 		clear
		echo ""
		echo "---READ_BUFFER_SIZE---"
		echo "Tamano del buffer para full access...."
		read -p "read_buffer_size, 1Mb por cada Gb de Ram: [$v_temp_buffer]: " read_buffer_size
		read_buffer_size=${read_buffer_size:-$v_temp_buffer}
		echo $read_buffer_size
 		echo ""
 		clear
		echo ""
		echo "---SORT_BUFFER_SIZE---"
		echo "Tamano del buffer para sorts...."
		read -p "sort_buffer_size, 1Mb por cada Gb de Ram: [$v_temp_buffer]: " sort_buffer_size
		sort_buffer_size=${sort_buffer_size:-$v_temp_buffer}
		echo $sort_buffer_size
 		echo ""
 		clear
		echo ""


		read -p "Path de trabajo de MySql: [/var/lib/mysql]: " work_path
		work_path=${work_path:-/var/lib/mysql}
		echo $work_path
		echo ""
		clear
	  	echo ""
	  	

	  	omitir_table_cache=0
	  	ls $work_path > /dev/null
	  	if [ $? -eq 0 ]; then
	  		num_tables=`find $work_path -name "*".frm|wc -l`
	  		table_cache_calc=`echo "$num_tables+(($num_tables*20)/100)"|bc`
	  		echo "---TABLE_CACHE---"
			echo "Tamano de la cache de tablas en numero de tablas...."
			read -p "+20% sobre el numero de tablas existentes: [$table_cache_calc]: " table_cache
			table_cache=${table_cache:-$table_cache_calc}
			echo $table_cache
 			echo ""
 			clear
			echo ""

			table_cache_calc_4=`echo "$table_cache_calc*4"|bc`
			table_cache_calc_3=`echo "$table_cache_calc*3"|bc`

			echo "---TABLE_DEFINITION_CACHE---"
			echo "Tamano de la cache para definiciones de tablas en numero de tablas...."
			read -p "4*table_cache: [$table_cache_calc_4]: " table_definition_cache
			table_definition_cache=${table_definition_cache:-$table_cache_calc_4}
			echo $table_definition_cache
 			echo ""
 			clear
			echo ""

			echo "---TABLE_OPEN_CACHE---"
			echo "Tamano de la cache de tablas abiertas en numero de tablas...."
			read -p "4*table_cache: [$table_cache_calc_4]: " table_open_cache
			table_open_cache=${table_open_cache:-$table_cache_calc_4}
			echo $table_open_cache
 			echo ""
 			clear
			echo ""

			echo "---OPEN_FILES_LIMIT---"
			echo "Limite de ficheros abiertos por mysql en numero de ficheros...."
			read -p "3*table_cache: [$table_cache_calc_3]: " open_files_limit
			open_files_limit=${open_files_limit:-$table_cache_calc_3}
			echo $open_files_limit
 			echo ""
 			clear
			echo ""			


	  	else
	  		echo "Error, el path de trabajo de mysql indicado, no existe. Omitimos configuracion de table_cache."
	  		omitir_table_cache=1
	  		confirmation
	  	fi
	

	  	echo "Definiendo configuracion de logs..."
	  	confirmation
	  	echo ""
	  	clear
	  	echo ""
	  	read -p "Path log de errores por defecto [/var/log/mysql/error.log]: " log_error
		log_error=${log_error:-/var/log/mysql/error.log}
		echo $log_error
		echo ""
		clear
	  	echo ""
	  	read -p "Path log de SlowQueries por defecto [/var/log/mysql/mysql-slow.log]: " log_slowqueries
		log_slowqueries=${log_slowqueries:-/var/log/mysql/mysql-slow.log}
		echo $log_slowqueries
		echo ""
		clear
	  	echo ""
	  	read -p "Tiempo para ser slowquery en segundos [2]: " long_query_time
		long_query_time=${long_query_time:-2}
		echo $long_query_time
		echo ""
		clear
	  	echo ""
	  	read -p "Log Queries que no usen indices [log-queries-not-using-indexes]: " log_queries_not_index
		log_queries_not_index=${log_queries_not_index:-log-queries-not-using-indexes}
		echo $log_queries_not_index
		echo ""
		clear
		echo ""
		echo "En entornos sin replicacion, no esta definido el log binario, por lo que no seria necesario."
		echo "No hay problema en tenerlo definido."
		read -p "Dias de retencion para log binarios. [10]: " expire_logs_days
		expire_logs_days=${expire_logs_days:-10}
		echo $expire_logs_days
		echo ""
		clear
		echo ""
		read -p "Tamano max para cada fichero de bin_logs en MB. [100]: " max_binlog_size
		max_binlog_size=${max_binlog_size:-100}
		echo $max_binlog_size
		unit="MB"	
		unit2="ms"
	  	echo ""
	  	clear
		echo ""
		echo "############################################"
		echo "#@@@@@@@@@@     LOG - MY.CNF     @@@@@@@@@@#"
		echo "############################################"
		echo "log_error = $log_error"
		echo "log_slow_queries = $log_slowqueries"
		echo "long_query_time = $long_query_time"
		echo "$log_queries_not_index"
		echo "expire_logs_days = $expire_logs_days"
		echo "max_binlog_size = $max_binlog_size$unit"
	  	echo ""
	  	echo ""
		echo "##############################################"
		echo "#@@@@@     CACHE y BUFFERS - MY.CNF     @@@@@#"
		echo "##############################################"
		echo "join_buffer_size = $join_buffer_size$unit"
		echo "read_buffer_size = $read_buffer_size$unit"
		echo "sort_buffer_size = $sort_buffer_size$unit"
		if [ $omitir_query_cache -eq 0 ]; then
			echo "query_cache_type = $query_cache_type"
			echo "query_cache_size = $query_cache_size$unit"
			echo "query_cache_limit = $query_cache_limit$unit"
		fi
		if [ $omitir_table_cache -eq 0 ]; then	
			echo "table_cache = $table_cache"
			echo "table_definition_cache = $table_definition_cache"
			echo "table_open_cache = $table_open_cache"
			echo "open_files_limit = $open_files_limit"
		fi
		echo ""
		echo ""
		echo "#############################################"
		echo "#@@@@@@@@@     INNODB - MY.CNF     @@@@@@@@@#"
		echo "#############################################"
		echo "default-storage-engine = $default_storage_engine"
		echo "innodb_buffer_pool_size = $innodb_buffer_pool_size$unit"
		echo "innodb_file_per_table"
		echo "innodb_flush_method = $innodb_flush_method"
		echo "innodb_thread_concurrency = $v_res"
		echo "innodb_log_file_size = $innodb_log_file_size$unit"
		echo "innodb_log_buffer_size = $v_innodb_log_buffer_size$unit"
		echo "innodb_flush_log_at_trx_commit = $innodb_flush_log_at_trx_commit"
		echo "#############################################"
		echo "#############################################"
		echo ""
	  	;;
	postgres )
		echo "Calculado sobre Postgres..."
	  	confirmation
	  	clear
	  	echo ""
		mem=$(get_mem)
		mem_calc=`echo "($mem*15)/100" | bc`
		echo "---SHARED_BUFFERS---"
		echo "Se recomiendan valores entre el 10% y el 25% de la memoria del servidor."
		echo "Tomaremos por defecto un 15% de la memoria."
		echo "15% de $mem MB: $mem_calc MB"
		read -p "Memoria dedicada a Postgres en MB [$mem_calc]: " memo
		memo=${memo:-$mem_calc}
		echo $memo
		echo ""
		clear
		echo ""
		v_cached_1=$(get_effective_cache_size $memo)
		v_cached_2=`echo "$mem/2"|bc`
		v_cached_def=`echo "($v_cached_1+$v_cached_2)/2"|bc`
		echo "---EFFECTIVE_CACHE_SIZE---"
		echo "Valor efectivo de la cache en MB, usado por el query planner."
		echo "Valores entre:"
		echo "[50% de la Ram: $v_cached_2 MB]"
		echo "Y"
		echo "[Memoria dedicada + Memoria cacheada: $v_cached_1 MB]"
		read -p "Cache efectiva en MB [$v_cached_def]: " memo_cache
		memo_cache=${memo_cache:-$v_cached_def}
		echo $memo_cache
		echo ""
		clear
		echo ""
		echo "---WORK_MEM---"
		echo "Memoria para ordenacion de queries y hash antes de usar ficheros temporales."
		echo "Valor no calculado, valor fijo sobre documentacion."
		read -p "Work_Mem en MB [16]: " work_mem
		work_mem=${work_mem:-16}
		echo $work_mem
		echo ""
		clear
		echo ""
		v_maintenance_def=`echo "($mem/1024)*50"|bc`
		echo "---MAINTENANCE_WORK_MEM---"
		echo "Memoria para tareas de VACUUM, CREATE INDEX...."
		read -p "Maintenance Work Mem, 50Mb por cada Gb de Ram: [$v_maintenance_def]: " v_maintenance
		v_maintenance=${v_maintenance:-$v_maintenance_def}
		echo $v_maintenance
 		echo ""
 		clear
		echo ""
		echo "---CHECKPOINT_SEGMENTS---"
		echo "Numero de WAL_BUFFERS antes de un checkpoint"
		echo "Si bajamos este valor se producirán mas checkpoints"
		echo "esto ocasiona escrituras en disco."
		echo "Un valor elevado disminuye las IO a disco pero emperora"
		echo "el tiempo de recuperación"
		read -p "Checkpoint Segments en [64]: " checkpoint_segments
		checkpoint_segments=${checkpoint_segments:-64}
		echo $checkpoint_segments
		echo ""
		clear
		echo ""
		echo "---WAL_BUFFERS---"
		echo "Tamano de buffer de WAL, todo lo no escrito en disco y que afecte a los datafiles."
		echo "Aproximadamente el 3% del valor de shared_buffers."
		wal_buffers_def=`echo "($memo*3)/100"|bc`
		read -p "Wal BuffersCheckpoint Segments: [$wal_buffers_def]: " wal_buffers
		wal_buffers=${wal_buffers:-$wal_buffers_def}
		echo $wal_buffers
		echo ""
		clear
		echo ""
		echo "---BGWRITER_DELAY---"
		echo "Tiempo entre escrituras a disco."
		echo "Mas tiempo --> Menor IO y mas riesgo de perdida de datos."
		echo "#Menor tiempo --> Mas IO y menos riesgo de perdida de datos."
		read -p "Bgwriter Delay en ms [1000]: " bgwriter_delay
		bgwriter_delay=${bgwriter_delay:-1000}
		echo $bgwriter_delay
		echo ""
		clear
		echo ""
		echo "#############################################"
		echo "#@@@@@@@@@     POSTGRESQL.CONF     @@@@@@@@@#"
		echo "#############################################"
		unit="MB"	
		unit2="ms"
		echo "shared_buffers = $memo$unit"
		echo "effective_cache_size = $memo_cache$unit"
		echo "work_mem = $work_mem$unit"
		echo "maintenance_work_mem = $v_maintenance$unit"
		echo "checkpoint_segments = $checkpoint_segments"
		echo "wal_buffers = $wal_buffers$unit"
		echo "bgwriter_delay = $bgwriter_delay$unit2"
		echo "#############################################"
		echo "#############################################"
		echo ""
		echo "#############################################"
		echo "#@@@@@@@@@       SYSCTL.CONF       @@@@@@@@@#"
		echo "#############################################"
		shmall=`getconf _PHYS_PAGES`/2
		shmall=`echo "$shmall"|bc`
		shmmax=(`getconf _PHYS_PAGES`/2)*`getconf PAGE_SIZE`
		shmmax=`echo "$shmmax"|bc`
		echo "kernel.shmall = $shmall"
		echo "kernel.shmmax = $shmmax"
		echo "#############################################"
		echo "#############################################"
		;;
	* )
		echo $"Usage: $0 {mysql | postgres}"
		exit 1
esac