데이터베이스 용량설계
데이터베이스 용량설계
데이타베이스 용량 분석의 목적
- 정확한 데이타 용량을 산정하여 디스크 사용의 효율을 높인다.
- 업무량이 집중되어 있는 디스크를 분리,설계하여 디스크에 대한 입출력 부하를 분산시킬 수 있다.
데이터량이 많고 데이터의 증가량이 많을 수록 디스크에 대한 입출력 분산이 철저하게 고려되어야 성능을 향상시킬 수 있다. - 여러 프로세스가 동시에 접근할 때 발생하는 디스크 입출력 경합을 최소화하여 데이터의 접근 성능을 향상시킨다.
- 데이터베이스 오브젝트의 익스텐트(범위,Extent) 발생을 줄인다.데이터베이스 용량 분석 절차
- (1) 용량분석을 위한 기초 데이터를 수집한다
(p313표 7-3 데이터베이스 용량설계-기초데이터 조사)
- 로우길이:해당 테이블의 컬럼길이를 모두 합하여 기록한다.
- 보존기간:테이블을 디스크에 어느 정도의 기간동안 보관할 것인지 기록.
- 초기건수:구축시스템을 운영하기 전 데이터가 어느정도인지 기록.
- 발생건수:일정주기별로 데이터가 얼마나 발생하는지 기록.
- 발생주기: 1년단위? 월단위?
- 년 증가율: 해가 바뀌어 감에 따라 증가 할 수 있는 데이터량을 기록.
(2)기초 데이터를 이용하여 DBMS에 이용하는 오브젝트별로 용량을 산정한다
1)오브젝트설계
2)테이블스페이스 용량산정
3)디스크 용량 산정
1. 테이블별 사이즈 확인 (쿼리 시점에 정확한 사이즈는 아니지만, 참고할 만한 데이터임)
# 5.7 버전에서 sql_mode 를 적용한다.
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SELECT
table_name,
table_rows,
round(data_length/(1024*1024),2) as 'DATA_SIZE(MB)',
round(index_length/(1024*1024),2) as 'INDEX_SIZE(MB)'
FROM information_schema.TABLES
where table_schema = '데이터베이스이름'
GROUP BY table_name
ORDER BY data_length DESC
LIMIT 10;
2. Database 별 사이즈 확인 (쿼리 시점에 정확한 사이즈는 아니지만, 참고할 만한 데이터임)
SELECT
count(*) NUM_OF_TABLE,
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;
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)