여는글
Client: 데이터베이스 사용자(유저)와 통신할때 어떤 통로를 이용할지를 명시한다.
왠만하면 기본 경로를 사용하면 된다. 보안상 port번호를 바꾸거나 socket위치를 바꾸기도 한다.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sockmysqld_safe: root비밀번호를 잊어버려서 초기화하거나 할 경우 mysql 안전모드에 접속하는 통로로 왠만해서는 안건드리는 것이 좋다.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
skip_name_resolve = offmysqld: 기본 셋팅은 생략
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
외부 접속을 위한 셋팅: bind-address값을 실제 IP주소로 변경하거나 주석처리한다.
# bind-address = 127.0.0.1
MySQL memory & concurrency setting
key_buffer = 1024M
# Indexing값을 저장하는 메모리량
max_allowed_packet = 16M
# MySQL서버와 실제 서버가 통신할때 주고받는 패킷의 크기 제한, 현 서버는 Text위주 이기 때문에
# 큰값이 필요하지 않다. 미러링 등을 할때에는 변경할 필요가 있으며, Default로 주어진 이 값
# 이외에 client, mysql_dump쪽에도 설정을 해야 적용이 된다고 한다.
thread_stack = 192K
# MySQL에 포함되는 메모리양은 아니다. MySQL을 관리하는 thread stack 크기이다. 자세한
# 내용은 잘모르겠다.
thread_cache_size = 8
# 위와 동일하게 OS에서 관리하는 값이며 아직 이에 대해선 잘 모르겠다.
max_connections = 1048
# 최대 동시접속해서 사용가능한 사용자의 수이다. 이는 즉 MySQL의 Thread count와 같다.
max_user_connections = 0
# 하나의 사용자가 접속할때 몇번의 connection을 허용할지에 대한 값이다. 0은 무한대이다.
# 이 값에 제한이 있다면 해당 횟수 N만큼의 query가 실행후에 연결이 끈겼다가 다시 연결된다.
# 사용자가 Database에 직접적으로 연결하는 것이 아니기 때문에 이 값은 무한대로 하는 것이
# 일반적이다. 사용자는 HTTP server를 통해서 HTTP server에 등록된 계정을 이용해서
# 접속을 하기 때문이다.
table_cache = 10480
# 전체 쓰레드가 사용가능한 테이블의 수를 나타낸다. 이 값은 다음과 같이 계산된다.
# max_connections * (하나의 쿼리에서 가장 많이 Join되는 table의 수)
# 여기서는 3072 * 10 = 30720 로 계산하였다.
join_buffer_size = 1M
# 하나의 Thread가 가지고 있을 buffer size이다. 이 값은 Indexing이 잘되어 있다면 작아도 상관이
# 없다. 하나의 Thread가 할당될때마다 이 값만큼 메모리를 할당하게 된다.
# 추가! MySQL의 총 메모리 계산 = Global memory + join_buffer_size * max_connections
# 보다 복잡한 계산법도 있지만 기본적으로 이렇게 행동한다.
tmp_table_size = 512M
# Table을 Join했을때 임시로 만들어지는 table의 크기이다. 이는 직접적으로 계산하기 힘들기
# 때문에 mysqltuner를 이용해서 지속적으로 봐줘야 한다. 그리고 이 값으 넘는다고 Join이
# 안되거나 하지 않는다.
max_heap_table_size = 512M
# 사용자가 생성한 Table을 저장하는 최대 크기이다. 이 값은 tmp_table_size보다 커야한다.
# 하지만 굳이 이 값을 크게 제공할 필요도 없다. 이 값도 tuner를 통해서 관찰하면서 조금씩
# 변경해주면 된다.
Query Cache Configuration
query_cache_limit = 16M
# 한 쿼리당 최대한 캐쉬하는 사이즈 크기이다. 16M를 초과할 경우 캐쉬대상에서 제외된다.
query_cache_size = 256M
# 저장하는 총 쿼리 캐쉬 사이즈. 다른 여러곳의 캐쉬와 같이 캐쉬사이즈가 크면 오히려 느려지는
# 현상이 나타난다. 이 값이 0일 경우 SQL캐쉬는 이뤄지지 않는다.
# query_cache_type = 1
# 캐쉬를 하는 SQL을 분별하는 척도를 정할 수 있다. 1일 경우가 default값이다. (그래서 주석처리)
# 0 : 캐쉬를 하지 않겠다.
# 1 : SELECT SQL_NO_CACHE …로 시작하는 것 이외는 모두 캐쉬의 대상으로 한다.
# 2 : SELECT SQL_CACHE ... 로 시작하는 것만 캐쉬의 대상으로 잡는다.Innodb Setting: MySQL에서는 다양한 엔진이 조합되어서 실행되게 된다. 그 중에서 가장 큰 부분이 sql에 직접적으로 관여하는 Innodb 엔진이다.
innodb_thread_concurrency = 0
# 하나의 Thread에서 동시에 작동할 child thread의 개수이다. 이 값을 특정 값으로 정하는 경우도
# 있지만, 0으로 설정하는 것을 추천한다. 0은 무한대를 의미하는 것이 아니라 가용 자원에서 최대한
# 생성한다는 의미를 담고 있다.
innodb_buffer_pool_size = 1GB
# Index 및 raw_data를 담는 최대의 buffer size이다. 이 값은 전체 Computer 메모리의
# 50~80%로 설정하는 것을 권장한다. 하지만, tunner의 결과는 이만큼(8~13GB)의 메모리가
# 필요하지 않다고 나온다. 따라서 tuner에서 제시해주는 값으로 선정하였다.
innodb_buffer_pool_instances = 1
# buffer pool을 생성하여 buffer가 넘칠경우 바로 대체할 수 있는 pool을 형성한다.
# tuner의 결과 1GB를 체우려면 한참 멀었기 때문에 pool은 하나로 유지하기로 결정했다.
로그: 에러로그를 제외하고 필요한 로그 정보가 있다면 설정을 통해서 추가할 수 있다. 지속적으로 Database를 최적화하기 위해서, Index가 없이 JOIN된 쿼리나 MySQL 판단에 생각외로 시간이 많이 걸린 쿼리(slow query)를 따로 로그로 남기게 설정하였다. log_error = /var/log/mysql/error.log # 일반적으로 error로그를 쌓을 파일 log_slow_queries = /var/log/mysql/mysql-slow.log # 이하의 설정에 있는 로그를 저장할 파일 long_query_time = 5 # long_query_time초보다 오래걸린 query를 slow query로 간주하여 로그를 남긴다. log-queries-not-using-indexes # index를 사용하지 않는 query에 대해서 로그를 남긴다. # server-id = 1 # 아직은 설정하지 않은 값이지만, Replication을 만들때 master-slave관계를 나타낼때 사용한다. # log_bin = /var/log/mysql/mysql-bin.log # master가 slave로 데이터를 전송할때 여기에 있는 sql로그를 이용한다. expire_logs_days = 10 # 로그를 저장할 최대한의 기간 max_binlog_size = 100M # 로그에 저장시킬 최대의 크기
mysqltuner
우분투 기준으로 튜너를 설치하고 보는 방법
설치
apt-get install mysqltuner
실행
mysqltuner
mysql에 대한 모든 권한을 가진 계정으로 로그인을 한다.
결과
보는법
기본적으로 !! 로 뜬 것은 수정이 필요한 요소들이다. 위의 결과를 예를들어서 보자.
Maximum possible memory usage …
메모리가 전체 시스템의 80% 이하일때까지 !!가 난다. 여기서 20%는 시스템에서 사용하기 위해서 남겨주길 바라기 때문이다. 실질적으로 OS는 1~2GB정도만 있어도 잘돌아가기 때문에 이 외의 메모리는 모두 사용해도된다. 즉, 메모리가 클경우 그의 20%도 1~2GB를 웃돌기 때문에 상관이 없다. 아래 Recommend에도 이에 대한 추천이 없다.
만약 100%를 초과할 경우 메모리를 늘려주세요라고 나온다.
Joins performed without indexes
인덱스되지 않는 값으로 Join을 한 테이블이 있습니다. 라고 알려준다. 요즘 인덱스를 마구마구 늘리면서 해결하려고 하지만, 힘들다. 다 추가해도 나오는 쿼리가 존재하기 때문에… 이는 mysql-slow.log파일을 보면서 실제 해결해야 할 문제이다.
tuner는 최소한 12시간(권장 24시간)뒤에 살펴보자.
바로 살펴볼 경우 잘못된 정보를 가져다 줄때가 있다.
- 따라서 설정변경 > restart > 24시간 후 tuner 로직을 통해서 최적화를 진행해야 한다.
