総メモリー使用量を算出するSQLクエリ

結構いろんなサイトで書かれてるけど、
自分もメモってたので、共有してみる。

MySQL用です。でもMariaDBでも動くんじゃないかな？

select
KEY_BUFFER_SIZE + QUERY_CACHE_SIZE + INNODB_BUFFER_POOL_SIZE + INNODB_LOG_BUFFER_SIZE + INNODB_ADDITIONAL_MEM_POOL_SIZE as GLOBAL_BUFFER_SIZE,
SORT_BUFFER_SIZE + JOIN_BUFFER_SIZE + READ_BUFFER_SIZE + READ_RND_BUFFER_SIZE + NET_BUFFER_LENGTH as THREAD_BUFFER_SIZE,
KEY_BUFFER_SIZE + QUERY_CACHE_SIZE + INNODB_BUFFER_POOL_SIZE + INNODB_LOG_BUFFER_SIZE + INNODB_ADDITIONAL_MEM_POOL_SIZE
+ (SORT_BUFFER_SIZE + JOIN_BUFFER_SIZE + READ_BUFFER_SIZE + READ_RND_BUFFER_SIZE + NET_BUFFER_LENGTH) * MAX_CONNECTIONS AS TOTAL_MEMORY_SIZE,
(KEY_BUFFER_SIZE + QUERY_CACHE_SIZE + INNODB_BUFFER_POOL_SIZE + INNODB_LOG_BUFFER_SIZE + INNODB_ADDITIONAL_MEM_POOL_SIZE
+ (SORT_BUFFER_SIZE + JOIN_BUFFER_SIZE + READ_BUFFER_SIZE + READ_RND_BUFFER_SIZE + NET_BUFFER_LENGTH) * MAX_CONNECTIONS)/1024 AS TOTAL_MEMORY_SIZE_kb,
(KEY_BUFFER_SIZE + QUERY_CACHE_SIZE + INNODB_BUFFER_POOL_SIZE + INNODB_LOG_BUFFER_SIZE + INNODB_ADDITIONAL_MEM_POOL_SIZE
+ (SORT_BUFFER_SIZE + JOIN_BUFFER_SIZE + READ_BUFFER_SIZE + READ_RND_BUFFER_SIZE + NET_BUFFER_LENGTH) * MAX_CONNECTIONS)/1024/1024 AS TOTAL_MEMORY_SIZE_mb,
(KEY_BUFFER_SIZE + QUERY_CACHE_SIZE + INNODB_BUFFER_POOL_SIZE + INNODB_LOG_BUFFER_SIZE + INNODB_ADDITIONAL_MEM_POOL_SIZE
+ (SORT_BUFFER_SIZE + JOIN_BUFFER_SIZE + READ_BUFFER_SIZE + READ_RND_BUFFER_SIZE + NET_BUFFER_LENGTH) * MAX_CONNECTIONS)/1024/1024/1024 AS TOTAL_MEMORY_SIZE_gb
from
(select VARIABLE_VALUE as SORT_BUFFER_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'SORT_BUFFER_SIZE') as table1,
(select VARIABLE_VALUE as READ_BUFFER_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'READ_BUFFER_SIZE') as table3,
(select VARIABLE_VALUE as JOIN_BUFFER_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'JOIN_BUFFER_SIZE') as table4,
(select VARIABLE_VALUE as READ_RND_BUFFER_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'READ_RND_BUFFER_SIZE') as table5,
(select VARIABLE_VALUE as KEY_BUFFER_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'KEY_BUFFER_SIZE') as table6,
(select VARIABLE_VALUE as INNODB_BUFFER_POOL_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'INNODB_BUFFER_POOL_SIZE') as table7,
(select VARIABLE_VALUE as INNODB_LOG_BUFFER_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'INNODB_LOG_BUFFER_SIZE') as table8,
(select VARIABLE_VALUE as INNODB_ADDITIONAL_MEM_POOL_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'INNODB_ADDITIONAL_MEM_POOL_SIZE') as table9,
(select VARIABLE_VALUE as NET_BUFFER_LENGTH from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'NET_BUFFER_LENGTH') as table10,
(select VARIABLE_VALUE as MAX_CONNECTIONS from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'MAX_CONNECTIONS') as table11,
(select VARIABLE_VALUE as QUERY_CACHE_SIZE from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'QUERY_CACHE_SIZE') as table12;