블로그 이미지
평범하게 살고 싶은 월급쟁이 기술적인 토론 환영합니다.같이 이야기 하고 싶으시면 부담 말고 연락주세요:이메일-bwcho75골뱅이지메일 닷컴. 조대협


Archive»


 
 

구글 클라우드 MySQL서비스의 흥미로운 가격 정책

조대협 (http://bcho.tistory.com)


구글 클라우드의 MySQL 서비스인 CloudSQL을 보다보니, 신기한 가격 정책이 있어서 정리해놓고자 한다.

1세대와 2세대의 가격 정책이 다른데, 1세대의 가격 정책이 재미있는점이 있다.


기본 가격 정책


1,2세대 모두 기본 적인 가격 정책은 다음과 같다


저장량 + 인스턴스 기동 비용 + 네트워크 비용

  • 저장량은 말 그대로 저장된 데이타의 양에 따라 과금이 된다
  • 네트워크 비용은 outbound로 나가는 트래픽만 과금이 되는데, 이것도 같은 리전 안의 구글 클라우드에서 호출하는 경우에는 과금이 되지 않는다. 과금이 되는 경우는 구글 클라우드를 쓰더라도 다른 대륙의 인스턴스가 호출을 하거나 또는 다른 클라우드 서비스에서 호출을 하는 경우에만 과금이 되기 때문에, 일반적인 상황에서는 거의 네트워크 트래픽 비용이 과금이 되지 않는다.
  • 인스턴스 기동 비용은 인스턴스가 떠있는 동안데 받는 비용인데 이 부분이 흥미롭다.
인스턴스 가동 비용

1세대 CloudSQL 경우 Package plan 과 Pay per use plan 두가지가 있다.
Package Plan은 일단위 과금이며, 인스턴스가 기동이 되어 있는 일 기준으로 과금이 된다. 이건 모 일반적인 것이고
흥미로운 가격 정책이라고 하는것은 Pay per use plan이다. Pay per use plan은 인스턴스가 떠 있는 시간동안 분당 과금을 하는 것이다. 일반적인 분당 과금 처럼 보일 수 있지만 재미 있는 것은 Cloud SQL 인스턴스 생성시 activation policy (활성화 정책) 이라는 것을 설정할 수 있는데, 이 활성화 정책을 ALWAYS(항상) 으로 해놓으면, 인스턴스를 수동으로 내리지 않는 이상은 항상 떠 있는 케이스이다. 여기까지가 일반적인것이고
활성화 정책중 ON DEMAND(요청시)로 해놓으면, MySQL이 15분 동안 아무 Request가 없으면 해당 MySQL 인스턴스는 자동으로 비활성화 되고 인스턴스 비용이 과금되지 않는다. 비활성화 상태에서 요청이 들어오면 자동으로 활성화 상태가 된다. ON DEMAND는 활성화 상태에서 사용량을 분당으로 과금한다.

아래 그림은 CloudSQL 인스턴스를 생성할때, ON DEMAND 활성화 정책 + 사용량에 따른 청구 방식으로 설정하는 화면이다.




쉽게 설명하면, 서버에 요청이 없으면 자동으로 대기 상태로 들어가면서 데이타 저장 비용만 과금이 되고, 인스턴스 비용은 과금이 되지 않는다는 이야기이다.

이는 항상 서버가 돌아도 되지 않는 일 배치나 또는 개발 환경등에 유용하게 사용될 수 있다. 
물론 이렇게 인스턴스가 될때만 과금하게 하는 것은 수동으로 데이타를 백업 받고 인스턴스를 내렸다가 사용할때 인스턴스를 새로 올리고 데이타를 부어도 되고 또는 수동으로 일일이 인스턴스를 껐다 켰다 해도 유사한 효과를 볼 수 는 있지만, AWS의 경우 시간당 과금이기 때문에, 개발 환경처럼 수분을 쓰고 마는 환경에서는 분당 과금인 CloudSQL에 비해서 금액 절약효과를 보기가 어렵고, 무엇보다 귀찮다.

아래는 20GB 용량을 하루에 4시간 정도 D32 인스턴스로(32GB 메모리 머신) , 300만 IO가 발생하는 일배치를 돌리는 시나리오에서 추가 IO가 없다고 가정할때 가격 시뮬레이션 한 케이스인데 Package plan을 이용할 경우 약 한달에 1116$, Pay per use plan을 사용할 경우 약 338$ 로 약 3.4배 정도의 가격 차이가 있는 것을 확인할 수 있다. 
(참고, Package Plan의 D32 인스턴스는 300만 IO까지는 무료이며, Pay Per Plan의 경우 무조건 100만건당 0.1$가 과금된다. 아래 계산 결과는 Pay per use는 모든 IO 가 과금이 되기 때문에 # of IO를 300만으로 입력하였고, Package Plan은 무료 IO를 넘는 부분에 대해서만 입력하기 때문에, 현재 계산에서 300만 IO는 D32 인스턴스 크기에서는 무료이기 때문에 별도 입력하지 않았다.)




1세대의  Pay per use plan을 사용하면 딱 트렌젝션을 돌릴때만 분당 과금을 할 수 있기 때문에 가격이 저렴해진다.
단 주의 할점은 오랜 시간 서버를 돌리는 경우에는 Pay per use plan 보다는 당연히 Package plan이 저렴하기 때문에 일정 시간 이상 인스턴스를 사용하는 경우는 Pay per use 보다는 package plan을 사용하기 바란다.

가격 정책에 대한 자세한 내용은 https://cloud.google.com/sql/pricing 에 있다. 
어떤 정책이 유리한지는 가격 시뮬레이션을 해보면 되는데 시뮬레이션용 계산기는 https://cloud.google.com/products/calculator/ 를 사용하기 바란다.



구글 CloudSQL(MySQL) 접속하기

조대협 (http://bcho.tistory.com)


개요 


구글 클라우드에서는 MySQL의 매니지드 서비스 형태로 CloudSQL 서비스를 제공한다. 

이 글에서는 CloudSQL을 서버에서 접근하는 방법과, 일반적인 MySQL 클라이언트로 접근하는 방법에 대해서 설명하고자 한다.


몇가지 배경


CloudSQL은 매니지드 MySQL서비스이다. 아마존에 RDS서비스와 같다고 보면 되는데 현재는 1세대를 서비스하고 있고, 곧 2세대가 서비스 예정이다.

1세대는 500GB까지의 용량까지 지원하고 있지만 2세대는 10테라까지 지원을 한다.

현재 지원되는 MySQL버전은 5.5와 5.6 지원하고, 내부 엔진으로는  InnoDB만을 제공한다.


2 세대에 기대되는 기능으로는 On Prem(호스팅 센터에 있는) MySQL과 복제 구성이 가능하다. On Prem에 있는 서버를 마스터로 할 수 도 있고, 반대로 마스터를 CloudSQL로 사용하고 읽기 노드를 On Prem에 구성하는 등 다양한 하이브리드 구성이 가능하다. (기대되는 부분)


자동 백업, 확장을 위한 읽기 전용 노드 (Read replica)등 필수적인 기능을 제공하고 있다.


연결 방식


CloudSQL은 RDS와는 다르게 private ip (10.x.xx)를 아직 지원하지 않고 public ip만을 지원한다. 그래서 서버에 접근하려면 이 public ip를 통해서 접근하면 된다. 보안을 위한 접근 통제 방법으로 특정 IP 주소에서 들어오는 트래픽만을 받아드리도록 설정이 가능하다.


또는 PaaS서비스인 구글 앱앤진을 사용하는 경우에는 구글 앱앤진의 인스턴스나 그룹 단위로 접근 통제가 가능하다.

다음 그림은 콘솔상에서 접근이 가능한 IP 주소를 지정하는 화면이다.



MySQL 클라이언트를 이용하여 접속을 할때 mysqlclient를 이용하여 직접 ip등을 입력하고 접속을해도 되지만 이 경우에는 CloudSQL에서 Ip를 허용해줘야 하기 때문에 개발이나 기타 운영 환경등에서 IP 주소가 바뀌면 그때 마다 설정을 해줘야 하기 때문에 불편할 수 있다.

이를 조금 더 편하게 하려면 mysqlclient를 사용하지 않고 구글에서 제공하는 "gcloud" 라는 도구를 이용하면, 별도로 접속 IP를 열지 않더라도 접속이 가능하다.

접속 방법은 먼저 gcloud  명령어를 인스톨 한 후에 


$ gcloud config set project [클라우드 프로젝트 이름]

$ gcloud beta sql connect [CloudSQL 인스턴스이름] —user=root

(여기서 --user=root 에서 root 사용자는 MySQL 인스턴스내의 사용자이다)


으로 접속하면 MySQL 클라이언트와 동일한 툴로 접속이 된다.


gcloud 툴킷을 이용한 자세한 접속 방법은 https://cloud.google.com/sql/docs/mysql-client#connect-ssl 를 참고하기 바란다.


참고

  • Toad, MySQL Workbench 등에서 안전하게 연결하는 방법 https://cloud.google.com/sql/docs/admin-tools#squirrel


MySQL 클러스터링을 위한 Galera Cluster

아키텍쳐 | 2015.11.18 23:52 | Posted by 조대협

MySQL Galera Replication


조대협 (http://bcho.tistory.com)


RDBMS 오픈소스 중에서 단연 가장 많이 사용되는 것은 MySQL인데, 근래에 웹 스케일이 커지면서, 단일 인스턴스로만 서비스가 불가능한 용량까지 가게 되서, 이 MySQL에 대한 클러스터링 스케일링에 대한 이슈가 많아졌다. 이에 Tungsten, MySQL Replication, NDB, Galera 등 다양한 클러스터링 방법이 있는데, 그중에서 갈레라 클러스터링 (Galera Clustering)에 대해서 간단하게 정리하고자 한다.


MySQL Replication


갈레라 클러스터링을 이해하기에 앞서서 먼저 가장 널리(그리고 쉽게) 사용되는 MySQL Replication 방식에 대해서 알아보자. MySQL Replication 방식은 Master/Slave 방식의 구성이 일반적이며, 이 구성의 경우 특정 노드는 쓰기를 담당하고 나머지 노드는 읽기를 담당하는 형태로 구성이 된다.

통상적으로 데이타 베이스 트랜젝션의 60~80%가 읽기 트렌젝션이기 때문에, 이러한 구조를 사용하더라도 충분히 성능의 향상을 기대할 수 있다.


다음 그림은 MySQL Replication 의 간단한 구조도 이다.

 




먼저 좌측의 Master Node에 쓰기 트렌젝션이 수행되면 Master node는 데이타를 저장하고, 트렌젝션에 대한 로그를 내부적으로 BIN LOG라는 파일에 저장한다. (시간 순서대로 수행한 업데이트 트렌젝션이 기록되어 있다.)


Slave Node에서는 이 BIN LOG를 복사해온다. 이 복사 작업을 IO Thread라는 스레드가 수행하는데, 이렇게 읽어온 내용은 Replay Log라는 파일에 기록이 된다. 이렇게 기록된 내용은 SQL Thread라는 스레드가 읽어서, 하나씩 수행을 해서  MySQL 데이타 파일에 기록을 한다.


쉽게 설명하면, insert 쿼리를 master node에서 실행했으면 그 쿼리가 master node의 bin log에 기록이 되고, 이 내용은 slave node에 복사가 된후에, slave node에서 같은 쿼리가 수행이 되서 복제가 반영되는 방식이다.


방식이 단순해서 신뢰도가 높은 반면, 단점으로는

  • 읽기와 쓰기 노드를 분리해야 하며,
  • 데이타 복제가 동기 방식이 아닌 비동기 방식으로 적용된다. 바꿔서 말하면, master node에 적용한 데이타 변경사항이 slave에 반영될때까지 일정 시간이 걸린다는 것으로, master와 slave node간의 순간적인 데이타 불일치성이 발생할 수 있다는 것이다.


Galera cluster


Galera cluster는 http://galeracluster.com/ 에서 제공되는 오픈소스로, 동기방식의 복제 구조를 사용하고 있다.

간단하게 구조를 살펴보자 아래 그림을 보면 




각각의 노드가 있을때, 아무 노드에나 쓰기나 업데이트가 발생하면, 모든 노드에 데이타를 복사를 완료하고 나서, 업데이트 내용이 파일에 저장된다. 아키텍쳐상의 구조를 보면, 위의 그림과 같이 각 MySQL 노드에는 WSREP 라는 모듈이 있다. 이 모듈은 데이타베이스에 복제를 위한 범용 모듈로 여기에 마치 드라이버처럼 Galera replication module을 연결해주면 데이타 변경이 있을때 마다, 이 Garela replication module이 다른 mysql node로 데이타를 복제한다.


약간 더 구체적인 구조를 살펴보면 노드간의 데이타 복제는 다음과 같은 흐름을 따르게 된다. 





노드에 트랜젝션이 발생하고 COMMIT이 실행이되면, 디스크에 내용을 쓰기 전에 다른 노드로 복제를 요청하고 다른 노드에 복제 요청이 접수되었을때, 해당 노드의 디스크에 실제로 데이타를 쓰게 된다.


이러한 특성으로, 전체 노드에 데이타가 항상 일관성있게 저장되고, 모든 노드가 마스터 노드로 작동을 하며, 특정 노드가 장애가 나더라도 서비스에 크게 문제가 없다. 

(MySQL Replication의 경우 마스터 노드가 장애가 나면 슬레이브 노드중 하나를 마스터로 승격을 해야하는 등 다소 운영 프로세스가 갈레라에 비해서는 복잡하다.)


상당히 좋아 보이는 구조이기는 한데, 반대로 가지는 단점도 만만하지 않다.


성능

먼저 성능적인 부분에서, 데이타를 디스크에 저장하기 전에, 다른 모든 노드에 데이타 복제 요청을 해야 하기 때문에, 비동기 방식의 MySQL Replication에 비해서, 쓰기 성능이 떨어지는 것으로 보인다.


장애 전파

이렇게 다른 노드에 복제 요청을 하는 클러스터 구조의 경우, 장애를 다른 노드로 전파 시킬 가능성이 높은데, 예전에 대표적인 웹 애플리케이션 서버인 웹로직의 경우 유사한 세션 클러스터링 구조를 사용했다. 

이 경우 복제를 요청했을때 복제 요청을 받은 노드가 장애 상황 특히 느려지거나 일시적으로 멈췄으때, 복제를 요청한 노드가 응답을 받지 못하고 대기하게 되고, 이 대기한 노드에 다른 노드가 복제를 또 요청하면, 같은 이유로 복제가 지연 되면서 클러스터를 타고 장애가 전파되는 현상을 야기하게 된다.

그래서 갈레라 클러스터의 경우 LOCK문제가 생기거나 슬로우 쿼리들이 많이 발생할때 장애를 전파시킬 수 있는 잠재적인 문제를 가지고 있다.


스케일링의 한계

갈레라가 모든 노드에 데이타를 복제하고 트렌젝션을 끝내는 만큼, 전체적인 노드수가 많아지게 되면, 복제를 하는데 그만큼 시간이 많이 걸림에 따라, 하나의 클러스터에서 유지할 수 있는 노드의 수가 한계가 있어져서, 횡적 스케일링의 한계가 올 수 있다. 


이런 단점에도 불구하고, 모든 노드에 읽기 쓰기가 가능한 멀티 마스터 구조와 모든 노드의 데이타를 일관적으로 유지 시켜준다는 장점과 쉬운 설정 방법으로 인하여 MySQL 클러스터를 구성한다면 한번쯤 검토해봐야 하는 솔루션이 아닌가 한다.


(아쉽게도 국내 사례는 그다지 많지 않은듯...)


몇가지 참고 사항

  • 갈레라 클러스터는 서로 다른 MySQL 버전간에도 클러스터로 묶을 수 있다.
  • 갈레라 클러스터에서 노드가 떨어졌다가 붙으면 일정 부분은 GTID (Global Transaction ID)를 이용하여, 데이타가 복제 되지 않은 델타 부분만 복제가 가능하지만, 시차가 오래되 버리면 풀 백업본을 가져다 엎어야 한다. (풀백업은 복구는 시간이 많이 걸림)




'아키텍쳐' 카테고리의 다른 글

MySQL 클러스터링을 위한 Galera Cluster  (3) 2015.11.18
요구 사항 정의 기법  (0) 2013.11.13
소프트웨어 개발팀의 구조  (0) 2013.11.01
Technical Debt  (1) 2013.10.30
License Key Management  (0) 2013.08.01
암호화 알고리즘 속도 비교 (대칭키)  (0) 2013.07.17

빠르게 훝어보는 node.js

#8 - MySQL 연동

조대협 (http://bcho.tistory.com)


NoSQL이 근래에 유행이기는 하지만, 데이터간의 관계를 표현하고, 트렌젝션에 일관성을 보장하는 RDBMS는 아직까지는 서버쪽에서는 필수적이다. node.js는 대표적인 오픈소스 RDBMS MySQL은 지원한다. 몇가지 MySQL연동 모듈이 있기는 하지만, 여기서는 가장 많이 사용되는 node-mysql 모듈에 대해서 소개하고자 한다.

node-mysql은 오픈소스로 https://github.com/felixge/node-mysql 에 코드가 공개되어 있다.

mysql 모듈을 사용하려면 npm install mysql 명령을 이용하여, node-mysql 모듈을 설치하자.

mysql에 대한 설치와 사용법에 대해서는 워낙 문서들이 많으니 생략하기로 한다.

mysql 연결

간단한 예제 작성을 위해서 express로 프로젝트를 생성한후에,mysql을 어떻게 연결하는지 살펴보도록 하자.

var express = require('express');

var routes = require('./routes');

var user = require('./routes/user');

var http = require('http');

var path = require('path');

먼저 아래와 같이 mysql 모듈을 로딩한다.

var mysql = require('mysql');

다음 로딩된 모듈로부터 Connection 객체를 생성한다. 이 때 실제적인 Connection 연결은 이루어지지 않는다.

var connection = mysql.createConnection({

    host    :'localhost',

    port : 3306,

    user : 'terry',

    password : 'asdf1234',

    database:'terry'

});

이렇게 명시적으로 connect 메서드를 이용해서 connection을 연결하거나 또는 첫번째 Query가 실행될 때, 위의 connection 객체의 정보를 이용해서 connection이 생성된다.

connection.connect(function(err) {

    if (err) {

        console.error('mysql connection error');

        console.error(err);

        throw err;

    }

});

 

다음으로, express 위한 환경을 변수들을 설정한후

var app = express();

// all environments

app.set('port', process.env.PORT || 3000);

app.set('views', path.join(__dirname, 'views'));

app.set('view engine', 'ejs');

app.use(express.favicon());

app.use(express.logger('dev'));

app.use(express.json());

app.use(express.urlencoded());

app.use(express.methodOverride());

app.use(express.cookieParser('your secret here'));

app.use(express.session());

app.use(app.router);

app.use(express.static(path.join(__dirname, 'public')));

 

Query 수행

먼저 insert 기능을 구현해보자. 먼저 mysql에 접속하여, 다음 SQL문을 이용해서 userid,name,address 컬럼을 갖는 테이블을 생성하자

CREATE  TABLE `terry`.`users` (

  `userid` VARCHAR(45) NOT NULL ,

  `name` VARCHAR(45) NULL ,

  `address` VARCHAR(45) NULL ,

  PRIMARY KEY (`userid`) );

그리고 Table insert할 값을 받을 HTML 파일을 만들자



다음은 위의 화면을 표현하는 index.html

<html>

<head>

    <title></title>

</head>

<body>

<form name="user" method="post" action="/users">

    <b>Add Users</b><p>

    id <input type="text" name="userid"/>

    <br>

    name <input type="text" name="name"/>

    <br>

    address <input type="text" name="address"/>

    <br>

    <button type="submit" >Submit</button>

</form>

</body>

</html>

HTTP/POST로 값을 받아서 index.html에서 입력된 form field로 입력하는 코드를 구현하면 다음과 같다. 간단하게 connection.query에서 sql 문장을 수행하고, 결과를 callback으로 받으면 된다. 인자가 있을 경우에는 {:} pair로 만들어서 query의 두번째 인자로 넘기면 된다. 아래는 insert into users (userid,name,address) values(폼에서 읽어온 userid,폼에서 읽어온 name,폼에서 읽어온 name)으로 SQL을 생성해서 실행해주는 문장이다. 당연히 비동기 호출이기 때문에, response callback 함수에서 res.send(200,xx)으로 보냈다.

// insert

app.post('/users',function(req,res){

    var user = {'userid':req.body.userid,

                'name':req.body.name,

                'address':req.body.address};

    var query = connection.query('insert into users set ?',user,function(err,result){

        if (err) {

            console.error(err);

            throw err;

        }

        console.log(query);

        res.send(200,'success');

    });

});

다음으로 select를 구현해보면 아래와 같이 구현이 된다. 리턴값은 callback의 두번째 인자로 전달되는데, res.json을 이용하면 간단하게 json형태로 변환하여 리턴할 수 있다.

//select all

app.get('/users', function(req,res){

    var query = connection.query('select * from users',function(err,rows){

        console.log(rows);

        res.json(rows);

    });

    console.log(query);

});

위에서 구현한 select를 실행해보면 다음과 같은 결과를 얻을 수 있다.



만약에 where문을 사용하고자 한다면string을 그냥 생성하는 것보다 mysql.escape라는 메서드를 사용하는 것이 좋다. boolean이나 array와 같은 데이터형을 SQL문장으로 적절하게 변환해줄뿐만 아니라 mysql.escape SQL injection attack을 방어해준다.

var query = connection.query('select * from users where userid='+mysql.escape(req.params.userid),function(err,rows){

        console.log(rows);

자아 이제까지 간단하게 node-mysql 모듈을 이용해서 기본적인 SQL 문장을 수행하는 것에 대해서 알아보았다.

Transaction 처리

node-mysql 모듈을 트렌젝션 처리도 지원해주는데, 아래 예제는 users 테이블과 log 테이블에 각각의 데이터를 하나의 트렌젝션으로 묶어서 수행해주는 예제이다.

// insert with transaction

app.post('/userstx',function(req,res){

    var user = {'userid':req.body.userid,

        'name':req.body.name,

        'address':req.body.address};

 

    connection.beginTransaction(function(err) {

        if (err) {

            throw err;

        }

        connection.query('insert into users set ?', user, function (err, result) {

            if (err) {

                console.error(err);

                connection.rollback(function () {

                    console.error('rollback error');

                    throw err;

                });

            }// if err

            console.log('insert transaction log');

            var log = {'userid': req.body.userid};

            connection.query('insert into log set ?', log, function (err, result) {

                 if (err) {

                     console.error(err);

                     connection.rollback(function () {

                        console.error('rollback error');

                         throw err;

                      });

                  }// if err

                 connection.commit(function (err) {

                    if (err) {

                        console.error(err);

                        connection.rollback(function () {

                               console.error('rollback error');

                               throw err;

                            });

                    }// if err

                    res.send(200, 'success');

 

                 });// commit

                });// insert into log

        });// inset into users

    }); // begin trnsaction

});

먼저 connection.begintransaction을 호출한 후에, transaction이 시작되면 쿼리를 수행하기 위해서, begintrasaction안쪽 부분에서 sql 문장을 수행한다.

insert into users SQL문장을 먼저 수행한후

다음 insert into log SQL 문장을 수행하기 위해서 insert into users Query call back에서 insert into log SQL 문장을 수행하였다.

그 다음으로 transaction commit 처리를 insert into logs callback함수에서 connection.commit을 이용해서 수행하였다. 코드를 보면 알겠지만, transaction begin,insert 두개, commit을 순차 수행하기 위해서 callback을 중첩 사용하였다. 코드도 복잡하고 {} 도 많다. 이렇게 node.js async callback 구조로 인하여, 코드의 복잡도가 증가하는 것을 보통 callback hell(지옥)이라고 하는데, 이를 해결하기 위한 모듈로 Async 와 같은 모듈들이 있다. callback hell에 대해서는 차후에 별도의 글로 따로 소개하도록 하겠다.

Connetion Pooling

node-mysql도 기본적으로 connection pooling을 제공한다. Connection pooling을 사용하는 법은 매우 쉽다.앞의 예제의 경우에는 전체가 하나의 connection만 사용하기 때문에 동시에 많은 요청이 들어올 경우 제대로 처리가 되지 않는다.

Connection을 만드는 부분을 살펴보면 DB Connection을 만드는 방법과 크게 다르지 않다.여기에 몇가지 인자를 추가 지정할 수 있는데, connectionLimit pool에 담을 수 있는 최대의 connection 수이다. 여기서는 20개로 설정하였다. (디폴트는 10)

다음으로 사용한 옵션이waitForConnection이라는 옵션인데, true로 되어 있으면 Pool내에 가용한 Connection이 없을 경우에 Connection이 반납되기를 기다리고, false로 되어 있으면, Connection이 없을 경우 바로 에러를 리턴한다.

var pool = mysql.createPool({

    host    :'localhost',

    port : 3306,

    user : 'terry',

    password : 'asdf1234',

    database:'terry',

    connectionLimit:20,

    waitForConnections:false

});

 

실제로 테스트를 해보면 재미있는 점이, Pool이 생성되도, 실제로 connection이 만들어지지 않는다. Pool.getConnection시에, 유휴 connection이 없을 경우에만 connectionLimit 범위안에서 connection을 생성한다.

아쉬운 점이 자바 엔터프라이즈에서 사용하는 Connection Pool의 경우 min/max 값을 정해서 자동으로 connection pool의 사이즈를 조정한다거나, Connection health 상태를 주기적으로 검사해서 문제가 있는 것은 끊는다거나 등의 기능이 되는데, 거의 기능이 없다. (장애 대응이 쉽지 않을 듯)

Pool을 사용해서 Query를 해보자. 앞에서 구현한 select * from users pool로 구현해주면 query 전에 pool.getConnection으로 싸주고, getConnection callback 함수안에서 두 번째 인자로 넘어온 connection 객체를 이용해서 쿼리를 수행하면 된다.

//select all

app.get('/users', function(req,res){

    pool.getConnection(function(err,connection){

        var query = connection.query('select * from users', function (err, rows) {

            if(err){

                connection.release();

                throw err;

            }

            console.log(rows);

            res.json(rows);

            connection.release();

        });

        console.log(query);

    });

});

여기서 주의할점은 connection을 사용한 후에, 반드시 connection.release 를 이용해서 pool connection을 반납해야 한다.특히 에러가 났을때도 connection을 반납해야 하는 것을 잊지 말아야 한다.이렇게 connection을 반납하지 않아서 유휴 conneciton pool에 남아있지 않는 현상을 connection leak이라고 하는데, connection pool을 사용할 때 상당히 자주 발생하는 장애이기 때문에 반드시 꼼꼼하게 처리하기 바란다. (나중에 leak이 발생하면 찾기도 어렵다.)

PoolCluster

단일 DB를 연결하는 Connection Pool이외에도 여러 개의 데이터 베이스를 연결하는 Conneciton Pool의 개념인 poolCluster를 제공한다. poolCluster는 동시에 여러 개의 ConnectionPool을 포함하는 일종의 집합이다. 여러 개의 mysql node를 묶어서 클러스터 구성을 할 수 있는 환경이 없어서, https://github.com/felixge/node-mysql 에 나와있는 예제를 인용하였다.

// create

var poolCluster = mysql.createPoolCluster();

 

poolCluster.add(config); // anonymous group

poolCluster.add('MASTER', masterConfig);

poolCluster.add('SLAVE1', slave1Config);

poolCluster.add('SLAVE2', slave2Config);

 

// Target Group : ALL(anonymous, MASTER, SLAVE1-2), Selector : round-robin(default)

poolCluster.getConnection(function (err, connection) {});

 

// Target Group : MASTER, Selector : round-robin

poolCluster.getConnection('MASTER', function (err, connection) {});

 

// Target Group : SLAVE1-2, Selector : order

// If can't connect to SLAVE1, return SLAVE2. (remove SLAVE1 in the cluster)

poolCluster.on('remove', function (nodeId) {

  console.log('REMOVED NODE : ' + nodeId); // nodeId = SLAVE1

});

 

poolCluster.getConnection('SLAVE*', 'ORDER', function (err, connection) {});

 

// of namespace : of(pattern, selector)

poolCluster.of('*').getConnection(function (err, connection) {});

 

var pool = poolCluster.of('SLAVE*', 'RANDOM');

pool.getConnection(function (err, connection) {});

pool.getConnection(function (err, connection) {});

 

// destroy

poolCluster.end();

이 코드는 “master- 2개의 slave 복제 구조로 되어 있는 mysql 에 각 DB 3개의 connection pool을 연결하여 pool cluster로 묶은 예제이다. poolCluster에서 connection 을 가지고 오는 것은 poolCluster.getConnection(“풀이름”) 을 사용하면 되는데, 위의 SLAVE*와 같이 ‘*’를 사용할 수 있다이 경우에는 어떤 정책으로 Pool을 가지고 올지를 정해야 하는데, 3 가지 정책을 지원한다.

Ÿ   RR : Connection을 해당 이름의 풀들에서 번갈아 가면서 한번씩 리턴한다.

Ÿ   ORDER : Connection을 무조건 첫번째 풀에서부터 리턴하는데, 장애가 났을 경우에만 다음 풀의 Connection을 순차적으로 리턴한다.  이 경우에는 HA 구성등에 유리하게 사용할 수 있다.

Ÿ   RANDOM : 순서 없이 무작위로 아무 풀에서나 connection을 리턴한다.

이 외에도 살펴보면, Store Procedure 수행이나, Streaming, Pipe와 같은 기능들도 제공한다. 그러나 아직까지 엔터프라이즈 자바에 비해서는 분산 트렌젝션 관리 능력이나 배치 처리등은 부족한 것이 사실이고, 오픈되어 있는 모듈도 아직 성숙도가 기존의 JDBC/MySQL 모듈에 비해서 높지 않다. 예를 들어서, connection이 끊어지지 않게 주기적으로 Keep alive query를 보내는 기능등. 일반적인 B2C 애플리케이션에서는 충분히 사용가능 하지만, 고가용을 요구하거나 데이타 분실이나 은행과 같은 높은 수준의 트렌젝션 보장이 필요한 곳에는 적절하지 않다. (물론 그런곳에는 mysql을 사용하지도 않겠지만)

RDBMS 프로그래밍시에는 해당 RDBMS에 대한 드라이버(모듈을) import해야 한다. sqlite의 경우는 이미 들어가 있다. 아래는 가장 기본적인 코드 이다.

import sqlite3


conn = sqlite3.connect("datafile")

cursor = conn.cursor()

cursor.execute("drop table test")

cursor.execute("create table test (name text,count integer)")

cursor.execute("insert into test(name,count) values('Terry',1)")

cursor.execute("insert into test(name,count) values('Cath',2)")

conn.commit()

result = cursor.execute("select * from test")

while True:

    row = result.fetchone()

    if row == None:

        break

    print row[0],row[1]

conn.close()

데이타 베이스 프로그래밍 순서

  1. 먼저 import를 통해서, 해당 데이타베이스를 접근하기 위한 모듈을 import한다.
  2. 해당 모듈.connect를 이용하여 데이타 베이스 connection을 연결한다. dbms에 따라서 connection string이 다르다. sqlite의 경우 파일 기반이기 때문에, 파일경로만 지정하면 되고, mysql의 경우에는 host,userid,userpasswd,dbms 식으로 기술해주면 된다.
  3. 다음은 cursor를 connection으로 부터 생성한다.
  4. cursor.execute를 통해서 query를 실행한다.
  5. select의 경우에는 result를 받은후에
    • - fetchall()의 경우 결과를 모두 리턴
    • - fetchone()의 경우 하나의 row를 리턴
    • - fetchmany(num rows)의 경우 rows의 숫자 만큼 리턴을 한다.
  6. conn.commit을 이용하여 transaction을 commit한다.
  7. 마지막으로 사용한 connection을 close한다.
mysql 데이타베이스도 다르지 않다. (정말 쉽다.)

다음은 약간 더 진보된 코드로, 데이타베이스에 대한 에러 핸들링을 포함하고 있다.

import sqlite3

try:
    conn = sqlite3.connect("datafile")
    cursor = conn.cursor()
    cursor.execute("drop table test")
    cursor.execute("create table test (name text,count integer)")
    cursor.execute("insert into test(name,count) values('Terry',1)")
    cursor.execute("insert into test(name,count) values('Cath',2)")
    conn.commit()
    result = cursor.execute("select * from test")
    while True:
        row = result.fetchone()
        if row == None:
            break
        print row[0],row[1]
except sqlite3.Error, e:
    if conn:
        conn.rollback
finally:
    if conn:
        conn.close()

기본적으로 sqlite 라이브러리가 포함되어 있는게 마음에 든다. 간단한 tutorial이나, 간단한 dbms 프로그래밍의 경우 별도의 라이브러리 설치가 필요없다.




AWS의 Zone은 같은 지역에 있는 물리적으로 다른 데이타 센터의 개념을 이야기 함.

RDS의 Multi Zone replication은 한 데이타 센터가 고장 나더라도 다른 데이타 센터에서 서비스가 가능한 구조.

기본적으로  Active-Stand by 형태로 복제하다가, 장애가 나면 stand by 서버로 fail over하는 구성


중요한 것중 하나는 MySQL RDS의 경우 자동 Back 시, 시스템이 일시적으로 멈추는 현상이 보이는데, Multi AZ deploy의 경우, back up시에, 자동 fail over하여, 멈추는 현상 없이 서비스가 가능함


http://aws.amazon.com/ko/about-aws/whats-new/2010/05/18/announcing-multi-az-deployments-for-amazon-rds/

Announcing Multi-AZ Deployments for Amazon RDS

We are excited to announce Multi-Availability Zone (Multi-AZ) deployments for Amazon Relational Database Service (Amazon RDS). This new deployment option provides enhanced availability and data durability by automatically replicating database updates between multiple Availability Zones. Availability Zones are physically separate locations with independent infrastructure engineered to be insulated from failure in other Availability Zones. When you create or modify your DB Instance to run as a Multi-AZ deployment, Amazon RDS will automatically provision and maintain a synchronous “standby” replica in a different Availability Zone. In the event of planned database maintenance or unplanned service disruption, Amazon RDS will automatically failover to the up-to-date standby so that database operations can resume quickly without administrative intervention.

The increased availability and fault tolerance offered by Multi-AZ deployments are well suited to critical production environments. To learn more, visit the Amazon RDS product page.


MySQL HA over AWS 옵션

클라우드 컴퓨팅 & NoSQL | 2012.10.29 22:39 | Posted by 조대협

메모를 안해놓으면 또 까먹기 때문에, 정리 차원에서 몇가지 정리


1. MySQL HA over AWS

먼저 MySQL을 아마존 위에서 Zone간 Fail Over를 위해서 몇가지를 고민했다.


1) MySQL Cluster

MySQL의 클러스터링 버전으로, Zone간 Fail Over가 이론적으로 가능하다. 제품 자체도 기존 MySQL과 상당히 차별화 되어 있다.그러나 가격이 상당히 비싼 편이고, 아직은 AWS위에 deployment된 reference가 없기 때문에 상당한 risk를 둬야 한다.


2) Garela, Tungsten

오픈소스로 Replication을 보장하지만, 국내 Support가 없기 때문에, Bug Fix가 어렵다. 그래서 Pass


3) HAProxy

상당히 재미있는 개념인데, Proxy로 어떻게 DB Replication을 하느냐 하는 의문이 들었는데, 원리 자체는 쉽다. 양쪽 Zone에 MySQL을 배치한후, 그 앞에 HAProxy를 넣어서, SQL 부하를 양쪽에 똑같이 분산 시킨다(복제) 즉. insert SQL을 치면 양쪽에 모두 보내서 복제 하는 방식이다. Garela가 이런 방식의 구현 방식을 취한다고 한다.

그러나, 이 아키텍쳐의 경우 Application단에서, write/read를 명시적으로 나눠줘야 하고, error 핸들링이 어려우며 분산 트렌젝션 처리등이 어려운 제약 사항을 가지고 있다.


4) RDS
만사 제일 편한 옵션이기는 한데, 다양한 replication architecture 사용이 어렵다. 그리고 MySQL over EC2에 비해서 용량상 제약이 따른다.


5) Oracle Golden Gate

일단 내가 아는 DB Replication 솔루션 중에 최강이다. 그만큼 가격도 최강이다. 모 업체가 위의 옵션을 다 고민도 해보고 production에 적용도 해봤는데,결론이 OGG(Oracle Golden Gate)로 왔다.


기본적으로 이러한 여러가지 옵션이 존재하는 이유가, MySQL EE의 HA 옵션은 DBCR (Linux의 HA솔루션)을 이용하는 방식인데, 전통적인 HA 방식으로 서버가 죽으면, 상대편 서버로 HA하면서 IP를 옮기는 방식인데, 아마존에서는 작동을 안한다고 한다. 그리고 Fail over는 IP를 넘겨서 한다고 쳐도 (물론 back단에서는 MySQL 의 기능을 이용해서 data replicaiton을 해야 한다.) Fail Back이 어떻게 되는지에 대해서 상세한 시나리오는 없다.

조금 더 리서치가 필요한 영역이긴 한데, 이미 다들 안되는 방향으로 잡아서 더 이상 파보지는 않았다.


MySQL Cluster

MySQL Cluster, which is a different product than the MySQL database server, provides several of these features natively. Synchronous replication and automatic partitioning are standard features in MySQL Cluster; however, this is a very different product than the MySQL server. The name is appropriate for features, yet deceiving when compared with MySQL.

While this product includes a SQL interface, data access for high throughput environments can be achieved from an Application Programming Interface (API). The strengths and features of MySQL Cluster are not always applicable to an Online Transaction Processing (OLTP) application using traditional MySQL replication. There are also additional limitations, including the database size with available memory. This product is ideal in certain situations and is widely used in the telecommunications and gaming industries. The use, understanding, and benefits of MySQL Cluster are topics for an entire book.

It should be noted that MySQL Cluster mainly differs from a regular MySQL server for the following additional reasons:

   There is currently no referential integrity. Foreign keys are being developed for a future version.

   You can only use the NDB storage engine and are then limited to READ-COMMITTED transaction level.

   While primary key lookups are fast and concurrent access is faster than a regular MySQL server, more complex queries, including table joins and group by operations, are more expensive.

For more information about MySQL Cluster, refer to http://www.mysql.com/products/cluster/.

 

Galera Cluster for MySQL

Galera Cluster for MySQL provides synchronous replication and multi-master features when using InnoDB. This means a Galera Cluster supports reads and writes to any node, and provides a true multi-master experience with no lag and no loss of transactions.

Galera Cluster is built on a more generic replication API called wsrep (Write Set REPlication). The wsrep API defines an interface between the database server and the replication plugin and is a separate open source project by Codership. MySQL-wsrep is a patch for MySQL to implement the wsrep API in the database server. This patch will enable the MySQL server to use a wsrep provider plugin, for example, Galera. Galera is the wsrep provider including synchronous multi-master replication.

Galera 2.x is the currently supported version available with MySQL 5.1 GA and MySQL 5.5 GA. Galera is an open source project available under the GPL v3 license, with integration components available under the GPL v2 license to be compatible with MySQL.

Current Limitations

When comparing Galera with a traditional MySQL server, there are some limitations; however, these are not serious roadblocks to trying and using Galera effectively:

   Only InnoDB tables are supported in replication.

   LOCK and UNLOCK statements, GET_LOCK() and RELEASE_LOCK() functions are not supported.

   Log output to TABLE using log_output is not supported. Logging must be to FILE.

   XA (eXtended Architecture) transactions are not currently supported.

 

Tungsten Replicator

Tungsten Replicator provides an extensive list of additional replication features to MySQL, including supporting seamless failover of MySQL servers, flexible filtering of operations, multi-master and multi-master to slave support (i.e., fan in), parallel replication, and much more.

In addition to MySQL replication, Tungsten Replicator can provide heterogeneous data management with other RDBMS and noSQL products, including data synchronization between MySQL and Oracle and vice versa.

Tungsten Replicator is available under the open source GNU GPL v2 license. Continuent, the creators of Tungsten Replicator, also provide commercial support with an enterprise offering. More information can be found athttp://www.continuent.com/solutions/overview.

Features

There is a long list of possible features that can be discussed. In this section, which is an introduction to Tungsten Replicator, the following will be discussed and demonstrated:

   Master failover via slave promotion

   Bidirectional replication (e.g., active/active multi-master)

   Parallelization and replication prefetching for improved performance

   Complex topologies

With traditional MySQL replication, the version of a MySQL slave should be the same or greater than that of the master. With Tungsten Replicator, this limitation does not exist. It is possible to replicate from MySQL 5.5 to MySQL 5.0, for example. There are limitations to any features that are in the newer version of MySQL; however, Tungsten can handle the error condition gracefully and not interrupt replication from continuing.

NOTE    Tungsten Replicator has one significant benefit over other products. There is no change to the standard MySQL installation necessary. There are no custom or patched MySQL binaries to install or maintain. Tungsten Replicator works with your existing MySQL environment and can replicate between MySQL 4.11*, 5.0, 5.1, 5.5, and 5.6 as well as different flavors, including MySQL Community, MySQL Enterprise, MariaDB, and Percona Server.

 

SchoonerSQL

SchoonerSQL by Schooner Information Technology (recently acquired by SanDisk) provides a commercial synchronous MySQL replication solution using a modified version of InnoDB. The documented features include no loss of data, instance failover, and automated recovery capabilities. A SchoonerSQL solution can also include traditional MySQL replication for additional slaves. As a commerical product, there is no access to the software without a formal pre-sales process. A request to review the software for this book was not granted.

 

 


Auto-Sharding

이번 MySQL의 Enhancement 중 재미있는 기능중의 하나가 Auto Sharding이라는 기능이다. 

Sharding에 대한 개념은 http://bcho.tistory.com/670 를 참고하면 되고.

보통 Sharding이라 하면 Application 에서 작성해서 컨트롤해야 하기 때문에, 데이타 저장 용량을 늘릴 수 있을지 몰라도, 전체적으로 Application의 구현 복잡도를 올리는 문제가 있었다.

이번 MySQL에서는 Auto-Sharding이라는 기능을 제공하는데, 요는 MySQL이 알아서 데이타를 여러 데이타 노드에 분산 저장함으로써 전체 저장 용량을 늘리고, 데이타를 분산 배치 함으로써, 처리 능력을 향상 시킬 수 있다.



위의 그림은 Auto-Sharding 아키텍쳐이다. Primary Key에 의해서 홀수는 좌측 클러스터에, 짝수는 우측 클러스터에 자동으로 나눠서 저장이 되게 된다.


Data Replication

또한 특정 노드 장애에 대비해서 데이타 노드에 저장된 데이타에 대해서 다른 노드에 복제본을 저장한다.


위의 하나의 좋은 예가 있는데, 두개의 클러스터 노드 그룹이 있고, 4개의 데이타 노드가 있다고 가정하자, 하나의 테이블은 데이타 노드 수인 4개만큼 Sharding으로 나뉘어서 각 노드에 저장되고, 각 노드 그룹안에서는 상대방 서버에 대한 Sharding 파티션에 대한 복제본을 저장한다. 이렇게 함으로써, 특정 노드가 장애가 나더라도, 정지 없고, 데이타 유실 없이 서비스가 가능한 구조가 된다.



     본 문서는 MySQL Cluster 버전을 기준으로 한다.

 

MySQL 배포 구조

MySQL 배포 구조는 다음과 같다.



크게 3가지 종류의 노드를 갖는다.

     MySQL Data Node : Data Node는 실제로 데이타를 저장하고, Query 등을 수행하는 역할을 한다.

     MySQL Application Node : 일종의 라우터 역할로, MySQL 클라이언트에 의해서 들어오는 request를 적절한 Data Node로 라우팅 한다.

     MySQL Management Node : 전체 클러스터에 대한 관리 기능을 수행한다.

위의 그림과 같이 Application Node Data Node는 다수가 존재할 수 있다. Application Node는 실제로 데이타를 저장하지 않고 라우팅 하는 역할만 하기 때문에, 부하 분산과 장애시 Fail Over가 가능하고, Data Node 역시 Node간에 데이타를 replication (복제) 하기 때문에, 특정 Node가 장애가 나더라도 전체 서비스에 문제가 없는 Single Point of Failure 가 없는 구조로 고 가용성을 보장한다.

Memcached 기반의 NoSQL

이번 릴리즈에서 재미있는 기능중 하나는 NoSQL이 유행하다 보니, 이 부분에 대한 보강이 이루어졌다.



Memcached NoSQL에 포함시켰는데, memcached MySQL의 맨 앞단에 넣고, MySQL과 통합 시켜 버렸다. Memcached Key/Value 기반의 스토리지 기능을 제공하는데, 메모리 기반이라는 한계로 인하여, 영구적으로 데이타를 저장할 수 없고 또한, 용량적인 한계도 가지고 있었다. 뒷단에 MySQL을 통합함으로써,

     전체 저장 용량을 확장하고

     데이타를 영구적으로 저장할 수 있으며

     MySQL Cluster의 복제 기능을 이용하여, 다른 MySQL과 복제가 가능하게 되서, 데이타 센터내의 복제나, 데이타 센터간의 복제가 가능하게 되었다.

기존에 MySQL을 사용하고 있으면, 운영 경험을 되살려서 쉽게 운영할 수 있으며, memcached NoSQL 계열에서 복잡도가 가장 낮고 사용이 편리하기 때문에 양쪽의 장점을 잘 융합한 형태라고 볼 수 있다.

Multi Site Replication

SNS 등의 영향으로 인하여 시스템이 거대화 되고, 지역적으로 분리된 데이타 센터를 거쳐서 서비스 하는 시나리오 많아짐에 따라 MySQL도 여러 데이타 센터에 걸친 복제(클러스터링)모델을 제공한다.



Multi-Site Replication이라고 하는데, 거의 근 실시간으로 물리적으로 떨어진 데이타 센터간의 데이타 동기화가 가능하다. 물론 네트웍 구간 속도에 대한 상당한 제약 사항을 가지고 있다.

Ÿ  Latency between remote data nodes must not exceed 20 milliseconds

Ÿ  Bandwidth of the network link must be more than 1 Gigabit per Second

데이타 센터간에 고속 네트웍이 없으면 사실상 어려운 설정이다. 그러나 같은 도시 정도의 근거리에 있는 센터라면 충분히 구축이 가능한 복제 모델이다.

 

이외에도,

1) Auto Sharding을 통한 용량 향상

2) Virtualization 환경을 지원 (XenServer와 OracleVM 만 Certi)

3) OnLine 상태에서 정지 없이 Scale out 할 수 있는 기능 (아마도 클라우드 환경에서 Auto Scale Out을 고려 한듯)

과 같이, 기존에 RDBMS가 가지고 있는 한계점을 보완하고, 클라우드 환경에 맞춰진 기능들이 보강된것이 많이 보인다...

물론 검증되고 잘 보강되어야 하겠지만, 이정도면 왠만한 애플리케이션은 NoSQL없이도 충분히 개발이 가능하지 않을까 싶다.


 

http://research.microsoft.com/en-us/people/sriram/raghuramakrishnan.pdf
물리적으로 분리된 위치에서 데이타 SYNC에 대한 솔루션을 research 하던중에 mysql에 대한 이야기가 많이 나온다. Facebook도 master 와 slave center (미국 서부와 동부)의 데이타를 mysql georeplication 을 이용해서 구현한것으로 보인다.


MySQL georeplication의 원리는 위의 그림과 같다. Master node의 변경 사항을 BinLog라는 형태로 저장하여 복제 대상에 전송한후 replay를 하는 방식이다. record & replay 방식인데, binlog는 오라클의 redo 로그와 유사하다. 데이타베이스의 redo 로그 자체를 레코딩해서 전송하는 방식이기 때문에 데이타 복제만이 가능하고 ETL과 같은 변환은 불가능하며, 변경된 부분만 전송하기 때문에, 성능이 빠르다. 그리고 XA(분산 트렌젝션)처리 등이 필요하지 않다.
사실 이 개념은 DR센터(재해복구를 위한 백업센터)로 데이타를 복제하는 아키텍쳐에 많이 사용되어 왔고, IBM의 CDC나 오라클의 Golden Gate가 매우 유사한 아키텍쳐를 사용한다.
그나저나 MySQL사용해본지가 몇년전인데, 정말 좋아졌다.