MySQL記憶體使用

壹頁書發表於2016-09-12
MySql記憶體
轉載自:
http://dev.mysql.com/doc/refman/5.6/en/memory-use.html


  1. #!/bin/sh
  3. mysql -e "show variables; show status" | awk '
  4. {
  5. VAR[$1]=$2
  6. }
  7. END {
  8. MAX_CONN = VAR["max_connections"]
  9. MAX_USED_CONN = VAR["Max_used_connections"]
  10. 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"]
  11. 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"]
  12. MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN
  13. MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONN
  15. printf "+------------------------------------------+--------------------+\n"
  16. printf "| %40s | %15.3f MB |\n", "key_buffer_size", VAR["key_buffer_size"]/1048576
  17. printf "| %40s | %15.3f MB |\n", "query_cache_size", VAR["query_cache_size"]/1048576
  18. printf "| %40s | %15.3f MB |\n", "innodb_buffer_pool_size", VAR["innodb_buffer_pool_size"]/1048576
  19. printf "| %40s | %15.3f MB |\n", "innodb_additional_mem_pool_size", VAR["innodb_additional_mem_pool_size"]/1048576
  20. printf "| %40s | %15.3f MB |\n", "innodb_log_buffer_size", VAR["innodb_log_buffer_size"]/1048576
  21. printf "+------------------------------------------+--------------------+\n"
  22. printf "| %40s | %15.3f MB |\n", "BASE MEMORY", BASE_MEM/1048576
  23. printf "+------------------------------------------+--------------------+\n"
  24. printf "| %40s | %15.3f MB |\n", "sort_buffer_size", VAR["sort_buffer_size"]/1048576
  25. printf "| %40s | %15.3f MB |\n", "read_buffer_size", VAR["read_buffer_size"]/1048576
  26. printf "| %40s | %15.3f MB |\n", "read_rnd_buffer_size", VAR["read_rnd_buffer_size"]/1048576
  27. printf "| %40s | %15.3f MB |\n", "join_buffer_size", VAR["join_buffer_size"]/1048576
  28. printf "| %40s | %15.3f MB |\n", "thread_stack", VAR["thread_stack"]/1048576
  29. printf "| %40s | %15.3f MB |\n", "binlog_cache_size", VAR["binlog_cache_size"]/1048576
  30. printf "| %40s | %15.3f MB |\n", "tmp_table_size", VAR["tmp_table_size"]/1048576
  31. printf "+------------------------------------------+--------------------+\n"
  32. printf "| %40s | %15.3f MB |\n", "MEMORY PER CONNECTION", MEM_PER_CONN/1048576
  33. printf "+------------------------------------------+--------------------+\n"
  34. printf "| %40s | %18d |\n", "Max_used_connections", MAX_USED_CONN
  35. printf "| %40s | %18d |\n", "max_connections", MAX_CONN
  36. printf "+------------------------------------------+--------------------+\n"
  37. printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", MEM_TOTAL_MIN/1048576
  38. printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", MEM_TOTAL_MAX/1048576
  39. printf "+------------------------------------------+--------------------+\n"
  40. }'


或者
  1. SELECT ( @@key_buffer_size  
  2. + @@query_cache_size  
  3. + @@innodb_buffer_pool_size  
  4. + @@innodb_additional_mem_pool_size  
  5. + @@innodb_log_buffer_size  
  6. + @@max_connections * ( @@read_buffer_size  
  7. + @@read_rnd_buffer_size  
  8. + @@sort_buffer_size  
  9. + @@join_buffer_size  
  10. + @@binlog_cache_size  
  11. + @@thread_stack  
  12. + @@tmp_table_size )  
  13. ) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB,  
  14. ( @@key_buffer_size  
  15. + @@query_cache_size  
  16. + @@innodb_buffer_pool_size  
  17. + @@innodb_additional_mem_pool_size  
  18. + @@innodb_log_buffer_size)/ (1024 * 1024 * 1024) AS MIN_MEMORY_GB;  

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-2124854/，如需轉載，請註明出處，否則將追究法律責任。

相關文章