[Mariadb] Memory Usage Check

Mariadb Memory Usage Check Script !!!

 

구글링 결과 tmp_table_size 는 Session 메모리에 포함돼 있는 경우도 있고, Global 영역에 포함돼 있는 경우도 있으므로 자체 테스트로 검증이 필요할것 같다.

 

-- #######################################################################
-- Memory Check Script !!!!
--
--     MAX_CONN      = max_connections          --> total connection limit
--     MAX_USED_CONN = max_used_connections     --> per user limit
--
--     BASE_MEM      = key_buffer_size + query_cache_size + innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size
--     MEM_PER_CONN  = read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size + binlog_cache_size + thread_stack + tmp_table_size
--
--     전역적 메모리 사용량의 합 + (지역적 메모리 사용량의 합 * 최대 동시 커넥션 개수)
--
--     MEM_TOTAL_MIN = BASE_MEM + MEM_PER_CONN * MAX_USED_CONN
--     MEM_TOTAL_MAX = BASE_MEM + MEM_PER_CONN * MAX_CONN
--
-- #######################################################################
 
#!/bin/sh
# you might want to add some user authentication here
mysql -uxxxx -pxxxx testdb -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["tmp_table_size"]
    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"
}'

스크립트 참조 : https://gist.github.com/fernandoaleman/5e693838f82a6858c11a534acb0f98d5