ubuntu 에 MariaDB 10.0 을 설치한 뒤에 /etc/mysql/my.cnf 를 수정하고, 재시작할 경우 다음과 같은 에러가 발생합니다.

$ sudo service mysql restart
 * Stopping MariaDB database server mysqld                               [ OK ] 
 * Starting MariaDB database server mysqld                               [ OK ] 
 * Checking for corrupt, not cleanly closed and upgrade needing tables.

syslog 를 확인해봅니다.

$ grep mysql /var/log/syslog | less
.....
/etc/mysql/debian-start[16909]: Looking for 'mysql' as: /usr/bin/mysql
/etc/mysql/debian-start[16909]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
/etc/mysql/debian-start[16909]: This installation of MySQL is already upgraded to 10.0.12-MariaDB, use --force if you still need to run mysql_upgrade
.....

위와 같은 경우에 발생한 것이라면, 다음처럼 강제로 mysql 업그레이드 합니다.

$ sudo mysql_upgrade --force -u root -p


Posted by 집시F

mysql 에서 외래키(foreign key)를 사용하여 종속성이 생겼을 때, 다음과 같은 오류가 발생할 수 있습니다.

Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails

ALTER TABLE 구문으로 외래키를 삭제했음에도 불구하고 DROP TABLE 이 안됩니다.

이와 같은 경우에는 다음의 구문을 실행하여 외래키 검사를 일시적으로 끕니다.

SET FOREIGN_KEY_CHECKS=0

이후에 DROP TABLE 을 수행하면 오류 없이 잘 진행될 것입니다.


참고:


Posted by 집시F

mysql 의 성능을 향상시켜서 좀더 빠르게 처리하는 방법을 찾던 중입니다.

그 중에서 HandlerSocket 이라고 DeNA 에서 개발한 mysql SQL 쿼리 파싱을 최소화하고,

InnoDB 를 직접 접근하는 형태로 개발된 plugin 이 있습니다.


2009 년 당시에 발표했을 때 성능은 약 7배정도의 향상이 있었습니다.

참고: http://yoshinorimatsunobu.blogspot.kr/search/label/handlersocket


이 글은 HandlerSocket 통신 프로토콜을 간략하게 한글로 번역해둔 것입니다.

원본: https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/protocol.en.txt


HandlerSocket 프로토콜



<기본 문법>


라인 단위로 식별된다.

각 라인은 LF(0x0a) 로 끝난다.

각 라인은 HT(0x09) 토큰으로 식별되는 집합으로 구성된다.

토큰은 NULL 이거나 인코딩된 문자열이다.

NULL 과 empty string 은 식별해야 한다.

NULL 은 NULL(0x00) 으로 표현된다.


인코딩된 문자열은 다음과 같은 규칙을 따른다.

* 0x10 ~ 0xff 그 자체로 인코딩되어 있다.

* 0x00 ~ 0x0f 는 앞에 0x01 가 있고, 0x40 쉬프트된다.

  예를 들면 0x03 은 0x01 0x43 으로 인코딩된다.


문자열은 비어 있을 수 있다.

연속으로 0x09 0x0a 이라면 각 라인에 empty string 이 있다.




<요청과 응답>


단순한 요청/응답으로 구성된다.

접속한 뒤에 클라이언트는 요청을 보내고, 서버는 응답을 보낸다.

요청/응답은 한 라인으로 구성된다.

요청은 중첩될 수 있다.

한번에 여러 요청을 보내면, 한번에 여러 응답을 받는다.





<인덱스 열기>


'open_index' 요청은 다음과 같다.


P <indexid> <dbname> <tablename> <indexname> <columns> [<fcolumns>]


<indexid> 는 10진수이다.

<dbname> <tablename> <indexname> 은 문자열이다.

일차키(primary key)를 열기 위해서는 <indexname> 에 PRIMARY 로 쓴다.

<columns> 는 여러 컬럼명을 콤마로 구분한다.

<fcolumns> 는 여러 컬럼명을 콤마로 구분한다. 이것은 선택적이다.


'open_index' 요청을 한 뒤에는 HandlerSocket 플로그인은 해당 인덱스를 열고

접속이 끊길 때까지 유지한다.


각 open_index 는 <indexid> 로 식별하고,

<indexid> 를 이미 열었다면 예전 open_index 는 닫는다.


같은 <dbname> <tablename> <indexname> 에 여러 번

각기 다른 <columns> 를 사용할 수 있다.


효율성을 위해서 <indexid> 를 가급적 적게 써라.




<데이터 얻기>


'find' 요청은 다음과 같다.


<indexid> <op> <vlen> <v1> ... <vn> [LIM] [IN] [FILTER ...]


LIM 다음과 같은 인자들이다.


<limit> <offset>


IN 은 다음과 같은 인자들이다.


@ <icol> <ivlen> <iv1> ... <ivn>


FILTER 는 다음과 같은 인자들이다.


<ftyp> <fop> <fcol> <fval>


<indexid> 는 숫자다. 'open_index' 에 명시된 숫자다.


<op> 는 비교 연산자이다. 현재는 '=' '>' '>=' '<' '<=' 를 지원한다.

<vlen> 뒤에 오는 <v1> ... <vn> 인자의 길이이다.

이 값은 'open_index' 요청의 <indexname> 에 해당되는 인덱스 컬럼 갯수보다 작거나 동일하다.


<v1> ... <vn> 은 인덱스 컬럼 값이다.


LIM 은 선택적이다. <limit> <offset> 은 숫자다. 생략하면 1 0 으로 명시된 것으로 동작한다.

이 인자는 SQL 의 LIMIT 와 유사하다.

이들 값은 필터에 의해 무시된 레코드 갯수를 포함하지 않는다.


IN 은 선택적이다.  SQL 문법에서 WHERE ... IN 과 유사하다.

<icol> 는 <indexname> 의 인덱스 컬럼 갯수보다 작아야 한다.

IN 이 명시되면 <v1> ... <vn> 값에서 <icol> 번째 파라미터가 무시된다.


FILTER 는 선택적이다. FILTER 는 필터를 명시한다.

<ftyp> 는 'F' (filter) 또는 'W' (while) 이다.

<fop> 은 비교 연산자이다.

<fcol> 은 'open_index' 에서 <fcolums> 에 해당되는 컬럼 갯수보다 작아야 한다.

여러 개의 필터가 지정되면, 그것들을 논리적으로 AND 로 동작한다.

'F' 와 'W' 의 차이는 지정된 조건의 레코드를 찾지 못했을 때 'F' 는 레코드를 무시하고,

'W' 는 찾는 것을 멈춘다.




<데이터 갱신/삭제>


'find_modify' 요청은 다음과 같다.


<indexid> <op> <vlen> <v1> ... <vn> [LIM] [IN] [FILTER ...] MOD


MOD 는 다음과 같은 인자들이다.


<mop> <m1> ... <mk>


<mop> 는 'U' (update) '+' (increment) '-' (decrement) 'D' (delete) 'U?' '+?' '-?' 'D?' 이다.

뒤에 '?' 명시되면 레코드가 변경되지 전의 내용을 반환한다.

명시되지 않으면 변경된 레코드의 갯수를 반환한다.


<m1> ... <mk> 설정할 컬럼 값들을 명시한다.

<m1> ... <mk> 길이는 'open_index' 의 <columns> 에 명시된 길이보다 작거나 같아야 한다.

<mop> 가 'D' 이면 <m1> ... <mk> 는 무시된다.

<mop> 가 '+' 또는 '-' 이면 값은 숫자이어야 한다.

<mop> 가 '-' 이고 컬럼 값이 음수에서 양수 또는 양수에서 음수로 바뀔 때는 값이 바뀌지 않는다.




<데이터 삽입>


'insert' 요청은 다음과 같다.


<indexid> + <vlen> <v1> ... <vn>


<vlen> 는 뒤에 오는 <v1> ... <vn> 의 길이다.

'open_index' 에 지정된 <columns> 의 길이보다 작거나 같아야 한다.


<v1> ... <vn> 는 설정된 컬럼 값을 지정한다.

<columns> 에 없는 컬럼은 기본값으로 설정된다.




<인증>


'auth' 요청은 다음과 같다.


A <atyp> <akey>


<atyp> 는 '1' 이어야 한다.


'auth' 요청은 <akey> 값이 'handlersocket_plain_secret' 또는 'handlersocket_plain_secret_rw' 에

정확하게 지정되어야 한다.


인증이 활성화된 상태에서 'auth' 요청 이전에 다른 요청은 모두 실패한다.




<응답 문법>


각 요청에 대해서 다음과 같은 문법으로 응답한다.


<errorcode> <numcolumns> <r1> ... <rn>


<errorcode> 는 요청이 성공인지 실패인지 알려준다.

'0' 은 성공이고, 그 외는 에러이다.


<numcolumns> 는 결과 세트의 컬럼 갯수를 나타낸다.


<r1> ... <rn> 은 결과 세트이다. <r1> ... <rn> 길이는 <numcolumns> 의 배수이다.

<r1> ... <rn> 이 비어있을 수도 있다.


<errorcode> 가 0 이 아니면, <numcolumns> 는 항상 1이고 <r1> 은 사람이 읽을 수 있는

에러 메시지이다. 가끔은 <r1> 이 없을 수도 있다.




<'open_index' 에 대한 응답>


'open_index' 가 성공하면 다음을 반환한다.


0 1




<'find' 에 대한 응답>


'find' 가 성공하면 다음을 반환한다.


0 <numcolumns> <r1> ... <rn>


<numcolumns> 는 'open_index' 의 <columns> 의 길이와 동일하다.


<r1> ... <rn> 은 결과 세트이다. N 열을 찾으면 <r1> ... <rn> 길이는 <numcolumns> * N 이 된다.





<'find_modify' 에 대한 응답>


'find_modify' 가 성공하면 다음을 반환한다.


0 1 <nummod>


<nummod> 는 변경된 열의 갯수이다.


예외로 <mop> 에 '?' 를 지정하면, 응답 결과는 ' find' 에 대한 응답이 된다.




<'insert' 에 대한 응답>


'insert' 가 성공하면 다음을 반환한다.


0 1




<'auth' 에 대한 응답>


'auth' 가 성공하면 다음을 반환한다.


0 1



최근에 제가 이 프로토콜을 직접 C++ 로 구현해서 성능을 측정해본 결과

mysql SQL 로 하나, HandlerSocket 을 이용하나 성능차이는 미비했습니다.

이에 대한 기록은 나중에 별도로 정리하도록 하겠습니다.


성능에 대한 참고:


Posted by 집시F

Oracle Linux 는 기본적으로 Red Hat Enterprise Linux 와 동일합니다.

따라서 CentOS 하고도 동일합니다. 단지 내부적으로 최적화를 위해서 몇가지 configuration 이 변경된 것으로 알고 있습니다. 자세한 사항은 http://en.wikipedia.org/wiki/Oracle_Linux 를 참고합니다.


이하 최신 Oracle Linux 6.4 를 기준으로 설치하고, Oracle DB 12c Release 1 을 설치해봅니다.


1. Oracle Linux 설치

다운로드 이후에, iso 이미지를 이용하여 설치를 진행합니다.

중간에 서버의 용도를 선택하는데 여기서 Database Server 로 선택합니다.


2. 네트워크 설정

CentOS 와 동일합니다. 참고: http://netmaid.tistory.com/91

DNS 도 설정합니다. 설정하지 않으면, 오라클 도메인을 찾지 못하여 업데이트가 안될 수도 있습니다.


3. 최신으로 업데이트

yum update 로 진행합니다.


4. Oracle DB 12c Release 1 다운로드

오라클 홈페이지에서 다운로드합니다. Linux x86-64 버전의 linuxamd64_12c_database_1of2.zip, linuxamd64_12c_database_2of2.zip 파일을 다운로드합니다.

scp 를 이용하여 설치하려는 서버로 업로드합니다.

참고: http://netmaid.tistory.com/23


5. 압축해제

$ unzip linuxamd64_12c_database_1of2.zip
$ unzip linuxamd64_12c_database_2of2.zip


6. 오라클 계정 생성

Oracle DB 는 보안상 root 계정으로 설치할 수 없게 되어 있습니다. 별도의 계정을 만듭니다.

$ adduser oracle

5번에서 압축해제한 파일이 root 권한으로 되어 있으므로, oracle 계정 공간으로 옮기고 권한도 바꿉니다.

$ mv database /home/oracle
$ chown -R oracle:oracle /home/oracle/database


7. Xfce 설치

Oracle DB 는 GUI 환경에서 설치를 진행합니다. 원격의 GUI 환경에서 접속하여 Oracle DB 를 설치합니다.

원격의 GUI 환경으로는 Xubuntu 를 선택했습니다. Xubuntu 는 Xfce 를 기본 xwindow 로 탑재하고 있습니다.

따라서 Oracle Linux 서버에도 Xfce 를 설치합니다.

Xfce 를 설치하려면 EPEL 저장소를 추가해야 합니다.

$ yum install wget
$ wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
$ rpm -ivh epel-release-6.8.noarch.rpm

Xfce 를 설치합니다.

$ yum groupinstall Xfce

추가로 폰트, 인증 모듈, 유틸 모듈도 설치합니다.

$ yum install xorg-x11-fonts-Type1 xorg-x11-fonts-misc
$ yum install xorg-x11-xauth xorg-x11-utils

참고:


8. ssh 를 통해서 xwindow 접속이 가능하도록 설정

sshd_config 를 수정하여 다음의 3개를 활성화시킵니다.

$ vi /etc/ssh/sshd_config
.....
X11Forwarding yes
X11DisplayOffset 10 
X11UseLocalhost yes


9. Xubuntu 를 설치한 서버에서 Oracle Linux 로 접속

터미널 창을 띄우고 ssh 로 -X 옵션을 지정하여 접속합니다. (원격으로 xwindow 를 활성화하여 접속하는 것입니다)

$ ssh -X oracle@oracle_linux_ip


10. hostname 점검

원격으로 접속을 성공했으면, hostname 과 ip 를 확인합니다.

$ cat /etc/sysconfig/network | grep HOSTNAME
HOSTNAME = test.domain.com

$ vi /etc/hosts
127.0.0.1 test test.domain.com localhost localhost.localdomain localhost4 localhost4.localdomain4
::1       test test.domain.com localhost localhost.localdomain localhost4 localhost4.localdomain4

제대로 설정되지 않으면 DB 설치 진행 중에 다음과 같은 오류 메시지가 뜰 것입니다.

PRVF-0002 : Could not retrieve local nodename

참고:


11. DB data 디렉토리 생성

root 권한으로 디렉토리를 생성합니다. 생성한 디렉토리는 oracle 계정 권한으로 만듭니다.

$ su root
$ mkdir /oradata
$ chown oracle:oracle /oradata


12. limits.conf 변경

root 권한으로 보안 설정을 바꿉니다. 이는 성능을 높이기 위해 시스템 자원을 많이 사용할 수 있도록 설정하는 것입니다. 이 설정을 바꾸지 않으면, 나중에 DB 설치 진행 중에 경고가 뜹니다.

$ vi /etc/security/limits.conf
.....
oracle	soft	nproc	2047
oracle	hard	nproc	16384
oracle	soft	nofile	1024
oracle	hard	nofile	65536

참고:


13. sysctl.conf 변경

root 권한으로 시스템 설정을 바꿉니다. 이 설정을 바꾸지 않으면, 나중에 DB 설치 진행 중에 경고가 뜹니다.

$ vi /etc/sysctl.conf
.....
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.aio-max-nr = 1048576
fs.file-max = 6815744

net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

참고:


14. Oracle DB 를 위한 종속적인 패키지 설치

root 권한으로 Oracle DB 설치에서 필요한 패키지를 추가로 설치해둡니다.

$ yum install compat-libcap1 gcc gcc-c++ ksh compat-libstdc++-33 libaio-devel


15. 시스템을 리부팅

12번과 13번에서 시스템 설정을 변경했기 때문에 root 권한으로 리부팅을 합니다.

$ sudo shutdown -r now


16. Oracle DB 설치

리부팅이 완료되면, 9번처럼 원격의 Xubuntu 에서 oracle 계정 권한으로 서버에 접속합니다. Oracle DB 를 설치합니다.

$ cd database
$ ./runInstaller

GUI 에 따라서 진행합니다. 오라클 계정이 있다면, 최신 업데이트를 받을 수 있습니다. 그리고, 중간에 새로운 database 를 생성하는 옵션으로 설정합니다. (대부분은 이것을 선택할 것입니다.)

설치를 진행하다가 다음 화면에서 멈춥니다.


17. root 로 스크립트 설치

다른 터미널을 통해서 Oracle Linux 서버에 root 권한으로 접속합니다.

GUI 에서 명시한 대로 스크립트를 실행합니다.

$ /home/oracle/app/oraInventory/orainstRoot.sh
$ /home/oracle/app/oracle/product/12.1.0/dbhome_1/root.sh


18. Oracle DB 설치 계속

스크립트를 실행한 뒤에 GUI 를 계속 진행합니다. 그러면 설치가 마무리 됩니다.


19. 방화벽 설정

외부 서버에서 접속이 가능하도록 방화벽의 포트를 개방합니다.

$ vi /etc/sysconfig/iptables
.....
.....

참고:


20. 접속 테스트



Oracle DB 설치 참고 문서:


Posted by 집시F

mysql 에서 prepared statement 는 굉장히 유용합니다.

주로 stored procedure 내에서 dynamic query 를 만들어서 실행하고자할 때 종종사용됩니다.


mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
mysql> DEALLOCATE PREPARE stmt1;


문제는 prepared statement 에는 제약점이 많습니다.

여기에서는 모든 sql 구문을 사용할 수 있지 않습니다.

다음과 같은 구문만 사용할 수 있습니다. (mysql 5.5 기준)


ALTER TABLE
ANALYZE TABLE
CACHE INDEX
CALL
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
COMMIT
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} TABLE
{CREATE | RENAME | DROP} USER
DELETE
DO
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES
  | LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES}
GRANT
INSERT
INSTALL PLUGIN
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE | QUERY CACHE}
REVOKE
SELECT
SET
SHOW {AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS}
SHOW BINLOG EVENTS
SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE} STATUS
SLAVE {START | STOP}
TRUNCATE TABLE
UNINSTALL PLUGIN
UPDATE


그런데 만약 IF ~ ELSE ~ END IF 구문을 사용하려고 한다면 오류가 발생합니다.

그리고 stored procedure 내에서 호출되는 prepared statement 의 경우에, procedure 내부에서 선언한 변수값은 사용할 수도 없습니다. 따라서 SELECT ~ INTO local_var 와 같은 구문을 사용할 수가 없습니다. 

또, prepared statement 에 넘겨지는 값은 EXECUTE ~ USING @var1, @var2... 형태로 입력으로만 제공될 뿐, 입출력 또는 출력으로는 사용할 수도 없습니다.

따라서 prepared statement 내에서 얻어진 출력 값을 외부에 제공할 때 난감합니다.

그렇다면 방법이 전혀 없는 것인가?


꼼수일 수도 있지만, MEMORY TABLE 을 사용하면 됩니다.

예제를 적어보면 다음과 같습니다.


DECLARE TB_NAME VARCHAR(100);
SET TB_NAME = CONCAT('MONTH_', DATE_FORMAT(NOW(), '%Y%m'));

CREATE TABLE TEMP_DATA(SEQ BIGINT) ENGINE=MEMORY;


SET @SQL = CONCAT('INSERT INTO TEMP_DATA(SEQ) SELECT SEQ FROM ', TB_NAME, ' WHERE USER_SEQ = ?');
PREPARE INS FROM @SQL;
SET @P1 = c_user_seq;
EXECUTE INS USING @P1;
DEALLOCATE PREPARE INS;


IF EXISTS (SELECT SEQ FROM TEMP_DATA) THEN
	SET @SQL = CONCAT('UPDATE ', TB_NAME, ' SET MONEY = ? WHERE USER_SEQ = ?');
	PREPARE UPD FROM @SQL;
	SET @P1 = c_money;
	SET @P2 = c_user_seq;
	EXECUTE UPD USING @P1, @P2;
	DEALLOCATE PREPARE UPD;
ELSE
	SET @SQL = CONCAT('INSERT INTO ', TB_NAME, ' (USER_SEQ, MONEY) VALUES (?, ?)');
	PREPARE INS FROM @SQL;
	SET @P1 = c_user_seq;
	SET @P2 = c_money;
	EXECUTE INS USING @P1, @P2;
	DEALLOCATE PREPARE INS;
END IF;


DROP TABLE IF EXISTS TEMP_DATA;


  1. TEMP_DATA 메모리 테이블을 미리 만들어 둔다.
  2. prepared statement 내에서 조건을 만족하면 데이터를 TEMP_DATA 에 넣어둔다.
  3. 상위 프로그램에서 TEMP_DATA 에 대해서 IF 조건을 검사한다.
  4. 이후에는 원하는 작업에 대해서 또 다른 prepared statement 를 실행한다.
  5. TEMP_DATA 메모리 테이블을 삭제한다.


참고:


Posted by 집시F

mysql 에서 stored procedure 내에서 function 을 원할하게 사용하려면 다음의 설정을 해줘야 한다.


mysql> SET GLOBAL log_bin_trust_function_creators = 1;


시스템에 영구적으로 설정하려면 /etc/mysql/my.cnf 에 다음을 추가한다.

[mysqld]
# enable for use function in procedure
log_bin_trust_function_creators = TRUE


물론 이 때, 구현된 프로시저가 DETERMINISTIC 특성을 가지고 있어야, replication 에서도 안전하다.


참고:


Posted by 집시F

mysql 에서 event scheduler 를 원할하게 사용가능하도록 다음과 같이 설정한다.


mysql console 에서는 다음과 같다.

mysql> SET GLOBAL event_scheduler = ON;


시스템이 리부팅하더라도 영구적으로 설정하려면 /etc/mysql/my.cnf 에 다음 항목을 추가한다.

[mysqld]
# event scheduler
event_scheduler = ON


참고:


Posted by 집시F

mysql 에서 CHAR, VARCHAR 타입은 유사하다.

단지 저장하고 읽을 때만 살짝 다르다.


CHAR(40) 에 "mydata" 를 저장할 때, 6바이트를 저장하고 나머지 34바이트의 비어 있는 공간에는 공백을 채워서 저장한다.

그리고 읽어들일 때, 임의로 채워진 공백들은 제거되어서 읽혀진다. (물론, PAD_CHAR_TO_FULL_LENGTH 모드를 활성화하면 공백까지 다 읽어온다)


VARCHAR(40) 에 "mydata" 를 저장할 때, 6바이트를 저장하고, 뒤에 1바이트(255길이 미만) 또는 2바이트(255길이 이상)의 길이 정보를 추가한다.

따라서 VARCHAR 에는 공백을 채워넣지 않으므로, 가변 길이의 정보에 적합하다.

문제는 VARCHAR(40) 에 새로운 데이터가 업데이트 될 경우이다. 더 큰 길이의 데이터가 저장되면, 공간이 부족하여 새로운 저장 영역에 새로 할당해야 하기 때문이다. 이 때문에, 데이터 파편화가 심해진다.


테이블 ROW 중에 CHAR, VARCHAR 타입이 섞여 있으면 데이터 파편화는 발생할 수 밖에 없다. 파편화를 염두하고 설계한다면, 테이블의 데이터 타입 중에 VARCHAR 타입을 두어선 안될 것이다.

그러나 MySQL 에서는 다음의 쿼리를 적용하면 파편화를 막을 수 있다. 

ALTER TABLE tblname ROW_FORMAT = FIXED;

VARCHAR 타입을 CHAR 타입처럼 동작하도록 강제로 지정하는 것이다. 덕분에 저장 용량은 증가하지만, VARCHAR 로 인한 파편화로 성능 저하는 막을 수 있다.


물론 VARCHAR 로 선언한다고 무조건 파편화가 발생하는 것은 아니다. 앞서서 적은대로, 처음에 데이터를 INSERT 한 뒤에, 다른 크기의 데이터로 UPDATE 가 발생할 경우에 파편화가 발생하는 것이다.

그러므로 LOG 테이블처럼 INSERT 만 허용되는 테이블에는 이 문제가 적용되지 않을 것이다.


그리고, 다음의 쿼리를 통해서 저장된 데이터를 분석해볼 수도 있다.

SELECT * FROM tblname PROCEDURE ANALYSE();


마지막으로, 테이블 설계할 때부터 데이터 타입은 신중하게 고려하면 좋다. IPv4 문자열 주소는 CHAR(15) 형태로 한다든가, 아니면 아예 INT 값으로 저장할 수도 있다.(IPv6 라면 CHAR(39) 이다.)

그리고 MD5 암호키 정보는 CHAR(32) 형태로 하는 것이다.


참고:

http://dev.mysql.com/doc/refman/5.5/en/char.html

http://dba.stackexchange.com/questions/424/performance-implications-of-mysql-varchar-sizes

http://stackoverflow.com/questions/7601424/any-benefit-of-uses-char-over-varchar


Posted by 집시F

mysql 에서 bigint 는 8바이트(64비트) 정수값이고, int 는 4바이트(32비트) 정수값이다.

그런데 뒤에 붙는 숫자값은 무슨 의미일까?

메뉴얼에서 보면 이 값은 zerofill 자리수를 의미하지, 저장될 숫자의 자리수를 의미하지 않는다.


일반적으로 DBMS 에서 이들 숫자값은 저장될 숫자의 크기를 의미하는데

mysql 에서는 단순히 결과값을 출력할 때, 숫자를 출력할 때 앞에 0을 채울 때 몇개를 채울지 hint 로 사용된다.

CREATE TABLE foo ( bar INT(20) ZEROFILL );
INSERT INTO foo (bar) VALUES (1234);
SELECT bar from foo;

+----------------------+
| bar                  |
+----------------------+
| 00000000000000001234 |
+----------------------+


실제로 bigint(5), int(2) 타입에 큰 값을 저장해도 잘 저장된다.

CREATE TABLE foo(bar1 BIGINT(5), bar2 INT(2));
INSERT INTO foo(bar1, bar2) VALUES (555555555, 22222222);
SELECT * FROM foo;

+-----------+----------+
| bar1      | bar2     |
+-----------+----------+
| 555555555 | 22222222 |
+-----------+----------+


참고:

http://stackoverflow.com/questions/3135804/types-in-mysql-bigint20-vs-int20-etcc

http://dev.mysql.com/doc/refman/5.1/en/numeric-type-attributes.html


Posted by 집시F

최근에 Google 의 BigTable 이후에 무제한 용량을 자랑하는 Database 혹은 File System 이 관심 이슈로 대두되었지요. 뭐 시기적으로 Yahoo 는 이전부터 Hadoop 을 내놓긴 했습니다만, 어쨋든 대규모 시스템에서는 필수요소라는 것은 확실합니다.

현재 공개된 NoSQL 오픈 소스는 여러가지가 있습니다.

참고: http://nosql-database.org/


그중에서도 Cluster node 장애가 발생하더라도 대응이 가능한 Cassandra 가 인상깊어서, 이것을 좀 조사해보았지요. 역사적으로 Facebook 개발팀에서 만들어서 공개한 것인데, 정작 Facebook 개발팀에서는 Hadoop/HBase 를 더 자주 이용하고 있고, 실제로도 이 오픈 소스에도 기여하고 있다고 하는군요.

지금 Cassandra 는 Facebook 개발팀에서는 버림받았고, Apache 재단에 넘겨진 상황입니다만, 여전히 매력을 가지고 있지요.

그럼 왜, Cassandra 는 Facebook 개발팀에서 채택되지 않았나 궁금증이 생기더군요.

짧은 구글링 실력으로 찾아보았습니다.

참고: http://www.quora.com/Why-is-Facebook-not-using-Cassandra-more-heavily

Facebook is very pragmatic about which tools we use; the fact that Cassandra was originally developed in-house doesn't give it a significant leg up when we're deciding how to store the data for some new site feature, except in that the fact that we already have in-house expertise would factor into decisions about maintainability and so forth.

We have a robust and scalable storage infrastructure built on top of MySQL, and that tends to be the default choice for any new project unless it has atypical storage requirements, simply because it's a huge development-time win to be able to reuse the same very well-proven storage code most of the rest of the team is using.

That said, Cassandra is definitely very much on the table as an option; in fact, earlier this year I participated in a detailed discussion of possible storage engines for a new project (not yet launched) in which we spent quite a bit of time going over the pros and cons of Cassandra. In the end the particular requirements of the project were a better fit for a different storage engine, though Cassandra was the solid second-place option of the five or six we discussed. It is worth noting that in that discussion, even the engineers responsible for Cassandra agreed it wasn't the best fit.

It is completely plausible that a subsequent project will have requirements that are a better match for Cassandra than for any other storage system; in that case we would happily choose it. As a piece of software it is well-respected internally.


결과로는 MySQL 기반에서 안정적이고, 확장이 유연한 구조를 이미 개발완료 했기 때문에 굳이 실험적으로 Cassandra 를 이용할 필요는 없었던 것으로 보입니다. (이건 추가적인 개발비용도 소모되지요.)

Facebook 창업자인 Mark Zuckerberg 모델로 나온 영화, Social Network 에서도 연관된 내용이 있지요.

잠시라도 서비스 장애가 발생하면, 사용자는 Facebook 을 떠날 것이고, 이것은 주변 친구들에게도 소문이 나게 되면서 파급효과가 어마어마해질 것이라고... 그럼 망하는 것이지요.

Social Network 의 영향으로 성공한 만큼, 반대로 그 영향으로 순식간에 실패할 수도 있다는 의미지요.

하나는 혁신적인 NoSQL 이 있고,

다른 하나는 이미 수년간 다수의 개발자들에게 검증되었고, 분산까지 겸비한 RDBMS (MySQL Cluster: http://www.mysql.com/products/cluster/) 가 있습니다.

무엇을 선택하겠습니까?



추가로, 당신이 CEO 입니다.

개발자를 채용하려고 하는데 in-house NoSQL 을 다루는 경력 개발자를 뽑으시겠습니까?

아니면, 업계에서 많이 다뤄온 MySQL 경력 개발자를 뽑으시겠습니까?

선택은 회사와 당신의 역량에 달려있겠지요.



덧붙여서, Facebook 에서는 NoSQL 인 Hadoop 을 서비스 용이 아닌, 데이터 분석용으로 사용하고 있습니다.

참고: http://developers.facebook.com/opensource/


그리고 in-memory table 기능이 없어서 Cassandra 가 실시간 서비스에 적용하기에는 아직 부적합한가봅니다.

참고: http://www.quora.com/Why-isnt-Facebook-News-Feed-powered-by-Cassandra

Posted by 집시F