- 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;

 

반응형
Posted by codens