■ Timestamp 와 timezone과의 관계timestamp 컬럼 속성을 가지는 데이터를 마이그레이션 할때 유의할 사항이 있습니다.그것은 timezone의 속성이 굉장히 중요하다는 것입니다.예를 들어 기존 MySQL 서버의 time zone이 KST(+9시간) 일때 만약 UTC tim zone(+0)시간을 가지는 MySQL 서버에 데이터를 입력할 경우 UTC의 시간으로 바뀝니다. 즉 현재 MySQL서버의 시간에 따라간다는 의미입니다.테스트를 통해 알아보도록 하겠습니다. ■ 테스트다음과 같은 데이터가 있다고 가정합니다.mysql> select @@time_zone;+-------------+| @@time_zone |+-------------+| +09:00 |+-------------+1..
■ 복제 에러 건너뛰기MySQL의 마스터-슬레이브 복제 환경에서 운영시 복제가 멈추는 상황이 발생합니다. 예를 들면데이터 중복오류슬레이브에 존재하지 않는 데이터베이스에 데이터 입력슬레이브에 존재하지 않는 테이블에 데이터 입력 시도등 여러가지가 있습니다. 이런 이유들로 인해 데이터 복제가 멈추게 됩니다. 여기에서는 이런 에러에 대해 해결하는 방법에 대해 알아보겠습니다. ■ 에러 상황복제 중지 에러가 발생하게 되면 보통 다음과 같은 에러가 발생하게 됩니다. 아래에 나와있는 show slave status 명령을 입력하게 되면 에러 내용에 대해 자세히 나오게 됩니다.위에 보면 Last_Errno와 Last_Error가 있습니다. 이곳에서 왜 에러가 발생했는지 원인을 파악해 볼 수 있습니다.mysql> show..
■ 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 구..
■ Redo Log란많은 데이터베이스 관리 시스템과 마찬가지로 MySQL은 데이터 내구성을 달성하기 위해 로그를 사용합니다(기본 InnoDB 스토리지 엔진을 사용할 때). 만약 DB 에 장애가 발생하여서 메모리 영역에만 남아있는 데이터를 디스크 영역으로 옮겨지지 못한채 서버가 다운되는 현상이 발생했을때 복구할수 있는방법이 바로 Redo Log, Undo log를 이용하는 것입니다. 이 로그들을 이용하면 트랜잭션이 커밋될 때 충돌이나 정전이 발생해도 데이터가 손실되지 않습니다. 리두로그에 대해선 따로 정리해서 포스팅하겠습니다. ■ Redo log 동작원리MySQL의 InnoDB 스토리지 엔진은 고정 크기(순환방식)의 Redo 로그 공간을 사용합니다. 크기는 innodb_log_file_size 및 inno..
■ InnoDB 스토리지 엔진 모니터링 방법InnoDB 스토리지의 상태를 확인하는 방법은 infomration_Schema와 sys스키마, performance 스키마를 확인하는 방법등이 있습니다.이중에서 가장 대표전인것이 Show 명령어를 이용하여 엔젠상태를 확인하는 방법입니다.이 명령어를 통해서 나오는 스토리지 엔진에 대해 하나하나 확인해 보겠습니다. ■ 스토리지 엔진 내용MySQL에서 다음과 같은 명령어를 입력하면 아래와 같은 화면이 나옵니다. 이 내용 안에는 Deadlock 정보, 버퍼풀 정보, 트랜잭션 정보등 정말 DBA에게 중요한 정보들이 많이 나옵니다.이 정보들에 대해 확인해 보겠습니다.MySQL> show engine innodb status;==========================..
sync_relay_logsync_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 인덱스의 리프노드에는 모든 칼럼이 같이 저장되어 있습니다.즉 클러스터링 테이블은 데이터와 프리머리 인덱스를 모두 포함하고 있는 구조가 되는 것입니다. ■ 세컨더리 인덱스(Secon..
■ 버퍼풀 상태 저장과 복원서버를 다시 시작한 후 워밍업 기간을 줄이기 위해 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> show var..
MySQL Performance Cheat Sheet MySQL은 확장가능하고 원하는 성능을 위해 최적화하고 조정할 영역이 많습니다. 일부 변경은 동적으로 수행할 수 있고 다른 변경은 서버를 다시 시작해야 합니다. 기본 구성으로 MySQL 설치를 하는 것은 매우 일반적이지만 때로는 워크로드 및 설정 자체에 적합하지 않을 수 있습니다. MySQL의 주요 영역을 요약하여 이를 살펴보겠습니다. System VariablesMySQL에는 변경을 고려할 수 있는 많은 변수가 있습니다. 일부 변수는 동적이므로 SET 문을 사용하여 설정할 수 있습니다. 다른 것들은 구성 파일(예: /etc/my.cnf, etc/mysql/my.cnf)에서 설정한 후 서버를 다시 시작해야 합니다. 서버를 최적화하기 위해 조정하는데 필..