[팁] mysql 데이터 파일 경로 변경

mysql을 쓰다가, 이상하게 해결할 수 없는 오류들을 만났다.

insert도 잘되던 데이터들이 올라가지 않고, truncate이나 drop 구문을 날렸는데, 5분이 지나도 hang되어 버렸다.

이렇게 hang이 걸리면, mysql stop도 시킬 수 없고 당연 start도 할 수 없었다.

어쩔 수 없이 mysql 프로세스를 kill시키고 다시 start할 수 밖에….
(kill & start는 되어서 정말 다행이었다…)

잠시였지만 고민 끝에 확인한 것이 storage가 full이었을 때의 증상이었다.

yum으로 mysql을 설치하니, /var/lib/mysql에 데이터가 저장되는 경로가 default로 설정이 되었고, /에 할당된 disk가 20GB밖에 되지 않아서 금방 full이 났다.

방법은 데이터 경로를 변경하는 것. (다음과 같이 쉽게 작업됨)

1. mysql을 stop 시킨다.

2. 새 데이터 directory를 생성하고 (예, /data/mysql), /var/lib/mysql/* 을 /data/mysql/로 이동시킨다.

3. my.cnf 파일에 생성한 디렉토리를 추가한다.
[mysqld] 아래에 다음 line들을 추가한다.
datadir=/data/mysql
innodb_data_home_dir=/data001/data/mysql
innodb_log_group_home_dir=/data001/data/mysql.

4. mysql을 start 시킨다.

추가로, log-error=on추가로, 를 추가하면  on.err파일이 생성된다.

[팁] MySQL 속도 튜닝

[1] 설치 시 기본

처음 Mysql을 설치하면, autocommit이 true로 설정된다.
이것은 데이터를 insert할 때마다 commit을 자동으로하기 때문에 속도가 느리다.
100,000개의 Insert 수행결과 54초 정도 걸렸다.

[2] autocommit 확인

autocommit을 false로 설정해 보았다.
방법은 [mysqld] 아래 init_connect=set autocommit=0 를 추가 하는 것이다. 그리고 mysql을 restart 해준다.
> show variables  like ‘%auto%';
+————————-+——-+
| Variable_name            | Value |
+———————-+——-+
| autocommit                  | OFF

정확하게 설정이 먹었다…
이번에 다시 똑같은 100,000개의 insert를 수행하니 26초루 줄었다…
이것두 빠르지 않넹…

[3] insert 쿼리 개선

이번엔 insert를 쿼리를 개선해 보았다.

  • inset into A (a) values (‘1′);
  • inset into A (a) values (‘2′);

이렇게만 insert할 수 있는지 알았는데, inset into A (a) values (‘1′),(‘2′)이와 같은 insert가 가능했다.
values를 이와 같이 100개씩 붙여서 insert했더니, 7초로 확~ 줄었다.
애초 54초에 비해서 1/6배나~~~
100개 이상도 value를 붙여 보았으나 크게 차이는 없었다~~

(database) ETL 툴에 대한 장단점

장점

  1. 시각적으로 데이터 변한 Logic 에 대한 Flow 가 보이므로 처리 흐름을 쉽게 파악할 수 있다. 작성도 용이함 (Drag&Drop, 속성 지정)
  2. Job Scheduling 및 Worlflow 관리 기능이 있으므로 작업간의 연관성(Dependecy), 성공, 실패, 중지시 처리흐름 구현이 쉽고, 작업에 수행주기를 구미에 맞게 설정 가능, 이기종 DB 및 시스템간 통합이 용이함
  3. ETL 작업 수행에 대한 각종 처리 관련 통계에 대한 관리 및 확인이 용이함
    (처리건수, 처리 시간, 실패건수 , 시스템 자원 사용율)
  4. 관리의 용이성
    데이터 처리 로직을 ETL Tool통해 일원화 할 수 있고 SQL구사 능력이 높지 않아도 사용가능함
  5. 관리해야 할 시스템이 늘어 날 수록 관리가 Hand Coding에 의한 로직 보다 쉬워짐

단점

  1. License 비용
  2. 특정 ETL에 대한 종속성 (해당 Tool을 다룰 수 있는 사람이어야 함)
  3. Hand Coding으로 최적화 하는 경우 보다는 일반적으로 느림 (대량 데이터의 경우 ETL 보다 DBA가 직접 처리 하는것이 유리함)