DBMS 2
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
우리는 시스템-레벨의 요소를 가지고 시작을 할 것인데, 그 이유는 이런 요소들이 초창기부터 시스템 성능을 개선 시키기 위해서 사용되었기 때문이다. OS를 잘 다루는 것이 매우 중요하다. 다중 CPU 시스템을 잘 다루기 위해서는, 솔라리스 (왜냐하면 이 OS는 쓰레드를 잘 처리한다) 또는 리눅스 (그 이유는 2.4 및 이후 버전의 커널은 SMP를 잘 지원한다)를 잘 다루는 것이 좋다. 구형 커널 버전의 리눅스는 파일 크기를 2GB만큼 디폴트로 사용한다는 점을 알아두자. 만일 여러분이 이러한 커널을 가지고 있고 2GB 보다 큰 파일을 필요로 한다면, ext2 파일 시스템에 대한 LFS (Large File System)을 가지고 있어야 한다. 상용 MySQL을 사용하기 전에, 우리는 여러분이 이것을 먼저 테스트 해 보기를 권장한다. 사용할 수 있는 다른 팁으로는 다음과 같은 것이 있다: 외부 잠금을 비 활성화 시키는 것은 MySQL을 하나의 서버에서만 구동 시키기만 하면 MySQL의 기능성에는 아무런 영향을 주지 않는다는 점을 알아두자. myisamchk를 구동 시키기 전에는 서버를 다운 (down)시켜야 한다는 점 (또는 관련된 테이블을 잠그거나 플러시)을 반드시 기억하기 바란다. 외부 잠금을 비 활성화 시킬 수 없는 유일한 경우는, 여러 대의 MySQL 서버 (클라이언트가 아님)를 하나의 데이터에 구동시킬 때, 또는 서버가 테이블을 우선 플러시한 다음에 잠그도록 명령하지 않은 채로 테이블 검사용 myisamchk를 구동 시킬 경우에만 해당된다. 하나의 데이터에 동시에 여러 대의 MySQL 서버가 접속을 하도록 하는 것은 일반적으로 권장하지 않는 다는 것을 알아두기 바란다. (MySQL클러스터는 예외임) LOCK TABLES 및 UNLOCK TABLES 명령문은 내부 잠금을 사용하기 때문에 외부 잠금이 비 활성화 되어 있는 경우에는 이것을 사용할 수가 있다. 여러분은 아래의 명령어를 사용해서 mysqld 서버가 디폴트로 사용하는 디폴트 버퍼의 크기를 알아볼 수가 있다: 이 명령어는 모든 mysqld 옵션 리스트와 구성 가능한 시스템 변수 리스트를 보여 준다. 이 결과에는 디폴트 변수 값이 포함되어 있고 아래와 같이 보이게 된다: 현재 구동 중에 있는 mysqld 서버의 경우, 여기에 접속을 해서 아래의 명령문을 입력하면 이 서버의 현재 시스템 변수 값을 알아 볼 수가 있다: 또한 아래의 명령어를 사용하면 구동 중에 있는 서버의 몇몇 통계치와 상태 인디케이터 (indicator)를 확인할 수가 있다: 시스템 변수 및 상태 정보는 mysqladmin를 사용해서 얻을 수도 있다: 모든 시스템 변수 및 상태 변수에 대한 전체 설명은, Section 5.2.2, “서버 시스템 변수”, 및 Section 5.2.4, “서버 상태 변수”를 참조하기 바란다. MySQL은 확장이 가능한 알고리즘을 사용하기 때문에 소량의 메모리만을 사용해서도 구동 시킬 수가 있다. 하지만, 일반적으로 보다 많은 메모리를 사용하면 보다 좋은 성능을 얻어낼 수가 있다. MySQL 서버를 튜닝할 때에는 key_buffer_size 와 table_cache 값을 정확히 구성하는 것이 매우 중요하다. 여러분은 다른 변수를 변경하기 전에 이 변수들을 올바르게 설정했는지에 대해 확인을 해야 한다. 이래의 예제는 서로 다른 런 타임 구성을 위한 전형적인 변수 값을 나타내는 것이다: 만일 사용할 수 있는 메모리보다 큰 테이블에서 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용 옵션을 명령어 라인에서 지정하였다면, 이것은 서버 호출에 대해서만 효력을 나타낸다. 서버가 구동될 때마다 사용될 수 있도록 하기 위해서는 옵션 파일에서 지정해야 한다. 파라미터 변경 효과를 보기 위해서는 아래와 같이 입력한다: 변수 값은 결과 끝 부분에 나타난다. --verbose 및 --help 옵션은 마지막에 오도록 한다. 그렇지 않으면, 명령어 라인 상에서 이것 뒤에 나오는 옵션들이 결과에 영향을 주지 못하게 된다. 쿼리 옵티마이저의 업무는 SQL쿼리를 실행하기 위한 옵티말 플랜 (optimal plan)을 찾는 것이다. MySQL 5.0.1에서는 보다 유연한 쿼리 최적화 방식을 제공하고 있다. 여러 가지 플랜에 관련해서 옵티마이저가 실행하는 방식은 두 가지 시스템 변수를 통해서 제어된다: 아래에 있는 대부분의 테스트들은 리눅스에서 MySQL 벤치 마크를 사용해서 실행한 것들이긴 하지만, 여러분은 이를 통해서 다른 OS에 대한 지침을 얻을 수가 있을 것이다. -static에 링크를 하면 가장 빠른 실행 속도를 얻을 수가 있을 것이다. 리눅스에서는, 서버를 pgcc 및 -O3와 함께 컴파일 하는 것이 가장 좋은 방법이다. 이러한 옵션을 가지고 sql_yacc.cc를 컴파일 하기 위해서는 약 200MB의 메모리가 필요한데, 그 이유는 gcc 또는 pgcc가 모든 함수를 처리하기 위해서 많은 메모리를 필요로 하기 때문이다. libstdc++ 라이브러리를 포함하지 않도록 MySQL을 구성할 경우에는 CXX=gcc로 설정하도록 한다. 보다 좋은 컴파일러와 컴파일 옵션을 사용하면 어플리케이션 처리에서 10~30%의 속도 증가를 얻어낼 수가 있다. 이것은 특히 여러분이 스스로 MySQL을 컴파일할 때에는 특히 중요하다. 표준 MySQL 바이너리 배포판은 모든 문자 셋을 지원하도록 컴파일 되어 있다. 여러분이 스스로 MySQL을 설치할 때에는 여러분이 사용하고자 하는 문자 셋만을 지원하도록 해주어야 한다. 이것은 --with-charset 옵션을 configure에 지정해 줌으로서 가능하다. 아래의 리스트는 우리가 이미 만들어 놓은 몇 가지 측정 값들이다: 아래의 리스트는 mysqld 서버가 메모리를 사용하는 방법을 설명하는 것이다: ps 및 다른 시스템 상태 프로그램은 mysqld가 많은 메모리를 사용한다고 보고할 수도 있다. 이것은 서로 다른 메모리 주소 상에 있는 쓰레드 스택에 의해 발생될 수도 있다. 예를 들면, Solaris 버전의 ps는 메모리를 사용하고 있는 스택 사이에서 사용되지 않는 메모리를 계산한다. swap -s를 사용해서 사용 가능한 스왑을 검사 해보면 이것을 알아볼 수가 있게 된다. 새로운 클라이언트가 mysqld에 접속을 하게 되면, mysqld는 그 요청을 처리하기 위해 새로운 쓰레드를 만든다. 이 쓰레드는 우선 호스트 이름 캐시에 있는 호스트 이름을 검사한다. 만약에 여기에 없다면, 쓰레드는 호스트 이름을 알아내기 위한 시도를 한다: 여러분은 --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 서버 최적화 하기
MySQL 서버 최적화 하기
시스템 요소 및 스타트 업 파라미터 튜닝
서버 파라미터 튜닝
shell> mysqld --verbose --help
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
mysql> SHOW VARIABLES;
mysql> SHOW STATUS;
shell> mysqladmin variables
shell> mysqladmin extended-status
shell> mysqld_safe --key_buffer_size=64M --table_cache=256 \
--sort_buffer_size=4M --read_buffer_size=1M &
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 &
shell> mysqld --key_buffer_size=32M --verbose --help
쿼리 옵티마이저 성능 제어하기
컴파일 및 링크 작업이 어떻게 MySQL 속도에 영향을 주는가
MySQL의 메모리 사용 방법
연결 버퍼와 결과 버퍼는 필요할 경우에 max_allowed_packet까지 동적으로 커진다. 쿼리가 동작을 하는 동안에는 현재 쿼리 스트링의 복사본도 역시 할당된다.
만일 내부 힙 (heap) 테이블의 크기가 tmp_table_size를 초과한다면, MySQL은 필요하다면 메모리 내에 있는 힙 테이블을 디스크 기반 MyISAM 테이블로 변경시켜서 자동으로 이것을 처리한다. 여러분은 tmp_table_size 옵션을 mysqld에 설정하거나 또는 클라이언트 프로그램에 있는 SQL 옵션 SQL_BIG_TABLES를 설정함으로써 임시 테이블의 크기를 증가 시킬 수가 있다.
MySQL의 DNS사용 방법