인트라원을 통해서 본 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. 인덱스를 타도록

변경전

Select  menuid,hid, seq, subject, left(excerpt,150)as excerpt,
 comment_cnt, attach_cnt, name, hit ,
DATE_FORMAT(regdate,’%Y-%m-%d’) >=
 date_add(DATE_FORMAT(now(),’%Y-%m-%d’), interval – 1 day) as isNew ,
DATE_FORMAT(regdate,’%Y-%m-%d’) REGDATE
From bbs
where subject<>”
and DATE_FORMAT(regdate,’%Y-%m-%d’) >
 date_add(DATE_FORMAT(now(),’%Y-%m-%d’), interval – 3 day)
and menuid not in(select seq from menu where root in(201))
order by seq desc  limit 0,50; 

변경후

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

Select  menuid,hid, seq, subject, left(excerpt,150) as excerpt,
comment_cnt, attach_cnt, name, hit,
datediff( curdate(), regdate) < 2 as isNew,
DATE_FORMAT(regdate,’%Y-%m-%d’) REGDATE
From bbs
where
regdate > date_sub(curdate(),interval 4 day)
and menuid not in (select seq from menu where root = 201)
and menuid not in (select seq from menu where Template = ‘album’)
order by seq desc

2. 필드추가

SELECT nid,hid, (select hname from customer where hid= a.hid) hname,
note,reg_date,reg_id,reg_name FROM cust_note a
where hid in (select hid from customer where cid=’146871′)
order by reg_date desc limit 0,10;

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

3. 쿼리 분리

# Query_time: 3  Lock_time: 0  Rows_sent: 20  Rows_examined: 14225
select a.nid, a.hid, b.hname, b.cid, b.cname, b.cpos2, b.phone, b.mobile,
 b.email, a.reg_id, a.reg_name, a.note, a.reg_date,
 DATE_FORMAT(a.reg_date,’%w’) reg_week
from cust_note a, customer b
where b.DEL_FLAG=’0′
  and a.hid = b.hid
order by a.reg_date desc limit 0, 20;

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

4. limit의 유혹

select seq, Subject, Excerpt, name, comment_cnt,
 DATE_FORMAT(regdate,’%Y-%m-%d %H:%i’) regdate
from bbs
where menuid=6 and seq<11641
order by seq desc limit 0,1;

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

기타 고려사항

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

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

2. 로그를 중요시 하자

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

사이냅 문서뷰어

어디서 어떻게 사용되고 있을까요?

사이냅 문서뷰어의 적용사례를 만나보세요

[개인정보 수집, 이용에 대한 동의 절차]

사이냅 문서뷰어 적용사례를 만나보세요

차원이 다른 HTML5 웹에디터

사이냅 에디터

사이냅 에디터가 어디에 활용될 수 있을까요?
다양한 적용사례를 만나보세요

[개인정보 수집, 이용에 대한 동의 절차]

한 차원 높은 HTML5 웹에디터를 만나보세요