2014년 1월 28일 화요일

netty 소스 파일 설치(intellij)

netty 소스 파일 추가
- dev 에 보면 intellij를 기본 툴로 쓴단다
  대충 써보니 이클립스보다 좋은거 같당 :)

1.intelJ down

2.https://code.google.com/p/msysgit/downloads/detail?name=Git-1.8.5.2-preview20131230.exe&can=2&q=
down

3.환경변수 추가
GIT_HOME
C:\Program Files (x86)\Git\bin
PATH에추가
%GIT_HOME%;

4.protocol https not supported or disabled in libcurl 해당 에러가 발생하면
C:\Windows\sysWOW64 and/or C:\Windows\System32에 libcurl.dll 의 이름을 변경
--http://stackoverflow.com/questions/17694502/libcurl-dll-error-with-git-push


5.C:\Program Files (x86)\JetBrains\Intelli\bin
idea64.exe.vmoptions 옵션 변경
idea64.exe 64비트로 실행

6.VCS 에서 네티 받기

2014년 1월 27일 월요일

java concurrency in practice ch 8 Applying Threads Pools

8.1.1Thread Starvation Deadlock

Whenever you submit to an Executor tasks that are not independent, be aware of the possibility of thread starvation deadlock, and document any pool sizing or configuration
constraints in the code or configuration file where the Executor is configured

Executor 에 서로 의존 하는 일을 submit 할때는 쓰레드 기아 데드락의 가능성을 인지해야한다. 그리고 Executor 의 설정 파일에 관해서 꼭 문서화 해야 한다.

아래 코드는 싱글 쓰레드 에서는 언제나 데드락이 걸린다. 또한 쓰레드 풀이 충분하지 못해 꽉차있다면 서로 기다리기때문에 데드락이 걸릴수 있다 ( 이와 같은 상황을 thread starvation deadlock) 이라고 한다.
public class ThreadDeadlock {
  ExecutorService exec = Executors.newSingleThreadExecutor();

  public class RenderPageTask implements Callable {
   public String call() throws Exception {
    Future[String] header, footer;
    header = exec.submit(new LoadFileTask("header.html"));
    footer = exec.submit(new LoadFileTask("footer.html"));
    String page = renderBody();
    // Will deadlock -- task waiting for result of subtask
    return header.get() + page + footer.get();
   }
  }
 }
8.1.2 Long-running Tasks
테스크가 너무 긴게 많다면 어느순간 모든 쓰레드가 긴 테스크로 채워질꺼다
해당 사항은 문제가 발생하기 때문에 긴테스크 등에 시간제한을 두고 다시 requeue 하는 방법이 있다

Thread.join, BlockingQueue.put, countDownLatch.await, Selector.select등

8.2.Sizing Thread Pools

2014년 1월 24일 금요일

mysql 강의 1~7강 중 아키텍처, 트랙잭션, 모델링

1.아키텍처

2.트랜잭션

6.모델링

high_performance_mysql_3rd_edition 쿼리튜닝

쿼리 튜닝
원칙1.서브 테스크를 없애거나 줄여라

Slow Query Basics: Optimize Data Access
보통 데이터 량이 문제다
해결
1.필요한것보다 더 많은 양의 데이터 (로우, 컬럼)을 요청하지 않는가?
2.mysql 서버가 필요한 양보다 더 많은 양을 분석하고 있지 않은가?

더 많은 데이터를 요청하는 예
1.Fetching more rows than needed
많은 app개발자가 MySQL에서 착각하는 부분은
로우 100줄 호출 후 10줄후 close 해버리면 10줄만 온다고 생각하는건대
Mysql은 실제로 100줄 다보낸다

그러니 LIMIT 써라
ex) top page 10


2.Fetching all columns from a multitable join
SELECT * FROM A, B
* 쓰지 말자!
꼭 필요한 컬럼만 호출하자

3.Fetching the same data repeatedly
같은 데이터 디비에 여러번 요청 하지 말자

mysql 서버가 너무 많은 양을 분석할때
query 정확할때
측정 기준
반응시간
조사된 데이터
리턴된 데이터

Response time
service time + queue time

service time
실제로 실행 된 시간

queue time
기다린 시간
ex)I/O, lock등

quick upper-bound estimate (QUBE)
플랜과 인덱스의 데이터 량을 보고 몇번의 io 가 필요할지
측정 한다 그래서 이 시간보다 빠른가 비교..
자세한 내용 책참조 하란다..
Relational Database Index Design and the Optimizers (Wiley).
In a nutshell:
examine the query execution plan and the indexes involved, determine how many
sequential and random I/O operations might be required, and multiply these by the
time it takes your hardware to perform them. Add it all up and you have a yardstick to
judge whether a query is slower than it could or should be


Rows examined and rows returned
몇개가 조사되고 몇개가 리턴 되었느냐인대
별 쓸모 없다 테이블 조인 하게 되면 1:X
X가 엄청 크다면? 필요가 있는가..

Rows examined and access types
쿼리 코스트를 생각 할때 테이블에서 row 하나 읽는 cost 를 생각해봐라
access 방법은 EXPLAIN의 type 에 나타남

읽는 양  많음 --> 적음
full table > index scan > range scan > unique index look up > constants


MySql이 where 를 적용 하는 방법
1.검색조건을 index look up 에 사용하여 적용되지 않는 로우를 없앰
*storage engine 레벨에서 적용됨

2.using index
storage engine에서는 인덱스를 읽고
server에서 필요없는 로우 없앰

3.using where
테이블에서 로우들을 읽고
server에서 필요 없는 로우를 없앰

*result row 에 비해 examined 로우가 정말 많다면?
1.커버링 인덱스를 사용하게 하라(테이블 접근을 줄여라)
2.summary 테이블을 사용해라
3.쿼리가 최적의 플랜으로 실행 되게 재작성하라

쿼리 재작성 방법
1.동일 결과를 같고 더 빠르게 할수도 있다
2.성능의 이득이 있다면 결과값을 변경 할수도 있다
2.어플리케이션 코드를 변경 해야 할 수도 있다.


복잡한 쿼리 vs 심플 쿼리
마이 sql은 일반 서버 에서 초당 100,000건의 간단한 쿼리를 쉽게 처리한다.
gigabit network 는 초당 2000건이상을 처리한다.

즉 네트워크 비용이 예전 만큼 중요하지 않다(?.. 중요한탠대..)

물런 지금도 가능하면 네트워크 비용을 줄이는게 맞다
하지만 불가능 하다면 싱글 쿼리를 여러개의 심플한 쿼리로 변경하는걸 겁내지말라
(그정도 효능이 있다면)

데이터를 잘게 쪼개 처리하기
한번에 너무 많은 데이터를 처리하면 문제가 생길 수 있다.
DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH);

이렇게 말고 아래 처럼 처리하자

rows_affected = 0
do {
rows_affected = do_query(
"DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH)
LIMIT 10000")
} while rows_affected > 0

Join Decomposition
성능을 위해 테이블에 싱글 쿼리를 날리고
조인을 어플리케이션 서버에서 하는 방법도 있다
이유
1.어플리케이션에서 캐쉬를 사용할 경우 큰 성능 효과를 기대할수있다
2.분리해서 사용할경우 lock 경쟁이 적어 질수 있다
3.DB를 확장할때 유리하다
4.MySql이 optimizer 를 사용할때 성능을 최적화 할 수 있다
5.디비에서 한번만 읽어오기 때문에 디비의 메모리 사용량과 네트워크 트래픽을 줄일수 있다
6.다른 확장 프로그램을 받는다면 nested loop join 이 아니라 hash join 으로 변경 할수 있다

MySQL이 쿼리를 처리하는 순서
1.클라이언트가 server에 sql문장을 쏜다
2.서버는 query cache 를 확인하고 존재한다면 cache에서 데이터 찾아 리턴
3.parse -> prepocess -> optimizes(sql to query execution plan)
4.query execution engine 이 플랜을 실행 (storage engine 의 API 콜)
5.결과를 클라이언트에 리턴

Mysql Clinet/Server Protocol
half-duplex 언제든 받거나 보내거나 둘중 하나를 할수있다 단 2개 동시에는 안된다.

문제
1.flow controll 할수없다
즉 클라에서 최초 몇줄이 필요하다고 해도 서버가 보내주는 모든 데이터를 받아야 한다.
why?
클라에서 서버로 쿼리를 던지는 순간 서버가 공을 가지게 된다.
그러면 클라는 서버에게 명령 할수 없다.
(통신 방법이 half-duplex니까)

2.서버가 클라로 데이터를 던질때 전체 데이터가 클라에 도착할때까지
쿼리에 필요했던 락과 리소스를 풀수 없다
(the query state is sending data)

3.클라 라이블러리를 설정하여
buffer를 사용할지 오는대로 처리할지 지정 할수 있지만(default buffer)
오는대로 처리하게 설정하면 server 와 clinet의 interact 가 끝날때가지 자원과 리솟스를
풀수 없다.

쿼리상태(query states) 값 설명
SHOW FULL PROCESSLIST .command 컬럼
Sleep
thread 가 클라에서 새로운 쿼리가 올때까지 대기
Query
쿼리를 실행 중이거나 클라한태 돌려주는중(server is sending data to client)
Locked
서버 level에서 table lock이 걸려서 thread가 대기하는 상태
(storage engine에서 직접 lock 을 거는 엔진의 상태는 표시 안됨(InnoDB 등)
Analyzeing and statistics
storage 엔진의 통계 를 확인해서 최적하는 하는중

Copying to tmp table[to disk]
group by , solt 등을 하기 위해 임시 테이블을 만들고 결과를
복사해 놓는 과정
[to disk 라고 적혀있다면 memory에서 -> disk 테이블에 쓴느중)


Sorting result
리절트를 솔팅하는 중

Sending data
서버에서 쿼리 실행 단계중에서 서로 데이터를 보내는것
또는 클라에게 보내는것

statistics 상태가 나온다면 서버 전체적으로 프로 파일링을 해봐야 한다. ㅠ_-

Query cache
1bit 라도 다르다면 매치 되지 않는다.
만약 매치 된다면 권한체크(파싱하지 않고)만하고 리턴

Query optimization process
서버 실행 계획 생성
= parsing -> preprocessing -> optimization

paring
sql을 트리로 만들면서 문법 체크

preprocessor
1.테이블, 컬럼 존재여부 애매한 컬럼등 조사
2.권한 조사

optimization
cost-based 사용
측정 기준
4KB page read

*SHOW STATUS LIKE 'Last_query_cost' 좀전에 실행된 쿼리의 코스트
964 random page 를 읽은거 같다는 뜻
1.(통계값임)
2.(캐쉬되서 리턴되는 값은 고려 안함 무저건 랜덤 I/O로 진행된다고 가정)

참조되는 값
테이블,인덱스 크기
cardinality(number of distinc values)
indexes
rows length , key length





최고의 플랜이 선택이 보장되지 않는 이유
1.통계가 틀릴경우
2.통계가 정확해도 예측값과 실제 실행 되는 자원의 값은 다르다
(mysql은 page가 메모리에 있는지 디스크에 있는지 알지 못한다.)
3.mysql은 최고로 빠른속도를 지향하지 않고 최소의 코스트를 지향한다.
4.해당 쿼리외에 동시에 실행 되는 다른 쿼리들이 영향을 줄수 있다
5.where 절에 특정 키워드가 있을 경우 최적화 안한다(FULLTEXT MATCH 등)
6.시스템 펑션이나, 유저 펑션등 mysql이 측정할수 없는애들도 있다
7.가능한 모든 플랜을 만들수 있는건 아니다. 즉 최적의 플랜을 못만들수도있다

최적화의 종류
static
parsing 할떄 where 절을 대수학의 동일 식으로 변경
dynamic
where절, 테이블 또는 인덱스의 로우 등에 따라 변경

*store procedure OR prepare statment를 실행 시킬떄
static 은 한번 하지만 dynamic늘 다시 한다.
최적화 할때 하는짓
reordering joins
테이블 조인 순서 변경
outer join to innerjoin
innerjoin으로 풀수 있다면 변경

필요없는 where 조건 정리
ex) where 5 = 5 and a>5 -> where a>5

COUNT(), MIN(), and MAX() optimizations
min일 떄 인덱스 오른쪽 끝값 읽음 등
해당 최적화가 이루어지면 아래처럼 표현됨
EXPLAIN plan : Select tables optimized away

커버링 인덱스

서브쿼리 최적화

Early termination
쿼리가 실행되는 도중에 멈출수 있다
LIMIT
DISTINCT, NOT EXISTS(), and LEFT JOIN

Equality propagation
SELECT film.film_id
-> FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE film.film_id > 500;

film, film_actor에 둘다 film_id > 500을 적용한다.
*현재는 MySql만 지원하는 기능
IN
MySql에서는 In안의 벨류를 정렬시킨 후
바이너리 검색을 한다.
OR의 경우 O(n)
MySql 이 바이너리 검색을 할경우 O(log n)

옵티마이저는 똑똑하니 이길려고 싸우지 말고 옵티가 잘 못하는게 있음
그부분을 수정해라

*테이블, 인덱스의 통계정보는 storage engine이 가지고 있다!(서버가 아님!!)

MySql에서 join 이라는 단어는 범위가 넒음
모든쿼리를 뜻함(한테이블에 날리는 싱글 쿼리는 조인)

why join이라고 하는가

모든 쿼리를 아래같은(수도코드) 코드로 실행 하기 때문에

mysql> SELECT tbl1.col1, tbl2.col2
-> FROM tbl1 LEFT OUTER JOIN tbl2 USING(col3)
-> WHERE tbl1.col1 IN(5,6);

outer_iter = iterator over tbl1 where col1 IN(5,6)
outer_row = outer_iter.next
while outer_row
inner_iter = iterator over tbl2 where col3 = outer_row.col3
inner_row = inner_iter.next
if inner_row
while inner_row
output [ outer_row.col1, inner_row.col2 ]
inner_row = inner_iter.next
end
else
output [ outer_row.col1, NULL ]
end
outer_row = outer_iter.next
end

* inner it는 필러링이 됨으로 == 조건으로 무조건 밖에 숫자가 작은게 유리하다!
조인시 로우수가 적은 테이블이 outer 에 있어야한다.
즉 driving 되는 테이블이 로우 수가 적어야된다!

*서브 쿼리일경우
서브쿼리를 먼저 실행시키고 그걸 temp table로 만든후(그래서 derived table!)
다시 위 루틴을 적용 시킨다.
*right일 경우는 전부 left 쿼리로 바꿈
*full outer join 이 안되는 이유임

Mysql은 쿼리를 실행 시키기 위해서 byte-code를 만들지 않는다.
EXPLAIN EXTENDED SELECT * FROM sakila.film A inner join film_actor B using(film_id);
SHOW WARNINGS;

***** 을 하게되면 최종적으로 실행된 쿼리를 확인 할수 있따!!!
어떤 쿼리도 트리로 나타낼수 있다
Mysql은 무조건 테이블을 조인해 하나의 테이블로 만들고 그걸또 조인시킨다

ex)
select * from A, B, C, D where~
일경우
A,B JOIN->temp1 ,C JOIN-<temp2 ,D JOIN->result set

*옵티마이징에서 가장 중요한건 조인 순서를 정하는거다
STRAIGHT_JOIN 순서대로 조인 하라는 힌트절
SELECT STRAIGHT_JOIN * FROM A

n 테이블을 조인할 경우 plan은 n!이 나온다
즉.. 10개 만 조인 하면 362800이 나온다..-> 즉 느려진다.
optimizer_search_depth 로 조절 가능

옵티마이징 안되는 쿼리들
left join
subquery
결과가 나와야 조인을 시킬수 있음으로

sort optimization
메모리를쓰던 안쓰던 file sort 라고 표시된다

file sort 종류
Two passes (old)
1.row point와 order by 컬럼을 읽고
2.order by로 정렬하고
3.정렬된 순서대로 reread 한다.

느린이유
1.테이블에서 row 를 두번 읽는다
2.다시 읽을때 random I/O가 발생할 가능성이 높다
single pass (new)
1.쿼리에 필요한 모든 컬럼을 읽고
2.정렬하고
3.result 필요한 컬럼만 선택후 출력

단점
많은 데이터를 읽어 함으로
file에 쓰고 merge 하는 작업을 해야 한다.

MySql은 filesort 할때 많은 공간이 필요하다
why?
왜냐하면 정렬시 고정 된 길이의 레코드를 사용하기 때문에
1.가장큰 길이의 레코드에 의해 결정된다.
2.varchar의 경우 최대 길이로
3.utf8로 되었을 경우 무조건 3바이트를 할당함

그래서 템프 테이블은 실제 디스크에 저장된 테이블의 전체 용량보다
더큰 경우도 많다.

첫번재 테이블로 sort 가 결정될 경우 정렬후 조인 Using filesort
결과같으로 정렬할 경우 Using temporary Using filesort
*limte가 외부에 설정되었어도 만들고 하는거기때문에 별 쓸고가 없다

쿼리 실행 엔진
옵티마이징 끝나면 execution plan 이 나온다
그냥 이 plan 대로 storage engine 에 api를 호출한다.

returning results to the client
만약 캐쉬가 on 되어있으면 이때 적재

서버는 result 가 생성되면 첫줄부터 클라이언트로 보낸다(지속적으로)
why?
서버 메모리에 적재하지 않아 메모리 사용량을 줄일라고

mysql 옵티마이저의 한계
상관관계 서브쿼리
SELECT * FROM sakila.film
WHERE film_id IN(
SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);

이렇게 풀릴거라고 예상한다.
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);

한대 직접 SHOW WARNINGS로 보면
SELECT * FROM sakila.film
WHERE EXISTS(
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);

이렇게 풀려있다 - -...
만약 film테이블이 크다면.. 답안나온다.

서브쿼리 변환 방법
DISTICT 나 group by 를 써야 한다면 EXIST 를 고려하자

SELECT DISTINCT film.film_id FROM sakila.film
INNER JOIN sakila.film_actor USING(film_id);

SELECT film.film_id FROM sakila.film
WHERE EXIST(SELECT * FROM sakila.film_actor
WHERE film.film_id = film_acotr.film_id)

Parallel Execution
하나의 쿼리를 여러개의 cpu 에서 병렬로 처리하는거 지원안함
Query Optimizer Hints
우선순위 변경
InnoDB등 락킹 잘관리하고 MVCC 하는애들에는 안쓰는게 좋음
MyISAM 에 조심
concurrent insert에 매우 않좋은 영향을 줄수 있음

HIGH_PRIORITY
해당 쿼리를 큐의 맨앞부분으로 이동(lock 우선순위 변경)

LOW_PRIORITY
해당 쿼리를 큐의 맨뒤부분으로 이동(lock 우선순위 변경)
기아 상태에 빠질수 있음

DELAYED
INSERT, REPLACE 문에 사용가능
클라에서 INSERT 오면 ok 응답 보내주고
BUFFER에 저장 후 테이블이 FREE 상태일때 INSERT

BULK INSERT를 위해 만들어짐
각각의 stmt에 I/O를 피할수 있음
*LAST_INSERTED_ID 사용 못함

STRAIGHT_JOIN
순서대로 조인하게 강제
*옵티가 오래걸릴(조인 테이블이 많을 경우)떄 사용하면 좋음

결과량
for SELECT, temp table, GFOUP BY, DISTINCT 할때
어떤 플랜을 짜면 좋은지 옵티에게 알림
SQL_SMALL_RESULT
작은 결과가 리턴될거임
SQL_BIG_RESULT
큰결과과 리턴될거임
그러니 디스크에 테이블을 만드삼
SQL_BUFFER_RESULT
쿼리 결과를 나오는대로 클라로 흘리는게 아니라
서버 메모리에 저장 후 결과 다 나오면 리턴
(즉 클라 메모리가 아닌 서버 메모리에 저장)
SQL_CACHE, SQL_NO_CACHE
cache에 적재 시킬래? 안시킬래
SQL_CALC_FOUND_ROWS
LIMIT 가 있어도 무저건 전체 로우를 사용
왠만하면 사용말자
FOR UPDATE and LOCK IN SHARE MODE
for SELECT
로우레벨 락킹을 지원하는 엔진에 쓸수있음
정말 잘 알지 못하고 사용하면 심각한 락 문제를 가져올수 있음

USE INDEX,  IGNORE INDEX and FORCE INDEX
오더에 INDEX 사용하기 위해
FOR ORDER BY, FOR GROUP BY 힌트 사용 가능

USE INDEX
왠만하면 인덱스 쓰렴
FORCE INDEX
무저건 INDEX 쓰렴

*아래 부터는 서버 옵션임
optimizer_search_depth
옵티가 만들수 있는 플랜들 조정(낮게 설정)
크게하면 가능 플랜 만드느라 느려짐
optimizer_prune_level
옵티는 테이블의 로우수에따라 특정 쿼리를 사용하지 않게 되어 있다
default enabled
optimizer_switch
인덱스 관련 상세 옵션
ex)
index merge query plan 쓸래 등

*힌트는 왠만하면 쓰지 말자
1.옵티는 겁나 많은 사람들이 만든거다 즉 똑똑하다
2.MySql 버전 upgrade할때 힌트가문제가 될수있다
*버전 업그래이드시 pt-upgrade 툴을 쓰면
해당 쿼리들이 새 버전에서도 문제 없이 동작하는지 확인 할 수 있다

특수 타입 쿼리 튜닝
*버전마다 방법이 다르다

COUNT Queries
카운트가 하는 종류
1.벨류들에서 NULL을 제외한 벨류를 센다
2.결과에서 로우 수를 샌다
COUNT(*)

*로우를 세고 싶다면 COUNT(*)을 사용(칼럼명 쓰지 말아라!)


MyISAM의 카운트가 빠르다?
노우
단 전체 로우수를 셀대만
where 절이 있으면 안됨

꼼수
정확한 숫자의 카운트가 필요없다면
explain에서 가져오는것도 방법
JOIN Queries
1.where 컬럼에 들어가 있는 애는 인덱스가 있는지 확인
2.group by, order by만 사용해라록 해라(\
그럼 메모리에서 할수 있다.

sub Queries
최대한 조인으로 변경해라

GROUP BY and DISTINCT
둘다 비슷한 기능을 하며 내부적으로 옵티 할때는 MySQL 내부에서 자주 바꿔쓴다.

SQL_BIG_RESULT and SQL_SMALL_RESULT를 사용해라

만약 LOOK up 테이블에서 벨류를 가져와 그값으로 조인해야 할때는
group by에 look up table 컬럼 을 쓰는게 더 좋은 방법이다.

만약 정렬할필요가 없는대 정렬이 일어 난다면
자동 정렬을 피하기 위해서
ORDER BY NULL 사용

WITH ROLLUP
원하지 않는 실행 계쇡이 나올때가 많다
가능하면 어플리케이션 코드로 해결하자

LIMIT and OFFSET
인덱스를 안타면 file sort하게 된다.

LIMIT 10000,200 하게되면 10200 로우를 가져와 10000 로우를 버린다
이럴때는 좀더 최적화한다.

1.deferred join 이라고 도 부른다.

SELECT film.film_id, film.description
-> FROM sakila.film
-> INNER JOIN (
-> SELECT film_id FROM sakila.film
-> ORDER BY title LIMIT 50, 5
-> ) AS lim USING(film_id);

2.미리 페이지 별로 인덱스를 만들고 인덱스를 조건으로 타는 방법도 있다

SELECT film_id, description FROM sakila.film
-> WHERE position BETWEEN 50 AND 54 ORDER BY position;

3.삭제가 되지 않는다면 AUTO_INCREMENT PK는 증가만 한다는걸 사용가능하다.

mysql> SELECT * FROM sakila.rental
-> ORDER BY rental_id DESC LIMIT 20;

This query returns rentals 16049 through 16030. The next query can continue from
that point:
mysql> SELECT * FROM sakila.rental
-> WHERE rental_id < 16030
-> ORDER BY rental_id DESC LIMIT 20;


*가장좋은건 아무리 깊이 페이징을 해도 별 영향이 없다

4.여분의 테이블을 만든다.
ORDER BY에서 필요한 컬럼들로만 만들어진
그후 원테이블이랑 조인 때린다.

5.클라쪽에서 페이지 크기보다 큰 로우를 받고 20개 보일때 1000개를 받음
그냥 보여주다가 1000개 넘어가면 요청하는거


*토탈 페이지수를 EXPLAIN에서 가져오는거(구글도 토탈 페이지를 ++ 표시)

UNION
**Mysql은 무조건 템프테이블 만든다.(결과를 순차적으로 클라로 안보낸다!)

UNION ALL을 써라!

UNION만 쓴다면 DISTICT 를 하게 되는대
템프테이블은 인덱스가 없기 때문에 전체 테이블에 대해서 유니크를 조사해야한다.

Static Query Analysis
pt-query-adviosr 를 사용해라
대략 나쁜 패턴 이라고 생각 되는 쿼리를 뽑아 낼수있다

Using User-Defined Variables P256
유저 정의 변수 == 컨넥션이 살아 있을때까지 살아 있음

RDB는 전체를 set으로 동시처리한다 하지만 유저 변수를 쓰면 순차적 처리도 가능하다

단점
*사용하면 쿼리 캐쉬 사용못한다.
*컨넥션당이다 inter컨넥션에서 안된다.
*컨낵션 풀링이나 영속적 연결 사용하면 문제 될수 있다
*옵티가 제거해버릴수도 있다
* := 의 할당 연산자는 우선순위가 가장 낮다

예제
유저 변수에 할당되는 순서는 순차적이지 않다
그러므로 할당 후 할당되야 되는 애들은 서브쿼리로 빼야된다.

mysql> SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
-> SELECT actor_id,
-> @curr_cnt := cnt AS cnt,
-> @rank := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS rank,
-> @prev_cnt := @curr_cnt AS dummy
-> FROM (
-> SELECT actor_id, COUNT(*) AS cnt
-> FROM sakila.film_actor
-> GROUP BY actor_id
-> ORDER BY cnt DESC
-> LIMIT 10
-> ) as der;

조금전에 변경된 로우의 값을 리턴한때 사용가능(UPDATE RETURNING)
UPDATE t1 SET lastUpdated = NOW() WHERE id = 1 AND @now := NOW();
SELECT @now;

INSERT ON DUPLICATE KEY UPDATE 할때 몇개의 로우가 듑에러가 나서 업데이트 되었는가?
INSERT INTO t1(c1, c2) VALUES(4, 4), (2, 1), (3, 1)
ON DUPLICATE KEY UPDATE
c1 = VALUES(c1) + ( 0 * ( @x := @x +1 ) );

*벨류는 업데이트 되지만 @x에 더하고 0을 곱하므로 카운트는 올라가고 실제 값은 변하지 않는다.


유저변수는 적용되는 부분에 따라 다르게 배치된다. where 절 걸리고 select 되고  order by걸리는것처럼
*안되는거 볼래면 EXPLAIN 의 EXtra 컬럼을 확인해라
동작안함

mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum := @rownum + 1 AS cnt
-> FROM sakila.actor
-> WHERE @rownum <= 1
-> ORDER BY first_name;

동작됨
mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum AS rownum
-> FROM sakila.actor
-> WHERE (@rownum := @rownum + 1) <= 1;

이런방식으로 hack해서 사용하기도 한다.
mysql> SET @rownum := 0;
mysql> SELECT actor_id, first_name, @rownum AS rownum
-> FROM sakila.actor
-> WHERE @rownum <= 1
-> ORDER BY first_name, LEAST(0, @rownum := @rownum + 1);

GREATEST(), LENGTH(), ISNULL(), NULLIF(), IF(), COALESCE() 를 사용할수있다
Building a Queue Table in MySQL
queue table을 하나만 만들면 시간이 지날쓰록 쓰기 힘들다
그렇기 히스토리 테이블을 따로 만들자

워커 프로세스가 쉴때
SELECT /* waiting on unsent_emails */ SLEEP(10000);
이렇게 하고 process list 에서 커먼트가 있음 죽인다.

이메일을 보내는 큐테이블을 만들어 보자~
CREATE TABLE unsent_emails (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-- columns for the message, from, to, subject, etc.
status ENUM('unsent', 'claimed', 'sent'),
owner INT UNSIGNED NOT NULL DEFAULT 0,
ts TIMESTAMP,
KEY (owner, status, ts)
);

owner = connection process id임 (CONNECTION_ID()값이 들어감)
0이면 자기꺼라고 주장한애가 없는것

내일임 주장하기(락최소화)

SET AUTOCOMMIT = 1;
COMMIT;
UPDATE unsent_emails
SET status = 'claimed', owner = CONNECTION_ID()
WHERE owner = 0 AND status = 'unsent'
LIMIT 10;
SET AUTOCOMMIT = 0;


SELECT id FROM unsent_emails
WHERE owner = CONNECTION_ID() AND status = 'claimed';
-- result: 123, 456, 789

*SELECT FOR UPDATE 왠만하면 쓰지 말고 위에 꺼처럼 써라

문제가 생겨 워커가 주장만 하고 처리 못한거 처리하기
SHOW PROCESSLIST
현재 일 처리하는 애들의 id얻어와서

UPDATE unsent_emails
SET owner = 0, status = 'unsent'
WHERE owner NOT IN(0, 10, 20, 30) AND status = 'claimed'
AND ts < CURRENT_TIMESTAMP - INTERVAL 10 MINUTE;

문제생긴놈꺼 풀어주기

*인덱스에 주목해라 (owner, status, ts)
ts 가 마지막에 있기때문에 range 까지 쓸수 있다

Computing the Distance Between Points
P258
MySQL에서 거리 계산하기

Using User-Defined Functions
C나 C++에 아주 익숙하다면 펑션을 만들어서 써도 된다.
























































high_performance_mysql_3rd_edition 스키마 데이터 타입 튜닝

최적의 데이터 타입 선택
1.한계값을 넘지 않는 가장 작은 데이터 타입을 선택해라
2.간단한 데이터 타입을 사용해라
스트링 보다는 int가 빠르다
날짜 기록에는 date type을 사용해라
ip를 저장하려면integer를 사용해라

*스트링 타입은 character set, collations 이 있다(정렬 룰)
3.널 값을 가능하면 피해라
널이 들어가면 쿼리를 옵티마이직 할때 더많은 코스트가 필요하다
널값을 의도 하지 않는 다면 not null을 사용해라

*이노디비는 널을 위해 추가 공간을 사용하지 않음
마이이삼은 함

데이터 타입
넘버
INTEGER, BOOL, NUMERIC 그냥 INT 다 ALIASES 일뿐
정수 bit
TINYINT 8
SMALLINT 16
MEDIUMNTINT 24
INT 32
BIGINT 64

UNSIGNED 를 사용하면 양수 범위 넒어짐(성능 차이는 없음)

INT(1) 과 INT (20)은 완벽하게 동일하다 (화면에 표시되는것만 다름)

실수
DECIMAL 실수를 위한거지만 BIGINT로 범위가 안되면 쓰면된다.
*정확한 소수점을 위해 사용 연산이 크기때문에 꼭 필요할 경우 사용
FLOAT
DOUBLE

DECIMAL(18,9)
전체 18 자리 = 실수 9 + 소수 9
최대 전제자리 (65숫자까지)

연산은 내부적으로 DOUBLE로 바꿔서 처리

*가능하다면  0.0001 * 1000 = 1등 INT 로처리할수있으면 그렇게 하라

스트링 타입
VARCHAR
실제 벨류 만큼만 저장해 저장공간 효율에 좋음
예외 :MyISAM table created with ROW_FORMAT=FIXED,

VARCHAR(10) 을 하게 되면 마지막에 1~2바이트의 길이 데이터가 들어가
실제적으로 11~12의 공간에 저장

여유공간이 이상을 UPDATE 하게 되면
MYISAM row fregmention
InnoDB page split 일어남!

*실제 최대 벨류보다 평균 벨류가 훨씬 작을때 사용하는게 좋다.
*업데이트가 적어야 좋다.

CHAR
CHAR VARCHAR 는 로직적으로 동일하나 저장포멧이 다른 뿐이다.

아주 작은 길이거나 평균 길이가 거의 비슷할때 좋다.

*CHAR(1) 한바이트사용 , VARCHAR(1) 길이때문에 2바이트 사용

*업데이트가 많은 칼럼으 경우는 CHAR 를 쓰자


바이너리 스트링을 저장할때
BINARY and VARBINARY

*비교할때 스트링이 아닌 바이너리로 비교하면 좋다.
*만약 비교하면 1바이트씩 비교하며 스트링 비교 보다 많이 빠르다

VARCHAR(5) VS VARCHAR(20)
메모리에 저장할때는 무조건 VARCHAR(20)의 메모리를 할당 받음으로 메모리 낭비가 생길 수 있다
그럼으로 가장 적절한 숫자를 쓴다.

BLOB
TEXT
이노디비는 테이블이 아닌  다른 부분에 저장
*다른 점은 blob은 저장 될때 CHARACTER SET, COLLATION이 없다는것
TEXT TYPE은 둘다 있음

*max_sort_length 솔트시 특정 길이로만 솔팅

쿼리에서 BLOB(or TEXT)을 사용다면 메모리디비에서 해당 타입을 지원하지 않기때문에
무조건 디스크에 임시테이블을 만들어야 된다.
(피하는 방법SUBSTRING(column, length) 을 하면 스트링으로 변해 메모리 사용가)

단 varchar 나 위의 트릭을 사용해도
max_heap_table_size or tmp_table_size 보다 사이즈가 크면 디스크에 쓴다.
ENUM
CREATE TABLE enum_test(
e ENUM('fish', 'apple', 'dog') NOT NULL
);

INSERT INTO enum_test(e) VALUES('fish'), ('dog'), ('apple'),(1);

SELECT e  FROM enum_test;

result ->
e
fish
dog
apple
fish

실제로는 숫자를 저장하지만 외부에는 스트링으로 노출된다.
* 하지만 정렬하게 되면 숫자 자체로 정렬된다.(스트링으로 정렬안됨)
필요하다면 이렇게 가능
SELECT e FROM enum_test ORDER BY FIELD(e, 'apple', 'dog', 'fish');

이늄에 값을 뒤에추가 할수는 있으나 거의 고정적인 값에만쓰자
varchar랑 enum 랑 조인 할 수는 있으나 enum이 스트링으로 형변환을 해야한다. 조금 느리다.


Date and Time Types
DATETIME
1001 year ~ 9999year 까지 표현가능
저장 될시 인티저로 YYYYMMDDHHMMSS로 저장됨 (8바이트)
TIMESTAMP
4바이트
1970 year ~2038 year 까지 표현가능
타임존을 사용하기 때문에 여러 나라에서 사용하기 좋음
*null 값으로 설정하면 mysql에서 현재 시간 집어 넣는게 디폴트 not null임

가능하다면 TIMESTAMP를 사용해라
만약 마일 세컨드를 저장하고 싶다면 BIGINT 나 DOUBLE을 사용해라

BIT-Packed Data Type
BIT(1)
5.0이상에서는 실제 1비트임 ture/false로 좋음
MyISAM에서는 실제 1비트(널값 허용안할시)
InnoDB, Memory는 TINYINT로 취급

mysql에서는 비트를 숫자로 취급

mysql> CREATE TABLE bittest(a bit(8));
mysql> INSERT INTO bittest VALUES(b'00111001');
mysql> SELECT a, a + 0 FROM bittest;

a | a + 0 |
+------+-------+
| 9 | 57

ASC2코드에서 57은 "9"임

*사용하지 말자 TRUE/FALSE 할꺼면 nullable CHAR(0)사용
""면 TRUE NULL이면 폴스
SET 도있지만 사용하지 말자

키값 컬럼
Integer
빠르다 A_I쓸수있어 좋다
String
가능하면 피해라
MyISAM은 스트링을 packed index로 디폴트로 만들어 느리다
MD5,UUID등은 좋지않다 P130참조

프로그램에서 ORM 쓰면 디비가 개판 날 가능성이 매우 많다.

MySql에서 스키마 짤때 조심해야 되는거!
TooManyColumn
Mysql은 엔진에서 던져준 컬럼을 디코딩해서 사용한다.
즉 컬럼이 100개 이상 되면 엄청 느려질수 있다.
TooManyJoin
조인 이 더 좋다고 하지만 Mysql에서 조인의 한계는 61개며
조인이 많으면 느려진다!

높은 동시성과 빠른 속도를 원하면 12개 정도 조인으로 결과값이 나오게하자
The all-powerful ENUM
mysql에서 이늄으로 오바하지 말자

Null 왠만하면 쓰지말자
꼭 필요하면 쓰고 아니면 대처하자
MySql에서index 만들면 null값도 인덱싱 된다(오라클과 다르게)


Normalization and Denormalization

정규화 장점
업데이트가 많으면 정규화 하는게 좋다
정규화 테이블이 일반적으로 조금하다 즉 메모리에 올라감으로 빠른경우가 많다.
정규화 테이블이 DISTINCT 나 GROUP BY를 사용할때 훨빠르다

정규화 단점
조인 해서 느리다.
하나의 인덱스로 커버 될수 있는 데이터를 여러개의 테이블로
쪼갤 수있다

비정규화 장점
조인 하는 테이블이 커서 메모리에 못올라올경우 full table 리드가 훨빠르다
(랜덤 IO가 없으니까)
인덱스 사용 계획에서 훨 빠를수 있다.
P136

프리미엄 유저가 쓴글 선택 LIMIT 0,10

정규화 되어 테이블이 두개면
A.날짜로 긁고 유저 조인해서 프리미엄인지 확인
또는 B.프리미엄 유저 같고와서 글과 조인

비정규화로 글테이블에 유저의 프리미엄 여부가 있으면
걍 읽으면됨


어떻게 할꺼냐?
select 와 upsert의 비율을 보고 필요한대로
믹싱해서 써라

조인에 꼭 필요한 컬럼이 있으면 디 노말 라이징 해라
derived 컬럼도 필요하면 컬럼으로 만들어라
eX)유저당 게시글수

Cache and Summary Tables
필요한 데이터만 뽑아서 다른 엔진에 캐쉬 테이블을 만들수있다
ex) fulltext search

캐쉬 또는 서머리 테이블 갱신할때 atomic으로 할수있다.

mysql> DROP TABLE IF EXISTS my_summary_new, my_summary_old;
mysql> CREATE TABLE my_summary_new LIKE my_summary;
-- populate my_summary_new as desired
mysql> RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary;

Materialized Views
mysql에서 지원하지 않지만 http://code.google.com/p/flexviews/
라는 오픈 소스가 있음 추가하면됨

데이터 변경, 그룹바이 지원함
*원본테이블에 로우 추가되면 통계뷰에서  바이너리 로그에서
가져와 +1한다고함


카운팅 테이블
여러개 열로 분리해 mutex 줄이고 나중에 섬
mysql> CREATE TABLE hit_counter (
-> slot tinyint unsigned not null primary key,
-> cnt int unsigned not null
-> ) ENGINE=InnoDB;

mysql> UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;

mysql> SELECT SUM(cnt) FROM hit_counter;

ALTER TABLE
*걍 slave에서 만들고 마스터로 교체

mysql은 alter 하면 새로운 테이블 만들고 예전데이터를 전부 밀어 넣는다.
(https://launchpad.net/mysqlatfacebook),
Shlomi Noach’s openark toolkit (http://code.openark.org/),
and Percona Toolkit (http://www.percona.com/software/).
Flexviews (discussed in “Materialized Views” on page 138)

를 사용하면 non block 으로 할수있다
아님 shadow copy를 사용하던가

MODIFY 쓰면 리빌딩한다.
ALTER TABLE sakila.film
-> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

ALTER COLUMN 하면  그냥 .frm 파일만 건드려 빠르다
ALTER TABLE sakila.film
-> ALTER COLUMN rental_duration SET DEFAULT 5;

* 비공식적이라서 위험함
백업뜨고 인덱스 빠르게 만들어야 될때
P144 확인

high_performance_mysql_3rd_edition 서버퍼포먼스

퍼포먼스
1.하나의 작업을 완료한 시간으로 측정
퍼포먼스는 응답시간이다

옵티마이징
1.하나의 작업 시간을 최소한으로 걸리게 함

잘못된 생각
속도를 위해  리소스 소비를 키울수도 있다

원칙
1.퍼포먼스는 응답시간이다
응답시간을 줄이기 위해서는 쿼리 처리 도중 어디에서 부하가 걸리는지 확인 하는것
2.내가 측정 할수 없는걸 옵티마이징 할수 없다.
3.옵티마이징 자체가 중요한게 아니라 쿼리가 어느부분에 지체 되는지 확인이 중요
옵티마이징 시간의 90%이상을 이부분에 사용
작업시간이 적게 걸리는 일은 튜닝 할필요가없다.(튜닝도 조금 밖에 안되기 때문)

프로파일링?
프로 파일링 이란 어디서 시간이 소비 되는지 분석 하는 작업이다.
1.작업을 측정하고
2.걸리는 시간을 측정한다.
3.정보를 분석해서 문제를 찾아낸다.

작업 시간 순으로 정리한 후 오래 걸린 순으로 정렬한다.

시간이 걸리는데는 크게 2가지로 생각 할수 있다.
1.실행 시간 자체가 긴것
2.오래 기다려서 느린것

프로파일링 툴의 한계점
1.어떤 쿼리를 튜닝하는게 효과적인지 알려주지 않는다.
슬로우 쿼리라고 꼭 튜닝 해야 하는건 아니다
가장 많은 영향력을 가진 쿼리를 선별해서 튜닝해야 된다.
2.lost time 이 있을 경우 확인 하라
실제 실행시간 - 프로파일러에 측정된 시간  = lost time
당신이 어떤 부분을 측정 하지 않고 있다는 뜻
3.평균을 신용하지 말라
반은 정말 빠르게 실행되고 반은 절망 느리게 실했된다면?
잘못된 정보를 습득하게된다.
4.한계의 쿼리는 상황에 따라 느리거나 빠를수 있다

어플리케이션 프로파일링
리스판스가 느릴때는 데이터베이스가 보틀넥일수도 있지만
다른대일수도 있다
1.외부 자원접근
call web service or search engine
2.많은 시간을 소모 하는 작업
ex) parsing big XML file
3.비싼 작업
ex) req expression
4.잘못된 알고리즘

어플리케이션을 만들때 프로파일링 코드를 삽입해라
물런 프로파일링 코드가 오버해드를 읽으킬수 있지만
그결과로 옵티마이징 할때 얻는 이익이 더 크다(라고 저자는 주장)
*전부 다하지 기록 하지 말고 랜덤으로 뽑아서 해라

PHP 일때
xhprof.php 좋단다(xdebug, valgr)

MySQL Enterprise Moniter 좋단다. 기획쪽 참고하기위해 한번 보자

프로파일링 Mysql queries
방법은 2가지가 있다.
1.전체 서버를 프로파일링 하여 어떤 쿼리가 가장 많은 load 가 걸리는지 확인 하는거

Mysql queries 의 슬로우 쿼리를 로그로 저장하는 방법
long_query_time 을 on 함으로서 슬로어 쿼리에 시간을 추가 할수 있다.
slow query 는 오버해드가 거의 없기 때문에 늘 켜놓고
로그를 순환으로 만들어라 A,B,C 를 순환하면서 쓰게(아니면 디스크를 꽉채운다)
C:\ProgramData\MySQL\MySQL Server 5.5\my.ini

log-output = FILE//로그를 파일에 쓰겠다 * 테이블도 가능
general-log = ON
general-log-file = gen.log
long_query_time = 0 //(10~1) 0으로 하게 되면 모두찍음
slow_query_log_file = slow.log
slow_query_log = ON
event-scheduler = ON //이벤트 스케쥴러 사용여부 default off
log-queries-not-using-indexes<--요런 놈도 있음

로그파일
C:\ProgramData\MySQL\MySQL Server 5.5\data에서 확인가능
물론 테이블에 적게 할수도있지만 퍼포먼스 많이 떨어진다.(로깅과 비교해 약 1000배)

쿼리를 서버에 적지 못할때 사용하는 방법
1.pt-query-digettool에서 지속적으로
SHOW FULL PROCESSLIST 를 호출하여 쿼리가 있으면 시작 사라지면 걸리시간 측정 하는것
connection 단위로 리스트에 나타
하나의 컨넥션에서는 당연이 하나의 쿼리만 execute 가능함
2.TCP network 트래픽을 저장하는 방법
tcpdump -> pt-querydigest --type=tcpdump

pt-querydigert 보는방법
Rank

QueryId
쿼리를 이름을 해시값으로 만든것
(literl value remove, remove white space, all lower case)

response

Item
정규화된 이름
V/M varince -to -mean ratio (분산 평균 비율)
V/M = 분산x분산/평균
값이 클수록 차이가 많이 나는 애임으로 튜닝 하기 좋은 후보




high_performance_mysql_3rd_edition 벤치마크

주의사항
1.실제와 동일한 데이터 사용
2.변수 쿼리 사용
3.벤치 마킹 후 로깅 확인(에러 일 경우)
4.시스템이 웜된상태에서 시작
5.너무 빠른 시간에 하지 말것

벤치마크 계획
1.문제와 골을 정함
2.데이터 베이스 스냅샷을 떠서 다시 할때 동일조건으로 할수있게
3.로깅에 남은 실제 쿼리를 가져다가 벤치 마크(1일~ 1주등)-멀티 쓰레드로
4.얼마나 해야 되는가?
잘모르면 스테이블 해질때까지 무작정 돌림
5.오랜시간 하지 않을꺼면 그냥 다른 사람들 결과를 확인 하는게 좋을 수도 있음
6.벤치 마크를 할대마다 폴더를 만들고 아무 로우 데이터를 다 집어 넣어라
*나중에 문제 생기면 로우 데이터가 짱이다
7.스크립트를 짜서 돌린다.
#!/bin/sh
INTERVAL=5
PREFIX=$INTERVAL-sec-status
RUNFILE=/home/benchmarks/running
mysql -e 'SHOW GLOBAL VARIABLES' >> mysql-variables
while test -e $RUNFILE; do
file=$(date +%F_%I)
sleep=$(date +%s.%N | awk "{print $INTERVAL - (\$1 % $INTERVAL)}")
sleep $sleep
ts="$(date +"TS %s.%N %F %T")"
loadavg="$(uptime)"
echo "$ts $loadavg" >> $PREFIX-${file}-status
mysql -e 'SHOW GLOBAL STATUS' >> $PREFIX-${file}-status &
echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
mysql -e 'SHOW ENGINE INNODB STATUS\G' >> $PREFIX-${file}-innodbstatus &
echo "$ts $loadavg" >> $PREFIX-${file}-processlist
mysql -e 'SHOW FULL PROCESSLIST\G' >> $PREFIX-${file}-processlist &
echo $ts
done
echo Exiting because $RUNFILE does not exist.
8.OS에서 다른잡이 도는지 안도는지 꼭확인해라
9.네트워크 트래픽도 확인해라
10.벤치마킹을 자동화 하라
11.결과값을 모으는 스크립트도 자동화 하라
#!/bin/sh
# This script converts SHOW GLOBAL STATUS into a tabulated format, one line
# per sample in the input, with the metrics divided by the time elapsed
# between samples.
awk '
BEGIN {
printf "#ts date time load QPS";
fmt = " %.2f";
}
/^TS/ { # The timestamp lines begin with TS.
ts = substr($2, 1, index($2, ".") - 1);
load = NF - 2;
diff = ts - prev_ts;
prev_ts = ts;
printf "\n%s %s %s %s", ts, $3, $4, substr($load, 1, length($load)-1);
}
/Queries/ {
printf fmt, ($2-Queries)/diff;
Queries=$2
}
' "$@"
12.평균치는 결과를 뭉떵그리기때문에 시간별 그래프를 만들어라
ex)gnuplot

high_performance_mysql_3rd_edition 레플리케이션

레플리케이션을
속도로만 생각하지 말자
재해복구, 백업, 통계, 데이터 웨어 하우싱 등으로도 사용가능
싱크 방법
특징
*비동기임
동기화에 얼마나 걸릴지 아무도 보장하지 않음

새로운 버전의 서버는 예전 서버를 복제 할수있음
반대는 안됨~
*마이너 버전끼리는 됨
마스터에는 많은 무리를 안줌

하는법
마스터 binary log 를 enable 하면됨(이건 기본으로 킴)
순서
1.마스터가 데이터의 변경을 바이너리 로그에 저장
(binary log events 라고도함)
트렌젝션이 커밋 되기 바로직전에 저장
여러 트랙잰션이 동시에 일어나서 트랜잭션당 쿼리 순서가 바껴도
트랜잭션 순서대로 저장
저장후 commit ok 하라고 storage engine 에 알림

슬레이브가 연결을 요청하면 슬레이브당 Binlog dump thread 를 만들고 binlog 를 넘겨줌

2.레플리카가 binary log 를 relay log에 복사
레플리카가 I/O slave thread로 마스터에 연결
마스터의 Binlog dump thread에서 데이터를 요청하고 그 후 대기
동기화가 된후 부터는 binlog dump 가 이벤트를 날리면 데이터를 요청하는형식 같음

받아온 데이터를 로컬 relay log에 저장
*4.0이전 버전은 싱크 방법 다름
3.레플리카가 relaoy log 를 재생해서 변경 데이터 적용
sql thread가 relay log를 읽어서 적용
(병행으로 읽어서 데이터 반영 가능)
종류
statement-based replication
row-based replication

레플리카로 풀리는것들
데이타 분산
지역적으로 떨어진 데이터 센터에 위치
로드 벨런싱
백업
하지만 따로 백업도 하는게좋다.
고가용성 및 failover
it prevent single point of failure
새로운 버전 Mysql테스트
슬레이브로 만들고 테스트


레플리케이션 세팅방법
1.replication 계정을 각각의 서버에 생성
I/O thread드가 일반 클라 연결을 맺기 때문에 래플리케이션 전용 유저를 모두에 생성
GRAMNT REPLICATION SALVE, REPLICATION CLITENT ON *.* TO repl@'162.126.0.%' INDETIFID BY '비번'
2.마스터 and 레플리카를 설정
master 변경
my.cnf
log_bin = mysql-bin
server_id = 10(유니크 서버 아이디 10부터 시작하자why default value 가 1이거든)

재시작후
SHOW MASTR STATUS 해서 bin log 켜졌는지 확인
slave 변경
my.cnf
log_bin = mysql-bin
server_id = 12
relay_log = /var/lib/mysql/mysql-relay-bin
log_slave_updates = 1   #to make the replica log
the replicated events to its own binary log
read_only = 1

3.레플리카시작 후  마스터에게 접속해 복재 시작하라고 명령
쿼리 창에서
CHANGE MASTER TO MASTER_HOST='server1',
MASTER_USER='repl',
MASTER_PASSWORD='비번',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS = 0 #0부터 찾기 시작해서 맞추라는뜻

확인
SHOW SLAVE STATUS

시작
START SLAVE;

http://search.cpan.org/~gmax/MySQL-Sandbox-3.0.12/lib/MySQL/Sandbox.pm#Making_a_replication_sandbox
에서 쉽게 할수있다


마스터가 있는 상태에서 레플리케이션 추가
1.마스터 clone 후 init 하는 방법
1.master의 스냅샷을 만든다.
2.마스터의 로그 파일과
스냅샵 시점의 바이트 오프셋을 기록한다.
(이후 logfile coordianates 라고한다)
SHOW MASTER STATUS 로 확인 가능
3.스냅샷 시점으로 부터 마스터의 binary log가 존재해야한다.

2.복사 방법
cold copy
1.마스터 셧다운
2.파일을 레플리카로복사(appendix c에 효율적 복사 방법)
3.new binary log 설정 후 마스터 시작
4.CHANGE MASTER TO 로 레플리카 시작
*순간 서비스 안됨
warmcopy
myisam 만 사용한다면 가능
mysqlhotcopy(rsync)로 가능 chapter 15확인
mysqldump
innoDB만 사용한다면
덤프후 레플리카로 복사
레플리카에게 마스터 쫓아가라고 명령

mysqldump --single-transaction --all-databases --masteres --master-data=1 --host=server1 \| mysql --hoset=server2
만약 트랜잭션 지원테이블이 아니라면
--lock-all-table로 대처 가능
from another replica
1.mysqldump 에 --master-data 옵션 안됨
2.show slave status 로 binary log corrdinates 확인가능

*만약 복사대상의 레플리카가 마스터와 싱크가 맞지 않는다면 잘못된 데이터를 복사하게됨

3.추천하는 레플리케이션 설정
마스터
sync_binlog=1
트랜잭션이 일어나면 커밋전에 파일에 남김
서버 crash에도 로그 파일 보존됨
(슬레이브에는 설정하지 말것)

innoDB의 경우
innodb_flush_logs_at_trx_commit=1 # Flush every log write
innodb_support_xa=1 # MySQL 5.0 and newer only

바이너리 로그의 이름을 모든 디비가 같게 설정
log_bin=/var/lib/mysql/mysql-bin
슬레이브
relay_log=/path/to/logs/relay-bin
skip_slave_start
read_only

skip_slave_start 슬레이브가 crash 후에 자동 시작 되는걸 막음
read_only
슈퍼 유저및 레프리카는 쓸수 있음

sync_master_info = 1    # 5.5이상
sync_relay_log = 1 # 5.5이상
sync_relay_log_info = 1 # 5.5이상

만약 마스터랑 너무 차이가 나게 되면 로그 파일이 디스크 용량을 넘어갈수 있음
binlog는 크기 제한 가능함

아래 옵션을 설정하면 한계가 되면 멈추고 디비에 적용 후 지우고 다시 받음
*버그때문에 쓰지 말것
relay_log_space_limit


innoDB테이블 쓸것
myisam의 경우 레플이 crash 될경우 데이터 정합성에 문제 생김


레플리케이션 상세
statement-based replication(logical replication)
마스터에 적용되는 쿼리문을 저장

장점
쉽게 구현가능(복사 자체)
바이너리 로그가 적음(네트워크 트래픽도적음)
*스키마의 데이터 타입이 달라고 복제 가능
슬레이브를 마스터로 상승 할경우 더빠름


단점
*만약 스토어 프로시저를 쓸경우 statment-base 사용말것

CURRENT_USER()등의 특정 함수 복제 불가
store routine, trigger 등에 문제
*NOW()등은 timestamp 로 변경함
가장큰 문제점은
serializable 되서 실행되야 해서
락이 많이 걸림

row-based replication
(5.1이상)
실제로 변경된 데이터를 바이너리 로그에 저장

장점
모든 문장이 정확이 복사가능(쿼리에 따라 빠를수도 있음)
ex)
INSERT INTO summary_table(col1, col2, sum_col3)
-> SELECT col1, col2, sum(col3)
-> FROM enormous_table
-> GROUP BY col1, col2
서버 크래쉬시 복구가 쉬움
쿼리를 파싱하지 않기때문에 cpu 를 상대적으로 덜사용
에러가 날경우(마스터에는 존재 하나 슬레이브에 존재하지 않는 쿼리 업데이트)
바로 정지해서 데이터 정합성 유지하기가 좋음
slave_exec_mode 로 정지하지 않고 지속되게 할수있음

단점
로그를 보고 어떤 쿼리가 실행 되었는지 알기 힘듬
바이너리 로그가 커질수 있음(statment 와 비교)
ex)
UPDATE enormous_table SET col1 = 0;
문제가 생기면 대처하기 매우 힘듬(다큐먼트가 거의 없음)

mysql은 default 로 statment base 를 사용하나 안전하지
않다고 생각하면 row based 로 바꿔서 사용함
*세션당 binlog_format 을 변경함으로 레플리케이션 설정가능

레플리케이션 관련 파일 설명
데이터 디렉토리 또는 서버 디렉토리(버전 마다 다름)
ex) /var/run/mysqld

mysql-bin.index
바이너리 로그와 이름은 같음
바이너리 로그가 하드디스크에 어떻게 기록되어있는지 저장
mysql-realy-bin.index
슬레이브에서 위와 동일하게 동작
master.info
레플리카가 마스터에 접속하기 위한 정보
*text로 되어있음으로 보안상 루트만 보개 해야함

realy-log.info
릴레이로그를 어디까지 재생했는지에 대한 info

설정
expire_logs_days 로 로깅을 얼마나 저장할지 선택가능
설정안하면 디스크 다 채우는 수가 있음 꼭 설정할것

레플리카를 마스터로 사용하기
레플리카의 log_slave_updates 기능을 사용하여
슬레이브를 다른 슬레이브의 마스터로 사용가능

*server a unique server ID 신경 쓰지 않으면 제대로 동작안함
@@server_id 로확인가능

왜 재대로 동작안하는가?
SQL thread reads the relay log, it discards
any event whose server ID matches its own.

레플리케이션 filters
왠만하면 사용말자 뻑하면 레플께짐
P466 10-3 그림 확인

마스터 설정
binlog_do_db and binlog_ignore_db.
절대 설정하지 말것
만약 사용한다면
USE test;
mysql> DELETE FROM sakila.film;
아래 사용할경우 걍 테이블 명으로만 필터링함


레플 설정
replicate_*
테이블에 like 문 걸수 있음

특정 쿼리가 실행 안되게도 할수있음
SET SQL_LOG_BIN=0,

레플리케이션 Topologies
mysql replication rule
1.슬레이브는 하나의 마스터만 가직수 있다
2.모든 슬레이브는 각각의 unique server id 를 가져야한다.
3.마스터는 여러 슬레이브를 가질수있다
4.슬레이브는 다른 슬레이브의 마스터가 될수있다

1.Master and Multiple replications
장점
쓰기가 적고
읽기가 만을때

전략
1.레플리카 마다 다른역활을 정해라
다른 인덱스를 걸고
다른 스토리지 엔진을 사용해라
2.레플리카 1대를 stand by 서버로 활용해라
3.레플리카 1대를 다른 데이터서버에 넣어라
재난복구
4.레플리카 1대 에 time delay 를 걸어 백업용으로 사용
5.레플리카 1대를 백업 또는 개발용 으로 사용해라

2.master-master in active-active mode
서로에게 마스터및 슬레이브 설정이 되어있음

문제
1.데이터가양쪽에어 업데이트 될때 가장 문제
ex)
서버1
mysql> UPDATE tbl SET col=col + 1;
서버2
mysql> UPDATE tbl SET col=col * 2;

데이터도 다르지만 문제는 에러도 안남
2.acive-active 모드에서 한쪽이 죽어버리고 어플리케이션에서 양쪽에 쓴다면...


autoincrement 는 아래 설정으로 충돌대비가능
한쪽은 홀수 한쪽은 짝수 사용
auto_increment_increment
auto_increment_offset

3.master-slave in active-passive mode
마스터 마스터 모드에서
한쪽으로 쓰고 한쪽은 오직 읽기만 가능

한쪽이 죽어버리면 바로 복구됨

좋을 경우
1.스키마를 변경하는대 오래 걸릴경우
active 쪽에서 stop the replication threads
엑티브 로그가 passive에 적용안됨
passive 모드에서 스키마 변경

스키마 변경 완료되면

active->passive
passive->active로

acive는 변경 완료 되었으며 그동안의 로깅을 따라간다.
passive는 오래 걸려도 live 쿼리가 들어오지 않기 때문에 빠르다

master-master 할때 반드시 확인 해야할 사항들
1. Ensure that the servers have exactly the same data.
2. Enable binary logging, choose unique server IDs, and add replication accounts.
3. Enable logging replica updates. This is crucial for failover and failback, as we’ll see
later.
4. Optionally configure the passive server to be read-only to prevent changes that
might conflict with changes on the active server.
5. Start each server’s MySQL instance.
6. Configure each server as a replica of the other, beginning with the newly created
binary log.


만약 log_slave_updates 를 킬경우
1.엑티브에서 이벤트 발생
2.인엑티스가 복사후 실행
3.인엑티스 서버의 log_slvae_duptate 설정때문에
binary_log에 변경사항 넘김
4.엑티브가 데이터를 읽어감
하나 적용할때 자기와 동일한 serverid 기 때문에 제외함

ring 형식
링으로 만들어 쓸수있으나
한대 죽으면 다죽음
물런 백업replication 각각 한대더 넣으면 조금더 좋아지긴함

master, distribution masdter and replicas
마스터에 어느 정도 이상의 replication 이 걸릴 경우 부담이 될수 있음으로
레플리케이션을 마스터로 만듬

데이터가 매우 많을 경우에 사용하는 전략

커스텀 topology

하나의 마스터에서 데이터를 쪼개서 각각의 데이터를 들고 있게 꾸미는 방법
데이터 베이스 단위로 설정가능
레플리카에
replicate_wild_do_table = sales.%
처럼 설정하면 sales 디비만 복사함