2011년 1월 30일 일요일

MySQL 5.1.42 SELECT 쿼리의 SharedLock 관련 버그


MySQL 5.1.42 버전에서 SELECT 쿼리가 Shared(Read) Lock을 거는 버그가 있었다.
모든 쿼리가 그런 것이 아니라, 확인된 쿼리는 아래 형태의 SubQuery로 Counting하는 쿼리이다.

간단히 재현하는 방법은 아래와 같다.

-------------------------------------------------------------
Session 1
-------------------------------------------------------------
CREATE TABLE lock_test (
  fd1 int not null,
  fd2 int not null,
  fd3 int not null,
  INDEX ix_12 (fd1, fd2),
  INDEX ix_21 (fd2, fd1)
) ENGINE=InnoDB;

INSERT INTO lock_test VALUES (1,2), (2,1);

COMMIT;

BEGIN;
SELECT (
  (SELECT COUNT(*) FROM lock_test WHERE fd1=1) +
  (SELECT COUNT(*) FROM lock_test WHERE fd2=1)
) AS cnt;


-------------------------------------------------------------
Session 2
-------------------------------------------------------------
SHOW ENGINE INNODB STATUS;


...
---TRANSACTION 1 3051889568, ACTIVE 5 sec, process no 9090, OS thread id 1261443392
19 lock struct(s), heap size 6752, 2 row lock(s)
MySQL thread id 178344, query id 17362465365 localhost root
Trx read view will not see trx with id >= 1 3051889569, sees < 1 3051889555
...

이 버그는 MySQL 5.0.68 이나 MySQL 5.1.54 버전에서는 발견되지 않았으며,
MySQL 5.1.42에서만 나타났고, MySQL 5.1.45 버전에서 Fix되었다.

그리고 관련해서 SHOW INNODB STATUS ENGINE에서 Deadlock을 보고하는 경우가 있는데,
실제로 이 내용은 Deadlock이 아니라, InnoDB가 CPU 사용률을 줄이기 위해서
ShortPath로 Deadlock으로 간주하도록 하는 부분도 있었다.

Bug #49001 (http://bugs.mysql.com/49001)

2011년 1월 13일 목요일

MySQL 5.5 GA 버전 릴리즈 : 새로운 기능(New Features) 소개

조금은 지난 일이지만, MySQL 5.5의 GA(General Available) 버전이 릴리즈 되었다.
이번 버전은 MySQL이 Oracle로 인수되고 난 이후 처음 릴리즈된 버전이다라는 것이
나름 Oracle 입장에서는 큰 의미를 주고 있는 듯 하며,
그 때문인지 이런 저런 새로운 기능들과 성능 향상을 WhitePaper에서 이야기하고 있다.

하지만, 대 부분은 InnoDB 가 플러그인 버전으로 변경되는 MySQL 5.1 수준에서
적용된 내용들이 재탕으로 이야기되고 있는 부분이 많고,
기존 버전에 비해 Linux는 370%, Windows는 1500%가 향상되었다고 이야기하고 있는데,
간단히 그래프를 보면, 개인적으로는 성능 향상이라고 하기보다는 동시성 처리가 상당히 개선되었다고
생각된다.


WhitePaper에서 이야기하고 있는 더 상세한 개선점들은 아래와 같다.


주요 개선 사항


  • InnoDB가 이제부터는 MySQL의 기본 스토리지 엔진으로 적용됨
    InnoDB as the default database storage engine 
  • 윈도우즈 서버에서 성능과 확장성 개선됨
    Improved Performance and Scalability on Windows 
  • 모든 플랫폼의 서버에서 멀티 코어 아키텍쳐를 최대한 사용 가능하도록 성능과 확장성 개선
    Improved Performance and Scalability to fully utilize the computing power of modern multi-core architectures across all platforms 
  • 가용성 개선됨
    Improved Availability 
  • 관리성과 효율성 개선됨
    Improved Manageability and Efficiency 
  • 사용성 개선됨
    Improved Usability 
  • 진단 도구들의 개선
    Improved Instrumentation and Diagnostics



[성능 및 확장성] 개선 사항

  • 쓰레드의 동시성 처리 성능 향상
    Improved Default Thread Concurrency
  • 백그라운드 I/O 쓰레드의 컨트롤 기능 도입
    Control of Background I/O Threads
  • InnoDB의 마스터(Replication의 마스터 아님) 쓰레드 의 I/O 비율 조절 기능 도입
    Control of Master Thread I/O Rate
  • 운영체제의 메모리 할당 기능을 사용하도록 조절 가능
    Control of Using Operating System Memory Allocators
  • AdaptiveHashIndex의 관리 기능 도입
    Control of Adaptive Hash Indexing
  • Insert Buffer 기능의 관리 파라미터 도입
    Control of Insert Buffering
  • Faster locking 알고리즘을 이용한 확장성 개선
    Improved scalability via Faster Locking algorithm
  • Group Commit 기능 복구(다시 사용할 수 있도록 개선됨)
    Restored Group Commit
  • 복구 (Recovery) 성능 개선
    Improved Recovery Performance 
  • InnoDB의 Buffer pool을 여러개 설정할 수 있도록 개선됨
    Multiple Buffer Pool Instances
  • Rollback 세그먼트를 여러개 설정할 수 있도록 개선됨
    Multiple Rollback Segments
  • Linux의 네이티브 비동기 I/O 적용
    Native Asynchronous I/O for Linux
  • Insert buffer의 기능을 인덱스의 INSERT뿐만 아니라 DELETE까지도 확장 적용
    Extended Change Buffering: Now with Delete and Purge Buffering
  • InnoDB buffer pool의 작업과 Flush 리스트를 관리하는 Mutext를 분리 적용
    Improved Log Sys Mutex and Separate Flush List Mutex
  • Bufer pool의 불필요한 페이지의 purge 작업의 스케쥴링 개선
    Improved Purge Scheduling
  • 트랜잭션 내에서 메타 락(오브젝트 잠금)과 다른 트랜잭션의 DDL 문장간의 락 점유 방식 개선
    Improved Metadata Locking Within Transactions





[가용성] 개선 사항

  • 반 동기화 복제
    Semi-synchronous Replication
  • 복제시 마스터와 슬레이브 사이의 Heartbeat 메시지 적용
    Replication Heartbeat
  • Relay 로그 자동 복구 기능
    Automatic Relay Log Recovery
  • 특정 Master의 Replication 로그 필터링 기능
    Replication Per Server Filtering
  • 마스터와 슬레이브간의 다른 데이터 타입 허용(동일 String 또는 Numeric 타입들 내에서)
    Replication Slave Side Data Type Conversions


[관리 및 효율성] 개선 사항

  • 빠른 인덱스 생성 및 삭제
    Faster Index Creation
  • 효율적인 데이터 압축
    Efficient Data Compression
  • LOB (BLOB, TEXT) 데이터 타입이나 길이가 긴 VARCHAR 타입의 관리 효율성 향상
    Efficient Storage Options for Large Objects and Variable-Length Columns
  • INFORMATION_SCHEMA의 잠금 및 트랜잭션 그리고 압축 관련 딕셔너리 추가됨
    New INFORMATION_SCHEMA tables 
  •  


[사용성] 개선 사항

  • SIGNAL과 RESIGNAL 명령 추가 (StoredRoutine 관련)
    New SQL Syntax for SIGNAL/RESIGNAL
  • 새로운 테이블이나 인덱스 파티셔닝 기능(Non-Integer 타입으로 List나 Range 파티션 생성 가능)
    New Table/Index Partitioning Options


[진단도구] 개선 사항

  • INFORMATION_SCHEMA의 잠금 및 트랜잭션 그리고 압축 관련 딕셔너리 추가됨
    New PERFORMANCE_SCHEMA


가장 중요한 변화 : MySQL 5.5 버전부터는 Enterprise Edition의 경우 소스를 다운로드할 수 없게 되었다. 이제부터 Enterprise edition은 빌드된 MySQL만 사용할 수 있게 될 것 같다.
Community edition은 예전과 동일함








2011년 1월 11일 화요일

MySQL의 버전별 기능(Features) 변경 이력


VersionFeatures
추가변경삭제
5.5
  • MyISAM 대신 InnoDB가 MySQL의 기본 스토리지 엔진으로 채택
  • 5.4.2
  • Plugin버전의 InnoDB가 Builtin 버전으로 다시 적용
  • 5.1.38
  • InnoDB Plugin
  • 5.1.24
    (Enterprise version)

  • "SHOW PROFILE"
  • 5.1.12
  • "general_log" 파라미터

  • General query log를 동적으로 변경 가능
  • 5.1.8
  • "Mixed" 복제 모드
  • 5.1.6
  • Partition pruning 기능
  • 5.1.5
  • EXPLAIN PARTITIONS(파티션 테이블의 실행 계획) 지원
  • "RBR"(Row Based Replication) 복제 모드
  • 5.1
  • Plugin API 도입
  • Plugin버전의 InnoDB 릴리즈 (InnoDB 의 많은 성능 개선과 변화가 있음)

  • BDB 스토리지 엔진
  • 5.0.32
    (Community version)

  • "SHOW PROFILES"
  • 5.0.7
  • LIMIT의 파라미터도 PreparedStatement에서 변수화 가능
  • 5.0.5
  • BIT 데이터 타입이 MEMORY, InnoDB, BDB, NDBCLUSTER 스토리지 엔진에 구현됨
  • 5.0.3
  • FEDERATED 스토리지 엔진
  • 신규 함수 추가
    STDDEV_POP()
    STDDEV_SAMP()
    VAR_POP()
    VAR_SAMP()

  • BIT 데이터 타입이 TINYINT와 호환성 없어짐


  • NUMERIC와 DECIMAL 타입의 저장 방식이 String에서 Binary로 변경
  • 5.0.2
  • TRIGGER 도입

  • HAVING 조건에 SELECT컬럼, GROUP-BY컬럼, OUTER-서브쿼리의 값 사용 가능(ANSI 표준)
  • 5.0.1
  • VIEW 도입

  • HAVING 조건에 SELECT컬럼, GROUP-BY컬럼, OUTER-서브쿼리의 값 사용 가능(ANSI 표준)
  • 5.0
  • StoredRoutine (Procedure,Function) 도입
  • CURSOR 도입
  • Archive 스토리지 엔진
  • INFORMATION_SCHEMA 딕셔너리 데이터베이스 도입 (ANSI 표준)

  • ISAM 스토리지 엔진 제거
  • 4.1.11
  • Blackhole 스토리지 엔진
  • 4.1.4
  • CVS 스토리지 엔진
  • 4.1
  • SubQuery 도입
  • WHOW WARNINGS
  • CREATE TABLE ... LIKE ...
  • GROUP_CONCAT() 구현
  • 유니코드(UTF8, UCS2) 지원
  • GIS 관련 기능(Spatial extension) 지원
  • ALTER DATABASE 명령 지원
  • DUAL 테이블 내부 지원(타 DBMS와의 호환성 유지)
    "SELECT 1" 명령과 "SELECT 1 FROM DUAL" 명령은 동일
  • Memory 스토리지 엔진에서 B-Tree 허용
  • EXPLAIN EXTENDED 구현

  • Column 코멘트 구현(CREATE TABLE...)
  • PASSWORD() 함수의 알고리즘 업그레이드
    기존 알고리즘은 OLD_PASSWORD()로 변경됨
  • CHAR, VARCHAR 타입의 길이가 바이트수에서 문자수로 변경됨
  • 파생 테이블(Derived tables) 내에서 UNION 사용 가능
  • 4.0.18
  • "TYPE" 키워드가 "ENGINE" 키워드로 변경(CREATE TABLE...)
  • 4.0.14
  • InnoDB의 BLOB와 TEXT 타입에 대한 인덱스 지원
  • 4.0.4
  • JOIN DELETE (Multiple Delete) 도입
  • JOIN UPDATE (Multiple Update) 도입
  • 4.0.2
  • Memory 스토리지 엔진에서 NULLABLE 컬럼의 인덱스 지원

  • VARCHAR 컬럼의 길이가 1~255에서 0~255로 변경됨
  • 4.0.1
  • Query Cache 도입
  • 4.0
  • UNION 집합 연산 도입
  • SQL_CALC_FOUND_ROWS 힌트와 FOUND_ROWS() 함수 구현

  • UPDATE와 DELETE 구문에 ORDER BY 사용 허용
  • 3.23
  • EXPLAIN(쿼리 실행계획) 구현
  • 전문 검색(Fulltext search) 도입
  • JOIN(SELECT만) 도입
  • NULL-SAFE 연산자(<=>) 도입

  • 길이가 0인 CHAR 컬럼 허용
  • 2011년 1월 3일 월요일

    JOIN DELETE (Multiple-table Delete)

    두개의 테이블을 조인하여 UPDATE를 실행하는 것(JOIN UPDATE)과 같이,
    두개의 테이블을 조인하여 그 결과 레코드를 삭제하는 것도 가능하다.

    이를 JOIN DELETE 또는 Multiple-Table DELETE라고 하는데,
    JOIN DELETE는 아래 두 가지 문법으로 작성할 수 있다.

    • DELETE와 FROM 절 사이에 삭제할 테이블 명시

      DELETE
      t1, t2
      FROM test1 t1 INNER JOIN test2 t2 INNER JOIN test3 t3
      WHERE t1.id=t2.id
        AND t2.id=t3.id;

    • FROM과 USING 절 사이에 삭제할 테이블 명시
      DELETE
      FROM t1, t2
        USING test1 t1 INNER JOIN test2 t2 INNER JOIN test3 t3
      WHERE t1.id=t2.id
        AND t2.id=t3.id;

    JOIN DELETE에서도 주의해야 할 사항이 있는데,
    • 조인 결과 레코드가 삭제되는 테이블이 어느 테이블인가 ?.
      • 첫번째 문법에서는 DELETE 키워드와 FROM 절 사이에 명시된 테이블의 레코드만 삭제한다.
      • 두번째 문법에서는 FROM 절과 USING 절 사이에 명시된 테이블의 레코드만 삭제한다.
      • 위의 두 예제에서는 t1(test1) 테이블과 t2(test2) 테이블의 레코드만 삭제하게 된다. t3(test3)의 레코드는 조인에만 참여하고 삭제되지는 않는다.
    • 테이블의 별명(Alias)는 어디에서 정의해야 하는가 ?.
      • 첫번째 문법에서는 FROM 절 이하에서 테이블의 별명(Alias)를 지정하며, 이 이외의 위치에서는 Alias를 지정할 수 없고, FROM 절에서 정의된 Alias를 사용만 해야 한다.
      • 두번째 문법에서는 USING 절 이하에서만 테이블의 별명(Alias)를 정의해야 한다.

    JOIN UPDATE (Multiple-table Update)

    MySQL에서도 두개 이상의 테이블을 조인하여,
    어떤 테이블의 필드 값을 또 다른 테이블의 컬럼에 업데이트하는 것이 가능하다.
    물론, 이때 2개 이상의 테이블의 레코드를 상호 업데이트하는 것도 가능하다.


    이러한 형태의 작업을 JOIN UPDATE 또는 Multiple table Update라고 표현한다.
    아래의 쿼리를 한번 살펴보자.


    UPDATE test1 t1, test2 t2
    SET t1.target_fd=t2.source_fd
    WHERE t1.fdpk=t2.fdpk;


    이 쿼리는 test1 테이블과 test2 테이블을 fdpk 컬럼으로 조인한 뒤,
    test2 테이블의 source_fd 컬럼의 값을 test1 테이블의 target_fd 컬럼에 업데이트를 실행하게 된다.
    이 예제는 하나의 테이블만 업데이트하고 있지만, 아래와 같이 상호 업데이트 하는 형태도 가능하다.


    UPDATE test1 t1, test2 t2 
    SET t1.target_fd=t2.source_fd,
      t2.target_fd=t1.source_fd
    WHERE t1.fdpk=t2.fdpk;

    물론 3개 이상의 테이블을 조인하여
    n개의 테이블로부터 필드 값을 모아서 하나의 테이블로 업데이트하는 것도 가능하다.



    단, 이런 형태의 업데이트에서 주의해야 할것이 있다.
    1. UPDATE 쿼리에 대해서는 실행 계획을 확인할 수 없으므로, JOIN의 순서를 확인할 수 없다.
       조인의 순서에 따라서 상당한 처리 성능 차이를 보일 수 있으므로, 가능한 조인의 순서를 고정시켜서 실행한다.
    2. JOIN에 참여하는 테이블 Tab1과 Tab2가 1:M 관계로 조인될 경우
       Tab1 테이블의 값을 Tab2로 업데이트하는 것은 문제없지만, Tab2를 Tab1으로 업데이트하는 경우
       M개의 값 중에서 어떤 값이 업데이트될지 예측할 수 없다.
       M:M 관계의 조인이라면 어느 쪽으로 업데이트되든지 업데이트 되는 값을 예측할 수 없게 된다.