[FUG-BR] Diferença absurda do mysql no Linux para o do FreeBSD

[Edson Brandi]

Em 11 de julho de 2012 14:33, Marcelo Gondim <gondim at bsdinfo.com.br> escreveu:
> Será que sem querer descobri algo interessante? rsrsrsrsrs

Marcelo,

Estava dando uma olhada em como o mysql tuning primer
(https://launchpad.net/mysql-tuning-primer/), chega nos números.

Pelo que vi ele não está usando nenhuma variavel do sistema
operacional, e esta fazendo praticamente todas as contas  tendo como
input variaveis do mysql.

Com base nesta lógica de calculo a unica explicação que vejo pros
numeros estarem diferentes é se estas variaveis forem diferentes entre
o seu mysql rodando no linux e o seu mysql rodando no FreeBSD. Não me
parece ser algo relacionado ao sistema operacional.

A unica informação que ele usa do sistema operacional é a quantidade de memoria:

get_system_info () {

    export OS=$(uname)

    # Get information for various UNIXes
    if [ "$OS" = 'Darwin' ]; then
        ps_socket=$(netstat -ln | awk '/mysql(.*)?\.sock/ { print $9
}' | head -1)
        found_socks=$(netstat -ln | awk '/mysql(.*)?\.sock/ { print $9 }')
        export physical_memory=$(sysctl -n hw.memsize)
        export duflags=''
    elif [ "$OS" = 'FreeBSD' ] || [ "$OS" = 'OpenBSD' ]; then
        ## On FreeBSD must be root to locate sockets.
        ps_socket=$(netstat -ln | awk '/mysql(.*)?\.sock/ { print $9
}' | head -1)
        found_socks=$(netstat -ln | awk '/mysql(.*)?\.sock/ { print $9 }')
        export physical_memory=$(sysctl -n hw.realmem)
        export duflags=''
    elif [ "$OS" = 'Linux' ] ; then
        ## Includes SWAP
        ## export physical_memory=$(free -b | grep -v buffers |  awk
'{ s += $2 } END { printf("%.0f\n", s ) }')
        ps_socket=$(netstat -ln | awk '/mysql(.*)?\.sock/ { print $9
}' | head -1)
        found_socks=$(netstat -ln | awk '/mysql(.*)?\.sock/ { print $9 }')
        export physical_memory=$(awk '/^MemTotal/ { printf("%.0f",
$2*1024 ) }' < /proc/meminfo)
        export duflags='-b'
    elif [ "$OS" = 'SunOS' ] ; then
        ps_socket=$(netstat -an | awk '/mysql(.*)?.sock/ { print $5 }'
| head -1)
        found_socks=$(netstat -an | awk '/mysql(.*)?.sock/ { print $5 }')
        export physical_memory=$(prtconf | awk '/^Memory\ size:/ {
print $3*1048576 }')
    fi
    if [ -z $(which bc) ] ; then
        echo "Error: Command line calculator 'bc' not found!"
        exit
    fi
}


Colei a função que calcula a alocação de memoria abaixo:

total_memory_used () {

## -- Total Memory Usage -- ##
        cecho "MEMORY USAGE" boldblue

        mysql_variable \'read_buffer_size\' read_buffer_size
        mysql_variable \'read_rnd_buffer_size\' read_rnd_buffer_size
        mysql_variable \'sort_buffer_size\' sort_buffer_size
        mysql_variable \'thread_stack\' thread_stack
        mysql_variable \'max_connections\' max_connections
        mysql_variable \'join_buffer_size\' join_buffer_size
        mysql_variable \'tmp_table_size\' tmp_table_size
        mysql_variable \'max_heap_table_size\' max_heap_table_size
        mysql_variable \'log_bin\' log_bin
        mysql_status \'Max_used_connections\' max_used_connections

        if [ "$major_version" = "3.23" ] ; then
                mysql_variable \'record_buffer\' read_buffer_size
                mysql_variable \'record_rnd_buffer\' read_rnd_buffer_size
                mysql_variable \'sort_buffer\' sort_buffer_size
        fi

        if [ "$log_bin" = "ON" ] ; then
                mysql_variable \'binlog_cache_size\' binlog_cache_size
        else
                binlog_cache_size=0
        fi

        if [ $max_heap_table_size -le $tmp_table_size ] ; then
                effective_tmp_table_size=$max_heap_table_size
        else
                effective_tmp_table_size=$tmp_table_size
        fi


        per_thread_buffers=$(echo
"($read_buffer_size+$read_rnd_buffer_size+$sort_buffer_size+$thread_stack+$join_buffer_size+$binlog_cache_size)*$max_connections"
| bc -l)
        per_thread_max_buffers=$(echo
"($read_buffer_size+$read_rnd_buffer_size+$sort_buffer_size+$thread_stack+$join_buffer_size+$binlog_cache_size)*$max_used_connections"
| bc -l)

        mysql_variable \'innodb_buffer_pool_size\' innodb_buffer_pool_size
        if [ -z $innodb_buffer_pool_size ] ; then
        innodb_buffer_pool_size=0
        fi

        mysql_variable \'innodb_additional_mem_pool_size\'
innodb_additional_mem_pool_size
        if [ -z $innodb_additional_mem_pool_size ] ; then
        innodb_additional_mem_pool_size=0
        fi

        mysql_variable \'innodb_log_buffer_size\' innodb_log_buffer_size
        if [ -z $innodb_log_buffer_size ] ; then
        innodb_log_buffer_size=0
        fi

        mysql_variable \'key_buffer_size\' key_buffer_size

        mysql_variable \'query_cache_size\' query_cache_size
        if [ -z $query_cache_size ] ; then
        query_cache_size=0
        fi

        global_buffers=$(echo
"$innodb_buffer_pool_size+$innodb_additional_mem_pool_size+$innodb_log_buffer_size+$key_buffer_size+$query_cache_size"
| bc -l)


        max_memory=$(echo "$global_buffers+$per_thread_max_buffers" | bc -l)
        total_memory=$(echo "$global_buffers+$per_thread_buffers" | bc -l)

        pct_of_sys_mem=$(echo "scale=0;
$total_memory*100/$physical_memory" | bc -l)

        if [ $pct_of_sys_mem -gt 90 ] ; then
                txt_color=boldred
                error=1
        else
                txt_color=
                error=0
        fi

        human_readable $max_memory max_memoryHR
        cecho "Max Memory Ever Allocated : $max_memoryHR $unit" $txt_color
        human_readable $per_thread_buffers per_thread_buffersHR
        cecho "Configured Max Per-thread Buffers :
$per_thread_buffersHR $unit" $txt_color
        human_readable $global_buffers global_buffersHR
        cecho "Configured Max Global Buffers : $global_buffersHR
$unit" $txt_color
        human_readable $total_memory total_memoryHR
        cecho "Configured Max Memory Limit : $total_memoryHR $unit" $txt_color
#       human_readable $effective_tmp_table_size effective_tmp_table_sizeHR
#       cecho "Plus $effective_tmp_table_sizeHR $unit per temporary
table created"
        human_readable $physical_memory physical_memoryHR
        cecho "Physical Memory : $physical_memoryHR $unit" $txt_color
        if [ $error -eq 1 ] ; then
                printf "\n"
                cecho "Max memory limit exceeds 90% of physical
memory" $txt_color
        else
                cecho "Max memory limit seem to be within acceptable
norms" green
        fi
        unset txt_color
}

#####

Você chegou a colocar o banco em produção com 4.000 conexões para ver
como o FreeBSD vai se comportar?