logo image
메인
기술
HOME
기술

mysql, mariadb DATABASE 별 데이터 용량 확인

목록
#mysql #mariadb #database #size
SELECT table_schema "Database", ROUND(SUM(data_length+index_length)/1024/1024,1) "TOTAL MB" , ROUND(SUM(data_length)/1024/1024,1) "DATA MB" , ROUND(SUM(index_length)/1024/1024,1) "INDEX MB" 
FROM information_schema.TABLES 
GROUP BY 1;
=>
+--------------------+----------+---------+----------+
| Database           | TOTAL MB | DATA MB | INDEX MB |
+--------------------+----------+---------+----------+
| DATABASE           |   4206.4 |  3964.5 |    241.9 |
| information_schema |      0.0 |     0.0 |      0.0 |
| mysql              |      0.6 |     0.5 |      0.1 |
+--------------------+----------+---------+----------+



SELECT table_schema "Database", ROUND(SUM(data_length+index_length)/1024/1024,1) "TOTAL MB" , ROUND(SUM(data_length)/1024/1024,1) "DATA MB" , ROUND(SUM(index_length)/1024/1024,1) "INDEX MB" 
FROM information_schema.TABLES 
WHERE table_schema = '{데이터베이스명}'
GROUP BY 1;
SELECT table_schema "Database",TABLE_NAME,ENGINE, ROUND(SUM(data_length+index_length)/1024/1024,1) "TOTAL MB" , ROUND(SUM(data_length)/1024/1024,1) "DATA MB" , ROUND(SUM(index_length)/1024/1024,1) "INDEX MB" 
FROM information_schema.TABLES 
# WHERE table_schema = '{데이터베이스명}'
GROUP BY 1,2;

Database  TABLE_NAME                        ENGINE  TOTAL MB  DATA MB  INDEX MB  
--------  --------------------------------  ------  --------  -------  ----------
DBNAME    XXXXXXXXXXXXXXXXXXX               InnoDB      72.2     56.6        15.6
DBNAME    YYYYYYYYYYYYYYY                   MyISAM      67.7     39.4        28.4


*. 엔진별 사용량
SELECT  ENGINE,
        ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB",
        ROUND(SUM(index_length)/1024/1024, 1) AS "Index MB",
        ROUND(SUM(data_length + index_length)/1024/1024, 1) AS "Total MB",
        COUNT(*) "Num Tables"
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE  table_schema NOT IN ("information_schema", "PERFORMANCE_SCHEMA", "SYS_SCHEMA", "ndbinfo")
    GROUP BY  ENGINE;
=>
ENGINE  Data MB  Index MB  Total MB  Num Tables  
------  -------  --------  --------  ------------
(NULL)   (NULL)    (NULL)    (NULL)             1
Aria        1.8       0.3       2.1            24
CSV         0.0       0.0       0.0             2
InnoDB      0.1       0.0       0.1             5
MyISAM   6502.8    2099.5    8602.3           681


목록
댓글
  • / /
  • No Nickname
    No Comment
  • 권한이 없습니다.
    {{m_row.m_nick}}
    -
목록형 📷 갤러리형
제목
[기본형] HTML (with 부트스트랩5.3 , jquery 3.7, vue.js)
유용한 리눅스(LINUX) 명령어
[공지] 기술 게시판
일
월
화
수
목
금
토
3.31
4.1
4.2
4.3
4.4
4.5
4.6
4.7
4.8
4.9
game_tictactoe
4.10
4.11
4.12
4.13
4.14
4.15
4.16
4.17
:scope selector , querySelector 에서 this, self node 지정
4.18
4.19
4.20
4.21
4.22
4.23
ui_treemap
4.24
4.25
4.26
4.27
4.28
4.29
4.30
5.1
5.2
5.3
5.4
tag