이 문서는 mysql-admin-cookbook 책에서 'Monitoring and Analyzing MySQL Installation' 을 정리한 것입니다.
1.InnoDB Tablespace 빈공간 체크하기.
MySQL의 디폴트 엔진인 'MyISAM'의 경우 각각의 테이블들에 대응하여 파일시스템상에서 파일들이 생성이 되어진다. 따라서 데이터가 계속 쌓인다 하더라도 파일시스템상에 빈공간이 남아있으면 별문제가 되지 않는다.
하지만 InnoDB는 다르다.
확인하는 방법은 모든 데이터베이스에 테이블들에 대해서 다음과 같이 확인을 하는 것이다.
[code sql]
SELECT DATA_FREE/(1024*1024) AS FREE_MB, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE="InnoDB";
[/code]
보통 테이블스페이스를 따로 지정을 하지 않으면 mysql은 공통테이블 스페이스를 사용하게 되고 이럴경우 모든 테이블의 테이블스페이스의 빈공간 용량은 동일하게 나온다.
각각의 테이블마다 테이블 스페이스를 지정하기 위해서는 'innodb_data_home_dir' 을 my.cnf 파일에 추가해주면 된다.
2.데이터베이스에 필요한 저장공간 계산하기.
맨 처음 데이터베이스를 생성할때에 고민이되는 것이 바로 저장공간을 얼만정도로 해줘야 할까하는 것이다. '적절한'저장공간 확보를 위해서는 많은 외부변수들을 종합적으로 감안을해야하는데 이럴경우 '적절한 저장공간은 없고, 최대한 많이'라는 결론만 나온다. 그래도 대충은 데이터 저장 공간을 계산해내서 거기서 생각을 넓혀갈 수는 없을까?
방법은 존재 한다.
일단 계산을 위한 테이블 스키마가 필요하다. 테이블 스키마의 정의에 따라서 값이 변하기 때문이다. 그리고 여기에 간단하게 샘플 데이터들을 넣는다. 샘플 데이터들의 양이 많으면 많을 수록 계산되어지는 저장공간에 정확도는 증가할 것이다. 그리고 얼정도의 레코드를 쌓을 것이지를 결정하고 다음과 같이 쿼리로 계산을 할수 있다.(여기서는 1,000,000 레코드를 쌓는 다는 전재다.)
[code sql]
mysql> SELECT 1000000 * (DATA_LENGTH + INDEX_LENGTH) / (SELECT COUNT(*) FROM sample.table1) / (1024*1024) AS REQUIRED_SPACE_MB FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="sample" AND TABLE_NAME="table1";
[/code]
위의 명령어는 sample이라는 스키마에 table1 이라는 테이블이 있고 1,000,000 레코드를 기록할 것이라고 예상했을 경에 필요로 하는 저장공간을 계산한 것이다.
3.Mysql 변수 확인 및 변경.
Mysql은 기본적으로 'my.cnf' 파일에 각종 변수들을 세팅하고 서버를 재시작하면 적용된다. 하지만 동작하고 있는 상태에서 그러한 변수들을 확안하고 일부 변수는 변경도 가능한데, 이를 알아보자.
1) 모든 변수 확인
[code sql]
mysql> SHOW VARIABLES;
[/code]
2) 특정 변수 확인
[code sql]
mysql> SHOW VARIABLES LIKE "version";
[/code]
3) 특정변수 그룹 확인
[code sql]
mysql> SHOW VARIABLES LIKE "version%";
[/code]
여기서 알아둘 것은 변수는 현재접속자(your own connection)에게만 적용되는 것과 전체 사용자에 영향을 주는 글로벌 설정으로 나뉜다는 것이다.
현재접속자의 경우에 'SET'으로 값을 변경을하면 그 값은 즉시 적용되어 접속이 이루어지는 동안만 유효하게 된다. 접속을 해제하고 다시 접속했을 경우 이전 값으로 나온다는 뜻이다. 이러한 변수를 세션 변수(session variables)라고 한다.
글로벌 설정은 현재접속자 뿐만 아니라 전체 사용자에게 영향을 미친다. 단, 현재 접속하여 사용하고 있는 사용자들은 재접속을 해야지만 변경된 값으로 적용이 되어 진다. 이러한 변수를 글로벌 변수(global variables)라고 한다.
4) 현재접속자에게만 변수 수정(접속해제하면 값은 사라짐). - 세션 변수 수정.
[code sql]
mysql> SET auto_increment_increment=2;
[/code]
5) 글로벌 변수 수정(재접속을 해야지만 적용됨). - 글로벌 변수 수정.
[code sql]
mysql> SET GLOBAL auto_increment_increment=3;
[/code]
글로벌 변수와 세션 변수의 적용 차이는 다음과 같다.
- 세션변수없이 단독글로벌 변수의 경우 변경 즉시 모든 접속자/비접속자에게 적용이 바로 된다.
- 글로벌변수와 세션변수 둘다 존재할 경우 모두 새로운 접속자에게만 적용이 된다.
이러한 적용이 차이는 글로벌변수가 변경이 되었을 경우 이 것을 세션 변수에 카피(copy)하기 때문이다.
6) 조건문으로 변수 찾기.
[code sql]
mysql> SHOW VARIABLES WHERE variable_name IN ('wait_timeout', 'autocommit') OR variable_name LIKE 'version%';
[/code]
7) 글로벌 변수 세팅 확인
[code sql]
mysql> SHOW GLOBAL VARIABLES like 'auto_increment_increment';
[/code]
4.데이터베이스 테이블 갯수 알아내기.
[code sql]
mysql> SELECT TABLE_SCHEMA, COUNT(*) AS TABLE_COUNT from INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA WITH ROLLUP;
[/code]
5.가장 큰 레코드 개수를 가진 데이터베이스, 테이블 알아내기
[code sql]
mysql> SELECT TABLE_SCHEMA,TABLE_NAME, (INDEX_LENGTH+DATA_LENGTH)/(1024*1024) AS SIZE_MB, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN("mysql", "information_schema") ORDER BY SIZE_MB DESC;
[/code]
6.가장 큰 컬럼 타입별로 정렬해서 보기.
가끔은 가장 큰 컬럼타입이 무엇일까 하고 궁금해질때가 있다. 이럴때는 다음과 같이 하면 나온다.
[code sql]
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH AS SIZE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA NOT IN ("mysql", "information_schema") ORDER BY size DESC LIMIT 30;
[/code]
그리고 VARCHAR의 특정 길이를 가진 테이블들을 알고 싶다면 다음과 같이 하면 된다.
[code sql]
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH AS SIZE FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE="VARCHAR" AND CHARACTER_MAXIMUM_LENGTH=64 AND TABLE_SCHEMA NOT IN ("mysql", "information_schema");
[/code]
7.다른 테이블 참조하는 테이블 찾아내기.
InnoDB를 사용하는 이유는 트랜잭션과 외래키를 통한 다른 테이블의 참조에 있다. 그런데 특정테이블을 참조(Referencing)하는 테이블들을 어떻게 하면 알아낼까? 다음과 같이하면 알아낼 수 있다.
[code sql]
mysql> SELECT TABLE_NAME, CONSTRAINT_NAME, UPDATE_RULE AS "UPDATE", DELETE_RULE AS "DELETE" FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE REFERENCED_TABLE_NAME="employees";
[/code]
'employee' 테이블을 참조하는 다른 테이블들을 모두 찾는다.
이번에는 'employee'가 참조하는 테이블들을 모두 찾는다.
[code sql]
mysql> SELECT REFERENCED_TABLE_NAME, CONSTRAINT_NAME, UPDATE_RULE AS "UPDATE", DELETE_RULE AS "DELETE" FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE TABLE_NAME="employee";
[/code]