'개발/Database'에 해당되는 글 19건

MariaDB 5.2 버전 이후부터 VirtualColumn이라는 것을 지원한다는 것을 알게 되었고,

Oracle에서도 11g 버전 이후로 지원을 한다.

VirtualColumn이 하는 역할은, 테이블 하나에 있는 값들로 연산을 해야 하는데,

좀 반복적인 연산이다 싶은 경우에 VirtualColumn을 하나 두고, 표현식으로 계산식을 넣어두면,

Table에 Row가 쌓일때, 해당 표현식으로 계산이 되면서 VirtualColumn에 값이 들어가게 된다.

예를 들면,

a, b, c 컬럼이 있고, d컬럼을 a + 1이 들어가게 하고 싶다. 라고 한다면

d컬럼의 표현식에 a + 1을 넣어주고, 가상 컬럼임을 인지할 수 있게, VIRTUAL type으로 만들어준다.

본인처럼 로직 연산의 개념으로 사용하려면, 메모리를 직접적으로 사용하지 않는 VIRTUAL Type이 성능상 이점이 많다고 한다.

(sqlYog에서는 VIRTUAL type을 지정할 수 있는 UI가 없기 때문에, 본인처럼 CLI가 생소한 사람들은 HeidiSQL(http://www.heidisql.com/)을 사용하면 된다.)

기존에 있던 테이블을 고쳐서, Virtual Column을 하나 집어넣으려고 하니

Lock Timeout이라고 뜨면서 트랜잭션을 다시 시작하랜다..

데이터도 별로 없는 테이블인데 이렇게 뜨는걸 보면... 버그인가보다.

InnoDB lock timeout값을 아무리 늘려봐도 동일했다.

Virtual Column을 테이블에 두려면, 처음 구조부터 그렇게 잡고 가야하나보다.

참고로, user가 생성한 function은 표현식에서 지원을 하지 않는다.

또한, 안정성을 보장하기 위해(이건 그냥 주관적인 생각..), Virtual Column은 Nullable하게 설정이 된다.

기존 테이블을 Copy한 뒤, 만들고, 기존꺼 지우고, 새로 만든 테이블을 원래 테이블 이름으로 교체하니깐 잘 만들어졌다!

뭐.. 라이브 서비스 도중에 Virtual Column을 갑자기 넣는건 좀 말도 안되는 거고, 로직으로 처리하겠지...

개발단계니까 가능한일.. ㅋㅋ

퍼즐 게임을 만들고 있는데, 데이터가 1,2,3:4,5,2:3,3,2 이렇게 순차적으로 들어가게 되는데,

마지막 스테이지가 몇인지를 매번 코드에서 Split하다보니 짜증나기도 하고, 비효율적인 것 같기도하고 해서..

Virtual Column으로 만들어버리고, Getter를 이용해서 손쉽게 사용하고 있다.

마지막 스테이지를 구하려면 :가 몇개인지 카운팅하고 +1을 더해주면 되는 구조라서,

아래와 같은 표현식을 사용해서 구성했다.

SELECT LENGTH(@String) - LENGTH(REPLACE(@String, ':', '')) + 1

http://stackoverflow.com/questions/11835155/mysql-split-comma-separated-string-into-temp-table

String의 총 길이를 구해서, Delimiter를 빈값으로 치환하고 치환된 문자열의 길이를 재서 원래 문자열의 길이와 Substract를 하면...

Replace를 통해 치환된 문자열의 총 길이를 알 수 있는 로직이다.

생각도 못했던 로직인데... 사용자 정의 function 미지원 소식을 듣고, 포기하기 직전에 StackOverFlow가 구원의 손길을 내려줌 ㅎㅎ

VirtualColumn이라는 새로운걸 알았으니, 자주 써먹어보도록 하자.

※ memcached등 캐시용도로 사용할때는, 메모리상에서 위의 함수를 구현해서

캐시에서 가져온 후, 함수 실행을 해주는 식으로 해야한다. (이거 때문에 큰일날뻔...)

SQL_Latin1_General_CP1_CI_AS 같은 경우 기본적으로 유니코드 대응이 되지만,

VARCHAR형태를 사용하면, 영어 및 특정 언어에 대해서만 대응이 가능하고, 한글이 깨져서 보이게 된다.

이럴 경우! NVARCHAR로 컬럼 타입을 바꿔버리면 해결이 된다.

여러가지 언어가 들어가는 컬럼의 경우 NVARCHAR를 사용해야 될 것 같다.

이렇게 바꿔도 안들어 갔었는데, 그건 쿼리가 잘못되서 였었다.

insert into T_TEST(A, B) values('가', '나')  --여전히 깨짐
insert into T_TEST(A, B) values(N'가', N'나')  -- 정상

NVARCHAR 컬럼에 수동으로 insert를 할 경우 앞에 꼭 N을 붙여야 한다.

MSSQL은 이번에 처음 다뤄보는거라.. 이글을 쓰면서 참 허접해보이는건 왜 일까 ㅠㅠ

SP 작성 중 INT 변수에 NULL을 기본값으로 넣고, 

!= 로 NULL이 아닌지 체크를 하여, 다른 쿼리를 실행할 일이 생겼었음.

근데 잘 안되길래, 뭔가 이상하다 싶어서 아래와 같이 테스트를 해봤는데..

DECLARE @i INT = NULL
SELECT @i

IF @i = NULL
    BEGIN
        SELECT 2
    END
ELSE 
    BEGIN
        SELECT 3
    END

NULL이라고 제대로 찍히면서, 밑에 IF문에서는 ELSE로 들어가는 것이었다.

여기저기 물어보니 표준 문법이 아니라서, 조건에 일치하지 않을거다라고 답변을 받고,

SET ANSI_NULLS OFF 이 옵션을 상단에 넣어주면 제대로 동작할거다라고 원인파악을 했다.

근데 SET ANSI_NULLS OFF 이 옵션을 쓰면!!

인덱스가 걸려있는 테이블에서 뭔가를 변경할 때 실패한다고 한다.

그래서 항상 SET ANSI_NULLS 는 ON으로 써야하고,

IF @i IS NOT NULL과 같이 표준에 맞게 사용하는것으로 정리가 되었음.

이럴꺼면, SP만들때 컴파일 에러나게 처리하는게 어려웠을까라는 생각이 든다.

ON을 표준으로 해놓고, 컴파일 에러나게 했었으면 개발자들 실수가 좀 줄어들었을텐데..

이미 다른 버전이 설치되어 있으면 확인 후 모두 삭제해야 한다.

$ rpm -qa | grep mysql

현재기준으로 CentOS 6.5버전에는 yum repository에 5.1버전밖에 없기 때문에,

5.5버전 설치를 위해서는 webtatic repo가 있는지 확인 후, 없으면 추가해주어야 한다.

$ rpm -qa | webtatic
$ rpm -Uvh http://mirror.webtatic.com/yum/el6/latest.rpm

설치한다.

$ yum --enablerepo=webtatic install mysql55w-server

서비스 실행

$ service mysqld start

※ repo 등록을 하지 않고 yum install mysql로 이미 설치를 해버린 경우,

yum remove mysql로 삭제가 제대로 되지 않는 경우가 있는데

이 경우 아래와 같이 rpm 명령어를 통해 삭제를 하면 된다.

$ rpm -e mysql
$ rpm -e --nodeps mysql-libs

사전에 apm (httpd, php, mysql)이 설치되어 있어야 한다.

httpd를 yum으로 설치했다는 가정하에 작성

1. PhpMyAdmin 설치

httpd document root로 이동

$ cd /var/html/www

phpmyadmin을 다운받음

$ wget https://files.phpmyadmin.net/phpMyAdmin/4.4.12/phpMyAdmin-4.4.12-all-languages.zip

unzip 해준다. (unzip이 없으면 yum으로 설치)

$ unzip phpMyAdmin-4.4.12-all-languages.zip

httpd 서비스를 재시작 후 브라우저에서 http://localhost/phpMyAdmin 확인

$ service httpd restart

2. 세부 설정

phpMyAdmin root에 보면, config.sample.inc.php 파일이 있는데, 

해당 파일을 copy후 config directory를 생성하여,  config.inc.php로 rename후 생성된 directory에 넣어준다.

$ cd /var/html/www/phpMyAdmin
$ mkdir config
$ cp config.sample.inc.php ./config/config.inc.php

브라우저에서 setup url을 호출하여 세부설정을 진행한다. (http://localhost/phpMyAdmin/setup)

※ mysql 계정설정에서 사전에 phpMyAdmin이 설치되어 있는 ip에 대해서 권한설정을 해주어야 한다.

ex) phpMyAdmin이 localhost1에 설치되어 있고, abcd라는 계정으로 모든 DB에 접근하고 싶은 경우 아래와 같이 한다.

mysql> GRANT ALL PRIVILEGES ON *.* TO abcd@localhost1 IDENTIFIED BY 'password'

※ mysql 서버를 여러대 관리하고 싶은 경우, setup에서 등록한 서버 목록을 선택할 수 있게끔 설정이 가능하다.

생성해두었던 config폴더에 들어가서, config.inc.php 파일만 바로 위 depth로 끄집어 내고, config 폴더를 날려버린 후에

config.inc.php 파일을 열어 아래와 같은 옵션을 추가해주면 된다.

$cfg['DisplayServersList'] = TRUE;

게임DB에서 데이터를 추출하여, 통계를 위한 DB로 보내야 하는 경우가 생겼다.

스크립트 언어로 개발하는게 귀찮아서, 그냥 mysql 터미널 명령어로 해결하기로 했음.

-e 옵션으로 쿼리 날려서 txt파일로 끄집어낸 후,

통계 DB에 해당 파일을 import시켜야 하는데, 이런 경우 local-infile이라는 옵션을 안주면,

원격지에 가서 파일을 검색한다.

이런 경우 local-infile=1옵션을 추가로 주면, 해당 머신에서 찾아서 import시켜준다. 

MongoDB 세팅을 해볼 기회가 생겨서, Auto FailOver를 지원한다는 ReplicaSet 구성부터 살펴보기로 하였다.

기본적으로 아무세팅도 건들지 않고, 서비스하는 경우 3.0버전 기준으로 Locking은 Read / Write Lock 형태로 Collection에 Lock이 걸리게 되기 때문에, 데이터 변동이 자주 일어나는 게임 서비스에는 부적합하다. 하지만, 3.0버전부터는 WiredTiger engine을 사용하여 Document 단위의 Lock을 지원하며, 현재 최신버전인 3.2버전부터는 WiredTiger engine을 기본 Storage Engine으로 제공하여, Document Lock을 지원한다.

자세한 설명은 아래 링크에 나와있다.

https://docs.mongodb.org/manual/core/storage-engines/

기본 Engine으로 제공할 정도면, 충분히 안정적이 되었다는 것이겠고, 앞으로 MongoDB는 3.2버전이상을 사용하는 것으로 하면 도움이 될 것 같다.

커널튜닝도 조금 필요하다. 

Ubuntu머신 기준으로 /sys/kernel/mm/transparent_hugepage/enabled, 

/sys/kernel/mm/transparent_hugepage/defrag 값이 always로 되어 있으면, Mongod데몬이 올라가면서 warning 메시지를 뱉게 된다.

Never로 세팅을 해주게 되면, 해당 Warning들이 없어지게 되며, 데이터베이스의 경우 저 옵션이 always로 되어 있는 경우에 예측하기 힘든 System Hang현상이 올 수도 있다고 한다.

아래 링크를 참고했음.

http://infoages.tistory.com/1297

여기까지 했으면, 중요하게 짚고 넘어가야할 설정에 대해서는 마무리 된 것 같고, 

다음으로 ReplicaSet에 대해 알아보자.

각 노드에서 mongod.conf (apt-get 설치 기준 default 위치 /etc/mongod.conf)를 열어서,

아래와 같은 내용을 추가한다.

replication:
   oplogSizeMB: 2048       // mysql의 binlog와 비슷한 개념으로, 데이터 복원 시 사용됨. 
                                // (숫자를 지정하지 않으면, 
                                //무한정 커지기 때문에 값을 지정해주는 것을 권장한다고 한다.)
   replSetName: rs-uvm      // replicaSet의 이름

각 노드들의 데몬을 재시작 한다.

마스터가 될 노드로 들어가서, mongo를 입력하여 command line으로 들어가서, 아래와 같은 명령어를 입력한다. (syntax만 맞다면 json 포맷이 마무리 되기 전까지 엔터를 쳐서 연결해도 문제 없다.)

# replicaSet의 정보를 config 변수에 담는다.

>  config = {
... _id: 'rs-uvm',
... members: [ {_id:0, host:'192.168.33.13:27017'},
... {_id:1, host:'192.168.33.14:27017'},
... {_id:2, host:'192.168.33.15:27017'}]}

# replicaSet을 config변수의 정보를 참조하여 start시킨다. (이때부터 각 노드의 데이터에 대해 복제가 시작됨)

> rs.initiate(config);

※ mongo에서 지원하는 replicaSet에 대한 auto FailOver의 경우 기본적으로 노드가 홀수로 구성이 되어야 한다. 그 이유는 master가 죽은 경우 나머지 노드들끼리 투표를 하여 누가 master가 될지를 정해야하는데, master를 제외한 노드가 하나밖에 없는 경우 어떤 장비가 더 적합한지 내부에서 판단하여 투표를 해서 master로 승격을 시켜야 하는데, 하나 밖에 없는 경우 그 기준이 애매하기 때문에 그렇다. 이런 경우 Arbiter노드라는 노드를 설정하여, 실제로는 데이터가 복제되지 않는 노드지만, master 장애 시 투표에 참여할 수 있게끔 구성이 가능하다. (장비가 부족하거나, 굳이 장비를 3대까지 세팅할 필요가 없는 경우에 유용)

Arbiter 노드는 위에 config 변수에 노드에 대한 정보를 넣을 때, arbiterOnly라는 element를 추가하여 true로 설정해주면 가능하다! ("arbiterOnly" : true)

아직 제대로 운영을 해본적은 없어서, 나머지 정보는 운영 후 다른 포스팅에 추가하는걸로.. 

테스트만 해보고 작성한거라, 잘못된 내용이 있을 수 있으니, 테클은 언제든 환영!

비슷한 구조를 가진 Table이 이름만 다른채로 여러개로 퍼져서 운영되는 경우,

join, union을 사용해서 데이터를 추출하려고 해도, Table이 많아지면 결국 노가다가 된다.

이런 경우 GROUP_CONCAT을 이용하면 유용하다.

# 결과 쿼리가 들어갈 변수 선언
SET @resultQuery = NULL;

# GROUP_CONCAT으로 쿼리 문자열에 쿼리문을 생성하여 등록
SELECT 
    GROUP_CONCAT(
    DISTINCT      // 문자열이 중복된 경우 제거해주는 옵션인 듯)
    CONCAT('(select 컬렴명1, 컬럼명2 from ', table_name, ' limit 1)') 
    SEPARATOR '\r\nUNION\r\n' // 각 쿼리를 뭘로 연결할 것인지 (센스있게 마지막쿼리엔 붙지 않음)
    )
INTO @resultQuery
FROM information_schema.TABLES
WHERE table_name LIKE '테이블명%';      // 본인의 경우 prefix가 같아서 이렇게 사용

# 문자열로 쿼리가 등록된 상황이므로, prepare statement를 이용하여 실행해준다. (select로 하게되면, 문자열 자체가 결과로 나오게됨)
PREPARE stmt FROM @resultQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

이슈1) UNION 문과 limit문을 같이 사용하는 경우, 쿼리에 괄호가 누락되면 제대로 동작을 하지 않는다. 각 쿼리에 limit문이 들어간다면, 괄호문을 꼭 넣어주도록 하자.

이슈2) 아마 쿼리문이 1024 byte를 넘어가면, 짤리는 현상이 생겨서 실행이 제대로 되지 않을 것이다.

이런 경우 아래 명령어로 group_concat의 최대 사이즈를 늘려주자. (varchar니깐 65535까지 되는 것 같음)

SET SESSION group_concat_max_len = 65535;

DBA에게 위에 요구사항을 만족하는 쿼리 요구를 했더니, 엑셀을 열더니 노가다 방법을 알려주시더군.. 뭐 그럴수있지;

몽고 DB의 Replication을 이용하는 경우, 로그 레벨을 production 레벨로 올려도 

쓸데없이 Info 로그가 찍히는 경우가 발생을 한다.

서비스를 오래하는 경우 무자비하게 파일이 커지기 때문에, Rotate기능을 찾던 중,

Mongo Shell에서 커맨드 한번으로 Rotate가 되는 기능을 찾을 수 있었다.

아래와 같이 입력하면 된다.

mongo> use admin
mongo> db.runCommand( { logRotate : 1} )

linux command line이나 bash에서 실행하고 싶은 경우,

위 커맨드를 js 파일로 만들어서 실행이 가능하다.

만약 위 명령어가 rotate.js 라고 가정해보면 아래와 같다.

$ mongo < rotate.js