package com.naver.jr.fun.model.quiz.handler;
import java.sql.SQLException;
import org.apache.commons.lang.StringUtils;
import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;
import com.naver.jr.fun.model.quiz.QuizCategory;
public class QuizCategoryTypeHandler implements TypeHandlerCallback {
@Override
public Object getResult(ResultGetter getter) throws SQLException {
String str = getter.getString();
if (StringUtils.equals(QuizCategory.COMMON.toString(), str)) {
return QuizCategory.COMMON;
} else if (StringUtils.equals(QuizCategory.COUNTRY.toString(), str)) {
return QuizCategory.COUNTRY;
} else if (StringUtils.equals(QuizCategory.PROVERB.toString(), str)) {
return QuizCategory.PROVERB;
} else if (StringUtils.equals(QuizCategory.RIDDLE.toString(), str)) {
return QuizCategory.RIDDLE;
} else if (StringUtils.equals(QuizCategory.TEXTBOOK.toString(), str)) {
return QuizCategory.TEXTBOOK;
} else if (StringUtils.equals(QuizCategory.TV.toString(), str)) {
return QuizCategory.TV;
}
else {
throw new SQLException("Unexpceted value " + str + " found. " + QuizCategory.class.toString()
+ " was expected.");
}
}
@Override
public void setParameter(ParameterSetter setter, Object parameter) throws SQLException {
switch ((QuizCategory)parameter) {
case COMMON:
setter.setString(QuizCategory.COMMON.toString());
break;
case COUNTRY:
setter.setString(QuizCategory.COUNTRY.toString());
break;
case PROVERB:
setter.setString(QuizCategory.PROVERB.toString());
break;
case RIDDLE:
setter.setString(QuizCategory.RIDDLE.toString());
break;
case TEXTBOOK:
setter.setString(QuizCategory.TEXTBOOK.toString());
break;
case TV:
setter.setString(QuizCategory.TV.toString());
break;
default:
throw new SQLException("Unexpceted value found. " + QuizCategory.class.toString() + " was expected.");
}
}
@Override
public Object valueOf(String str) {
if (StringUtils.equals(QuizCategory.COMMON.toString(), str)) {
return QuizCategory.COMMON;
} else if (StringUtils.equals(QuizCategory.COUNTRY.toString(), str)) {
return QuizCategory.COUNTRY;
} else if (StringUtils.equals(QuizCategory.PROVERB.toString(), str)) {
return QuizCategory.PROVERB;
} else if (StringUtils.equals(QuizCategory.RIDDLE.toString(), str)) {
return QuizCategory.RIDDLE;
} else if (StringUtils.equals(QuizCategory.TEXTBOOK.toString(), str)) {
return QuizCategory.TEXTBOOK;
} else if (StringUtils.equals(QuizCategory.TV.toString(), str)) {
return QuizCategory.TV;
}
return null;
}
}
2010년 9월 5일 일요일
TypeHandlerCallback 예시
2010년 8월 5일 목요일
Unitils와 DBUnit 에서 NoSuchColumnException 에러
분명히 DB에 컬럼이 존재함에도 불구하고
Unitils에서 DBUnit를 연동해서 dataset의 내용을 처리할때
아래와 같은 에러가 발생할 경우가 있다.
org.dbunit.dataset.NoSuchColumnException
이것은 라이브러리 버전의 문제일수도 있다.
DBUnit 2.4.7 과 Untils- DBUnit 3.1을 사용했더니 위의 문제가 발생되었다.
DBUnit 의 버전을 2.2.2 로 내리고 나니 해결이 되었다.
(pom.xml에서 빼면 자동으로 2.2.2를 포함한다)
2010년 8월 4일 수요일
java.lang.NoSuchMethodError 에러메시지
간혹 NoSuchMethodError가 발생할 경우가 있다.
메소드를 찾지 못해서 발생하는 에러인데
코드상, 라이브러리상에서 전혀 문제가 없는 경우 발생할 때에는
주로 classpath상에서 중복되는 class가 있어서 정상적으로 method를
찾지 못해서 발생하는 문제이므로 아래와 같이 조치를 해보자
- lib폴더에서 중복되는 jar파일의 존재를 확인한다
- maven의 pom.xml파일에서 dependency 부분에서 complict가 발생되는 라이브러리가
없는지 확인해보도록 하자
2010년 7월 25일 일요일
Ant의 FileSet을 Source 이용하기
Apache의 Ant에서는 FileSet라는 파일 및 폴더 관리 방식이 있다.
매우 유연하게 폴더 및 파일을 접근할 수 있는데 그부분은 Java 소스에서
이용하는 방법을 알아보도록 하자.
예제1
String fileSetExclude = "**/S.jsp";
File ws = new File("D:/Projects/lucy/text-finder/work/jobs/Test/workspace");
FileSet fs = new FileSet();
org.apache.tools.ant.Project p = new org.apache.tools.ant.Project();
fs.setProject(p);
fs.setDir(ws); // Root경로를 지정
fs.setIncludes(fileSet); //포함할 파일 조건
fs.setExcludes(fileSetExclude); //제외할 파일 조건
DirectoryScanner ds = fs.getDirectoryScanner(p);
// Any files in the final set
String[] files = ds.getIncludedFiles();
if (files.length == 0) {
System.err.println("FileSet Empty");
throw new Exception();
}
for (String file : files) {
File f = new File(ws, file);
if (!f.exists()) {
System.err.println("Error Find File : " + f);
continue;
}
if (!f.canRead()) {
System.err.println("Error Read File : " + f);
continue;
}
System.out.println("File : " + f.getName());
}
위의 소스가 간단하기 때문에 쉽게 파악할 수 있을것이다.
FileSet의 조건을 주는 방식은 ant의 설명을 참고하길 바라며 콤마(,)를 이용해 다수의 경로조건을 입력할 수 있다.
2010년 7월 19일 월요일
IEToy에서 네이버 불펌 방지 해지
네이버에서 블로그나 카폐등을 돌아 다니다 보면 불펌 방지가 되어 있는 글을 볼 수 있다.
필요한 내용이 있을 경우, 특히 소스 등의 경우 타이핑으로 복사하는건 너무 힘든일이다.
이런 불편을 IEToy에서 해결 하기 위한 방법을 설명해보겠다.
1. http://userscripts.org/scripts/show/61326 에 install버튼을 클릭한다.
2. 파일 이름을 antidisablerfornaver.user.js 로 변경해서 IEToy설치 폴더의 gmm_Scripts폴더에 이동한다.
3. IEToy 환경 설정(Win+I)에서 사용사 스크립트 항목에서 "Anti-Disable for Naver"을 체크한다.
4. 위의 사이트가 문제가 있을 경우 아래의 링크를 다운받아서 2~3번의 절차를 거친다.
antidisablerfornaver.user.jsSpring Test Context Framework 사용시 Type mismatch 에러
TDD기반의 프로그래밍 중 스프링의 IC를 사용할때 Spring TestContext Framework를 사용하게 된다.
그럴 때 편의를 위해서 어노테이션을 사용하게 되는 경우가 많은데
아래와 같이 테스트용 메소드 상단에 기록을 하는 것이 일반적이다.
// specifies the Spring configuration to load for this test fixture
@ContextConfiguration(locations={"daos.xml"})
public final class HibernateTitleDaoTests {
// this instance will be dependency injected by type
@Autowired
private HibernateTitleDao titleDao;
public void testLoadTitle() throws Exception {
Title title = this.titleDao.loadTitle(new Long(10));
assertNotNull(title);
}
}
그런데 이렇게 설정을 해놓고 실행시에 아래와 같은 오류가 발생할 경우에는
설치된 JUnit 의 버전을 확인 해보자.
JUnit 4.4 이후의 버전을 사용하게 되면 위의 오류는 사라질 것이다.
설치된 JUnit 의 버전을 확인 해보자.
또한 위와 같이 Autowire를 설정시에는 꼭 ApplicationConext.xml 파일의 beans 설정에 defalt-autowire속성이 설정되었는지 확인해야 한다.
<beans xmlns="http://www.springframework.org/schema/beans" ... 중략 ... default-autowire="byName">
<bean id="boardDAO" class="com.naver.bbs.dao.BoardDAOImpl" />
</beans>
Eclipse Code Template 에서 ${user}변수 변경
Eclipse(이클립스) 사용시 Code Template(코드 템플릿)에서 유저명을 변수로 사용할 수 있다.
예)
* @author ${user}
*/
그런데 간혹 이 유저명이 내가 현재 원하는 유저명이 아닌 OS유저명이 적용되는 것을 볼 수 있다.
이것을 변경하기 위해서는 이클립스가 설치된 경로의 설정파일에 다음의 한줄을 추가하자
eclipse.ini
-Duser.name="사용하고자하는 유저명"
2010년 7월 12일 월요일
JDBC와 DAO
JDBC
1. Java DataBase Connectivity
- 자바의 표준 DB접속 방법
- DataBase와 독립적인 구현
2. JDBC Driver Type
- type1 : JDBC-ODBC 브리지(UserCode -> JDBC -> JDBC-ODBC -> ODBC -> DB)
- type2 : native API Driver (UserCode -> JDBC -> DB library(native) -> DB)
- type3 : network-protocol driver (UserCode -> JDBC -> DB Midleware -> DB)
- type4 : native protocol driver (UserCode -> JDBC -> DB)
3. JDBC Spec.
- 커넥션, SQL 질의 및 파라미터, 결과의 수신,
- 기본 매핑(SQL Type & Java Type), 메타데이터 제공, 트랜잭션, 로깅 등
4. 커넥션 방법
- DriverManager로 접속하는 방법
Driver 로딩 : Class.forName("com.driver.class.name");
DriverManager로 커넥션 획득 : DriverManage.getConnection("접속정보");
- javax.sql.DataSource(JDBC 2.0)
WAS start(DataSOurce Configuration) -> Referenceable -> JNDI
Context.lookup(UserCode <-> JNDI <-> DataSources)
DataSource.getConnection(UserCode <-> DataSource)
- PooledDataSource
UserCode <-> Pooled Connection <-> ConnectionPool <-> PoolingDataSource
Data Access Layer
1. DAO FrameWorks
- JDBC Templates
- SQL Mappers
- OR Mappers
2. JDBC Templates
자주 사용하는 표준적인 DB접근, 질의 등을 템플릿 형태로 사용함
장점 - 쉽다, 설정필요이 따로 필요 없다
단점 - 코드안에 모든내용포함 된다, 코드가독성이 낮다, DB 의존적이다
3. SQL Mappers
쿼리 등을 외부로 빼내고 쿼리에 대한 결과를 매핑해주는 기능으로 bean에 결과를 매핑한다.
장점 - 코드가 줄어듦, 배우는게 쉽다, 코드와 쿼리가 분리된다
단점 - XML설정이 필요하다, DB에 의존적이다
4. OR Mappers
테이블의 Row를 하나의 객체로 인식하고자 함
설정을 통해서 테이블과 클래스, Row와 인스턴스를 연결하고 객체만을 사용
장점 - 코드가 줄어듦, 직관적이다, 쿼리와 DB의존적이지 않다
단점 - XML설정이 필요하다, 배우기 어렵다
2010년 7월 8일 목요일
[MySQL 모니터링 및 엔진 최적화]InnoDB 스토리지 엔진 최적화
InnoDB의 옵션의 개요
InnoDB의 메모리 관련 옵션
- Innodb_buffer_pool_size
가장 중요한 옵션
데이블의 데이터와 인덱스를 캐싱하기 위해서 사용
사이즈가 클수록 성능이 향상됨
OS Cache보다 훨씬 효율적으로 메모리를 사용하며 Write성능에 큰 영향을 미침
서버메모리 용량의 70~80%정도로 설정하는 것이 적당
기본값은 8MB이며 반드시 재설정 필요
- Innodb_additional_mem_pool
DataDictionary(테이블 스키마 등)를 저장하기 위해서 사용. 필요한 경우 자동으로 증가
InnoDB의 로그 관련 옵션
- Innodb_log_file_size
InnoDB redo로그 파일 크기
Write 성능에 매우 큰 영향을 미침
설정 파일 크기에 따라 복구 시간이 증가될수 있어서 256M 사용권장
- Innodb_log_files_in_group
로그 그룹안에 포함될 로그 파일 수
기본적으로 2이며 3을 권장함
- Innodb_log_buffer_size
매우 큰 BLOB를 사용하지 않는한 2-8M의 기본값 사용
InnoDB log flush 주기 조절
- Innodb_flush_log_at_trx_commit(기본값은1)
0으로 설정하면 1초에 한번씩 디스크에 기록하고 씽크 - MySQL,시스템다운시 1시간 데이터 누락가능성 있음
1로 설정하면 commit시 디스크에 기록하고 씽크 - 어떤 다운시에도 데이터 유지
2로 설정하면 commit를 할때마다 디스크에 기록하고 싱크는 1초에 한번만함 - 시스템 다운시 1초간 데이터 누락
InnoDB의 로그 사이즈 재조정
- 일반적인 옵션처럼 수치만 변경해서 조절할 수 없음
MySQL종료
Data 디렉터리 안의 ib_log* 파일 삭제
설정의 innodgb_log_file_size 수정
MySQL 재시작
InnoDB의 flush 방법 설정
- InnoDB가 OS의 FileSystem과 연동하는 방식 설정
- 윈도우에서는 unbufferedIO가 늘 사용됨
- UNIX에서는 fsync(), O_SYNC/O_DSYNC를 파일 flush를 위해 사용가능
- 리눅스에서는 O_DIRECT를 사용하여 unbufferedIO를 사용할 수 있다 (double buffering을 막아줌)
InnoDB의 테이블 별 테이블 스페이스
- Innodb_file_per_table 옵션이 설정 가능
- 테이블 별로 테이블스페이스를 설정함
- 테이블 별로 설정해도 공통 테이블스페이스는 필요함
- 분리시 데이터를 여러개의 디스크로 분산 가능
- 테이블을 drop하면 디스크의 공간이 반환됨
- 테이블이 많을 경우 MySQL기동/종료시 속도가 빨라짐
그 밖의 InnoDB의 옵션들
- Innodb_thread_concurrency : 기본값은 8, 동시 사용 쓰레드수로 변경하지 않음
- FOREIGN_KEY_CHECKS/UNIQUE_CHECKS
데이터를 입력시 Foreign키와 Unique를 검사하지 않음
대용량 데이터 입력시 사용함(AUTOCOMMIT을 0으로 하는것도 추천)
- innodb_fast_shutdown
종료시 내무 메모리 구조 정리 작업과 버퍼 정리 작업을 건너뜀. 무결성에는 영향없음
ㅇㄹㅇ
[MySQL 모니터링 및 엔진 최적화]MyISAM 스토리지 엔진 최적화/모니터링
MyISAM의 mysqld옵션
- 전체 mysqld 세팅
key_buffer_size(기본 8Mb) : 인덱스 캐시, 올리게 되면 성능 향상
데이터 row에 대한 캐시는 OS에서 핸들링
- 쓰레드별 세팅 : 일반적인 동작에 관계 없음
Myisam_sort_buffer_size(기본 8Mb)
Myisam_repair_threads(기본1) - 벌크 임포트와 myisam테이블 복구에 사용
Key와 관련된 Status 환경 변수
- MyISAM 엔진은 key캐시를 조절하는 것이 성능과 직결됨
- 일반적인 경우 다음과 같은 상황이 바람직함
낮은 key_reads(물리 디스크를 읽는 것우)
매우 낮은 key_reads/key_read_request비율(0.03이하)
- 다음방법으로 캐시 사용을 확인
key_block_used와 key_block_unused는 얼마나 많은 쿼리캐시 공간이 사용중인지 나타냄
key_cache_block_size로 블록 사이즈를 결정
key_buffer_size 최적화
- 값을 높여주면 더많은 메모리를 사용해서 성능이 높아지나 너무 높이게 되면 데이터로딩시 OS캐쉬의 이용을 할 수 없어 성능이 낮아질수 있음
- 전체메모리의 25%정도로 설정로 보통 512M이하로 설정
- SHow_status like 'key%' 로 key 사용상황 점검
대용량 데이터 로딩 및 수리
- MyISAM_sort_buffer_size
인덱스 생성에 사용되는 메모리의 양, 대용량 데이터 입력시 성능을 높히기 위해서 할당함
- Myisam_repair_threads
1이상으로 설정할 경우 병렬로 인덱스 생성이 가능, 코어캣수만큼 설정, repair시에만 사용됨
MyISAM모니터링
- MyISAM은 키캐시에 대부분의 성능이 좌우됨
- 키 캐시 쓰기 요청
- 키 캐시 쓰기
[MySQL 모니터링 및 엔진 최적화]일반적인 MySQLD 옵션
MySQL의 기본 설정 파일
- 총 5개의 기본설정 파일(windows환경에서는 확장명이 ini임)
my-small.cnf : 64MB이하의 메모리를 시스템 설정
my-medium.cnf : 128MB 시스템 설정
my-large.cnf : 512MB이하 시스템
my-huge.cnf : 1-2GB 시스템
my-innodb-heave-4G.cnf : 4GB이상 메모리상에서 InnoDB를 사용하는 경우(주로사용)
- 저장 위치
Linux/Unix : /etc/(1순위), 설치경로(2순위), 데이터디렉토리(3순위) 뒤쪽의 것이 오버라이트함
Windows : Windows디렉토리(1순위), 설치경로(2순위), 데이터디렉토리(3순위) 뒤쪽의 것이 오버라이트함
MySQL의 파라메터 조정
- 옵션중 일부는 동작중 변경 가능
SESSION : 조정된 값이 현재 커넥션에서만 영향을 미침
GLOBAL : 조정된 값이 전체 서버에 영향을 미침
BOTH : 값을 변화시킬때 SESSION/GLOBAL을 반드시 명기해야함
- 동작중 변경값은 기동이 종료되면 소실됨
- 모든 옵션의 현재값은 아래 커맨드로 확인가능
MySQLD 옵션의 변경
- 변경된 옵션은 전체 서버에 적용됨
- 대부분의 옵션은 동작중에 아래와 같이 변경 가능(대부분 super권한을 가져야함)
- 세션별로 변경가능한 옵션은 아래와 같이 적용 가능
주요 GLOBAL옵션
- table_cache(기본 64)
사용하는 테이블에 대한 핸들러를 캐시에 저장,
동시테이블 사용량이 높으면 높임
- thread_cache(기본 0)
재사용을 위해서 보관해야할 쓰레드수,
클라이언트가 커넥션풀을 사용할 경우 의미없음
- max_connections(기본100)
허용가능한 최대 접속수
함부로 늘려서는 안됨(각 커넥션의 사용할 메모리 양의 총합이 동접 증가수만큼 늘어날 수 있으므로)
커넥션 관련 옵션
- Connect_timeout
connection접속후 요청 후 대기 시간
- net_buffer_length
MySQL이 전송하는 초기 메시지의 크기, 기본값 사용 권장
- max_allowed_packed
서버/클라이언트간 최대 전송 가능 패킷 크기(디폴트 2M)
TEXT나 BLOG컬럼이 있는 것우 또는 리플리케이션을 사용하는 경우에는 최소 16M권장
- back_log
커넥션이 대량으로 몰리는 경우 대기 가능한 커넥션의 수, 기본값 사용 권장
커넥션 관리
- max_connections : 최대 접속수
- inactive_timeout : 유저와 상호작용을 하는 커넥션 타임아웃, 클라이언트 툴 등에서 접속중 유효시간
- wait_timeout : 일반적인 서버,클라이언트 환경에서 타입아웃시간.(close를 안하는 경우 문제가 될수 있으므로 적절하게 조절해야함)
- net_read_timeout/net_write_timeout : 클라이언트의 네트워크를 통한 읽기/쓰기 타임아웃, 기본값 사용 권장
- net_retry_count/max_connect_error : 통신이 잘못되었을때 몇번만에 블럭되는지 지정, 블럭이 되면 FLUSH HOST명령전에는 접근불가. 최대한 높은 값으로 설정해놓음
Table Cache 최적화
- table_cache값을 올리면 OS상의 file descriptor의 수를 증가해줘야함
테이블 스캔 성능의 향상
- 결과 값을 찾기 위해 모든 row를 탐색해야하는 경우에 사용되므로 성능에 큰 영향을 미침
- 테이블 스캔 디스크 엑세스 감소를 위해 read_buffer를 사용
- read_buffer_size는 기본 128Kb이고 높일 수록 테이블 스캔 성능이 올라감
- 테이블 스캔하는 모든 쓰레드에 적용되므로 너무 높이게 되면 메모리 자원에 문제가 발생할 수 있음
- 풀 테이블 스캔을 사용하는 쿼리 앞단에서 올리고 종료히 내려주는 방법도 사용가능
조인 성능의 향상
- 조인되는 컬럼의 인덱스가 존재하지 않는 경우 조인버퍼를 사용
- 인덱스를 추가하는 것이 바람직하나 임시적으로 join_buffer_size를 높여주는 것도 가능함
- 두 테이블간 조인일 경우 하나의 join_buffer가 추가되지면 테이블이 추가될 수록 버퍼수도 늘어남
정렬 성능의 향상
- 대량의 정보를 OrderBy하거나 GroupBy를 처리하게되는 경우 디스크 자원 사용
- 이러한 작업을 메모리내에서 처리하기 위해 sort_buffer_size를 조절
- 적절하게 사용하여 디스크를 사용하지 않고 메모리버퍼를 쓰게되면 25%정도의 성능향상 가능
- sort_buffer에 데이터를 정렬한 후 실제 결과값을 처리하는 것은 read_rnd_buffer_size의 영향을 받음
- 세션별로 설정해서 임시적으로 사용하는 것을 권장
Query Cache
- SELECT 쿼리와 그 결과를 저장
- 목적 : 빈번하게 사용되는 SELECT쿼리의 성능 향상
- 테이블에 변화(INSERT,UPDATE,DELETE)가 일어나게 되면 해당테이블과 관련된 쿼리 캐시내의 쿼리는 초기화
- Query_cache_size 환경 변수를 통해서 조절(기본은 비활성화)
- SHOW STATUS LIKE 'Qcache_%' 커맨드로 쿼리 캐시 관련 항목 모니터링
- RESET QUERY CACHE 커맨드를 통해 수동으로 캐시 삭제 가능
Query Cache 최적화
- Query_cache_limit 를 통해서 저장할 쿼리의 사이즈를 제한할 수 있음
- Query_cache_min_res_unit(blocksize)를 설정하여 쿼리 캐시의 조각화를 줄일 수 있음, 기본4Kb
- 남는 블록이 있음에도 불구하고 캐시 된 쿼리가 제거된다면 Qcache_free_blocks, Qcache_lowmen_prune을 참고
- Query_cache_hits와 Com_select를 비교하여 캐시 적중률을 파악할 수 있음
[MySQL 모니터링 및 엔진 최적화]MySQL 환경변수
커넥션 관련 환경 변수
- max_used_connections : 피크 타임의 동시 접속수 (튜닝시 중요)
- bytes-receved, bytes-sent : 모든 클라이언트와 전송량
- connection : 시도된 커넥션의 총합
- aborted_connects : 접속이 끊어진 커넥션의 총 합 (높을 경우 어플리케이션 커넥션정보 확인필요)
쓰레드 관련 환경 변수
- threads_connected : 현재 열려 있는 커넥션 수
- threads_cached : 재사용 가능한 동작중이지 않은 커넥션 수
- threads_created : 서버 시작후 현재까지 만들어진 쓰레드 수
- threads_running : sleeping가 아닌 동작중인 쓰레드
- slow_launch_threads : 쓰레드 생성시 시간이 2초이상이 걸린 쓰레드의 수 (0에 가까워야함 높아지면 부하가 높아진다는 뜻)
- threads_created/connections - 캐시 적중률(적중률이 낮으면 cache사이즈를 증가시켜주는것이 좋음)
핸들러 관련 환경 변수
- 모든 handler_xxx 환경 변수들은 내부의 테이블 핸들러의 동작 상황에 대한 정보를 제공
- 핸들러 관련 환경 변수에 대한 일반적인 해석
- handler_read_first가 높은 경우 -> 많은 풀 인덱스 스캔이 이루어짐 (메모리)
- handler_read_next가 높은 경우 -> 풀 인덱스 스캔과 레인지 스캔이 이루어짐 (메모리)
- handler_read_random가 높은 경우 -> 많은 풀 테이블 스캔과 레인지 스캔이 이루어짐 (디스크)
- handler_read_key가 높은 경우 -> 인덱스를 읽은 경우가 많음(메모리), 좋은 수치
성능 관련 문제를 보여주는 항목들
- MySQL의 느린 응답을 나타내는 항목
slow_queries, slow_luanch_thread
- 부하가 심하다는 것을 나타내는 항목
thread_created가 큰경우,
max_used_connections가 큰경우,
opend_tables가 큰경우(table_cache를 올리는 것이 좋음),
handler_read_key가 높은 경우
- 락 경쟁과 관련된 항목(MyISAM에서 중요함)
table_locks_waited VS table_locks_immediate (락획득시 대기/비대기 수치)
업데이트가 많아지면 Lock경쟁이 높아진다 -> InnoDB로 변경하는 것이 해결책
쿼리 관련 문제를 보여주는 항목들
- Created_tmp_disk_tables 환경 변수가 큰 경우
메모리에 적용할 수 없는 큰 임시 테이블이 많이 만들어졌다는 의미
-> tmp_table_size를 올려줘서 해결(사용에 주의)
- Select_xxx 환경변수의 값이 큰 경우
select쿼리가 최적화되지 못했음을 의미
-> select_full_join과 select_range_check는 일반적으로 더 많은 인덱스를 작성해줘야함
- Sort_xxx 환경변수 값이 큰 경우
sort_merge_passes가 큰 경우 ordering하는 작업비용이 크다는 의미
-> sort_buffer_size를 늘리거나 인덱스를 추가
[MySQL 모니터링 및 서버 최적화]서버 모니터링
퍼포먼스 모니터링
- OS자체 도구
리눅스/유닉스 :vmstat, iostat, mpstat
윈도우 : 작업관리자 성능 탭
- MySQL자체 도구
기동 후 메모리 상의 성능 수치를 추적
SHOW STATUS 커맨드로 확인 가능
Cricket, SNMP 또는 자체 제작 스크립트 사용 가능
MySQL Administrator 사용 가능(GUI형태)
- 쿼리는 MySQL 로그를 통해서 추적
General Log :
일반적으로 사용안함(IO증가량이 높음, 5.0이전은 설정변경시 재기동 필요),
모든 사용자의 입력을 로깅,
액션전에 저장되어 유용
Slow Query Log :
느린 쿼리에 대한 로그
MySQL에서 Thread 모니터링
- MySQL은 Thread기반 서버
SHOW FULLPROCESSLIST
STATE컬럼을 통해 각 쿼리가 현재 어떻게 수행되고 있는지를 확인가능
- 성능관련 문제는 아래 작업을 통해 확인 가능
Processlist 모니터링
SHOW STATUS의 내용을 확인
- 급박한 문제는 쓰레드 KILL을 통해 제거 가능
잘못된 쓰레드는 Processlist로 확인
해당 프로세스 ID를 KILL
MySQL STATUS
- 동작 상태에 대한 항목 수집
- 서버의 현재 동작 상태를 모니터링
- 서버를 최적화 할때 가이드로 이용
- Status항목은 아래 두가지 방법으로 확인 가능
MySQL Administrator - GUI형태로 제공
기본적인 STATUS 모니터링
- mysqladmin은 기본적인 관리툴 : 10초마다 갱신
3rd Party 모니터링 툴 - MyTOP
- MySQL의 정보를 TOP과 같이 보여주는 툴
- 전체 쓰레드 리스트를 보여줌
- Database나 Host별 필터링 가능
- 커넥션에 대한 kill을 쉽게 수행
- QPS를 쉽게 확인 가능
3rd Party 모니터링 툴 - innotop
- InnoDB엔진에 대한 Status는 "show engine innodb status"로 확인 가능
- 그 결과를 해석하기 편하게 정리해주는 툴
- http://www.xaprb.com/blog/2006/07/02/innotopmysql-innodb-monitor/
기본 STATUS 항목
- 서버의 시작후 동작시간은 uptime으로 확인 가능
- com_xxx항목은 서버시작 이후 xxx관련 명령이 얼마나 수행되었는지 나타냄
- Questions는 서버로 전송된 총 쿼리 수를 나타냄
- 이러한 전체 동작 양상과 select와 U/D/I 비율 등을 추출할 수 있음
[MySQL Stored Procedure]Stored Program의 보안
Stored Programs에 사용시 필요한 권한
- CREATE ROUTINE, ALTER ROUTINE, EXECUTE
실행모드 옵션 - DEFINER
- SQL SECURITY DEFINER
루틴실행이 DEFINER 지정 유저 권한으로 실행됨
생성자가 SUPER권한을 가졌으면 다른 계정 지정 가능
- 테이블의 권한이 없어도 프로시져를 통해서 접근이 가능
실행모드 옵션 - INVOKER
- 루틴을 호출하는 사람의 권한으로 실행
- Invoker 권한으로 실행시에는 권한이 없을 경우 에러 처리 필요
Stored Program의 성능은?
- 연산작업은 처리하지 않는다
- 통계작업 등은 네트워크 트래픽을 많이 사용하는 경우에는 성능이 좋다
- Self Join시 단계적 로직을 이용하여 Self Join을 피해서 처리하면 성능향상
- Update문 안에 SELECT가 있을경우 커서를 이용해서 분리시 성능향상 가능
[MySQL Stored Procedure]View
CREATE VIEW Syntax
[ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]
[DEFINER={USER|CURRENT_USER}]
[SQL SECURITY {DEFINER|INVOKER}]
VIEW view_name [(column list)]
AS select_statement
[WITH [CASCADE|LOCAL] CHECK OPTION]
ALGORITHM 종류
- UNDEFINED : MySQL이 자체적으로 선택, 대부분 MERGE
- MERGE : 뷰와 포함된 쿼리를 효율적인 방식으로 Merge해서 사용
- TEMPTABLE : 뷰와 연결된 임시테이블을 만들고 사용, 인덱스를 전혀 사용 못함
뷰와 테이블의 차이
- 뷰는 정의시점에 고정, 테이블에 컬럼이 추가되어도 반영되지 않음
- SELECT문에서 system이나 user변수 사용 못함
- 뷰 정의시 임시테이블 참고 못함
- 트리거를 뷰에 연결시킬 수 없다
- 뷰정의시 ORVER BY를 포함할 수 있으나 VIEW사용시 ORDER BY를 지정하면 정의된 ORDER BY는 무시됨
[MySQL Stored Procedure]Trigger
트리거 생성 문법
{BEFORE|AFTER}
{UPDATE|INSERT|DELETE}
ON table_name
FOR EACH ROW
trigger_statements
- Definer의 경우 슈퍼권한이 있을 경우 다른 계정이 지정가능
컬럼값의 참조
- NEW : 새로 입력된 값(Insert,Update에서 사용가능)
- OLD : 삭제된 데이터를 지칭(Delete,Update에서 사용가능)
BEFORE, AFTER 트리거
- AFTER의 경우 값의 변경이 불가능
Trigger 사용
- 중요테이블 로깅, 데이터 입력력 트랙킹, 입출력 데이터 검증
[MySQL Stored Procedure]Stored Function
Stored Function이란?
- 하나의 값을 반환하는 stored program
- OUT, INOUT변수가 아닌 RETURN으로 값을 반환
- 내장함수와 동일한 형태로 DML에서 사용 가능
- 복잡한 코딩을 줄여줄 수 있다
Stored Function만들기
- Syntax
RETURNS datatype
[[NOT]DETERMINISTIC]
[{CONTAINS SQL|NO SQL|MODIFIES SQL DATA|READS SQL DATA}]
[SQL SECURITY {DEFINER|INVOKER}]
[COMMENT string]
function_statement
- Return절은 필수임
- 파라메터는 IN으로 처리됨
Return 문
- 리턴문은 하나만 있는 것이 좋다(조건문으로 분기시 변수 사용)
DETERMINISTIC과 SQL절
- 바이너리 로그를 사용할 경우 반드시 고려해야 함
바이너리 로그를 사용하는 경우 해당 FUNCTION이 동일한 결과(DETERMINISTIC)를 가지는지 확인해야함(시스템시간등의 사용 유의)
모든 Stored program을 위한 기본값은 NOT DETERMINISTIC CONTAINS SQL이므로 반드시 명시적으로 표시가 필요
NOW() 함수 또는 시간을 기반으로 하는 함수와 하나의 랜덤값을 발생시키는 함수는 NON DETERMINISTIC이 아님(사용해도 무방)
이를 막기 위해서 DETERMINISTIC, NO_SQL또는 READ_SQL_DATA키워드를 정의하거나 log_bin_trust_routine_creater옵션을 1로 설정해야 실행됨
Stored Function 생성시 주의할점
- 옵티마이저를 사용하지 않으므로 성능 테스트 필요
- 자주 사용되는 쿼리에는 사용하지 않음
[MySQL Stored Procedure]트랜잭션 관리
Isolation Level
- READ UNCOMMITED
Dirty read허용
속도는 빠르지만 commit 되지 않은 ROW를 다른 세션에서 볼 수 있음
- READ COMMITED
Commit된 row만 읽을 수 있음
- REPEATABLE READ
트랜잭션이 시작된 시점 기준의 값을 볼 수 있음
Default 설정
- SERIALIZABLE
각 트랜잭션이 독립적으로 동작
SELECT시에도 락을 사용
트랜잭션 관련 Command
- START TRANSACTION : 시작, AUTO_COMMIT를 0으로 설정
- COMMIT : 트랜잭션의 변경 내용을 저장, LOCK해제
- ROLLBACK : 트랜잭션의 변경 취소
- SAVEPOINT savepoint_name : 저장단계 지정
- ROLLBACK TO SAVEPOINT savepoint_name : 단계로 롤백
- SET TRANSACTION : Isolation레벨 지정
- [LOCK|UNLOCK] TABLE : 테이블에 락을 지정/해제
유의사항
- 아래 구문들은 트랜잭션 처리가 되지 않음
CREATE [DATABASE]FUNCTION|INDEX|PROCEDUER|TABLE]
DROP [DATABASE]FUNCTION|INDEX|PROCEDUER|TABLE]
LOCK TABLES/UNLOCK TABLES
RENAME TABLE/TRUNCATE TABLE
BEGIN, SET AUTOCOMMIT=1, START TRANSACTION
LOAD MASTER DATA
LOCK의 종류별 발생 Case
- UPDATE : 변경되는 모든 ROW에 락설정
- INSERT : PK, Unique Key레코드에 락설정
- LOCK TABLES : 전체 테이블에 락설정
- SELECT ... FOR UPDATE : Select 결과 row에 대해 Exclusive 락 설정, Read/Write 불가능
- SELECT ... LOCK IN SHARE MODE : Select 결과 row에 대해 Shared Lock 설정, Read는 가능
Deatlock
- InnoDB는 DeadLock상황을 탐지해서 트랜잭션들을 강제로 Rollback함
LockTimeout
- 지정한 시간동안 Lock를 획득하지 못하면 Rollback처리
Locking 전략
- Pessimistic Locking Strategy
트랜잭션에서 읽는 row에 미리 락을 설정
concurrent update가 빈번하다 가정
단순하고 견고한 코드
트랜잭션 처리 시간이 길어지는 경우 성능 저하 유발
- Optimistic Locking Strategy
마지막으로 업데이트 하는 순간 Lock를 확인하고 처리
update 빈도가 낮다고 가정
트랜잭션 디자인 가이드라인
- 최대한 작게 유지
- Rollback는 최대한 자제 - Retry할 수 있도록 예외 처리를 하는 것이 좋음
- Savepoint는 사용 자제
- Pessimistic Locking Strategy를 기본으로 하고 처리량이 중요한 경우 Optimistic 고려
[MySQL Stored Procedure]장애처리(Handler)
Condition Handler
- Syntax
[SQLSTATE sqlstate_code|MySQL error code|condition name]
handler_actions
핸들러의 종류
- EXIT핸들러
현재 실행중인 블록 중단, outer블럭일 경우 종료됨
- CONTINUE
에러를 발생시킨 문장이 계속 실행됨
핸들러의 조건
- MySQL Error Code : 에러코드를 조건으로 받음
- ANSI-Standard SQLSTATE code : ANSI SQL 2003 표준 SQLSTATE 코드를 조건으로 받음
- Named Conditions
사용자가 임의로 에러코드에 이름을 부여하여 코드의 가독성을 높인다
DECLARE CONTINUE HANDLER FOR foreign_key_error SET duplicate_key=1
SQL 2003에서 빠진 부분
- SQLCODE 또는 SQLSTATE에 대한 직접 접근
- SIGNAL문의 사용
[MySQL Stored Procedure]커서의 사용
INTO절에서 SELECT
- 오직 하나의 값만 나오게 처리해야함
커서의 생성
- 하나이상의 결과를 return하기 위해서 사용
- 커서의 선언은 모든 변수를 생성한 이후에 선언해야함
- Syntax
커서의 사용
- OPEN : 커서를 사용하기 위해서 fetch전에 반드시 처리
- FETCH : 커서가 다음 ROW로 이동
- CLOSE : 커서를 꼭 닫아줘야함
- 전체 결과를 FETCH하는 경우 LOOP를 사용하며 이때 마지막 row을 fetch할때 "no data to fetch"에러를 발생한다 이것을 피하기 위해서 error handler을 정의해서 해결
커서의 Loop
- 예시
dept_loop : LOOP
FETCH dept_csr INTO l_id, l_name, l_location;
IF no_more_departments=1 THEN
LEAVE dept_loop;
END IF
SET l_count=l_count+1
CLOSE dept_csr;
SET no_more_departments=0;
END LOOP;
Nested Cursor Loops
- 하나의 커서 종료후 not found변수를 reset한다.
- NOT FOUND의 경우 커서별로 지정할 수 있다(같은 블럭내에서는 하나의 not found변수만 활성화)