Oracle/MySQL 관련 게시판
2002/08/06(17:59) from 61.80.99.240
작성자 : 강줄기 (jkkang65@hanmail.net) 조회수 : 6137 , 줄수 : 213
Re: [MySQL] MySQL 성능 향상 방법 - 2
제  목:[번역/mysql]10.11-10.15 mysql 성능향상법        관련자료:없음  [165]
보낸이:문태준  (taejun  )  1999-01-28 01:29  조회:739
10.11 INSERT 문에서 속도에 영향을 미치는 부분 <** insert 최적화 **>

insert 하는 시간은 다음와 같이 구성된다:

   Connect: (3)
   Sending query to server: (2)
   Parsing query: (2)
   Inserting record: (1 x size of record)
   Inserting indexes: (1 x indexes)
   Close: (1)

(숫자)는 비례적인 시간이다. 이것은 테이블을 개방할때 초기의 overhead를
고려하고 있지는 않다. (매 동시병행적으로 수행되는 질의마다 발생)

The size of the table slows down the insertion of indexes by N log N (B-trees).


테이블의 크기는 N log N(B-trees)에 따라 인덱스의 입력이 느려진다.
(**말이 좀 이상. 테이블이 커짐에 따라 인덱스 생성도 느려진다는
뜻이겠죵 **)

테이블에 락을 걸거나 insert 문에서 다중 값 목록을 사용하여 입력 속도를
빠르게 할 수 있다. 다중 값 목록을 사용하면 단일 insert 보다 5배 정도
속도가 빨라진다.

mysql> LOCK TABLES a WRITE;
mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);
mysql> INSERT INTO a VALUES (8,26),(6,29);
mysql> UNLOCK TABLES;

주요한 속도 차이는 모든 INSERT 문이 완료되고 난 후에 한번에 인덱스 버퍼가
쓰여기지 때문에 생긴다. 보통 서로 다른 여러 INSERT 문이 있으면 많은
인덱스 버퍼 플러쉬가 있을 것이다. 모든 줄을 단일 문으로 입력하면 락은 필요
없다.

락킹은 또한 다중 연결 테스트의 총 시간을 줄일 수는 있다. 그러나 어떤 스레드
에서는 총 대기시간은 증가할 수 있다.(왜냐면 락을 기다리기 때문이다)
예를 들어보자:

thread 1 does 1000 inserts
thread 2, 3, and 4 does 1 insert
thread 5 does 1000 inserts

락을 사용하지 않으면 2, ,3 4는 1과 5 전에 끝마칠 것이다. 락을 사용하면 2,3,4는
아마도 1이나 5 전에 끝나지 않을 것이다. 그러나 총 시간은 40% 빨라진다.

INSERT, UPDATE, DELETE 오퍼레이션은 mysql에서 매우 빠르다. 그렇기 때문에
줄에서 5개  이상의 insert나 update를 할 때 락을 추가하면 더 좋은
성능을 얻을 수 있다. 줄에 매우 많은 자료를 입력한다면 다른 스레드에서
테이블에 접근하도록 하기 위해 때때로(각 1000줄마다) UNLOCK TABLES를  
사용하는 LOCK TABLES 실행하면 된다. 이렇게 하면 좋은 성능을 낼 수 있다.
(** 열심히 입력을 하고 중간에 락을 풀었다가 다시 락을 거는 것 반복함**)

물론 LOAD DATA INFILE 이 더 빠르다.



10.12 DELETE 문에서 속도에 영향을 미치는 부분 <** DELETE 문 최적화 **>

레코드를 삭제하는 시간은 정확히 인덱스 숫자에 비례한다. 레코드를 빠르게
지우기 위해 인덱스 캐쉬의 크기를 증가시킬 수 있다. 기본 인덱스 캐쉬는
1M 이다; 빠르게 삭제하기 위해 증가되어야 한다.(충분한 메모리를 가지고 있
다면 16M로 하자)



10.13 mysql에서 최대 속도를 얻는 방법

벤치마킹을 시작하자! mysql 벤치마크 스위트에서 어떤 프로그램을 사용할 수
있다. (일반적으로 'sql-bench' 디렉토리에 있음) 그리고 입맞에 맞게 수정
하자. 이렇게 하면 당신의 문제를 해결할 수 있는 다른 해결책을 찾을 수 있
으며 당신에게 가장 빠른 해결책을 테스트할 수 있다.

- mysqld를 적절한 옵션으로 시작하자. 메모리가 많을수록 속도가 빠르다.
 10.1 [MySQL parameters] 참고.
- SELECT 문의 속도를 빠르게 하기 위해 인덱스를 만들자.
10.4 [MySQL indexes] 참고.
- 가능한 효율적으로 컬럼 타입을 최적화하자. 예를 들면 가능한 NOT NULL로
컬럼을 정의하자. 10.10 [Table efficiency] 참고.
- --skip-locking 옵션은 SQL 요청에서 파일 락킹을 없앤다. 속도가 빨라지지
만 다음의 과정을 따라야 한다:
       ㅇ isamchk로 테이블을 체크하거나 수리하기 전에 mysqladmin flush-tables
로 모든 테이블을 플러시해야 한다. (isamchk -d tbl_name은 언제나 허용된다.
왜냐하면 이건 단순히 테이블의 정보를 보여주기 때문이다)
       ㅇ 동시에 뜬 두개의 mysql 서버가 동일한 테이블을 업데이트하려
한다면 동일한 데이터 파일에 두개의 mysql 서버를 띄우면 안된다.

       --skip-locking 옵션은 MIT-pthreads로 컴파일할때 기본값이다. 왜냐면
모든 플랫폼의 MIT-pthreads에서 flock()가 완전하게 지원이 되지 않기 때문이다.

- 업데이트에 문제가 있다면 업데이트를 미루고 나중에 하자. 많은 업데이트를
하는 것이 한번에 하나를 업데이트하는 것보다 더 빠르다.
- FreeBSD 시스템에서 MIT-pthreads에 문제가 있으면 FreeBSD 3.0 이후 버전
으로 업데이트 하는것이 좋다. 이렇게 하면 유닉스 소켓을 사용하는 것이
가능하며(FreBSD에서 유닉스 소켓이 MIT-pthreads에서 TCP/IP 연결을 사용
하는 것보다 빠르다) 그리고 스레드 패키지가 조정(intergrated?)되어야 한다.
- 테이블이나 컬럼 단계를 체크하는 GRANT는 성능을 떨어뜨린다.


10.14 로우 포맷과 다른 점은 무엇인가? 언제 VARCHAR/CHAR을 사용해야 하는가?

mysql은 실제의 SQL VARCHAR 타입이 없다.
그대신 mysql은 레코드를 저장하고 이것을 VARCHAR로 에뮬레이트하는데
세가지 방법이 있다.

테이블에 VARCHAR, BLOB, TEXT 컬럼이 없으면 고정 row size를 사용한다.
그외에는 동적 row size를 사용한다. CHAR 과 VARCHAR 컬럼은 애플리케이션의
관점에서 동일하게 취급된다; 둘다 trailing space는 컬럼을 가져올때
제거된다.

isamchk -d 를 이용 테이블에서 사용하는 포맷을 체크할 수 있다.
(-d 는 "테이블 묘사"를 의미)

mysql은 세가지 다른 테이블 포맷을 가지고 있다; 고정길이, 다이나믹, 압축.


고정 길이 테이블
- 기본 포맷. 테이블에 VARCHAR, BLOB, TEXT 컬럼이 없을 때 사용.
- 모든 CHAR, NUMERIC, DECIMAL 컬럼은 컬럼 길이에 space-padded 이다.
(** space-padded를 무엇이라고 번역해야 할지 애매모호해서 **)
- 매우 빠름
- 캐쉬하기 쉽다
- 손상 후 복구가 쉽다. 왜냐면 고정된 위이에 레코드가 위치하기 때문이다.
- 많은 양의 레코드가 지워졌거나 운영 시스템에서 자유 공간을 늘리길
원치 않는다면 (isamchk를 이용) 재조직화할 필요없다.
- 보통 다이나믹 테이블보다 많은 디스크 공간을 필요로 한다.


다이나믹 테이블
- 테이블이 VARCHAR, BLOB, TEXT 컬럼을 포함하고 있을 때 사용.
- 모든 문자열 컬럼은 다이나믹하다.(4보다 작은 길이를 가진 문자열 제외)
- 컬럼이 문자열 컬럼에서 비었거나 ('') 숫자형 컬럼에서 0(NULL 값을
가진 컬럼과 동일한 것이 아니다) 을 나타내는 비트맵이 모든 레코드 앞에
선행된다. 문자열 컬럼에서 trailing space를 제거한 후 zero의 길이를
가지거나 숫자형 컬럼이 zero의 값을 가지면 비트 맵으로 표시되고
디스크에 저장되지 않는다. 비지 않은 문자는 문자내용에 길이
바이트만큼 추가되어 저장된다.
- 보통 고정 길이 테이블보다 디스크 공간 절약.
- 줄의 길이를 확장하는 정보를 가지고 줄을 업데이트하면 줄은 단편화될
것이다. 이런 경우 더 좋은 성능을 위해 때때로 isamchk -r 을 실행해야
한다. 통계적으로(?) isamchk -ei tbl_name을 사용하자.
- 손상후 복구가 어렵다. 왜냐면 레코드가 많은 조각드로 단편화되고 링크
(단편)가 없어지기 때문이다.
- 다이나믹 사이즈 테이블의 예상되는 열 길이 :
   3
   + (number of columns + 7) / 8
   + (number of char columns)
   + packed size of numeric columns
   + length of strings
   + (number of NULL columns + 7) / 8

각 링크마다 6 바이트가 더 있다. 다이나믹 레코드는 업데이트로 레코드가 늘
어날때마다 링크된다. 각 새로운 링크는 최소 20바이트일 것이며, 그래서
다음의 확장은 아마도 동일한 링크로 될 것이다. 그게 아니라면 다른 링크가
있을 것이다. isamchk -ed 로 얼마나 많은 링크가 있는지 체크할 수 있다.
모든 링크는 isamchk -r 로 제거할 수 있다.(** ?? **)

There is a penalty of 6 bytes for each link. A dynamic record is linked
whenever an update causes an enlargement of the record. Each new link will
be at least 20 bytes, so the next enlargement will probably go in the same
link. If not, there will be another link. You may check how many links
there are with isamchk -ed. All links may be removed with isamchk -r.


압축 테이블

- 읽기 전용 테이블은 pack_isam 유틸리티로 만들 수 있다. 확장 mysql 이메일
지원을 구입한 모든 고객은 내부적인 용도로 pack_isam을 사용할 권리가
주어진다.
- 압축해제 코드는 모든 mysql 배포판에 있으므로 pack_isam이 없는 고객도
pack_isam으로 압축된 테이블을 읽을 수 있다. (테이블이 같은 플랫폼에서
압축되어 있는한)
- 매우 적은 디스크 용량을 사용.
- 각 레코드는 개별적으로 압축이 된다.( 매우 적은 액세스 overhead) 레코드의
헤더는 테이블의 가장 큰 레코드에 따라 (1-3 바이트) 고정된다. 각 컬럼은
다르게 압축이 된다. 압축 타입은 다음과 같다:

       ㅇ 일반적으로 각 컬럼마다 다른 Huffman 테이블이다.
       ㅇ Suffic 공간 압축
       ㅇ Prefix 공간 압축
       ㅇ 0 값을 가진 숫자는 1비트로 저장.
       ㅇ integer 컬럼의 값이 작은 범위를 가졌다면, 컬럼은 최대한 작은
타입으로 저장된다. 예를 들면 BIGINT 컬럼은 모든 값이 0부터 255라면 TINIINT
컬럼(1바이트)로 저장된다.
       ㅇ 컬럼이 몇가지 가능한 값으로만 구성되어 있다면, 컬럼 타입은
ENUM으로 변환된다.
       ㅇ 컬럼은 위 압축 방법을 조합하여 사용한다.
- 고정 길이나 다이나믹 길이의 테이블을 다룰 수 있다. 그러나 BLOB나 TEXT
컬럼은 다룰 수 없다.
- isamchk로 압축을 해재할 수 있다.

mysql은 다른 인덱스 타입을 지원한다. 그러나 일반적인 타입은 NISAM이다.
이것은 B-tree 인덱스이며 모든 키의 갑을 합하여 (키 길이+4)*0.67로
인덱스 파일의 크기를 대강 계산할 수 있다. (이것은 모든 키가 정렬된
순서로 입력된 가장 나쁜 경우이다)


String indexes are space compressed. If the first index part is a string, it wi
ll also be prefix compressed.
Space compression makes the index file smaller if the string column has a lot o
f trailing space or is a
VARCHAR column that is not always used to the full length. Prefix compression h
elps if there are many
strings with an identical prefix.

문자열 인덱스는 공간이 압축된다. 첫번째 인덱스 부분이 문자열이라면,
prefix가 압축된다. 문자열 컬럼이 다량의 trailing space를 가졌거나
언제나 완전한 길이를 사용하지 않는 VARCHAR 컬럼일 때 space 압축은
인덱스 파일을 더 작게 만든다. prefix 압축은 많은 문자열에 동일한
prefix가 있을 때 유용하다.
Modify Delete Post Reply Backward Forward List
Powered by Kang Jul Ki