우연히 ARCHIVE Storage 엔진에 대한 소식을 듣고 공식 홈페이지를 통해 구글 번역과 참고를 통해 

정리해 보았다.

구글 번역이 점점 자연스러워 지고 있네..!!!!(감사합니다.ㅠ)

어디에 어떻게 적용해 볼지 고민을 해봐야겠다.(Log DB를 생각중...)

혹시라도 도움을 주실 수 있다면 댓글로 감사히 받겠습니다. 경험담 좀 부탁 드립니다.ㅠ

• 대용량의 데이터를 작은 단위의 풋프린트(footprint)없이 저장하기 위해 사용
• 설치 하려면 source 에서 configure에 --with-archive-storage-engine 옵션 추가해야 함
• •  CMake with the -DWITH_ARCHIVE_STORAGE_ENGINE   (look in the storage/archive directory)(mysql공식 홈페이지)
• 체크 >> show variables like ‘have_archive’
• 테이블을 생성하면 하나의 테이블 포맷 파일을 생성(*.frm)
• 데이터 파일의 경우 *.ARZ / *.ARM 파일 생성
• ARN 파일은 최적화(Optimization operations)를 하는 동안 나타날 수도 있음
• 하지만 mysql5.7 에서는 ARZ/ARN/FRM 파일 3개만 생성 되
• 테이블 하나당 대략 4개의 파일이 생성된다고 보면 될 듯
• 
  
• INSERT와 SELECT만 가능(DELETE, REPLACE, UPDATE 미지원)
• • mysql5.7에서는 INSERT / REPLACE / SELECT 지원 (Order by 미지원, BLOB 등 미지원)
• Auto Increment속성 지원(다른 컬럼으로 index 를 생성되지 않음)
• row level lock
• row 추가 시(INSERT) zlib 무손실 데이터 압축 진행
• Optimizer Table를  사용하여 테이블 분석할 수 있음
• Check Table를 사용할 수 있음
• • 데이터 추가 시 압축 버퍼로 넣으며, 필요한 만큼 버퍼 플러시가 이루어 지는데, 이 때 데이터 보호를 위해 lock이 지원
  • select의 경우 강제 플러시 실행
• Bulk Insert의 경우 동시간에 다른 Insert가 발생하지 않더라도 완료 후에 볼수 있으며 부분적으로는 볼 수 있음
• 검색: 캐쉬에 없을 경우 압축을 해제한 후 select 진행
• • select 는 consistent read 로 진행
  • 대량 INSERT진행 시 SELECT를 사용하면 압축 관련 지연될 것이다.
• ARCHIVE 의 효과를 올리기 위해서는 Optimize Table , Repair Table을 사용할 수 있음
• ARCHIVE 테이블의 데이터 수는 show table status를 통해 항상 확일할 수 있음

텐써플로우 첫걸음 이라는 책을 사놓고..

보지도 않고 있다가

오늘에서야 조금씩 보기 시작했다.

DBA로써 내 분야에 대해 공부와 더불어 스트레스를 풀기 위해 데이터 분석도 진행하려고 샀다.

2. 비용함수와 경사 하강법 알고리즘

* 사실 이해를 제대로 못했다...하지만 한번 훑고 넘어 가고 다시 한번 보도록 해야겠다.

mysqldump로 백업을 진행하다 보니 테스트 기회가 생겨 테스트를 진행해 보았다.

sql파일을 바로 Import 진행하니 중간에 세션이 끊기거나 한다면 ㅊㅓ음부터 다시 진행해야하는 불상사가 발생해서.

검색 도중 innodb를 myisam으로 변경 후 진행하는 방식을 추천해 주어 진행해 보았다.

기본 테스트 테이블의 정보는 아래와 같다.

2번째 테이블이 대상이고 빈 테이블에 myisam으로 변경 후

68,769,549 건을 진행했다.

시간을 보면 알 수 있듯이

대략 7시간 정도 소요 되었다.

(메모리는 32G 기준 )

참고로, 동일 테이블에 동일 방식으로 innodb를 진행하였을 경우 10시간 이상 진행해도 끝나지 않았다.

이렇게 되면 MyISAM 으로 변경 후 진행해야 되는 걸까??

이 부분에 대해서 추가 작업이 있음을 인지하고 있어야 한다.

해당 테이블을 MyISAM 에서 InnoDB로 Storage 엔진을 또 변경해야 된다는 것이다.

데이터가 없을 때에는 바로 변경이 된다.

하지만 데이터가 있을 경우 temp 테이블에 추가하였다가 진행해야 되기에 시간이 만만치 않게 소요 된다.

아쉽게도 정확한 측정을 못했지만 대략 4시간 정도 소요 되었다.

약 7억건의 데이터를 옮기는데 이정도 소요된다.

다시 한번 21건에 대해서 진행 중인데, 정확한 수치를 알고 있어야 될 듯 싶으며,

이번 기회를 통해 백업 방식을 xtrabackup으로 테스트 및 변경을 고려 해 봐야 할 듯 싶다.

좋은 정보가 있어서 공유 합니다.

https://www.cockroachlabs.com/docs/stable/cockroachdb-in-comparison.html#yes-feedback

CockroachDB in Comparison

* In DynamoDB, distributed transactions and ACID semantics across all data in the database, not just per row, requires an additional transaction library.

MariaDB로 누가 접속 했는지 어떠한 행동을 했는지 확인을 위해서 Audit를 테스트 진행해 보았다.

진행 방식은 다음과 같다.

1. 지속적으로 로그인 성공 및 시간 조회 / 로그인 암호 실패 

2. 지속적으로 Sysbench를 이용하여 10000000 건의 테이블에 대해 select 진행 

3. 이 부분에 대해 cpu / Disk IO / QPS 등을 체크

처음에는 이와 같은 방식으로 지속적으로 체크를 한다.

이후 Audit을 on으로 한 이후에 (로그를 남김) 해당 부하들의 변화률을 체크 하기로 하자.

참고로 Audit 은 아래 블로그를 참고해서 플러그인을 설치하자. 

직접 올려도 되지만 더 자세하게 잘 알려준 블로그가 오히려 좋을 듯 싶다.

1. 지속적으로 로그인 아웃을 하는 스크립트는 파이썬을 이용했다. Thread를 이용하면 좋겠지만...간단하게 개발하기 위해서..내 실력이 안되기에..ㅠ

#-*- coding:utf-8 -*-

import pymysql
import time

def LoginSucess(tp=''):
    db = pymysql.connect(host='192.168.0.1', port=int(3306), user='root', passwd='root', db='mysql',
                         autocommit=True, use_unicode=True, charset="utf8")
    if tp.lower() == 'dict':
        cursor = db.cursor(pymysql.cursors.DictCursor)
    else:
        cursor = db.cursor()
    query = """
        SELECT now();
    """
    cursor.execute(query)
    row = cursor.fetchone()
    print(row)

def LoginFail(tp =''):
    try:
        db = pymysql.connect(host='192.168.0.1', port=int(3306), user='root', passwd='1234', db='mysql',
                             autocommit=True, use_unicode=True, charset="utf8")
        if tp.lower() == 'dict':
            cursor = db.cursor(pymysql.cursors.DictCursor)
        else:
            cursor = db.cursor()

    except Exception as e:
        print(str(e))


if __name__ == "__main__" :
    while True :
        LoginSucess()
        LoginFail()
        time.sleep(1)

- 여기서는 지속적으로 돌면서 로그인 아웃을 시도한다....mysql close하는 부분이 없네.......OTL

2. Sysbench를 이용해서 다른 서버에서 해당 서버로 스트레스 테스트를 진행하는 것이다.

주석을 달아 놨듯이.. 처음에는 10,000,000 건을 Insert 이후에 해당 건을 while문이 돌면서 지속적으로 스트레스를 주는 것이다.

왜 while로 했냐면 해당 가상 머신 사양이 좋지 않아 CPU를 100%로 만들지 않으면서도 지속적인 스트레스를 주기 위함이다..

CPU를 100%하면 이후에 어떠한 반응도 체크를 하기 힘들기에 나는 적정선이 70%내를 유지하기 위함이다.

이렇게 한 후 이제 CPU와 QPS등을 체크를 해보자.

체크하는 것은 또한 Python을 이용하였다.

보는 바와 같이 QPS / CPU 등이 일정한 것을 확인할 수 있다...아쉬운 건 Disk I/O를 발생시키지 못한 점이다...

이제는 Audit을 ON으로 한 후 다시 진행 해 보자.

정상적으로 로그가 남기고 있는지 확인해 보자

따로 경로를 설정하지 않았다면, Data폴더에 가면 확인할 수 있다.

Data폴더에서는 일정 크기의 로그가 쌓이면 자동 백업을 진행하게 된다.

아래 일부를 캡쳐했는데, 유저 / 어디서 들어오는지..쿼리는 어떤것을 남기는지 확인할 수 있다.

여기까지 진행하고 DB / OS 상황을 보자.

별다른 특이 사항은 없어 보인다.

이제는 위와 같은 방법으로 로그인 성공 / 실패 를 하는 스크립트를 실행하고, 추가로 스트레스 테스트도 진행하자.

이렇게 어느정도의 스트레스가 완료 되었다면 둘을 비교해 보자.

비교는 ipython Jupyter를 이용하였다. 추가로 matplotlib을 이용하여 둘의 결과를 그래프로 표현해 보았다.

파란색 선이 Audit 을 Off로 한 후에 진행한 그래프이며, 녹색 선이 Audit 을 On 으로 한 후 진행 하였다.

결과를 보면 큰 차이가 없는 것을 확인할 수 있다.

간단한 결과만 보고 Audit을 On으로 한다고 부하가 발생ㅎㅏ지 않는다고 단정할 수 없다.

8 쓰레드로 진행하였으며(sysbench에서 8쓰레드로 명시했지만 제대로 됬는지는 확인을 못했다..), session도 30 내외로 진행하였기 때문에 더더욱 실제 운영에는 적용할 수 없다.

하지만 간단하게 테스트한 부분이기에 이 부분을 조금 더 보완하여 진행 한다면 분명 좋은 결과를 도출해 낼 수 있을 것이다.

Prometheus를 설치 했으니 Grafana로 모니터링을 진행해 보자.

사이트 : http://docs.grafana.org/

간단하다.........즉 Prometheus 의 Metric 을 이용하여 해당 정보를 시각화 하여 보여 준다는 것이다.

그 외의 사용에 대해서는 테스트 하지 않아서 모르겠다.

그러면 앞서 Prometheus를 설치 및 데이터 수집ㅇㅣ 되는 것을 확인 하였다.

이제는 그 위에 Grafana를 설치해서 수집된 데이터를 한번 그래프화 해 보자.

Prometheus도 자체의 그래프를 가지고 있지만 심플하고 다양한 Metric을 한 눈에 보기 힘들다는 단점이 있다. 이런 부분을 Grafana가 해결해 주는 것이다.

Grafana 설치 

* Ubuntu / Centos / Mac / Windows 등 다양하게 설치 할 수 있다.

* 아래 사이트를 참조 하자.

http://docs.grafana.org/installation/rpm/

https://www.percona.com/blog/2016/02/29/graphing-mysql-performance-with-prometheus-and-grafana/

나 역시 yum으로 설치를 진행 하였다.

설치 후에는 service grafana-server start로  실행했다.

이제 실행해 보자.

Prometheus와 동일하게 Grafana도 http://설치한서버IP:3000 으로 접속을 할 수 있으며,

초기 비밀번호는 admin / admin 이다.

이후 나는 몇일을 고생했다..어떻게 하는지 몰라서 말이다..ㅠㅠ

먼저 좌측 메뉴를 PIN으로 고정을 하자..

이 후 Data source를 통해 기본 서버를 구성하자.

아래 메뉴는 PIN으로 고정 및 Data Source를 선택 한 화면이며 Add data source 로 추가하자.

Type  은 Prometheus로 하고 url은 prometheus를 설정한 곳을 지정해 주자.

그리고 Direct로 접속 설정 후 save & Test를 하면 다음과 같이 확인할 수 있다.

나같이 성질 급하고 영어 못하고..컴맹은 메뉴얼을 봐도 삽질을 하고 결국은 해결은 하겠지만 몇일 걸릴 것이다.ㅠㅠ

위와 같이 떴다면 성공이다...

다시 Data Sources를 선택하면 다음과 같은 화면이 나올 것이다.

나온 화면에서 Dashboards를 선택해 보자.

이 후 Dashboards 의 New를 선택해서 하나씩 나의 대쉬보드에 원하는 데이터를 추가해 보자

여러 템플릿이 있겠지만 가장 많이 사용하는 Graph를 이용해서 추가해 보자.

Graph를 선택해 보자.

앗!!뭔가 나왔다...하지만 뭔가가 이상하다.

나는 수집도 하지 않은 데이터가 차트로 그려졌다.

정확하게 다시 말하면 내가 node를 설치하기 전의 데이터도 수집 된 것으로 나온다.

뭔가가 이상하다....(처음에는 이게 끝인줄 알았다.....ㅠㅠ)

Panel Title이라는 글씨를 누르게 되면 상단처럼 위에 View - Edit - Duplicat - Share 등이 나온다.

여기서 Edit를 선택해 보자

그래...밑에 Metrics라는 것이 있는데 내용에 fakedata source 라고 나온 것을 확인할 수 있다.

즉. 해당 데ㅇㅣ터는 임시 데이터로써 나의 수집된 데이터와는 무관하다는 뜻이다.

하단에 Panel data source 항목 옆 default를 선택하면 우리가 Data source로 추가한 Test_Source가 나온다. 

Test_Source 를 추가하면 아래와 같이 나온다.

이제 우리가 원하는 Metric 을 추가할 수 있다.

Metric looup 항목에 metric name을 선택하면 여러가지 Metric이 항목으로 ㄴㅏ올 것이다.

여기서 원하는 Metric을 선택해 주자.

정말 환호의 순간이다..원하는 데이터가 나온다.

하지만 여기서 나의 센스가 없는 모습이 나온다.

다른 항목을 선택했더니 데이터가 나오지 않았다...열심히 삽질했다.

정말 센스있는 사람이라면 찾았을 것이다. query의 내용이 겹쳐있다는 것을...

쿼리는 우리가 원하는 데이터를 다시 한번 더 가공할 수 있다. 하지만 여기서는 가공하는 방법을 알려주지 않는다.

원하는 분은 아래를 참고하시기 바랍니다.

Query Edit

http://docs.grafana.org/features/datasources/prometheus/#query-editor

쿼리 내용을 깨끗이 지우고 다시 Metric을 추가하면 원하는 데이터가 나올 것이다.

원하는 데이터가 나왔다면 X 를 누르면 된다.

걱정 안해도 된다 이미 저장 되어 있기 때문이다.

이와 같은 방법으로 2개를 만든 모습ㅇㅣ다.

정말 화려한 그래픽이다...

그래서 원하는 내용을 빠르게 보거나 아니면 패턴을 빨리 익힐 수 있다.

패턴을 알고 나면 이제는 어떤 부분이 문제가 있는지..또는 배치가 얼마나 자주 도는지..

그에 대한 메모리 사용을 확인하고 수정이 필요한지...

아니면 확장을 할 것인지 많은 해결 방법을 찾을 수 있다.

잠시 몇달동안 일을 하면서 팀장님에게 배운 것 중 가장 기억에 남는것이 있다면 바로 모니터링이다.

모니터링이 누구에게는 따분할 수 있지만..

모니터링을 잘 활용하면 얼마든지 개선할 수 도 있고, 패턴을 익혀 프로그램의 주기를 변경할 수 있는 기회가 생긴다.

프로그램의 주기를 변경하면 효율적으로 자원을 활용할 수 있고 자연스럽게 장애도 예방된다.

또한 패턴을 이용해서 해당 시점의 쿼리를 수집하여 튜닝을 할 수도 있고..

무슨일이 어떻게 돌아가는지 익힐 수 있는 기회가 생긴다.

그러니 모니터링을 하찮게 생각하지 않았으면 한다.

경계에 실패한 자는 용서할 수 없듯이.. 모니터링에 실패하는 자는 훌륭한 DBA가 될수 없다고 혼자 생각해 본다-ㅎㅎ

이직한지 3개월째 되어가고 있지만 아직도 많이 부족하고 내가 모르는 것들이 많다는 것을 계속 느끼고 있다.

정말 이럴때는 자괴감??까지 들 정도이다..ㅎ

그래도 새로운 것을 공부하고 설치해 보고 이에 대해서 팀장님이랑 의논하다 보면 스트레스가 오히려 지식으로 바뀌어 나가고 있다는 것을 느낄 때도 많다.

오늘은 여기까지만 잡설을 하자...ㅋ

Prometheus라는 모니터링 툴이 있으니 한번 검토해 보라는 오더를 받았다.

하지만 이놈의 꽂히는게 어디냐에 따라서 다른 방향으로 가는경우가 있다 보니 이번에도 실컷 Prometheus라고 생각한 것을 Grafana만 해버린 꼴이 되었다.

덕분에 Prometheus 와 Grafana에 대해서 둘다 경험할 수 있는 좋은 기회가 되었다.

내가 조사하고 느낀 것 뿐만 아니라 테스트 한 것에 대해서 공유해 본다.

하아...그래 정의는 그렇다고 치자...그렇다면 작동원리는 어떻게 되는지 알아보자

그전에 아키텍처 그림을 보자. 해당 그림은 Prometheus 홈페이지에서 확인 가능하다.

흠...이렇게 힘들게 글로 읽고 조금이라도 이해 했다면 이제 진행해 보자.

1. Prometheus 설치 및 구성

다운 받을 위치는 다음과 같다

https://prometheus.io/download/

설치 방법은 아래와 같다

https://prometheus.io/docs/introduction/install/

https://prometheus.io/docs/introduction/getting_started/

사실 설치라고 하기 보다는 압축을 풀고 구성하면 된다.

이제 prometheus.yml 를 수정해보자.

아래 보면 Target에는 모니터링 하고자 하는 서버들의 IP를 설정하고 내가 모니터링 하고자 모듈은 node_export / mysqld_exporter 이다.

그에 대한 포트는 9100 과 9104 이다.

그리고 두대의 모니터링 서버이며...그거에 대한 명은 'linux' , 'linux2' 로 명명 했다. - job_name

이제 prometheus 서버는 구성이 완료 되었다. 실행은 각 노드들(모듈)을 설치하고 실행하자.

2. 모듈 설치 및 구성

나는 node_export 와 mysqld_export를 통해 원하는 데이터를 읽어 오려고 한다.

각 서버에 모듈을 설치해 보자.

아래 사이트에서 원하는 모듈을 다운 후 압축을 풀어보면 파일이 하나만 생길 것이다.

(모듈을 다 설치해 보지 않았으나 내가 원하는 모듈들은 nodex_epxort / mysqld_export 만 했기에..)

모듈 : https://prometheus.io/download/

DB1에는 이미 prometheus 서버를 진행 했기 때문에 혼란을 ㅍㅣ하기 위해 DB2에서 모듈을 설정 및 실행해 보겠다.

먼저 node_exporter 압축 푼 곳을 확인해 보자

별 달리 설정해 줄 것이 없다.

ㅂㅏ로 실행해서 node_exporter가 수집할 수 있도록 하자.

9100포트가 오픈 되어 있으며 이제 수집을 진행할 것이다.

다음으로는 mysqld_exporter를 진행해 보자.

여기서 해야될 업무들이 있다. mysqld_exporter가 수집할 수 있도록 유저 생성 및 .my.cnf를 생성해서 유저 정보를 알려 주는 것이다.

설정 정보들은 아래 사이트를 참고해 주면 된다.

https://github.com/prometheus/mysqld_exporter

하지만 난 유저 생성시 PROCESS, REPLICATION CLIENT, SELECT 해당 권한만 주면 된다고 했는데 막상 실행해 보니 제대로 실행이 되지 않아서

다음과 같은 권한을 부여했다.

물론 항상 그런거는 아니었지만 나의 경우 서버에서는 다음과 같이 prom이 디비와 테이블을 생성 한 것을 확인할 수 있었다.

추측은 노드는 상관 없지만 서버에서는 Grafana와 연동 시 데이터를 남기는 작업을 진행하는 것 같아서 그런것 같다.

이거는 어디까지나 추측이기 때문에 아는 분이 계시면 댓글 부탁 드립니다.

유저를 생성 했다면 .my.cnf를 만들어 해당 유저와 패스워드 정보를 작성해 주자.

이것 까지 완료 하였다면 이제 mysqld_export를 실행하자.

이제 이 친구도 수집을 진행할 것이다.

이 작업들을 다른 서버에서도 동일하게 진행 해 주자.

서버들에서 모두 모듈들을 실행 했다면 마지막으로 Prometheus 서버에서 실행하자.

별 문제 없이 실행 되었다면 이제 웹에서 prometheus를 실행해 보자..

3. 운영 및 체크

이제 웹 페이지에서 http://프로메테우스서버IP:9090 으로 접속 해 보자.

혹시라도 접속이 안되거나 한다면 방화벽 포트를 의심해 보자.

첫..화면이다...참 심플하다.

이제 한번 정상적으로 프로메테우스 서버에서 각 서버 모듈들의 정보를 가져오는 지 확인해 보자.

Status > Targets 메뉴를 확인해 보자

정상 적으로 각 서버들에 접속해서 정보들을 가져오는 것을 확인 가능하다.

정상적으로 가져오지 못하고 있다면 State에서 다른 정보들이 보일 것이다.

예를들어서 DB2 ㅇㅔ서 mysqld_exporter 를 내렸을 경우다. Down...보기만해도 가슴이 철렁 거린다...ㅋㅋ

여기서 재미있는 부분이 해당 Endpoint 를 클릭해 보자.

무슨 텍스트들이 엄청나게 많다...

이 뜻은...각 메트릭스( Key) 에 대한 수집된 값(정보-value) 들을 볼 수 있다는 것이다.

즉..각 서버내 모듈들은 이렇게 Key-Value 형태로 정보를 가지고 있다가 Prometheus 서버가 직접 해당 모듈들을 http 로 호출해서 정보를 가져 온다는 것이다.

그렇다면 정의 의 내용들을 조금 이나마 이해 할 수 있을 것이다.

다음은 해당 프로메테우스가 수집한 정보를 보는 방법이다.

Execute 옆 드롭 박스를 클릭하면 메트릭 정보들이 쭉 나와 있을 것이다.

이 정보들을 선택 후 Execute를 하게 되면 수집된 정보들을 보여 줄 것이다.

ㄴㅏ는 go_gc_duration_seconds 라는 메트릭 을 선택후 결과를 보면(Execute 클릭) value가 쭉 나올 것이다.

여기서 다시 그래프를 누르면 그래프로 표현이 될 것이다.

이정도로 간단하게 사용하는 방법에 대해서 정리해 본다.

Grafana 는 다음 포스팅에서 진행해 보겠다.

참고로 이러한 Prometheus 의 좋은 기능 간단한 설치 방법 이 있으나 단 칼에 팀장님에게 잘린 이유는 다음과 같다.

이 내용들은 어디까지나 팀장님의 의견이고..나도 이해한 것이므로 반드시 그렇다!!!는 아니다.

절대적으로 잘 사용하는 곳이나 더 좋은 의견이 있으면 댓글로 남겨 주세요...많은 경험의 결과는 사용하신 분들이 잘 알기에 저는 그 의견들을 듣고 싶습니다.

그 외의 Alert 등의 자세한 내용 및 추가적으로 더 좋은 정보는 아래 블로그 참조하세요!!

정말 소개가 잘 나와 있어서 꼭 확인해 보세요!!

참고 : 

https://blog.outsider.ne.kr/1254

http://opennaru.tistory.com/126

Galera Cluster에서 auto increment  컬럼에 따로 값을 지정하지 않으면 해당 노드들끼리 중복이 생겨 정합성이 깨지는 경우가 생길 수 있다.

그래서 옵션에서 중복허용되지 않게 하기 위해 증가되는 값을 설정함으로써 중복을 방지한다.

예를들어 2개의 노드라고 한다면 1번 노드에서는 1,3,5,7,9로 증가하고 2번 노드에서는 2,4,6,8,10 이런식으로 증가한다.

해당 부분을 테스트하여 공유 및 이에 대한 자세한 내용도 첨부해 본다.

* 해당 서버는 노드수가 5개라 현재 5로 되어 있는 것을 확인할 수 있다

- 1번 노드에서  Insert 진행

- 3번 노드에서 insert 진행

노란색을 보면 28까지는 5씩 증가 했지만 a3 에서 넣는 순간 부터는 29에서 진행 후 다시 5씩 증가하는 것을 확인 가능하다.

좀더 자세한 내용은 아래 확인하면 될 듯 싶다.

[출처] http://h391106.tistory.com/323

1.    MariaDB Galera Cluster Variables

wsrep_auto_increment_control

wsrep_causal_reads

wsrep_certify_nonPK

wsrep_cluster_address

wsrep_cluster_name

wsrep_convert_LOCK_to_trx

이 문서는 위 Variables 에 대해 다루고 있다.

테스트에 사용한 MariaDB 는 5.5.39 이며, wsrep_provider_version 은 내장되어 있던 25.3.5(r178) 를 사용하였다.

문서에서 언급 한 각 Variables 의 default, Introduced, deprecated 의 정보는 8.A 를 확인한 내용이며, 이는 wsrep API patch v0.8 를 기준으로 한다.

2.    wsrep_auto_increment_control

Default = on

Introduced = 1

Cluster membership 에 변화가 있을 때

auto_increment_increment 와 auto_increment_offset system variables 을 자동으로 조정한다.

Cluster Membership 의 변화란 클러스터를 구성하는 노드에 변화가 있어 클러스터 사이즈가 커지거나 작아지는 경우를 말한다.

계획 된 작업으로 신규 노드를 추가하는 일도, 장애가 발생해 노드가 클러스터 구성에서 빠지는 일도 Cluster Membership 의 변화이다.

wsrep_auto_increment_control = on 사용으로 INSERT 구문으로 인한 충돌을 줄여준다고 한다.

여기서 말하는 충돌은 MariaDB 의 Duplicate entry 에러를 의미하며, pk 로 auto_increment 를 사용하는 경우를 말한다.

auto_increment_increment

Default = (wsrep_cluster_size)

auto_increment_offset

Default = (Cluster 를 구성하는 노드 1, 2, 3, … 순차적으로 값을 가져간다.)

위의 두 파라미터는 Galera cluster 이전에도 존재하던 Variables 이다.

특히 매뉴얼을 보면 MASTER – TO MASTER Replication (DUAL MASTER) 셋팅에서 사용한다고 한다.

3 node Galera Cluster 를 구성하면 다음과 같이 System Variable 값을 가져간다. ( wsrep_auto_increment_control = on )

전 노드 공통으로 auto_increment_increment = 3

각 노드는 auto_increment_offset = [1|2|3]

auto_increment_offset 의 값이 auto_increment_increment 의 값보다 큰 경우 auto_increment_offset 의 값은 무시되며,

이외의 경우 auto_increment 의 기대되는 값은 다음의 공식을 따른다.

(현재의 auto_increment 보다 큰 가장 작은 값 ) = auto_increment_offset + N * auto_increment_increment

wsrep_auto_increment_control = on 을 사용하는 3 NODE CLUSTER 에서 A 라는 테이블이 AUTO_INCREMENT 를 사용하는 B 라는 컬럼을 가진다고 가정하자.

A 테이블의 B 컬럼의 AUTO_INCREMENT 로 할당 된 가장 큰 값이 20 이라고 할 때 기대되는 다음 AUTO_INCREMENT 는 다음과 같다.

auto_increment_increment = 3

auto_increment_offset = 1

ð  22

auto_increment_increment = 3

auto_increment_offset = 2

ð  23

auto_increment_increment = 3

auto_increment_offset = 3

ð  24

결과적으로 auto_increment_offset = on 의 사용으로 AUTO_INCREMENT 를 PKEY 로 사용하는 경우에 대해

INSERT 시 DUPLICATE ENTRY 에러를 줄일 수 있다.

3.    wsrep_causal_reads

Default = off

Introduced = 1

Deprecated = 3.6

이 기능은 3.6 에서 deprecated 되었으며, wsrep_sync_wait 로 대체되었다.

wsrep_sync_wait

Introduced = 3.6

아래 값을 지정하면 해당하는 오퍼레이션을 수행하기 전에 동기화 된 읽기 view 를 보장한다.

1 – SELECT, SHOW, BEGIN / START TRANSACTION 을 포함한 READ Statement 를 검사

2 – UPDATE, DELETE statements 를 검사

3 – 1, 2 를 포함한다.

4 – INSERT, REPLACE statement 를 검사

4.    wsrep_certify_nonPK

Default = on

Introduced = 1

wsrep_certify_nonPK = on 인 경우 PK 없는 테이블에 대해 DML 을 허용한다.

(내부적으로 pk 를 생성하는 듯 함 – 확인할 것…)

wsrep_certify_nonPK = off 인 경우 PK 없는 테이블에 DML 을 사용하는 경우 다음과 같은 에러가 발생한다.

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

5.    wsrep_cluster_address

Cluster Membership 을 구성하는데 사용되는 System Variable 이다.

클러스터의 최초 노드를 구성하는 경우 gcomm:// 셋팅으로 startup 한다.

이 의미는 다른 클러스터 노드에 접속하지 않는다란 뜻이다.

이후 해당 vriable 을 클러스터를 구성하는 전 노드로 업데이트 한다.

A, B, C 라는 3개의 Address 가 있다고 하면 gcomm:// 셋팅으로 인스턴스를 시작한 이후,

gcomm://A,B,C 로 변경한다.

사실 위 과정 없이도 Joiner 노드의 올바른 wsrep_cluster_name 구성과 wsrep_cluster_address 구성이 있다면

startup 하는데 문제가 없다.

6.    wsrep_cluster_name

Default = my_wsrep_cluster

Introduced = 1

Logical 한 Cluster name 을 지정한다.

Cluster name 이 다르면 Cluster Membership 참가가 실패한다.

이를 테면 Donor node 에서는 다음과 같은 에러가 발생하다 Joiner node 프로세스가 죽는다.

[Note] WSREP: handshake failed, my group: 'mariatest', peer group: 'my_wsrep_cluster'

이 Variable 셋팅으로 하나의 장비에 여러 서비스 목적의 Cluster 를 구성하는 경우나,

wsrep_cluster_address 를 실수로 잘못 지정한 경우에 데이터를 보호할 수 있다.

7.    wsrep_convert_LOCK_to_trx

Default = off

Introduced = 1

LOCK / UNLOCK TABLES 를 BEGIN / COMMIT statements 로 바꿔준다.

암묵적으로 locking sessions 을 transactions 을 사용하도록 변환한다.

이 옵션이 off 인 상태에서는

NODE #1 – lock table test [ read | write ]

NODE #1 – Insert : Waiting

NODE #2 – No Waiting

NODE #3 – No Waiting

on 인 경우에는 같은 테스트에서 NODE #1 에서의 Insert : Waiting 이 없다.

8.    REFERENCE

A.     wsrep 파라미터
http://galeracluster.com/documentation-webpages/mysqlwsrepoptions.html

B.     mariadb 파라미터
https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/system-variables/server-system-variables/          

C.     wsrep_auto_increment_control
http://www.percona.com/blog/2011/01/12/conflict-avoidance-with-auto_increment_incremen-and-auto_increment_offset/

D.     16.1.2.2 Replication Master Options and Variables
http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html

E.     Bootstrapping the cluster
http://www.percona.com/doc/percona-xtradb-cluster/5.5/manual/bootstrap.html

개발은 어렵지는 않지만 시간이 오래 걸린다.

특히나 많은 검증을 위해 테스트를 해야되고 이에 대해 오류가 생기거나, 다양한 경우에 대한 대비가 필요하다..

아래 스크립트는 Replication 도중에 Sync가 안맞아 잘못된 데이터나 데이터가 없는 경우에 대해서 확인하여 추가하여 준다.

(오히려 Slave에서 데이가 있으며...Master에서 없는 경우는 해당 사항이 없다.....ㅠ)

로직은 간단하다.

1. file(ini파일로 지정) 를 읽어 해당 파일 내의 테이블 명을 읽는다.(ini 파일 작성 형ㅌㅐ : 테이블1 (줄바꿈) 테이블2 (줄바꿈) ...으로 구분한다.) 

2. 테이블 정보를 가지고  Master에 접속하여 해당 테이블에 대한 PK를 확인 및 테이블 컬럼들을 받아 온다.

3. 다시 해당 테이블 정보를 가지고 full scan한다.

4. Slave에 접속하여 마스터에 대한 테이블 정보( PK)를 가지고 데이터 여부를 확인한다.

5. 해당 데이터가 있으면 Update를 없으면 Insert를 한다.

스크립트 내용은 아래와 같다...스크립트 개발 시 참고하시기를 ....ㅠ

(해당 스크립트에 대한 검증없이 적용 하셨다가 사고가 나도 책임 못집니다........ㅠㅠ 검증의 검증을 하시기를...)

import pymysql
import os
import time

now = time.localtime()
todate = "%04d%02d%02d %02d:%02d:%02d" % (now.tm_year, now.tm_mon, now.tm_mday, now.tm_hour, now.tm_min, now.tm_sec)

print "======================================================="
print " Start Time is %s  " % todate
print "======================================================="

##### Use Korean Character #######
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
##################################
# Master Info
srv_Master = '마스터IP'

# DBName
srv_DBName = '디비명!!'

# slave1 / slave2 / ...
lst_srv = ['슬레이브IP1','슬레이브IP2',....]
#lst_table = [] -> file 에서 읽어서 사용
#File 형태는 확장자가 ini 이며, 내부에는 한 라인당 하나의 테이블명으로 지정
_filepath = 'INI파일이 저장되어 있는 위치(경로) /home/path...'

def fn_ConnectDB(srv, strDBName) :

    db = pymysql.connect(host=srv, port=3306, user='접속유저명', passwd='접속패스워드', db='접속디비명', autocommit=True, use_unicode=True, charset="utf8")

    dbCursor = db.cursor()

    return dbCursor


def fn_TruncateTable(tmpTable, strDBName) :
    for srv in lst_srv: # 2servers
        ##check Data in Slave DB
        curSlave = fn_ConnectDB(srv, srv_DBName)

        sqlTruncate = "TRUNCATE TABLE %s.%s" % (strDBName, tmpTable)

        print "Nothing Key in table : %s.%s" % (strDBName, tmpTable)
        print sqlTruncate

        #check curSlave.execute(sqlTruncate)
        curSlave.close()


def fn_GetTableInfo(curMaster, strDBName, tmpTable) :
    #PK확인
    sqlPK = "select COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA " \
            "   from INFORMATION_SCHEMA.COLUMNS " \
            "where " \
            "   TABLE_NAME='%s' " \
            "   and TABLE_SCHEMA='%s' " \
            "   and COLUMN_KEY = 'PRI'" % (tmpTable, strDBName)
    curMaster.execute(sqlPK)
    #pkResult = curMaster.fetchall()
    pkResult = curMaster.fetchall()

    #if pkResult == None :
    if curMaster.rowcount == 0 :
        print 'Nothing Key!!!'
        #truncate 진행 -> Key가 없을 경우 truncate 진행
        fn_TruncateTable(tmpTable, strDBName)

    # 아래처럼 group concat 하면 PK가 무조건 앞에 위치하게
    sqlColumns = "SELECT GROUP_CONCAT(COLUMN_NAME) " \
                 "FROM INFORMATION_SCHEMA.COLUMNS " \
                 "WHERE " \
                 "  TABLE_NAME = '%s' " \
                 "  AND TABLE_SCHEMA='%s' " % (tmpTable, strDBName)
    curMaster.execute(sqlColumns)
    # pkResult = curMaster.fetchall()
    colResult = curMaster.fetchall()

    return(pkResult, colResult)

def fn_WorkingData(tmpMasterData, tmpTable, tmpInfo) :
    for srv in lst_srv: # 2servers
        ##check Data in Slave DB
        curSlave = fn_ConnectDB(srv, srv_DBName)
        print "==================================================================================="
        for tmpData in tmpMasterData :
            tmpPK = ''
            #curMaster[0]은 무조건 PK값임  / tmpInfo[0] - pk / tmpInfo[1] - all columns
            if len(tmpInfo[0]) > 1 :
                cntPk = 0
                intcolPK = 0
                #for colPK in tmpInfo[0][cntPk][0] :
                for intcolPK in range(0,len(tmpInfo[0])) :
                    colPK = tmpInfo[0][intcolPK][0]
                    if cntPk <> (len(tmpInfo[0])-1) :
                        tmpPK += colPK + """= \"""" + str(tmpData[cntPk]) + """\" AND """
                    else :
                        tmpPK += colPK + """= \"""" + str(tmpData[cntPk]) + """\" """
                    cntPk += 1
                print "Server PK Info : %s" % tmpPK
            elif len(tmpInfo[0]) == 1 :
                tmpPK = str(tmpInfo[0][0][0]) + """= \"""" + str(tmpData[0]) + """\" """
            else :
                #PK 가 없을경우 무조건 insert로 취급
                tmpPK = " 1=0 "

            sqlCheckData = "SELECT 1 FROM %s.%s " \
                           "WHERE " \
                           "    %s " % (srv_DBName, tmpTable, tmpPK)
            curSlave.execute(sqlCheckData)

            tmpCheckCnt = curSlave.rowcount

            #tmpColName 은 Insert에서 사용하기 위함 ColName 은 Update에서 사용하기 위함
            tmpColName = tmpInfo[1][0]
            ColName = tmpColName[0].split(',')

            if tmpCheckCnt == 0 :
                # 데이터 생성을 위한 초기화 작업
                j = 0
                strData = ""
                for i in tmpData:
                    if j == 0:
                        strData = """(\"""" + str(i) + """\","""
                    elif j == (len(tmpData) - 1):
                        strData += """\"""" + str(i) + """\")"""
                    else:
                        strData += """\"""" + str(i) + """\","""
                    j += 1

                #값이 없으니 Insert 진행
                sqlInsertSlave = """INSERT INTO %s.%s (%s) VALUES %s""" % (srv_DBName, tmpTable, tmpColName[0], strData)

                print ("Insert Query : %s") % sqlInsertSlave

                #check 필요 curSlave.execute(sqlInsertSlave)

            else :
                #값이 있기 때문에 Update 진행
                strData = ""
                #ColName = tmpInfo[1].split(',')
                for i in range(0,len(tmpData)) :
                    if i == (len(tmpData)-1):
                        strData += str(ColName[i]) + """ = \"""" + str(tmpData[i]) +"""\""""
                    else:
                        strData += str(ColName[i]) + """ = \"""" + str(tmpData[i]) + """\","""

                sqlUpdateSlave = """UPDATE %s.%s SET %s WHERE %s""" % (srv_DBName, tmpTable, strData, tmpPK)

                print ("Update Query : %s") % sqlUpdateSlave

                #Check 필요 curSlave.execute(sqlUpdateSlave)
        print "==================================================================================="
def fn_main(curMaster, tmpTable) :

#    for tmpTable in lst_table : #60 tables
        #tmpInfo[0] = pk column, tmpInfo[1] = All Column(col1,col2...)

    tmpInfo = fn_GetTableInfo(curMaster, srv_DBName, tmpTable)

    int_Start = 0

    #for tCol in range(0, len(tmpInfo[1][0]))

    tmpAllCol = tmpInfo[1][0]
    #print tmpAllCol -> (u'CODEID,CGID,CG_CID,CODENAME,CODEMEMO,SORTS,USEYN,RDATE',) 이렇게 표현되어 tmpAllCol[0]으로 변환
    while True :
        #만건씩 잘라서 진행
        sqlMasterTable = "SELECT %s  " \
                         "FROM %s.%s " \
                         "limit %d, 10000" % (tmpAllCol[0], srv_DBName, tmpTable, int_Start)
        print ("Limit Query : %s" % sqlMasterTable)

        curMaster.execute(sqlMasterTable)
        
        int_Start += 10000
        tmpMasterData = curMaster.fetchall()

        fn_WorkingData(tmpMasterData, tmpTable, tmpInfo)

        if curMaster.rowcount < 10000 :
            total_count = int_Start+curMaster.rowcount
            print "Last data in Table %s (last select count %d/%d)" % (tmpTable, curMaster.rowcount, total_count)
            print "==================================================================================="
            break

def fn_read_log(_filename) :
    f = open(_filename, 'r')
    t_line = f.readlines()
    f.close()

    f_line = []
    #중간에 \n 에 대해서 삭제 후에 진행
    for i in t_line:
        f_line.append(i.strip())

    return f_line

if __name__ == "__main__":

    curMaster = fn_ConnectDB(srv_Master, srv_DBName)

    filenames = os.listdir(_filepath)
    for filename in filenames :
        full_filename = os.path.join(_filepath, filename)
        ext = os.path.splitext(full_filename)[-1]

        if ext == '.ini':
            print "Talbes File : " + full_filename
            lst_table = fn_read_log(full_filename)
            print "Table List : " + str(lst_table)

            for aTable in lst_table :
                print "Table Name : " + aTable

                if aTable <> '' :
                    fn_main(curMaster, aTable)

now = time.localtime()
finishtime = "%04d%02d%02d %02d:%02d:%02d" % (now.tm_year, now.tm_mon, now.tm_mday, now.tm_hour, now.tm_min, now.tm_sec)

print "======================================================="
print " Start Time is %s  " % todate
print " End Time is %s " % finishtime

출처 : Real Maria DB [위키북스]