mysql 디스크 사용량 조회
- mysql 8 disk space usage
//-------------------------------------
* DB 정보 저장 경로
- 리눅스 : /var/lib/mysql
- 윈도우 : C:\ProgramData\MySQL\MySQL Server 8.0\Data
//-------------------------------------
* 리눅스 명령
- 폴더 용량
$ sudo du -hs /var/lib/mysql
- 용량 순 정렬
$ sudo du -sch /var/lib/* | sort -hr | head -n20
$ sudo ls -lS /var/lib/mysql/ | sort -hr
//-------------------------------------
* sql 쿼리
- DB 전체 용량 총합
select table_schema, round(sum((data_length+index_length)/1024/1024)) AS MB from information_schema.tables ;
- DB별 차지하고 있는 용량
select table_schema, round(sum((data_length+index_length)/1024/1024)) AS MB from information_schema.tables group by 1;
- DB별 용량 세부 사항
SELECT concat(table_schema) 'Database Name',
concat(round(SUM(data_length/power(1024,3)),2),'G') DATA,
concat(round(SUM(index_length/power(1024,3)),2),'G') 'INDEX',
concat(round(SUM(data_free/power(1024,3)),2),'G') 'DATA FREE',
concat(round(sum(data_free)/(SUM(data_length+index_length))*100,2)) '% FRAGMENTED',
concat(round(SUM(data_length+index_length)/power(1024,3),2),'G') TOTAL
FROM information_schema.TABLES
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
GROUP BY table_schema;