1. MySQL 성능향상시키기
(1) MySQL의 속도저하시킬 있는 원인이 다음과 같이 있는데 어느 정도는 MySQL 튜닝을 통해 성능향상
이 가능하다.
1) 클라이언트와 서버사이의 네트워크속도
2) 키를 만드는데 필요한 프로세싱시간
3) 디스크의 속도
(2) 성능향상을 위한 주요 변수값 조정
1) 변수의 확인법
mysqladmin variable
2) 변수값의 수정법
mysqld -O 변수=값
mysqld_safe -O 변수=값 (권장)
=> 참고로 원래는 mysqld라는 명령으로 실행시켜야 하지만, 보통 mysqld_safe라는
스크립트파일을 사용하므로 아래의 수정법을 사용하면 된다. 또한 백그라운드로 실행시켜야
하므로 맨 뒤에 '&'를 붙여 사용하도록 한다.
3) 성능향상과 관련된 주요변수
back_log : 한번에 접속할 수 있는 TCP/IP연결의 수를 정한다. 만약 데이타베이스에 동시연결하
는 원격사용자들이 많을 경우, 이 값을 증가시켜야 한다. 이 값을 높이면 메모리와
CPU를 더 사용하게 된다.
key_buffer_size : 최근에 사용한 키들을 저장하는 버퍼의 크기이다. 만약 속도가 느린 질의가
생기는 경우, 이 값을 증가시키는 것이 도움이 된다. 이 값을 증가키시면 메
모리를 조금 더 사용하게 된다.
max_connection : 데이타베이스 서버에 연결할 수 있는 동시 사용자의 수이다. 만약 사용자들이
바쁜 시간에 연결이 거부되었을 경우 이 값을 증가시켜야 할 필요가 있다.
이 값을 증가시키면 서버가 부하가 많이 걸린다. 즉, CPU사용, 메모리사용,
디스크입출력이 증가하게 된다.
table_cache : 자주 사용되는 테이블의 내용을 가지고 있는 버퍼의 크기이다. 만약 이 값을
많이 주면 테이블을 메모리에 저장함으로써 디스크 입출력을 크게 줄일 수 있다.
단점은 메모리의 사용량이 상당히 크게 증가한다.
2. MySQL 관리하기
(1) MySQL의 데이터구조
1) 개요: MySQL은 각각의 테이블을 세개의 파일로 저장한다. 예를 들어, mytable이라는 테이블이
있을 경우 버전에 따라 다음과 같은 데이타파일을 생성한다.
2) 버전별 파일의 역할
3.22 이전 3.23 이후 역할
mytable.ISD mytable.MYD 실제 데이터를 저장한다.
mytable.ISM mytable.MYI MySQL로 하여금 ISD파일안에 있는 데이터를 빨리 찾을 수
있도록 하는 키에 대한 정보와 다른 내부 테이터들을 저장한다.
이 파일이 MySQL의 성능에 가장 중요한 역할을 하면
이 파일을 관리하는 isamchk라는 유틸리티가 존재한다.
mytable.frm mytable.frm 테이블 구조 그 자체를 가지고 있다.
(2) 손상된 테이블 복구하기
1) 개요: 서버가 다운되거나 자연적인 재해로 인하여 데이타베이스에 존재하는 테이블이
손상되었을 경우에는 isamchk나 myisamchk유틸리티로 복구할 수 있다.
2) 방법
ㄱ. mysql서버를 중단시킨다.
ㄴ. 'isamchk 테이블이름' 또는 'myisamchk 테이블'이름을 수행한다.
예) 만약 mytable이라면
myisamchk mytable
=> 이 옵션을 이용하면 테이블에 생길 수 있는 대부분의 문제점을 해결할 수 있다.
-i와 -v옵션을 주면 어떤 것이 잘못되어나에 대한 더욱 자세한 정보를 출력한다.
3) 옵션을 이용한 복구
ㄱ. myisamchk -rq mytable
=> 빠른 검사를 수행하고 필요한 경우 복구한다.
ㄴ. myisamchk -e mytable
=> 전체검사를 수행한다. 거의 대부분의 손상을 복구할 수 있다.
4) 참고
ㄱ. 만약 MySQL서버가 동작하고 있는 중에 myisamchk유틸리티를 사용했다면 mysqladmin reload를
수행하여야 변경된 테이블을 인식하게 된다.
ㄴ. myisamchk를 -r옵션으로 수행하고자 할 경우에는 서버를 종료하는 것이 좋다. 만약 실행되고
있다면 테이블을 손상시킬 수도 있기 때문이다.
ㄷ. 항상 테이블의 내용이 변화될 수 있는 명령어를 주기 전에는 데이터를 백업하는 것이 좋다.
myisamchk등의 유틸리티는 오류를 수정하는 데 매우 뛰어나지만 간혹 복구된 내용이 테이블의
다른 부분에 겹치는 경우가 있고 데이타가 지워질 수도 있으므로 백업을 하는 것이 좋다.
(3) 복구가 되지 않는 경우
1) .frm파일이 삭제된 경우
ㄱ. 데이터파일인 .MYD와 인덱스파일인 .MYI파일을 임시 디렉토리로 복사한다.
ㄴ. MySQL을 실행한 후 create table을 이용하여 임의의 테이블을 생성한다.
ㄷ. 테이블을 생성한 후 MySQL을 종료하고 복사해 둔 .MYD와 .MYI파일을 다시 원래 위치로 복사
한다.
ㄹ. myisamchk를 이용하여 테이블을 복구한다.
(4) isamchk및 myisamchk 유틸리티를 이용한 관리지침
1) 데이타베이스가 단기간 동작했다면 myisamchk -a를 자주 수행한다. 대부분의 데이타베이스
어플리케이션은 대규모의 데이타가 데이타베이스의 초반에 삽입되기 때문이다. 데이타베이스의
크기가 두배로 커질 때마다 수행하는 것이 좋다.
2) myisamchk -d를 일년에 한두번 수행한다. 만약 테이블에 제거된 영역이 많은 경우라면, myisam
chk -r을 수행함으로써 불필요한 공간을 제거하여 공간을 확보할 수 있다. 정보의 삭제가 빈번하
다면 자주 수행하도록 한다.
3) 많은 데이타가 한꺼번에 추가,제거되는 경우가 아니라면 myisamchk는 데이타베이스에서 문제점
이 발견되었을 때만 수행하는 것이 좋다.
(실습1) .frm파일이 삭제된 경우 복구 실습
(1) 다음과 같은 테이블을 생성한다.
mysql> select * from name;
+-------+--------+------+
| id | name | sex |
+-------+--------+------+
| zzang | 홍길동 | M |
| weppy | 성춘향 | F |
| power | 임꺽정 | M |
| dream | 이몽룡 | M |
+-------+--------+------+
mysql> show index from name;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------++
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part ||
+-------+------------+----------+--------------+-------------+-----------+-------------+----------++
| name | 0 | PRIMARY | 1 | id | A | 4 | NULL ||
+-------+------------+----------+--------------+-------------+-----------+-------------+----------++
(2) name.frm삭제
(3) name.MYD와 name.MYI 파일을 임의의 디렉토리로 이동시킨다.
(4) 동일한 이름인 name이라는 이름으로 임의의 테이블을 생성한다.
mysql> create table name
-> (id int));
=> 전혀 형식에 맞지 않아도 된다.
(5) mysql를 종료하고 name.MYD와 name.MYI파일을 원래 위치에 복사한다.
(6) myisamchk로 복구한다.
(실습2) .MYI파일이 삭제된 경우 복구 실습(테이블은 위와 같다고 가정하자.)
(1) 기존의 데이터파일인 .MYD와 .frm파일을 임의의 디렉토리로 옮긴다.
(2) 동일한 테이블명인 name로 임의의 테이블을 생성한다.
mysql> create table name(id int);
(3) MySQL을 종료한다.
mysql> quit
(4) 데이터디렉토리에 생성된 새로운 name.frm과 name.MYD파일을 삭제한다.
(5) 기존의 name.frm과 name.MYD파일을 데이터디렉토리에 복사한다.
(6) myisamchk -r name을 수행한다.
(7) MySQL을 접속해서 select문을 수행시켜보면 데이타가 깨져 있으므로 데이타를 삭제한다.
mysql> delete from name
(8) MySQL을 종료한다.
mysql> quit
(9) 기존의 name.MYD만 다시 복사해온다.
(10) myisamchk -r name명령을 다시 수행한다.
3. 로그파일 관리하기
(1) MySQL의 로그파일: MySQL에서 로그파일은 MySQL 서버와 관련된 기록이 저장되는 파일을 말한다.
로그파일은 그 쓰임에 따라 3가지로 분류하며 보통 /usr/local/mysql/var
(리눅스 소스컴파일 기준)에 위치한다.
(2) 로그파일의 종류
1) 에러로그 : '설정한_호스트네임.err'형태로 저장되며 서버실행시 에러를 기록한다.
2) 일반적인 로그 : '설정한_호스트네임.log'형태로 저장되며 MySQL에 접근하는 사용자와 그들이
파일과 관련된 쿼리를 실행할 경우에 기록한다. MySQL은 데이터를 파일에 기록하
므로 파일과 관련된 쿼리는 데이터베이스 생성/삭제, 테이블 생성/삭제, 레코드
삽입/갱신과 같은 쿼리를 말한다. 이 로그파일은 MySQL 서버실행시 --log옵션을
주어야 한다.
3) update 로그
ㄱ. host_name.#(#는 일정하게 증가되는 번호)로 저장되면 update관련된 쿼리를 기록하는 로그
ㄴ. MySQL 서버실행시 --log-update라고 주면 되고 서버가 다시 실행되거나 mysqladmin
refresh 또는 mysqladmin flush-logs명령을 내릴때마다 host_name.001, host_name.002 등과
같이 증가한다.
ㄷ. 로그파일을 한 파일로 관리할 경우에는 --log-update=update.log와 같이 파일이름과
확장자를 같이 주면된다.
ㄹ. SQL언어중 update 이외에 delete, create등의 쿼리도 모두 기록된다.
ㅁ. 이 로그파일은 실수로 delete하거나 update하였을 경우 기존의 값을 복원하는 데 사용할 수
있다.
ㅂ. 이 로그파일은 MySQL사용량이 많은 경우 파일의 크기가 매우 커질 수 있으므로 일정주기
마다 삭제해야 한다.
댓글 없음:
댓글 쓰기