노땅의 IT 세계

MySQL Replication을 이용하여 DBMS 단방향 이중화하기

웹서버 부하로 인해 L4를 이용하여 로드밸런싱으로 웹서버의 부하를 해결하였지만, DB 서버의 부하로 인하여 사이트가 느리게 열리는 현상이 발생하게 되었습니다

DB 서버를 이중화하는 방법은 없을까 하여 찾아보니 MySQL의 리플리케이션이라는 기능이 있더군요 이 기능을 이용하면 DB를 이중화 할 수 있는다는 것을 알게 되었습니다

이번 포스팅에서는 MySQL의 리플리케이션은 무엇이고, 리플리케이션을 이용한 DB를 이중화하는 방법을 알아보도록 하겠습니다.

리플리케이션(Replication)은 복제를 뜻하며 2대 이상의 DBMS를 나눠서 데이터를 저장하는 방식이며, 사용하기 위한 최소 구성은 Master / Slave 구성을 하여야 됩니다.

Master DBMS 역할 : 

웹서버로 부터 데이터 등록/수정/삭제 요청시 바이너리로그(Binarylog)를 생성하여 Slave 서버로 전달하게 됩니다

(웹서버로 부터 요청한 데이터 등록/수정/삭제 기능을 하는 DBMS로 많이 사용됩니다)

Slave DBMS 역할 : 

Master DBMS로 부터 전달받은 바이너리로그(Binarylog)를 데이터로 반영하게 됩니다

(웹서버로 부터 요청을 통해 데이터를 불러오는 DBMS로 많이 사용됩니다)

MySQL 리플리케션(Replication)은 사용목적은 크게 실시간 Data 백업과 여러대의 DB서버의 부하를 분산 시킬수 있습니다

1, 데이터의 백업

예로 Master 서버를 데이터의 원본서버, Slave서버를 백업서버로 지칭하겠습니다

먼저 Master 서버에 DBMS의 등록/수정/업데이터가 생기는 즉시 Slave 서버의 변경된 데이터를 전달하게 됩니다 이러한 과정으로 데이터의 백업을 할수 있으며, 또한 Master 서버의 장애가 생겼을 경우 Slave 서버로 변경하여 사용할수 있습니다.

그림으로 표현한다면 먼저 사용자가 사용하는데 발생하는 쿼리를 Master 서버에 요청하며, Master 서버의 발생된 쿼리를 Slave 서버로 전달하게되어 백업의 용도로 사용할수 있습니다

2. DBMS의 부화분산

사용자의 폭주로 인해 1대의 DB서버로 감당할수 없을때, MySQL 리플리케이션(Replication)을 이용하여 같은 DB 데이터를 여러대를 만들수 있기에 부하를 분산하수 있습니다

그림으로 표현한다면 Master 서버를 등록/수정/삭제를 사용하는 서버로 사용하고, Slave 서버를 데이터를 읽는용도로 사용하게 되면 DBMS의 부하를 분산하는 용도로 사용할 수 있게 됩니다

MySQL Replication을 사용시 다음과 같은 주의하여야 될 사항들이 있습니다 반드시 필독 후 하시고 진행하시기 바랍니다.

1. 호환성을 위해 Replication을 사용하는 MySQL의 동일하게 맞추는것이 좋습니다

2. Replication을 사용하기에 MySQL 버전이 다른 경우 Slave 서버가 상위 버전 이여야 합니다

3. Replication을 가동시에 Master 서버, Slave 순으로 가동시켜야 합니다

이번 리플리케이션 포스팅에서는 2대의 DBMS를 이용하여 등록/수정/삭제를 하는 Master 서버와 Select 를 사용하는 Slave 서버로 하겠으며, 구성은 다음과 같습니다

위 그림과 같이 웹서버에서 데이터 등록/수정/삭제는 Master 서버로 구성하고, 데이터를 읽을경우 Slave 서버로 구성하여 MySQL DBMS를 이중화 하도록 하겠습니다.

이제 MySQL Replication을 설정 해보도록 하겠으며, 먼저 Master 서버의 설정부터 하겠습니다

MySQL 리플리케이션을 사용하기 위해선 먼저 DB, 계정, 리플리케이션 계정을 생성하여 됩니다

구성정보는 아래와 같이 하겠습니다.

[Master 서버 DB, 계정정보]

IP : 192.168.65.148(Master), 192.168.65.149(Slave)

DadaBases : repl_db

ID : user1

PW : test123

[Replication 계정 정보]

IP : 192.168.65.148 - (Master)

ID : repl_user

PW : test456

- Master 서버에 데이터를 Slave 서버로 복제하기 위해선 MySQL 계정이 필요합니다

- MySQL root 계정으로 사용하는것은 보안상 좋지 않기 때문에 복제계정을 생성하는것이 좋습니다

1. MySQL DB, 계정생성 및 권한설정

1) DB 생성

2) 계정생성

3) 권한부여

2. 리플리케이션 계정생성

3. MySQL 설정 - my.cnf

처음 설치시 위와 같은 설정이되어 있으며, 없다면 새로 추가하시면 됩니다

4. MySQL 재시작

5. Master 서버 정보 확인

* File : MySQL 로그파일

* Position : 로그 파일내 읽을 위치

* Binlog_Do_DB : 바이너리(Binary)로그 파일(변경된 이벤트 정보가 쌓이는 파일)

* Binlog_Ignore_DB : 복제 제외 정보

Slave 서버설하기에 앞서 먼저 계정을 생성하겠습니다

MySQL DB, 계정생성 및 권한설정

1) DB 생성

2) 계정생성

3) 권한부여

이제 Slave 서버 설정을 하는 방법 2가지 방법이 있습니다

첫번째는 mysql에 들어가서 설정하는 방법과 mysql 설정파일(my.cnf)에서 설정하는 방법이 있으며, 먼저 mysql에서 설정하는 부분부터 알아보도록 하겠습니다

1. MySQL에 접속하여 설정

1) MySQL 설정 - my.cnf

Server-id : Master 서버의 server-id를 제외하고 1~(2^32)-1내의 숫자로 설정하시면 됩니다

replicate-do-db : 복제하고자 하는 데이터베이스를 의미하며 2개이상의 데이터베이스를 할경우 replicate-do-db를 추가하시면 됩니다

2) MySQL 복원

1) Master DBMS에서 복제할 데이터베이스를 dump하여 복원합니다

3) Master 서버로 연결하기 위한 설정

MASTER_HOST : Mster 서버 IP 입력

MASTER_USER : 리플리케이션 ID

MASTER_PASSWORD : 리플리케이션 PW

MASTER_LOG_FILE : MASTER STATUS 로그파일명

MASTER_LOG_POS : MASTER STATUS에서 position 값

4) MySQL 재시작

2. MySQL에 my.cnf에서 설정하기

1) MySQL 설정 - my.cnf

replicate-do-db : 복제하고자 하는 데이터베이스를 의미하며 2개이상의 데이터베이스를 할경우 replicate-do-db를 추가하시면 됩니다

master-host : Master 서버의 IP를 입력

master-user : Master 서버에 생성한 리플리케이션(Replication) ID 입력

master-password : Master 서버에 생성한 리플리케이션(Replication) PW 입력

master-port : MySQL에서 사용하는 포트 입력

Server-id : Master 서버의 server-id를 제외하고 1~(2^32)-1내의 숫자로 설정하시면 됩니다

2) MySQL 재시작

MySQL 리플리케이션(Replication)이 정상적으로 완료되었다면 이제 상태를 확인해야 되겠죠? 다음과 같이 확인하시면 됩니다.

1. Master 서버 상태보기

1) 쓰레드 상태보기

Master 서버에서 위 내용과 같이 명령어를 입력하면 Id:1 쓰레드의  Slave서버(192.168.65.149)의 repl_user계정으로 연결되어 있는 것을 확인하실수 있습니다

2. Slave 서버 상태보기

1) 쓰레드 상태보기

쓰레드1(ld: 1)에서는 Master 서버와 통신하기 위한 쓰레드이며, 스레드2(ld: 2)는 업데이트된 내용을 처리하기 위한 SQL 쓰레드 입니다 이러한 2개의 쓰레드에서는 오류가 발생하면 안된다고 합니다.

2) 쓰레드 주요인자 상태보기

Slave_IO_State : Master서버의 연결을 시도하고 Master서버로 부터 이벤트를 기다리며, 재연결하는지에 대해 알려줍니다

Master_Host : 연결된 Master서버 호스트 입니다

Master_User : Master서버 연결하는데 사용되는 사용자 입니다

Master_Port : Master서버 연결하는데 사용되는 포트 입니다

Connect_Retry : --master-connect-retry 옵션의 현재 값 입니다

Master_Log_File : I/O 쓰레드에서 현재 읽고 있는 바이너리 로그파일 이름 입니다

        Read_Master_Log_Pos : I/O 쓰레드에서 현재 Master 서버의 바이너리 로그에서 읽은 곳가지의 위치 입니다

             Relay_Log_File : SQL 쓰레드에서 현재 relay 로그파일 이름 입니다

              Relay_Log_Pos : SQL 쓰레드에 의해 Relay 로그에서 읽고 실행한 곳까지의 위치 입니다

      Relay_Master_Log_File : SQL 스레드에 의해 실행된 최근 Master서버의 바이너리 로그 파일의 이름입니다

           Slave_IO_Running : I/O 쓰레드가 시작되어 Master서버의 성공적으로 연결되어있는지 여부 여부 입니다

          Slave_SQL_Running : SQL 쓰레드가 시작되었는지의 여부 입니다

            Replicate_Do_DB : Master서버에서 업데이트된 데이터를 반영될 DB 입니다

        Replicate_Ignore_DB : 생략.

         Replicate_Do_Table : 생략.

     Replicate_Ignore_Table : 생략.

    Replicate_Wild_Do_Table : 생략.

Replicate_Wild_Ignore_Table : 생략.

                 Last_Errno : 가장 최근에 사용된 쿼리의 에러메시지의 번호로 리턴됩니다

                 Last_Error : 가장 최근에 사용된 쿼리의 에러메시지의 번호로 리턴됩니다

               Skip_Counter : 생략.

        Exec_Master_Log_Pos : Master서버의 바이너리 로그의 Relay_Master_Log_File로 부터 SQL쓰레드의 의해 마지막 이벤트의 위치 입니다

            Relay_Log_Space : 존재하는 모든 Relay 로구우ㅏ 전체 사이즈 입니다

            Until_Condition : 생략.

             Until_Log_File : 생략.

              Until_Log_Pos : 생략.

         Master_SSL_Allowed : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

         Master_SSL_CA_File : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

         Master_SSL_CA_Path : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

            Master_SSL_Cert : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

          Master_SSL_Cipher : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

             Master_SSL_Key : Master서버에 연결하기 위해 Slave에 의해 사용된 SSL 파라미터 입니다

      Seconds_Behind_Master : Master서버에서 실행된 이벤트의 타임스탬프 이후 경과된 시간(초 단위)의 수 입니다

간혹 위와 같이 에러가 발생하면서 Slave 서버가 작동이 잘안되는 경우가 있습니다

위와 같은 경우는 Slave 서버에 에러가 발생하면 에러가 발생하였던 시점으로 부터Master 서버로 부터 갱신된 쿼리를 실행하지 않게되며, 이경우 에러를 넘겨야 다음 쿼리를 실행하게 됩니다

위와 같은 옵션을 잠시 설정하여 반영하시기 바랍니다. my.cnf에서 설정을 반영하신 후에는 MySQL을 재시작하시기 바랍니다

출처: <http://server-talk.tistory.com/240>

MySQL 서버 최적화 하기

시스템 요소 및 스타트 업 파라미터 튜닝

우리는 시스템-레벨의 요소를 가지고 시작을 할 것인데, 그 이유는 이런 요소들이 초창기부터 시스템 성능을 개선 시키기 위해서 사용되었기 때문이다.

OS를 잘 다루는 것이 매우 중요하다. 다중 CPU 시스템을 잘 다루기 위해서는, 솔라리스 (왜냐하면 이 OS는 쓰레드를 잘 처리한다) 또는 리눅스 (그 이유는 2.4 및 이후 버전의 커널은 SMP를 잘 지원한다)를 잘 다루는 것이 좋다. 구형 커널 버전의 리눅스는 파일 크기를 2GB만큼 디폴트로 사용한다는 점을 알아두자. 만일 여러분이 이러한 커널을 가지고 있고 2GB 보다 큰 파일을 필요로 한다면, ext2 파일 시스템에 대한 LFS (Large File System)을 가지고 있어야 한다.

상용 MySQL을 사용하기 전에, 우리는 여러분이 이것을 먼저 테스트 해 보기를 권장한다.

사용할 수 있는 다른 팁으로는 다음과 같은 것이 있다:

• 만일 여러분이 충분한 RAM을 가지고 있다면, 모든 스왑 (swap) 디바이스 (device)를 삭제할 수가 있다. 어떤 OS는 사용 가능한 메모리를 가지고 있다고 하더라도 문장 안에서 스왑 디바이스를 사용하기도 한다.
• 외부 잠금을 사용하지 말 것. MySQL 4.0 이후에는, 모든 시스템에서 외부 잠금을 비활성화 하도록 디폴트로 만들어졌다. --external-locking 및 --skip-external-locking 옵션은 외부 잠금을 명확하게 활성화 및 비활성화 시킨다.

외부 잠금을 비 활성화 시키는 것은 MySQL을 하나의 서버에서만 구동 시키기만 하면 MySQL의 기능성에는 아무런 영향을 주지 않는다는 점을 알아두자. myisamchk를 구동 시키기 전에는 서버를 다운 (down)시켜야 한다는 점 (또는 관련된 테이블을 잠그거나 플러시)을 반드시 기억하기 바란다.

외부 잠금을 비 활성화 시킬 수 없는 유일한 경우는, 여러 대의 MySQL 서버 (클라이언트가 아님)를 하나의 데이터에 구동시킬 때, 또는 서버가 테이블을 우선 플러시한 다음에 잠그도록 명령하지 않은 채로 테이블 검사용 myisamchk를 구동 시킬 경우에만 해당된다. 하나의 데이터에 동시에 여러 대의 MySQL 서버가 접속을 하도록 하는 것은 일반적으로 권장하지 않는 다는 것을 알아두기 바란다. (MySQL클러스터는 예외임)

LOCK TABLES 및 UNLOCK TABLES 명령문은 내부 잠금을 사용하기 때문에 외부 잠금이 비 활성화 되어 있는 경우에는 이것을 사용할 수가 있다.

서버 파라미터 튜닝

여러분은 아래의 명령어를 사용해서 mysqld 서버가 디폴트로 사용하는 디폴트 버퍼의 크기를 알아볼 수가 있다:

shell> mysqld --verbose --help

이 명령어는 모든 mysqld 옵션 리스트와 구성 가능한 시스템 변수 리스트를 보여 준다. 이 결과에는 디폴트 변수 값이 포함되어 있고 아래와 같이 보이게 된다:

back_log                          50
binlog_cache_size                 32768
bulk_insert_buffer_size           8388608
connect_timeout                   5
date_format                       (No default value)
datetime_format                   (No default value)
default_week_format               0
delayed_insert_limit              100
delayed_insert_timeout            300
delayed_queue_size                1000
expire_logs_days                  0
flush_time                        1800
ft_max_word_len                   84
ft_min_word_len                   4
ft_query_expansion_limit          20
ft_stopword_file                  (No default value)
group_concat_max_len              1024
innodb_additional_mem_pool_size   1048576
innodb_autoextend_increment       8
innodb_buffer_pool_awe_mem_mb     0
innodb_buffer_pool_size           8388608
innodb_concurrency_tickets        500
innodb_file_io_threads            4
innodb_force_recovery             0
innodb_lock_wait_timeout          50
innodb_log_buffer_size            1048576
innodb_log_file_size              5242880
innodb_log_files_in_group         2
innodb_mirrored_log_groups        1
innodb_open_files                 300
innodb_sync_spin_loops            20
innodb_thread_concurrency         8
innodb_thread_sleep_delay         10000
interactive_timeout               28800
join_buffer_size                  131072
key_buffer_size                   8388600
key_cache_age_threshold           300
key_cache_block_size              1024
key_cache_division_limit          100
long_query_time                   10
lower_case_table_names            1
max_allowed_packet                1048576
max_binlog_cache_size             4294967295
max_binlog_size                   1073741824
max_connect_errors                10
max_connections                   100
max_delayed_threads               20
max_error_count                   64
max_heap_table_size               16777216
max_join_size                     4294967295
max_length_for_sort_data          1024
max_relay_log_size                0
max_seeks_for_key                 4294967295
max_sort_length                   1024
max_tmp_tables                    32
max_user_connections              0
max_write_lock_count              4294967295
multi_range_count                 256
myisam_block_size                 1024
myisam_data_pointer_size          6
myisam_max_extra_sort_file_size   2147483648
myisam_max_sort_file_size         2147483647
myisam_repair_threads             1
myisam_sort_buffer_size           8388608
net_buffer_length                 16384
net_read_timeout                  30
net_retry_count                   10
net_write_timeout                 60
open_files_limit                  0
optimizer_prune_level             1
optimizer_search_depth            62
preload_buffer_size               32768
query_alloc_block_size            8192
query_cache_limit                 1048576
query_cache_min_res_unit          4096
query_cache_size                  0
query_cache_type                  1
query_cache_wlock_invalidate      FALSE
query_prealloc_size               8192
range_alloc_block_size            2048
read_buffer_size                  131072
read_only                         FALSE
read_rnd_buffer_size              262144
div_precision_increment           4
record_buffer                     131072
relay_log_purge                   TRUE
relay_log_space_limit             0
slave_compressed_protocol         FALSE
slave_net_timeout                 3600
slave_transaction_retries         10
slow_launch_time                  2
sort_buffer_size                  2097144
sync-binlog                       0
sync-frm                          TRUE
sync-replication                  0
sync-replication-slave-id         0
sync-replication-timeout          10
table_cache                       64
thread_cache_size                 0
thread_concurrency                10
thread_stack                      196608
time_format                       (No default value)
tmp_table_size                    33554432
transaction_alloc_block_size      8192
transaction_prealloc_size         4096
updatable_views_with_limit        1
wait_timeout                      28800

현재 구동 중에 있는 mysqld 서버의 경우, 여기에 접속을 해서 아래의 명령문을 입력하면 이 서버의 현재 시스템 변수 값을 알아 볼 수가 있다:

mysql> SHOW VARIABLES;

또한 아래의 명령어를 사용하면 구동 중에 있는 서버의 몇몇 통계치와 상태 인디케이터 (indicator)를 확인할 수가 있다:

mysql> SHOW STATUS;

시스템 변수 및 상태 정보는 mysqladmin를 사용해서 얻을 수도 있다:

shell> mysqladmin variables
shell> mysqladmin extended-status

모든 시스템 변수 및 상태 변수에 대한 전체 설명은, Section 5.2.2, “서버 시스템 변수”, 및 Section 5.2.4, “서버 상태 변수”를 참조하기 바란다.

MySQL은 확장이 가능한 알고리즘을 사용하기 때문에 소량의 메모리만을 사용해서도 구동 시킬 수가 있다. 하지만, 일반적으로 보다 많은 메모리를 사용하면 보다 좋은 성능을 얻어낼 수가 있다.

MySQL 서버를 튜닝할 때에는 key_buffer_size 와 table_cache 값을 정확히 구성하는 것이 매우 중요하다. 여러분은 다른 변수를 변경하기 전에 이 변수들을 올바르게 설정했는지에 대해 확인을 해야 한다.

이래의 예제는 서로 다른 런 타임 구성을 위한 전형적인 변수 값을 나타내는 것이다:

• 최소 256MB의 메모리와 많은 테이블을 가지고 있고 중간 정도 성능의 클라이언트와 함께 최고의 성능이 나오도록 하고 싶다면, 아래와 같은 것을 사용해야 한다:
  shell> mysqld_safe --key_buffer_size=64M --table_cache=256 \
             --sort_buffer_size=4M --read_buffer_size=1M &
• 만일 여러분이 128MB의 메모리와 적은 수의 테이블만을 가지고 있지만, 정렬할 것이 많이 있다면, 아래와 같이 사용한다:
  shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M
  
  만일 동시 접속 수가 많이 존재한다면, 각각의 접속에 대해서 mysqld 이 적은 량의 메모리만을 사용하도록 구성되지 않는 한 스왑핑 (swapping) 문제가 발생될 수도 있다. mysqld는 모든 접속에 대해서 충분한 메모리가 있는 경우에 성능이 좋게 된다.
• 메모리는 작고 접속은 많은 경우에는, 아래와 같이 한다:
  shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \
             --read_buffer_size=100K &
  또는:
  
  shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \
             --table_cache=32 --read_buffer_size=8K \
             --net_buffer_length=1K &

만일 사용할 수 있는 메모리보다 큰 테이블에서 GROUP BY 또는 ORDER BY 연산을 실행한다면, 열을 읽은 후 정렬 연산 속도를 증가 시키기 위해서는 read_rnd_buffer_size 값을 늘려 주어야 한다.

MySQL을 설치하면, support-files 디렉토리는 my.cnf 샘플 파일을 가지게 된다: my-huge.cnf, my-large.cnf, my-medium.cnf, 및 my-small.cnf. 여러분은 이것을 시스템 최적화용으로 사용할 수 있을 것이다. (윈도우의 경우, MySQL 설치 디렉토리를 찾아 보기 바란다.)

여러분이 mysqld 또는 mysqld_safe용 옵션을 명령어 라인에서 지정하였다면, 이것은 서버 호출에 대해서만 효력을 나타낸다. 서버가 구동될 때마다 사용될 수 있도록 하기 위해서는 옵션 파일에서 지정해야 한다.

파라미터 변경 효과를 보기 위해서는 아래와 같이 입력한다:

shell> mysqld --key_buffer_size=32M --verbose --help

변수 값은 결과 끝 부분에 나타난다. --verbose 및 --help 옵션은 마지막에 오도록 한다. 그렇지 않으면, 명령어 라인 상에서 이것 뒤에 나오는 옵션들이 결과에 영향을 주지 못하게 된다.

쿼리 옵티마이저 성능 제어하기

쿼리 옵티마이저의 업무는 SQL쿼리를 실행하기 위한 옵티말 플랜 (optimal plan)을 찾는 것이다. MySQL 5.0.1에서는 보다 유연한 쿼리 최적화 방식을 제공하고 있다.

여러 가지 플랜에 관련해서 옵티마이저가 실행하는 방식은 두 가지 시스템 변수를 통해서 제어된다:

• optimizer_prune_level 변수는 각 테이블에 접속 한 열의 숫자를 추정해서 옵티마이저로 하여금 몇몇 플랜들은 건너 띄도록 만든다. 우리의 경험상 이러한 종류의 “경험적 추정 (educated guess)”은 옵티말 플랜을 거의 빠트리지 않으며, 놀라울 정도로 쿼리 컴파일 시간을 절약 시켜 준다. 이런 까닭으로 이 옵션이 디폴트 (optimizer_prune_level=1)가 되는 것이다. 하지만, 만일 옵티마이저가 보다 우수한 쿼리 플랜을 빠트렸다고 생각을 한다면, 이 옵션을 (optimizer_prune_level=0)으로 설정해 놓을 수도 있지만 이렇게 하면 보다 오랜 쿼리 처리 시간이 소요된다.
• optimizer_search_depth 변수는 옵티마이저가 각각의 불완전한 플랜을 나중에 확장할 수 있는지 조사하도록 만든다. optimizer_search_depth 값이 작을수록 쿼리 컴파일 시간이 작게 된다. 예를 들면, 12,13, 또는 그 이상의 테이블을 가지고 있는 쿼리는, optimizer_search_depth가 쿼리 안에 있는 테이블 숫자와 비슷해지는 경우에는 몇 시간 또는 하루 정도가 걸리게 된다. 동시에, 만일 optimizer_search_depth와 함께 컴파일 되는 테이블이 3개 또는 4개 정도라면, 옵티마이저는 동일 쿼리에 대해서는 몇 분 밖에 걸리지 않을 것이다. 만일 여러분이 optimizer_search_depth에 가장 적당한 값이 어떤 것인지를 확신할 수 없다면, 이 값을 0으로 설정해서 옵티마이저로 하여금 이 값을 자동으로 판단하도록 만들면 된다.

컴파일 및 링크 작업이 어떻게 MySQL 속도에 영향을 주는가

아래에 있는 대부분의 테스트들은 리눅스에서 MySQL 벤치 마크를 사용해서 실행한 것들이긴 하지만, 여러분은 이를 통해서 다른 OS에 대한 지침을 얻을 수가 있을 것이다.

-static에 링크를 하면 가장 빠른 실행 속도를 얻을 수가 있을 것이다.

리눅스에서는, 서버를 pgcc 및 -O3와 함께 컴파일 하는 것이 가장 좋은 방법이다. 이러한 옵션을 가지고 sql_yacc.cc를 컴파일 하기 위해서는 약 200MB의 메모리가 필요한데, 그 이유는 gcc 또는 pgcc가 모든 함수를 처리하기 위해서 많은 메모리를 필요로 하기 때문이다. libstdc++ 라이브러리를 포함하지 않도록 MySQL을 구성할 경우에는 CXX=gcc로 설정하도록 한다.

보다 좋은 컴파일러와 컴파일 옵션을 사용하면 어플리케이션 처리에서 10~30%의 속도 증가를 얻어낼 수가 있다. 이것은 특히 여러분이 스스로 MySQL을 컴파일할 때에는 특히 중요하다.

표준 MySQL 바이너리 배포판은 모든 문자 셋을 지원하도록 컴파일 되어 있다. 여러분이 스스로 MySQL을 설치할 때에는 여러분이 사용하고자 하는 문자 셋만을 지원하도록 해주어야 한다. 이것은 --with-charset 옵션을 configure에 지정해 줌으로서 가능하다.

아래의 리스트는 우리가 이미 만들어 놓은 몇 가지 측정 값들이다:

• 여러분이 pgcc를 사용하고 모든 것을 -O6로 컴파일 한다면, mysqld 서버는 gcc 2.95.2 보다 1% 정도 속도가 개선된다.
• 여러분이 동적으로 (-static 없이) 링크를 한다면, 리눅스에서는 13% 정도 속도가 느려지게 된다. 여러분은 여전히 클라이언트 어플리케이션에 대해서는 MySQL 라이브러리를 동적으로 링크 시킬 수가 있다는 점을 알아 두기 바란다. 성능에 대해서는 서버 쪽이 보다 심각해지는 것이다.
• 동일한 호스트에 있는 서버로 접속을 하는 클라이언트의 경우, 만일 여러분이 유닉스 소켓 파일을 사용하지 않고 TCP/IP를 사용하는 경우라면, 7.5% 정도의 속도 저하가 생기게 된다. (유닉스의 경우, 만일 여러분이 호스트 이름 localhost에 접속을 한다면, MySQL은 디폴트로 소켓 파일을 사용한다.)
• 클라이언트에서 서버로의 접속을 TCP/IP로 하는 경우에는, 다른 호스트에 있는 원격 서버와의 접속이 동일 호스트에 있는 서버와의 접속보다 약 8-11% 정도 느려지게 된다.
• 보안 접속을 사용해서 벤치마크 테스트를 구동 시키면 (모든 데이터를 내부 SSL을 가지고 암호화 함) 성능은 암호화를 하지 않은 접속에 비해 55% 정도 느려지게 된다.
• 여러분이 --with-debug=full를 사용해서 컴파일을 하는 경우에는, 대부분의 쿼리가 20% 정도 느려지게 된다. 어떤 쿼리들은 심각할 정도로 오래 걸리기도 한다; 예를 들면, MySQL 벤치 마크는 35% 정도 느려지게 된다. 만일 여러분이 --with-debug (=full 없이)를 사용한다면, 속도는 15% 정도 느려진다. --with-debug=full을 사용해서 컴파일 된 mysqld의 경우, 여러분은 서버가 시작될 때 --skip-safemalloc 옵션을 사용해서 런 타임시의 메모리 검사를 비 활성화 시킬 수가 있다. 이렇게 하면 --with-debug를 사용해서 구성했을 때 얻어지는 수준만큼의 실행 속도를 얻을 수가 있게 된다.

MySQL의 메모리 사용 방법

아래의 리스트는 mysqld 서버가 메모리를 사용하는 방법을 설명하는 것이다:

• 키 버퍼 (변수 key_buffer_size)는 모든 쓰레드가 공유한다; 서버가 사용하는 다른 버퍼들은 필요할 경우에 할당된다.
• 각각의 접속은 쓰레드-관련 공간을 사용한다:
• 스택 (디폴트 192KB, 변수 thread_stack)
• 연결 버퍼 (변수 net_buffer_length)
• 결과 버퍼 (변수 net_buffer_length)
  연결 버퍼와 결과 버퍼는 필요할 경우에 max_allowed_packet까지 동적으로 커진다. 쿼리가 동작을 하는 동안에는 현재 쿼리 스트링의 복사본도 역시 할당된다.
• 모든 쓰레드는 동일 베이스 메모리를 공유한다.
• 쓰레드를 더 이상 사용하지 않으면, 그 쓰레드가 다시 쓰레드 캐시로 들어 가지 않는 한 여기에 할당된 메모리는 시스템에 돌려지게 된다.
• 압축된 MyISAM 테이블만이 메모리에 매핑이 된다. 그 이유는 4GB의 32-비트 메모리 공간은 대부분의 커다란 테이블용으로는 부족하기 때문이다. 64-비트 주소 공간을 가지는 시스템이 보다 보편화 되면, 우리는 이 점을 보완할 예정이다.
• 테이블을 순차적으로 스캔하는 각각의 요청은 읽기 버퍼 (read buffer) (변수 read_buffer_size)를 할당한다.
• 임의적인 순서로 열을 읽는 경우에는 (예를 들면, 정렬 순으로), 디스크 검색을 피하기 위해서 랜덤 읽기 버퍼 (random-read buffer ) (변수 read_rnd_buffer_size)를 할당할 수도 있다.
• 모든 조인 (join)은 단일 패스 내에서 실행되며, 대부분의 조인 (join)은 임시 테이블을 사용하지 않고서도 실행할 수 있다. 대부분의 임시 테이블은 메모리-기반 해시 (hash) 테이블이다. 길이가 긴 열을 가지고 있는 임시 테이블 (모든 컬럼 길이의 합으로 계산된) 또는 BLOB 컬럼을 가지고 있는 테이블은 디스크에 저장된다. 
  만일 내부 힙 (heap) 테이블의 크기가 tmp_table_size를 초과한다면, MySQL은 필요하다면 메모리 내에 있는 힙 테이블을 디스크 기반 MyISAM 테이블로 변경시켜서 자동으로 이것을 처리한다. 여러분은 tmp_table_size 옵션을 mysqld에 설정하거나 또는 클라이언트 프로그램에 있는 SQL 옵션 SQL_BIG_TABLES를 설정함으로써 임시 테이블의 크기를 증가 시킬 수가 있다.
• 대부분의 정렬 요청은 정렬 버퍼를 할당하며 결과 셋 크기에 따라서 두 개의 임시 파일을 0으로 만든다.
• 거의 모든 파싱 (parsing)과 계산 (calculating)은 로컬 메모리에 저장된다. 작은 아이템의 경우에는 메모리 오버 헤드가 거의 발생하지 않기 때문에 일반적인 슬로우 메모리 할당 및 해제 (freeing)은 피할 수가 있다. 메모리는 예상치 못한 대형 스트링에 대해서만 할당된다. 이것은 malloc() 과 free()를 가지고 실행된다.
• 열려 있는 각각의 MyISAM 테이블에 대해서는 인덱스 파일이 일단 열리게 된다; 데이터 파일은 현재 구동 중에 있는 각각의 쓰레드에 대해서 한번 열리게 된다. 각각의 현재 쓰레드에 대해서는, 테이블 구조, 각 컬럼에 대한 컬럼 구조, 그리고 3 × N 크기의 버퍼가 할당된다 (여기에서 N 은 최대 열 길이이며, BLOB 컬럼을 계산하지는 않는다). BLOB 컬럼은 BLOB 데이터 길이보다 5에서8 바이트를 더 필요로 한다. MyISAM 스토리지 엔진은 내부 용도로 하나의 열 버퍼를 추가로 관리한다.
• BLOB 컬럼을 가지고 있는 각 테이블에 대해서는, 버퍼가 BLOB에 들어 있는 큰 값을 읽을 수 있도록 동적으로 커진다. 만일 여러분이 테이블을 스캔한다면, BLOB 값 만큼 커다란 버퍼가 할당된다.
• 사용 중에 있는 테이블에 대한 핸들러 구조 (Handler structure)는 캐시 안에 저장되며 FIFO 방식으로 다루어진다. 캐시는 디폴트로64개의 엔트리를 가진다. 동시에 구동되는 두 개의 쓰레드가 하나의 테이블을 사용한다면, 캐시는 그 테이블용으로 두 개의 엔트리를 가지게 된다.
• FLUSH TABLES 명령문 또는 mysqladmin flush-tables 명령어는 한번도 사용되지 않은 테이블을 닫아 버리며 사용 중에 있는 모든 테이블은 현재 실행 중에 있는 쓰레드가 종료가 되면 닫히게끔 표시를 해 둔다. 이러한 방식이 사용 중에 있는 대부분의 메모리를 효과적으로 해제 시키는 방법이다. FLUSH TABLES은 모든 테이블이 닫히기 전까지는 리턴되지 않는다.

ps 및 다른 시스템 상태 프로그램은 mysqld가 많은 메모리를 사용한다고 보고할 수도 있다. 이것은 서로 다른 메모리 주소 상에 있는 쓰레드 스택에 의해 발생될 수도 있다. 예를 들면, Solaris 버전의 ps는 메모리를 사용하고 있는 스택 사이에서 사용되지 않는 메모리를 계산한다. swap -s를 사용해서 사용 가능한 스왑을 검사 해보면 이것을 알아볼 수가 있게 된다.

MySQL의 DNS사용 방법

새로운 클라이언트가 mysqld에 접속을 하게 되면, mysqld는 그 요청을 처리하기 위해 새로운 쓰레드를 만든다. 이 쓰레드는 우선 호스트 이름 캐시에 있는 호스트 이름을 검사한다. 만약에 여기에 없다면, 쓰레드는 호스트 이름을 알아내기 위한 시도를 한다:

• 만일 OS가 gethostbyaddr_r() 와 gethostbyname_r() 호출을 지원한다면, 쓰레드는 이것들을 사용해서 호스트 이름을 알아낸다.
• 만일 OS가 위와 같은 호출 (thread-safe)을 지원하지 않는다면, 쓰레드는 뮤텍스 (mutex)를 잠그고 gethostbyaddr() 및 gethostbyname()를 대신 호출한다. 이와 같은 경우가 되면, 다른 첫 번째 쓰레드가 뮤텍스 잠금을 해제하기 전까지 어떠한 쓰레드도 호스트 이름 캐시에 들어 있지 않는 호스트 이름을 알아낼 수가 없게 된다.

여러분은 --skip-name-resolve 옵션을 사용해서 mysqld를 시작함으로써 DNS 호스트 이름 룩업 (lookup)을 비 활성화 시킬 수가 있다. 하지만, 이렇게 되면 여러분은 MySQL 그랜트 테이블에 있는 IP번호만을 사용할 수가 있게 된다.

만일 여러분이 매우 느린 DNS와 많은 수의 호스트를 가지고 있다면, --skip-name-resolve를 사용해서 DNS를 비 활성화 시키거나 또는 HOST_CACHE_SIZE 값 (디폴트 값: 128)을 늘린 다음에 mysqld를 재 컴파일 해주면 보다 좋은 성능을 얻을 수가 있게 된다.

--skip-host-cache 옵션을 사용해서 서버를 시작하면 호스트 이름 캐시를 비 활성화 시킬 수가 있다. 호스트 이름 캐시를 초기화 (clean) 시키기 위해서는, FLUSH HOSTS 명령문 또는 mysqladmin flush-hosts 명령어를 실행하면 된다.

TCP/IP 접속을 전체적으로 허용하지 않기 위해서는, mysqld를 --skip-networking 옵션을 사용해서 시작한다.

출처 : MySQL 코리아

출처: <http://www.dbguide.net/db.db?cmd=view&boardUid=146599&boardConfigUid=9&boardIdx=129&boardStep=1>

1. MS-SQL 2005

backup log [dbname] with no_log

dbcc shrinkfile('IGROO_DB_Log',1)

2. MS-SQL 2008

ALTER DATABASE [dbname] SET RECOVERY SIMPLE

GO

DBCC SHRINKFILE(2, 1)

GO

ALTER DATABASE [dbname] SET RECOVERY FULL

[MS SQL] CPU를 많이 차지하는 쿼리 조회

DBA - MS SQL 2011/04/22 17:37 posted by 춤추는하현

[활용]

1. 사용중인 서버의 CPU가 높을 경우 문제가 되는 쿼리 추출

[버젼]

MS SQL Server 2005, MS SQL Server 2008

[스크립트]

각 팩터들 중 실제 시스템 영향에 많은 영향을 주는 것은

총 사용된 CPU 타임, 실행 횟수, 최종 실행 시간

위에 세가지 칼럼 정보를 통해 현재 CPU를 많이 잡고 있는 쿼리를 찾아 내야 한다.

Average CPU used - 평균 CPU 사용량

Total CPU used - 총 사용된 CPU 타임

Execution count - 해당 plan이 실행된 횟수

Individual Query - 실제 실행된 plan

Parent Query - 해당 실행 plan이 포함된 쿼리

DatabaseName - 해당 DB명

                         임시 및 준비된 SQL 문의 경우 NULL입니다

creation_time - 해당 쿼리의 plan 생성 일자

last_execution_time - 최종 실행 시간

SELECT TOP 20 

 [Average CPU used] = total_worker_time / qs.execution_count

,[Total CPU used] = total_worker_time

,[Last CPU used] = last_worker_time

,[MAX CPU used] = max_worker_time

,[Execution count] = qs.execution_count

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 

         (CASE WHEN qs.statement_end_offset = -1 

            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 

          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

,qs.creation_time

,qs.last_execution_time

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Average CPU used] DESC; 

SP_LOCK 이라는 저장프로시저를 통해 MSSQL Server Management Studio 에서 EXEC SP_LOCK 을 실행한다.

필요한건 Mode 와 spid 인데, Mode가 X로 표시된게 Lock이 걸린 세션이며, spid 값을 통해 처리가 가능하다.

세션을 강제 종료시키는 방법은 KILL spid 이지만,

또 발생시킬수 있을수 있으니 누가 소유자 인지 확인을 하고 종료를 시켜야 한다.

EXEC SP_WHO2 spid 로 누가 소유자 인지 확인하고,

DBCC INPUTBUFFER(spid) <-- 어떤 쿼리가 LOCK 걸리게 하였는지 확인할수 있다.

이와 같이 입력하여 EVENTINFO 를 확인해 본다.

SP_LOCK  을 통해 LOCK 된 세션이 있는지도 확인해본다.

USE master

GO

DBCC FREESYSTEMCACHE ('ALL')

GO

DBCC FREEPROCCACHE

GO

USE [tempdb]

GO

DBCC SHRINKFILE (N'tempdev' , EMPTYFILE)

GO

dbcc shrinkfile (tempdev,1024)

GO

exec sp_helpdb tempdb

GO

Msconfig 에서 cpu 수 1로 조정 한 후 설치 및 패치 까지 진행한다.

ALTER DATABASE 데이터베이스명 SET RECOVERY SIMPLE

GO

DBCC SHRINKFILE(2, 원하는크기)

GO

ALTER DATABASE 데이터베이스명 SET RECOVERY FULL

GO