MySQL 테이블 크기 조사하기-공유

MySQL에서 CUBRID로 전환 시, DB용량 산정에 유익하여 해당 내용을 공유합니다.

참조 : http://www.mysqlperformanceblog.com/2008/03/17/researching-your-mysql-table-sizes/


MySQL 서버에서 인덱스 크기에서 테이블의 개수 , 컬럼, 전체 데이타의 크기


SELECT count(*) TABLES,
concat(round(sum(table_rows)/1000000,2),'M') rows,
concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
round(sum(index_length)/sum(data_length),2) idxfrac
FROM information_schema.TABLES;
+--------+--------+-------+-------+------------+---------+
| TABLES | rows   | DATA  | idx   | total_size | idxfrac |
+--------+--------+-------+-------+------------+---------+
|    501 | 30.54M | 8.47G | 5.06G | 13.53G     |    0.60 | 
+--------+--------+-------+-------+------------+---------+
1 row in set (46.07 sec)




가장 큰 데이타베이스 찾기

SELECT
count(*) TABLES,
table_schema,concat(round(sum(table_rows)/1000000,2),'M') rows,
concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
round(sum(index_length)/sum(data_length),2) idxfrac
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY sum(data_length+index_length) DESC LIMIT 10;
+--------+--------------------+--------+-------+-------+------------+---------+
| TABLES | table_schema       | rows   | DATA  | idx   | total_size | idxfrac |
+--------+--------------------+--------+-------+-------+------------+---------+
|    369 | advanced2          | 26.73M | 5.74G | 5.03G | 10.77G     |    0.88 | 
|     17 | james_2            | 2.29M  | 1.71G | 0.01G | 1.72G      |    0.01 | 
|     17 | james_1            | 2.32M  | 0.81G | 0.01G | 0.82G      |    0.02 | 
|     17 | james_3            | 0.00M  | 0.15G | 0.00G | 0.15G      |    0.00 | 
|      7 | agent              | 0.02M  | 0.08G | 0.00G | 0.08G      |    0.00 | 
|      1 | test               | 0.08M  | 0.00G | 0.00G | 0.00G      |    0.00 | 
|     30 | tracker3           | 0.00M  | 0.00G | 0.00G | 0.00G      |    0.33 | 
|     17 | james_5            | 0.00M  | 0.00G | 0.00G | 0.00G      |    0.04 | 
|      9 | james_4            | 0.00M  | 0.00G | 0.00G | 0.00G      |    0.02 | 
|     17 | information_schema | NULL   | 0.00G | 0.00G | 0.00G      |    NULL | 
+--------+--------------------+--------+-------+-------+------------+---------+
10 rows in set (5.52 sec)



저장엔진별로 보기


SELECT engine,
count(*) TABLES,
concat(round(sum(table_rows)/1000000,2),'M') rows,
concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
round(sum(index_length)/sum(data_length),2) idxfrac
FROM information_schema.TABLES
GROUP BY engine
ORDER BY sum(data_length+index_length) DESC LIMIT 10;
+-----------+--------+--------+-------+-------+------------+---------+
| engine    | TABLES | rows   | DATA  | idx   | total_size | idxfrac |
+-----------+--------+--------+-------+-------+------------+---------+
| InnoDB    |    406 | 26.81M | 6.11G | 5.00G | 11.12G     |    0.82 | 
| MyISAM    |     76 | 2.45M  | 2.32G | 0.06G | 2.38G      |    0.02 | 
| FEDERATED |      6 | 0.02M  | 0.09G | 0.00G | 0.09G      |    0.00 | 
| MEMORY    |     13 | NULL   | 0.00G | 0.00G | 0.00G      |    NULL | 
+-----------+--------+--------+-------+-------+------------+---------+
4 rows in set (11.90 sec)