๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ

๋ชฉ๋ก์ด ์—†์Šต๋‹ˆ๋‹ค.

[MySQL/MariaDB] ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์šฉ๋Ÿ‰ ๋ณด๊ธฐ, ํ…Œ์ด๋ธ” ๋ณ„ ์šฉ๋Ÿ‰ ํ™•์ธ

2020. 6. 8. ๐Ÿ›ข DB/MySQL
  1. ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ณ„ ์šฉ๋Ÿ‰ ํ™•์ธ
  2. ํ…Œ์ด๋ธ”๋ณ„ ์šฉ๋Ÿ‰ ํ™•์ธ

 

์„œ๋ฒ„ ๊ด€๋ฆฌ๋ฅผ ํ•˜๋‹ค๋ณด๋ฉด ์–ด๋–ค ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ์šฉ๋Ÿ‰์˜ ์–ผ๋งˆ๋‚˜ ๋˜๋Š”์ง€ ๋˜๋Š” ์–ด๋–ค ํ…Œ์ด๋ธ”์˜ ์šฉ๋Ÿ‰์ด ์–ผ๋งˆ๋‚˜ ๋˜๋Š”์ง€๊ฐ€ ๊ถ๊ธˆํ•  ๋•Œ๊ฐ€ ์ƒ๊น๋‹ˆ๋‹ค. ์ด๋Ÿฐ ๊ฒฝ์šฐ์— ์„œ๋ฒ„์— ์ ‘์†ํ•˜์ง€ ์•Š๊ณ  ์ฟผ๋ฆฌ๋งŒ์œผ๋กœ ์•Œ ์ˆ˜ ์žˆ๋Š” ๋ฐฉ๋ฒ•์ด ์žˆ์–ด์„œ ์ •๋ฆฌํ•ด๋ณด๋ ค๊ณ  ํ•ฉ๋‹ˆ๋‹ค.

 


๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ณ„ ์šฉ๋Ÿ‰ ํ™•์ธ

SELECT table_schema "Database Name",
SUM(data_length + index_length) / 1024 / 1024 "Size(MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

 

ํ…Œ์ด๋ธ”๋ณ„ ์šฉ๋Ÿ‰ ํ™•์ธ

SELECT
concat(table_schema,'.',table_name),
concat(round(data_length/(1024*1024*1024),2),'G') DATA,
concat(round(index_length/(1024*1024*1024),2),'G') idx,
concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,
round(index_length/data_length,2) idxfrac
FROM information_schema.TABLES
WHERE table_rows is not null;

 


 

์ด์ƒ์œผ๋กœ Database์•ˆ์—์„œ ํ…Œ์ด๋ธ”๋ณ„ ์šฉ๋Ÿ‰ ํ™•์ธ ์ฟผ๋ฆฌ ์ •๋ฆฌ๋ฅผ ๋งˆ์นฉ๋‹ˆ๋‹ค.

 

๋ถ€์กฑํ•œ ๊ธ€ ์ฝ์–ด์ฃผ์…”์„œ ๊ฐ์‚ฌํ•ฉ๋‹ˆ๋‹ค.