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)
[출처] MySQL 테이블 크기 조사하기|작성자 쭌