원문 : Thinking about running OPTIMIZE on your Innodb Table ? Stop! / Posted by Peter Zaitsev 링크 : http://www.mysqlperformanceblog.com/2010/12/09/thinking-about-running-optimize-on-your-innodb-table-stop/ 출처 : MySQL Performance Blog ( http://www.mysqlperformanceblog.com ) 번역 : 박현우 (ez.amiryo@gmail.com / @lqez) InnoDB/XTraDB 테이블은 수시로 재구성을 함으로써 성능적인 혜택을 얻는다. 데이터를 물리적으로 Primary Key 순서에 맞게 나열할 뿐 아니라, Primary Key와 인덱스의 페이지도 재배열하여 더 나은 성능과 더 적은 공간 소비를 가능하게 한다. 허나, 무작정 OPTIMIZE TABLE을 수행하는 것은 최선의 방법이 아니다. 만약 Percona Server with XtraDB에서 InnoDB 플러그인을 사용중이라면, '삽입' 방식이 아닌 '정렬' 방식의 인덱스를 생성할 수 있는 '멋진' 기능을 통해 이득을 볼 수 있다. 이 작업은 UUID와 같은 무작위 순으로 입력된 대용량의 인덱스에 대해 특별히 빠르게 수행될 수 있을 뿐 아니라, 더 효율적인 공간 활용을 가능하게 한다. 문제는... InnoDB 테이블에 대한 OPTIMIZE TABLE 명령은 이유를 불문하고 그러한 장점을 얻을 수 없다. 버퍼 풀에 할당된 메모리보다 10배 정도 큰 테이블에 대해 최적화 작업을 수행한 아래 벤치마크를 살펴보자. SQL:
어떤가?! 테이블 최적화를 그냥 수행하면 3시간도 넘게 걸리던 것에 비해, Primary Key를 제외한 인덱스를 내리고 테이블 최적화를 진행한 후에 다시 인덱스를 복원하는 작업은 10분 밖에 걸리지 않는다. 거의 20배나 빠를 뿐 아니라, 최종적으로 더 작은 크기의 인덱스를 얻을 수 있다. 따라 서, 만약 이 트릭을 이용해 테이블 최적화를 수행하고자 한다면, 테이블이 인덱스 없이 노출되어도 괜찮은 슬레이브 단에서 작업하는 것이 좋다. 단, InnoDB 테이블에 락을 걸 때에, 인덱스 없이 테이블을 읽는 많은 수의 쿼리들로 인해 컴퓨터(box)가 멈추는 일이 없어야 함을 보장해야 함을 유의하라. 이 트릭을 테이블 재생성을 필요로 하는 ALTER TABLE 시에도 활용할 수 있다. (역주:MySQL은 ALTER TABLE 작업을 새로운 스키마를 가지는 임시 테이블 생성 > 임시 테이블로 복제 > 기존 테이블 DROP > 임시 테이블의 이름을 기존 테이블의 이름으로 변환하는 식으로 수행한다) 모든 인덱스를 제거하고, ALTER를 수행한 후에 인덱스를 복원하는 것이 그냥 ALTER TABLE을 수행하는 것보다 훨씬 빠르다. 덧) 왜 이런 경우에 정렬을 통해 인덱스를 생성하는 기능이 지원되지 않는지 모르겠다. 고수준 명령어나 도구들(예를 들어 mysqldump)이 왜 인덱스를 만드는데 느리디 느린 '삽입 방식'을 사용해, 이러한 장점을 가질수 없게 만들어졌는지 이해할 수 없다. ---- 과거 MySQL로 MMORPG의 대용량 로그 데이터를 다루던 경험에 비추어 보아도, 아래의 동작들은 정말 이해가 가지 않았다. (1) Row의 용량이 줄어드는 ALTER TABLE의 경우에도 위에서 언급한 것과 같이 전체 rebuilding을 하는 것. |
2011년 8월 23일 화요일
MySQL : InnoDB 테이블을 OPTIMIZE 하려고? 잠깐~!
MYSQL 한글입력
MySQL-5.0 설치후 터미널에서 한글입력이 안되거나 한글이 깨져보이는 경우 다음과 같이 해결했습니다. 저와 같은 문제에 있었던 분에게 도움이 될지 모르겠네요.
* 테스트환경 - 리눅스 Fedora core3, mysql: 5.0.18 버전
1. 소스컴파일시 옵션 ( euckr, readline 추가해줌)
./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --with-charset=euckr --with-readline
2. 홈 디렉토리의 .bashrc 파일에 다음 두줄을 추가합니다.
export LANG=ko_KR.EUC-KR
export LANGUAGE=ko_KR:ko:en_GB:en
3. 저장후 쉘> source .bashrc 를 수행합니다.
4. mysql데몬실행후 접속해서 콘솔에서 한글 입력하면 됩니다.
* 테스트환경 - 리눅스 Fedora core3, mysql: 5.0.18 버전
1. 소스컴파일시 옵션 ( euckr, readline 추가해줌)
./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --with-charset=euckr --with-readline
2. 홈 디렉토리의 .bashrc 파일에 다음 두줄을 추가합니다.
export LANG=ko_KR.EUC-KR
export LANGUAGE=ko_KR:ko:en_GB:en
3. 저장후 쉘> source .bashrc 를 수행합니다.
4. mysql데몬실행후 접속해서 콘솔에서 한글 입력하면 됩니다.
Mysql - explain
1. 문장 끝에 ; 대신 \G 를 사용하면 결과를 세로로 볼 수 있다.
2. select 문장 앞에 explain 키워드를 사용하면 플랜을 볼 수 있다.
I. plan 결과 설명
※ http://dev.mysql.com/doc/refman/5.5/en/using-explain.html
1. select_type 값
- SIMPLE
. 평범한 SELECT 문장
- PRIMARY
. 하위 쿼리나 UNION을 사용할 때 외부(첫 번째) 쿼리
- UNION
. UNION 두 번째나 다음 번 쿼리
- DEPENDENT UNION
. UNION에서 첫 번째 쿼리에 따른 두 번째나 다음 번 쿼리
- UNION RESULT
. UNION 겨로가
- SUBQUERY
. 내부 하위 쿼리
- DEPENDENT SUBQUERY
. 첫 번째 쿼리에 따른 내부 하위 쿼리(즉, 상관 하위 쿼리)
- DERIVED
. FROM 절에서 사용한 하위 쿼리
- UNCACHEABLE SUBQUERY
. 그 결과가 캐쉬될 수 없으며 각 행마다 다시 계산되어야 하는 하위 쿼리
- UNCACHEABLE UNION
. UNCACHEABLE SUBQUERY에 속한 UNION에서 두 번째나 그 이후의 SELECT
2. table
- 쿼리에 응답하는 테이블 명
3. type
- 쿼리에서 테이블이 어떻게 조인되는지 설명
- const 또는 system
. 테이블이 한 번만 읽어온다. 테이블에 row가 단 하나만 있을 때 일어날 수 있다.
- eq_ref
. 조인하는 테이블에서 row 집합을 읽어올 때마다 이 테이블에서 한 row씩 읽는다.
테이블의 인덱스를 사용할 때 조인이 사용되며 인덱스는 UNIQUE하거나 기본 키이다.
- fulltext
. fulltext 인덱스를 사용하여 조인한다.
- ref
. 조인하는 테이블에서 row 집합을 읽어올 때마다 이 테이블에서 대응되는 row 집합을 읽어온다.
조인 조건에 따라서는 한 행만 선택할 수 없을 때, 조인에서 키의 일부분만이 사용되었을 때,
혹은 키가 UNIQUE 하지 않거나 기본 키가 아닐 때
- ref_or_null
. ref 쿼리와 비슷하지만 NULL 인 row 도 찾아본다. (하위 쿼리에서 가장 많이 사용된다)
- index_merge
. Index Merge 라는 특별한 최적화 기법이 사용되었다.
- unique_subquery
. 유일한 한 줄이 리턴될 때 IN 하위 쿼리에서 ref를 대신하여 사용할 수 있다.
- index_subquery
. unique_subquery 와 비숫하지만 인덱스된 유일하지 않은 하위 쿼리에서 사용한다.
- range
. 조인하는 테이블에서 row 집합마다 이 테이블 중 특정 범위에 들어가는 row들을 읽어온다.
- index
. 모든 인덱스를 읽는다.
- ALL
. 이 테이블의 모든 행을 읽는다.
4. rows
- 조인을 실행하기 위해 테이블마다 읽어야 하는 row의 수를 대충 계산
쿼리가 사용하는 총 row의 수를 얻기 위해서는 값들을 곱해야 한다.
5. possible_keys
- 테이블을 조인하기 위해 사용할 수 있는 키
- PRIMARY 는 테이블마다 일반적으로 있기 때문에 대부분 나온다.
6. key
- row 을 조인할 때 실제로 사용하고 있는 키
- NULL 은 키를 사용하고 있지 않다는 뜻이다.
7. key_len
- 사용된 키의 길이를 나타낸다.
8. ref
- 테이블에서 row를 선택할 때 사용한 키를 나타낸다.
9. Extra
- 어떻게 조인을 실행하는가에 대한 정보
- Distinct
. 처음으로 일치하는 줄이 발견되면 끝난다.
- Not exists
. LEFT JOIN 을 사용하여 쿼리가 최적화되어 있다.
- Range checked for each record
. 조인하는 테이블에서 row 집합을 읽어올 때마다 사용할 수 있는 인덱스를 찾는다.
- Using filesort
. 테이터를 정렬하려면 두 번 읽어야 한다. (따라서 시간도 두 배 걸린다)
- Using index
. 테이블에 있는 모든 정보를 인덱스에서 얻어왔다. 따라서 실제 row는 읽지 않았다.
- Using join buffer
. 조인 버퍼를 사용하여 테이블을 부분적으로 읽어 들인다. 그 후 쿼리를 수행할 때 버퍼에서 각 행을 추출한다.
- Using temporary
. 이 쿼리를 실행하는 동안 임시 테이블을 사용하였다.
- Using where
. 행을 선택하는 데 where 절을 사용하였다.
II. sql 쿼리 튜닝
1. 조인 최적화를 위해 키 분배를 검사
- myisamchk 유틸리티 사용
#>myisamchk --analyze <pathtomysqldatabase/table명>
#>myisamchk --analyze <pathtomysqldatabase/*.MYI>
#>myisamchk --analyze <pathtomysqldatadirectory/*/*.MYI>
- Analyze Table 문을 사용
sql>analyze table customers, orders, order_items, books;
2. 테이블 최적화
- 테이블의 단편화를 없앤다.
sql>optimize table <table명>;
#>myisamchk -r table
- 테이블의 인덱스를 정렬하고 인덱스에 맞게 데이터를 재배치 한다.
#>myisamchk --sort-index --sort-records=1 <pathtomysqldatadirectory/*/*.MYI>
2. select 문장 앞에 explain 키워드를 사용하면 플랜을 볼 수 있다.
explain
select * from User \G
select * from User \G
I. plan 결과 설명
※ http://dev.mysql.com/doc/refman/5.5/en/using-explain.html
1. select_type 값
- SIMPLE
. 평범한 SELECT 문장
- PRIMARY
. 하위 쿼리나 UNION을 사용할 때 외부(첫 번째) 쿼리
- UNION
. UNION 두 번째나 다음 번 쿼리
- DEPENDENT UNION
. UNION에서 첫 번째 쿼리에 따른 두 번째나 다음 번 쿼리
- UNION RESULT
. UNION 겨로가
- SUBQUERY
. 내부 하위 쿼리
- DEPENDENT SUBQUERY
. 첫 번째 쿼리에 따른 내부 하위 쿼리(즉, 상관 하위 쿼리)
- DERIVED
. FROM 절에서 사용한 하위 쿼리
- UNCACHEABLE SUBQUERY
. 그 결과가 캐쉬될 수 없으며 각 행마다 다시 계산되어야 하는 하위 쿼리
- UNCACHEABLE UNION
. UNCACHEABLE SUBQUERY에 속한 UNION에서 두 번째나 그 이후의 SELECT
2. table
- 쿼리에 응답하는 테이블 명
3. type
- 쿼리에서 테이블이 어떻게 조인되는지 설명
- const 또는 system
. 테이블이 한 번만 읽어온다. 테이블에 row가 단 하나만 있을 때 일어날 수 있다.
- eq_ref
. 조인하는 테이블에서 row 집합을 읽어올 때마다 이 테이블에서 한 row씩 읽는다.
테이블의 인덱스를 사용할 때 조인이 사용되며 인덱스는 UNIQUE하거나 기본 키이다.
- fulltext
. fulltext 인덱스를 사용하여 조인한다.
- ref
. 조인하는 테이블에서 row 집합을 읽어올 때마다 이 테이블에서 대응되는 row 집합을 읽어온다.
조인 조건에 따라서는 한 행만 선택할 수 없을 때, 조인에서 키의 일부분만이 사용되었을 때,
혹은 키가 UNIQUE 하지 않거나 기본 키가 아닐 때
- ref_or_null
. ref 쿼리와 비슷하지만 NULL 인 row 도 찾아본다. (하위 쿼리에서 가장 많이 사용된다)
- index_merge
. Index Merge 라는 특별한 최적화 기법이 사용되었다.
- unique_subquery
. 유일한 한 줄이 리턴될 때 IN 하위 쿼리에서 ref를 대신하여 사용할 수 있다.
- index_subquery
. unique_subquery 와 비숫하지만 인덱스된 유일하지 않은 하위 쿼리에서 사용한다.
- range
. 조인하는 테이블에서 row 집합마다 이 테이블 중 특정 범위에 들어가는 row들을 읽어온다.
- index
. 모든 인덱스를 읽는다.
- ALL
. 이 테이블의 모든 행을 읽는다.
4. rows
- 조인을 실행하기 위해 테이블마다 읽어야 하는 row의 수를 대충 계산
쿼리가 사용하는 총 row의 수를 얻기 위해서는 값들을 곱해야 한다.
5. possible_keys
- 테이블을 조인하기 위해 사용할 수 있는 키
- PRIMARY 는 테이블마다 일반적으로 있기 때문에 대부분 나온다.
6. key
- row 을 조인할 때 실제로 사용하고 있는 키
- NULL 은 키를 사용하고 있지 않다는 뜻이다.
7. key_len
- 사용된 키의 길이를 나타낸다.
8. ref
- 테이블에서 row를 선택할 때 사용한 키를 나타낸다.
9. Extra
- 어떻게 조인을 실행하는가에 대한 정보
- Distinct
. 처음으로 일치하는 줄이 발견되면 끝난다.
- Not exists
. LEFT JOIN 을 사용하여 쿼리가 최적화되어 있다.
- Range checked for each record
. 조인하는 테이블에서 row 집합을 읽어올 때마다 사용할 수 있는 인덱스를 찾는다.
- Using filesort
. 테이터를 정렬하려면 두 번 읽어야 한다. (따라서 시간도 두 배 걸린다)
- Using index
. 테이블에 있는 모든 정보를 인덱스에서 얻어왔다. 따라서 실제 row는 읽지 않았다.
- Using join buffer
. 조인 버퍼를 사용하여 테이블을 부분적으로 읽어 들인다. 그 후 쿼리를 수행할 때 버퍼에서 각 행을 추출한다.
- Using temporary
. 이 쿼리를 실행하는 동안 임시 테이블을 사용하였다.
- Using where
. 행을 선택하는 데 where 절을 사용하였다.
II. sql 쿼리 튜닝
1. 조인 최적화를 위해 키 분배를 검사
- myisamchk 유틸리티 사용
#>myisamchk --analyze <pathtomysqldatabase/table명>
#>myisamchk --analyze <pathtomysqldatabase/*.MYI>
#>myisamchk --analyze <pathtomysqldatadirectory/*/*.MYI>
- Analyze Table 문을 사용
sql>analyze table customers, orders, order_items, books;
2. 테이블 최적화
- 테이블의 단편화를 없앤다.
sql>optimize table <table명>;
#>myisamchk -r table
- 테이블의 인덱스를 정렬하고 인덱스에 맞게 데이터를 재배치 한다.
#>myisamchk --sort-index --sort-records=1 <pathtomysqldatadirectory/*/*.MYI>
2011년 4월 6일 수요일
2011년 1월 23일 일요일
ContextLoaderListener 기능
Spring 에서 DispatcherServlet은 그 자체로서 서블릿 이기 때문에 1개 이상의 DispatcherServlet을 설정하는 것이
가능하다.
만약 web.xml 에서 아래와 같이 DispatcherServlet을 설정하였다면 두개의 독릭적으로 작동하는 컨텍스트가
생성되게 된다.
<servlet>
<servlet-name>aController</servlet-name>
<servlet-class>
org.springframework.web.servlet.DispatcherServlet
</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>/WEB-INF/context/applicationContext_a.xml</param-value>
</init-param>
</servlet>
<servlet>
<servlet-name>bController</servlet-name>
<servlet-class>
org.springframework.web.servlet.DispatcherServlet
</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>/WEB-INF/context/applicationContext_b.xml</param-value>
</init-param>
</servlet>
이 경우 두 DispatcherServlet은 각각 별도의 WebApplicationContext를 생성하게 된다.
이 때 두 컨텍스트는 독립적이므로 aController는 applicationContext_a.xml의 설정파일로 컨텍스트가 생성되고,
bController는 applicationContext_b.xml 설정파일을 이용헤서 컨텍스트가 생성되는데
aController에서는 applicationContext_b.xml 설정파일을 이용해서 생성된 빈객체를 사용할수가 없다.
bController에서도 마찬가지 이다.
이때 만약 aController 와 bController 에서 동시에 필요한 의존성(공통빈)이 있어야 하는경우
ContextLoaderListener를 사용하여 공통으로 사용된 빈을 설정할수 있게 된다.
다음과 같이 설정한다.
<context-param>
<param-name>contextConfigLocation</param-name><param-value>/WEB-INF/applicationContext_service.xml/WEB-INF/applicationContext_dao.xml</param-value>
</context-param>
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
ContextLoaderListener 와 DispatcherServlet은 각각 WebApplicationContext 인스턴스를 생성하게 되는데,
ContextLoaderListener 가 생성한 컨텍스트가 root컨텍스트가 되고, DispatcherServlet이 생성한 인스턴스는
root컨텍스트를 부모로 사용하는 자식 컨텍스트가 된다.
이때 자식컨텍스트들은 root컨텍스트가 제공하는 빈을 사용할 수 있기 때문에 ContextLoaderListener를 이용하여
공통빈을 설정하는 것이다.
참고로...
ContextLoaderListener는 ContextConfigLocation을 명시하지 않으면 /WEB-INF/applicationContext.xml을 설정파일로 사용한다.
이때, 설정파일이 존재하지 않으면 에러가 발생하니 주의해야 한다.
http://blog.naver.com/minis24/80097770192
2011년 1월 13일 목요일
피드 구독하기:
글 (Atom)