■ 복제 에러 건너뛰기 MySQL의 마스터-슬레이브 복제 환경에서 운영시 복제가 멈추는 상황이 발생합니다. 예를 들면 데이터 중복오류 슬레이브에 존재하지 않는 데이터베이스에 데이터 입력 슬레이브에 존재하지 않는 테이블에 데이터 입력 시도 등 여러가지가 있습니다. 이런 이유들로 인해 데이터 복제가 멈추게 됩니다. 여기에서는 이런 에러에 대해 해결하는 방법에 대해 알아보겠습니다. ■ 에러 상황 복제 중지 에러가 발생하게 되면 보통 다음과 같은 에러가 발생하게 됩니다. 아래에 나와있는 show slave status 명령을 입력하게 되면 에러 내용에 대해 자세히 나오게 됩니다. 위에 보면 Last_Errno와 Last_Error가 있습니다. 이곳에서 왜 에러가 발생했는지 원인을 파악해 볼 수 있습니다. mys..
■ Undo Log란? • 임시 테이블 스페이스에있는 언두 로그는 사용자 정의 임시 테이블의 데이터를 수정하는 트랜잭션에 사용됩니다. 실행 취소 로그 레코드의 집합으로 Transaction 실행후 Rollback 시 Undo Log 를 참조해 이전 데이터로 복구할수 있도록 로깅 해놓은 영역입니다. 서버가 실행되는 동안 롤백에만 사용됩니다. 이 유형의 실행 언두 로그는 재실행 로깅 I/O를 피함으로써 성능을 향상시킵니다. 트랜잭션이 데이터를 쓸 때 항상 테이블 인덱스 또는 데이터(버퍼 풀 또는 실제 파일)에 데이터를 삽입합니다. 개인 복사본이 생성되지 않습니다. 활성 XtraDB/InnoDB 트랜잭션에 의해 수정되는 이전 버전의 데이터는 실행 취소 로그에 저장됩니다. 그런 다음 원본 데이터를 복원하거나 일..
■ DBMS에서 커밋(Commit)이란?? COMMIT 문은 관계형 데이터베이스 관리 시스템(RDBMS)에서 트랜잭션을 종료하고 다른 사용자에게 변경된 모든 사항을 보이도록 만드는 명령문입니다. 일반적으로 트랜잭션 종료시 해당 업데이트를 확정한다는 의미에서 "commit"이라고 합니다. 반대로 업데이트를 취소 처리를 롤백 (ROLLBACK)이라고 하며, 이러한 제어를 약속 제어라고 부르기도합니다. SQL에서는 ROLLBACK 문이 그 처리를 담당합니다. SQL에서 COMMIT은 RDBMS 내에 있는 데이터베이스 트랜잭션을 종결시키고, 모든 변화를 다른 사용자들이 볼 수 있게 합니다. 일반적인 포맷은 BEGIN WORK 구문으로 시작하여, COMMIT 구문이 나오게 됩니다. 다른 방법으로 ROLLBACK ..
■ InnoDB 스토리지 엔진 모니터링 방법 InnoDB 스토리지의 상태를 확인하는 방법은 infomration_Schema와 sys스키마, performance 스키마를 확인하는 방법등이 있습니다. 이중에서 가장 대표전인것이 Show 명령어를 이용하여 엔젠상태를 확인하는 방법입니다. 이 명령어를 통해서 나오는 스토리지 엔진에 대해 하나하나 확인해 보겠습니다. ■ 스토리지 엔진 내용 MySQL에서 다음과 같은 명령어를 입력하면 아래와 같은 화면이 나옵니다. 이 내용 안에는 Deadlock 정보, 버퍼풀 정보, 트랜잭션 정보등 정말 DBA에게 중요한 정보들이 많이 나옵니다. 이 정보들에 대해 확인해 보겠습니다. MySQL> show engine innodb status; ===================..
sync_relay_log sync_relay_log를 1로 설정하면 수신된 각 트랜잭션이 디스크에 기록된 후 릴레이 로그를 디스크에 동기화하도록 복제 I/O 스레드에 지시합니다. 즉 성능은 줄이되 안정성을 높이는 방법입니다. 가능하면 1로 설정을 권고합니다. 0이나 다른 숫자로 바꾸면 성능이 좋아지긴 합니다. 이건 선택적인 문제이기도 합니다. 참고로 sync_relay_log를 1보다 크게 또는 sync_relay_log를 0(여기서 동기화는 운영 체제에서 처리됨)인 경우 복제본이 예기치 않게 중단되는 경우 디스크에 동기화되지 않은 커밋된 트랜잭션이 있을 수 있습니다. 이러한 트랜잭션으로 인해 디스크에 마지막으로 동기화된 릴레이 로그에 있는 정보를 기반으로 복구 중인 복제본이 트랜잭션을 건너뛰는 대신 ..
■ InnoDB 스토리지 엔진의 테이블 데이터 저장 방식 MySQL 테이블의 데이터는 IOT(Index Oraganized Table)라고 하여 프리머리 키값을 이용하여 데이터를 정렬 후 테이블에 저장하게 되어 있습니다. 이 프리머리 키를 다른말로 클러스터링 인덱스라고도 합니다. 그리고 MySQL에서는 이 클러스터링 인덱스가 좀 특별하게 다루어집니다. 그림 1. MySQL B-Tree 인덱스 구조 위의 그림은 MySQL의 클러스터링 인덱스의 구조인데 일반적인 B- Tree 인덱스와 비슷합니다.. 그러나 MySQL에서 B-Tree 인덱스의 리프노드에는 모든 칼럼이 같이 저장되어 있습니다. 즉 클러스터링 테이블은 데이터와 프리머리 인덱스를 모두 포함하고 있는 구조가 되는 것입니다. ■ 세컨더리 인덱스(Sec..
■ 버퍼풀 상태 저장과 복원 서버를 다시 시작한 후 워밍업 기간을 줄이기 위해 InnoDB는 서버 종료시 각 버퍼 풀에 대해 가장 최근에 사용한 페이지의 백분율을 저장하고 서버 시작시 이러한 페이지를 복원합니다. 최근에 저장된 페이지의 백분율은 innodb_buffer_pool_dump_pct 구성 옵션으로 정의됩니다. 사용중인 서버를 다시 시작한 후에는 버퍼풀에 있던 디스크 페이지가 메모리로 다시 가져오기 때문에 (같은 데이터를 쿼리, 업데이트 등) 일반적으로 워밍업 시간이 꾸준히 증가합니다. 시작시 버퍼 풀을 복원하는 기능은 DML 조작이 해당 행에 액세스 할 때까지 기다리지 않고 다시 시작하기 전에 버퍼 풀에 있던 디스크 페이지를 다시로드하여 예열시간(warmup)을 단축시킵니다. 또한 대규모 일괄..
보통 IP를 DB에 저장할때는 캐릭터 형태의 컬럼(varchar 혹은 char)을 많이 사용하는것으로 알고 있습니다. 저또한 마찬가지 입니다. 그러나 MySQL에서는 IP를 특화된 방법으로 저장하고 불러올 수 있습니다. 또한 저장 방법이 Integer방식이기 때문에 검색에서 더 효율적이기도 합니다. MySQL에서 IP를 저장하는 방법과 호출하는 방법에 대해 알아보겠습니다. ▶︎ 준비사항 MySQL에서 IP를 저장하는 방법은 숫자형으로 저장이 됩니다. MySQL문서에서는 INT형보다는 INT UNSIGNED 컬럼을 사용할것을 권고하고 있습니다. create table addr(ip int(11) unsigned); ▶︎ 사용방법 INET_ATON(expr) 일발적인 IPv4 네트워크 주소 방식으로 표현된 ..
■ Connection 관련 파라미터와 MySQL 상태 변수 컨넥션에 관련된 파라미터와 상태 확인 변수들입니다. 상태 변수 확인 후 관련 파리미터를 설정하여 성능을 개선시킬 수 있습니다. 파라미터미터와 상태 변수의 상호 관계와 어떤 부분을 모니터링하고 확인해야 하는지 알아봅니다. 1. Variables (예 : show variables like '%max_connection%';) • max_connections 허용되는 최대 동시 클라이언트 연결 수입니다. 접속 최대수를 초과해 접속 시도를 하는 경우 "too many connections error"이 발생 합니다. max_connection 수가 늘어 나면 innodb 의 thread별 메모리 사용량이 늘어나 같이 고려해야 합니다. mysql> s..
MySQL Performance Cheat Sheet MySQL은 확장가능하고 원하는 성능을 위해 최적화하고 조정할 영역이 많습니다. 일부 변경은 동적으로 수행할 수 있고 다른 변경은 서버를 다시 시작해야 합니다. 기본 구성으로 MySQL 설치를 하는 것은 매우 일반적이지만 때로는 워크로드 및 설정 자체에 적합하지 않을 수 있습니다. MySQL의 주요 영역을 요약하여 이를 살펴보겠습니다. System Variables MySQL에는 변경을 고려할 수 있는 많은 변수가 있습니다. 일부 변수는 동적이므로 SET 문을 사용하여 설정할 수 있습니다. 다른 것들은 구성 파일(예: /etc/my.cnf, etc/mysql/my.cnf)에서 설정한 후 서버를 다시 시작해야 합니다. 서버를 최적화하기 위해 조정하는데 ..
MySQL 서버 하드웨어 및 OS 조정: 1. 전체 InnoDB 파일을 메모리에 로드하기에 충분한 물리적 메모리가 있어야 합니다. InnoDB는 디스크가 아닌 메모리에서 파일에 액세스할 수 있을때 훨씬 빠릅니다. 2. 스와핑을 피합니다. 스와핑은 디스크에서 읽는 것이므로 속도가 느립니다. 3. 배터리 지원 RAM(Battery-Backed RAM)을 사용합니다. 4. 고급 RAID를 사용합니다. 가급적이면 RAID10 이상을 사용합니다. 5. RAID5를 피합니다. 무결성을 보장하는데 필요한 체크섬은 비용이 많이 듭니다. 6. OS와 데이터 파티션을 논리적으로뿐만 아니라 물리적으로 분리합니다. 비용이 많이 드는 OS 쓰기 및 읽기는 데이터베이스 성능에 영향을 미칩니다. 7. mysql 임시 공간과 복제 ..
■ MySQL 기본 설정 • 하드웨어 CPU코어와 메모리는 많을수록 좋습니다. 어떤 DBMS던 이건 공통적인 내용입니다. 스토리지 측면에서 SSD 디스크는 기존의 스핀들을 성공적으로 대체하여 비용 대비 최고의 성능을 제공합니다. RAID 10은 여전히 대부분의 워크로드에 가장 권장되는 수준이지만 먼저 RAID 컨트롤러가 SSD 드라이브의 성능을 활용할 수 있고 실제 병목 현상이 되지 않는지 확인해보아야 합니다. 더 많은 IOPS가 필요한 경우 많은 PCI-e 플래시 드라이브도 있습니다. • 운영 체제 Linux는 고성능 MySQL 서버를 위한 가장 일반적인 운영 체제입니다. 최신 커널과 결합된 Linux의 EXT4 또는 XFS와 같은 최신 파일 시스템을 사용하는것이 좋습니다. 그리고 각각 고유한 한계..