인트라원을 통해서 본 MySQL 튜닝

인트라원을 통해서 본 MySQL 튜닝
v1.0 2007/12/14 Copyleft by 전경헌@사이냅소프트

이 문서는 사이냅소프트 사내 인트라넷(인트라원)에서 사용하는 MySQL이 메모리를 많이 사용하고, 자료량이 늘어나면서 느려지는 문제가 발생하여, 주요한 몇몇 파라미터와 쿼리를 튜닝하는 방법에 대하여 조치하고 그 내용을 세미나한 자료임.

사용자와 개발자

사용자(User) <-> 소프트웨어 <-> 개발자(Developer) <-> 개발툴 <-> 전문가(Expert)

개발툴이란?

Function Library
Database Management System
Web Server
Eclipse IDE
Compiler/Interpreter
Operating System
Hardware
Version Control System
Issue Tracking System
Memory Usage Program
…

당신은 개발툴의 유저인가요?

컨피그를 만질 줄 알면 수퍼유저인 것처럼,
개발툴의 컨피그를 만질줄 알고 로그를 볼 줄알면 수퍼개발자.

MySQL 컨피그(my.cnf) 튜닝

튜닝은 해도 해도 끝없는 청소와 같은 것이다.
청소할 때 큰 것부터 치우는 것 처럼
큰 값과 많이 사용되는 값에 주목한다.

서버당 파라미터

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size = 256M
#innodb_buffer_pool_size = 1G

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# “Qcache_lowmem_prunes” status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size = 32M
#query_cache_size = 128M

접속당 파라미터

# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY
# queries. If sorted data does not fit into the sort buffer, a disk
# based merge sort is used instead – See the “Sort_merge_passes”
# status variable. Allocated per thread if sort is needed.
sort_buffer_size = 4M
#sort_buffer_size = 8M

# This buffer is used for the optimization of full JOINs (JOINs without
# indexes). Such JOINs are very bad for performance in most cases
# anyway, but setting this variable to a large value reduces the
# performance impact. See the “Select_full_join” status variable for a
# count of full JOINs. Allocated per thread if full join is found
join_buffer_size = 4M
#join_buffer_size = 8M

# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client’s threads are put in the cache if there aren’t
# more than thread_cache_size threads from before.  This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn’t give a notable performance
# improvement if you have a good thread implementation.)
thread_cache = 4

로그관련 파라미터

# Enable binary logging. This is required for acting as a MASTER in a
# replication configuration. You also need the binary log if you need
# the ability to do point in time recovery from your latest backup.
#log_bin

# Enable the full query log. Every query (even ones with incorrect
# syntax) that the server receives will be logged. This is useful for
# debugging, it is usually disabled in production use.
#log

# Log slow queries. Slow queries are queries which take more than the
# amount of time defined in “long_query_time” or which do not use
# indexes well, if log_long_format is enabled. It is normally good idea
# to have this turned on if you frequently add new queries to the
# system.
log_slow_queries

# All queries taking more than this amount of time (in seconds) will be
# trated as slow. Do not use “1” as a value here, as this will result in
# even very fast queries being logged from time to time (as MySQL
# currently measures time with second accuracy only).
long_query_time = 2

SQL 쿼리 튜닝

SQL이란? 본래 일반 사무원들이 쓸 수 있도록 만든 DB언어, 개발자용으로 만들어진게 아님.
개발자라면 SQL을 쓸 때 안쪽에서 어떻게 수행될 지도 머리속에 그릴 수 있어야 한다.

1. 인덱스를 타도록

변경전

변경후

Expression 형태로 된 where 절의 경우, regdate에 색인이 걸려있다고 해도 사용되지 못한다. Expression을 변형하여 regdate가 한쪽항에 온전히 나올 수 있도록 해야한다.

2. 필드추가

위 쿼리는 매우 잘 만들어지긴 했지만, 사용빈도가 매우 높은 경우라면 cust_note 테이블에 hname을 넣도록 하여 조인을 없애고 더 빠르게 만들 수 있다. 이런 과정을 정규화의 역과정 denomalization이라고 한다.

3. 쿼리 분리

쿼리가 간단해 보이더라도 조인은 매우 커다란 테이블을 만들고, 메모리와 시간을 많이 소모한다. cust_note에서 20개만 가져오는 쿼리와 customer에서 기본정보를 가져오는 쿼리 2개로 분리하여 구현하면 매우 효율적이다.

4. limit의 유혹

최대값이나 최소값을 가져오기 위해 limit 1을 사용하기보다는 max나 min을 활용한다.
위의 쿼리의 경우 max(seq)를 구하는 query를 먼저 실행하고, 해당 max_seq에 대하여  bbs값을 가져오도록 한다.

기타 고려사항

1. 모든 걸 DB에 올리지 말자

DBMS는 정형화된 자료의 입출력, 소팅 등을 잘 할 수 있는 도구일 뿐이다.
파일시스템이나 Berkeley DB, 검색엔진 같은 다른 구조가 더 유용한 경우가 많다.

2. 로그를 중요시 하자

중요하다고 생각한 것과 실제로 중요한 것은 다를 수 있다.
문제는 항상 의외의 곳에서 생긴다.
로그를 봐야만 문제를 정확히 알 수 있다.
우리가 만드는 소프트웨어는 로그를 잘 기록해야 한다.
로그가 너무 쌓여서 Disk가 Full 되지 않도록 잘 관리해야 한다.