MySQL Tuning for innodb #2

다즐링의 이미지

MySQL Tuning for innodb #1

writtn by 배권한 a.k.a 다즐링 at 2008.06.19

== Chapter 3. open mind , open my.cnf #1 ==

my.cnf 를 열어보도록 합시다.
최신버젼을 깐다면 대부분의 경우 내용이 없습니다.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
 
 
[mysql.server]
user=mysql
basedir=/var/lib
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

자 이제 여기부터 mysql version 과 OS에 따라 옵션들이 바뀌기 시작합니다.
모든 설정은 중요 테이블들이 innodb 임을 가정하고 설정에 대해서 이야기를 드립니다.
mysql 5.0 을 기준으로 합니다.

innodb_buffer_pool_size:가장 중요한 값입니다. 기본값(default) 는 8메가정도인데 이러면 디비가 죽어납니다.
전체 메모리의 50~80% 를 권장드립니다. 단 64비트 OS에서 입니다. -_-;;; 32비트는 2기가를 넘지 못합니다.
( 대략 1600M 정도로 잡아주시면 좋습니다. ). 이 사이즈는 인덱스와 자주쓰는 테이블의 내용을 캐슁합니다.
메모리가 8기가일 경우 5G 혹은 6G 정도를 권장드립니다.

innodb_flush_method=O_DIRECT : 더블 버퍼링을 막아줍니다. 무슨 말이냐면 원래 linux상에서 disk io 는 남는
메모리에 캐슁을 합니다. 그러면 innodb_buffer_pool 과 중복이 됩니다. 그부분을 막아줍니다. 단 32비트에서
메모리가 2기가 이상이면 이 옵션을 줍시다.(4기가머신이면)
innodb_buffer_pool 이 작은 경우에는 옵션을 꺼줌이 옳습니다. buffer_pool 이 모자라면 disk 접속을
하는 경우가 생기기때문에 그런경우를 위함입니다.
그리고 ext3 파일 시스템을 쓰더라도 O_DIRECT 옵션을 주는 경우는 저널링이 되지 않는것으로 알고 있습니다.
캐쉬가 있는 레이드카드를 쓰고 레이드카드에 배터리가 달려 있는 경우에 ( 혹은 데이터가 조금 날아갈수도..; )
추천드립니다. 어느정도 하드웨어가 설정이 되어 있어야 가능한 옵션입니다.
오라클에서 DIRECT_IO를 쓰는것과 같은 이유입니다.

innodb_file_per_table : 4.1에서는 문제가 많았던걸로 로그에 나오는데 5.0 부터는 아주 괜찬아진것으로
알고 있습니다. 단 테이블이 많은 경우는 기존의 방식대로 하나의 파일에 저장하는것이 좋습니다.
이것의 장점은 disk 추가시에 접속이 많은 테이블이라던가 사용량이 많은 테이블을 분리 가능합니다.
단 처음에 디비에 데이터를 넣기전에 ( create table 문이 실행되기전에 ) 설정되어 있어야합니다.

innodb_flush_log_at_trx_commit=2 innodb 가 커밋을 할때마다 커밋로그를 디스크에 기록하는것이 아니라 1초마다 기록합니다. mysqladmin status 를 보시면 avg query 가 나오는데 1초에 그만큼 io 가 일어나는 것은 좋치 않겠죠 ^_^;
1일 경우는 커밋이 일어날때마다 0일 경우는 log 가 꽉차거나 flush logs 를 할때까지 커밋을 하지 않습니다.
( 또 다른 경우도 있던거 같은데 기억에 없군요;) 값은 2를 추천드립니다.

innodb_log_file_size=256M 쿼리로그의 사이즈를 이야기 합니다. 256M 정도가 적절합니다.
innodb_log_buffer_size=4M blob 을 안쓰는 경우 ( 쿼리가 커지지 않는 경우 ) 4메가가 적절합니다.

innodb_thread_concurrency= CPU CORE 갯수 * 2 정도가 적정합니다.

몇부분 중요한 부분이 남았는데 그부분은 #3에서 다시금 이야기를 드리겠습니다.

== Chapter 4. do not believe yourself , but explain ==

디비튜닝에 있어서 가장 중요한점은.. 실시간성이 되겠습니다. 물론 그러지 않은 경우도 많치만
mysql 같은 통상적으로 "저렴한" 디비에서는 그런걸 요구하지 않으니까요. -_-;

그럼 결론은 쿼리튜닝이 제일 중요합니다.
먼저 느린 쿼리를 로깅하기 위해 my.cnf 를 열어봅시다.

long_query_time = 1
log-slow-queries = /var/log/mysql/mysql-slow.log

물론 /var/log/mysql 디렉을 만들어주시고 mysql 권한으로 바꾸어 줍니다.

그런데 잘안되는 경우가 있더군요. 그래서 safe_mysqld 를 여시고 수정해야하는 경우도 있습니다.

일단 로깅이 되면 쿼리들을 보고 수행을 해봅니다.

explain query 를 날리시면 다음과 같습니다.

mysql> explain SELECT * FROM `posts`   WHERE (links_count > 0)  ORDER BY registered DESC LIMIT 0, 10;
+----+-------------+-------+-------+-------------------------------------+-------------------------------------+---------+------+--------+-----------------------------+
| id | select_type | table | type  | possible_keys                       | key                                 | key_len | ref  | rows   | Extra                       |
+----+-------------+-------+-------+-------------------------------------+-------------------------------------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | posts | range | index_posts_on_links_count          | index_posts_on_links_count     | 5       | NULL | 196100 | Using where; Using filesort |
+----+-------------+-------+-------+-------------------------------------+-------------------------------------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

글이 너무 길어져서 다음으로 넘어갑니다. -_-;; =3 ( 감질맛나게 )

댓글

dhunter의 이미지

감질맛나긴 ㄱ-
--
from bzImage
It's blue paper

from bzImage
It's blue paper

appler의 이미지

갈수록 흥미 진지해 지는데요..

던전에는 언제 들어갈까요??


laziness, impatience, hubris

不恥下問 - 진정으로 대화를 원하면 겸손하게 모르는 것은 모른다고 말하는 용기가 필요하다.


laziness, impatience, hubris

不恥下問 - 진정으로 대화를 원하면 겸손하게 모르는 것은 모른다고 말하는 용기가 필요하다.

kirrie의 이미지

ㅋㅋ 마치 통곡의 동굴 앞에서 파티 구하던 쪼랩시절이 떠오르는군요..
--->
데비안 & 우분투로 대동단결!

--->
데비안 & 우분투로 대동단결!

codepage의 이미지

정말 좋은 글입니다.
한거번에 10점 올려줄 수 없나??ㅋㅋ

paek의 이미지

innodb 과 DB Replcation 에 관한 부분에서 여러가지 상황에 따른 innodb 옵션 설정문제도 있습니다.

그부분도 언급 해보는건 어떨까요?

--------------------------------------------------------
세상에서 나의 존재는 하나이다.
그러므로 세상에서 나는 특별한 존재이다.
-
책망과 비난은 변화가 아니다.
생각만으로 바뀌는것은 아무것도 없다.

--------------------------------------------------------

세상에서 나의 존재는 하나이다.
그러므로 세상에서 나는 특별한 존재이다.
-
책망과 비난은 변화가 아니다.
생각만으로 바뀌는것은 아무것도 없다.

lansea의 이미지


mysql 튜닝에 관해서
검색하다가 여기에 오게 되었는데요..

글이 너무 길어져서 다음으로 넘어가신다고 적혀있는데;;
다음이 아무리 봐도 없는데요..

다음 튜닝글은 그냥 접으신건가요?

-----------------------------------------------
I'm free to be whatever I, whatever I Choose

-----------------------------------------------
I'm free to be whatever I, whatever I Choose

idotrip의 이미지

말만 하고 약속안지키시는 분같군요.

바라는 한분이라도 있다면 올려주심이 어떠신지요?

다즐링의 이미지

저의 실력이 미천하고 경험이 모자라서 아직 적을만한 분량을 채우지 못하였습니다.

죄송합니다. =)

생각있으시면 이어서 적어주시죠?

참고로 이 때 튜닝했던 회사가 지금은 네이버에 인수되어서 쉘로 접근해서 값을 빼야하는데 애매합니다.

아르바이트로 했던거라 =)

------------------------------------------------------------------------------------------------
Life is in 다즐링

------------------------------------------------------------------------------------------------
Life is in 다즐링

jachin의 이미지

위의 적힌 내용만으로도 MySQL 성능에는 엄청난 변화가 생깁니다.
대부분의 배포판에서 기본값으로 정한 값은 작은 시스템을 가정한 것이니까요.

각 옵션에 대해 이리도 친절하게 써 놓은 글을 보고도,
'이것만으로 부족하니, 어서 더 써보아라~! 찰싹! 찰싹!' 하는 글을
용케도 쓰셨군요.

이런 약속을 할 수 있는 사람도 얼마 없습니다.
스스로 그런 약속을 할 수 있는 사람이 되세요.
====
( - -)a 이제는 학생으로 가장한 백수가 아닌 진짜 백수가 되어야겠다.

권순선의 이미지

idotrip 님이 이 커뮤니티에 도움이 되는 사람인지, 아닌지 생각해보기 시작했습니다. troll도 토론을 촉진한다는 점에서 어느정도 유용함(?)은 있지만 이렇게 자발적으로 뭔가 나누는 사람에게 싸가지없이 요구하는 troll은 전혀 도움이 되지 아니합니다.

어느날 갑자기 접속이 안되면 블럭당한줄 아세요.

idotrip의 이미지

그러는 님은 대체 한게 뭐이신지요?

아무리 봐도 맨날 남의 글에 딴지 걸어놓고 즐기는 거밖엔 안보입니다만...

다콘의 이미지

자신한테 하는 말인가요?

jachin의 이미지

저에 대해서 쓴 답글입니다. :)

저도 별로 한 것이 없어서 부끄럽네요. ( // //) ~ 아잉

올 해에는 조금 더 즐거운 일을 만들어서 많이 알리도록 하겠습니다.

지난 해에는 아시는 분들만 아시고 계셔서...
====
( - -)a 이제는 학생으로 가장한 백수가 아닌 진짜 백수가 되어야겠다.

jachin의 이미지

이거... 분명... 포켓몬스터에서 나오는 대사 같아요.
"제가 누구신지 모르신다면, 대답해 드리는게 인지상정..."

전 사랑과 진실, 어둠을 뿌리고 다니는, KLDP의 감초, 귀염둥이 악당...
(아니, 이건 아닌데...)

idotrip님께서 영국에 계시기 때문에 절 모르신다면,
영국에 있는 자유소프트웨어 개발자에게
KDE 프로젝트의 한국 대표가 누구냐고 물어보시면 됩니다.
====
( - -)a 이제는 학생으로 가장한 백수가 아닌 진짜 백수가 되어야겠다.

bookgekgom의 이미지

왜 냐옹이 대사 나오는 부분에서 짜르는 거냐능

'ㅅ' 차별이냐능

냐옹~

ㅎㅅㅎ 농담임

---------------------------------------------------------------------------------------------------------------
루비 온 레일즈로 만들고 있는 홈페이지 입니다.

http://jihwankim.co.nr

여러 프로그램 소스들이 있습니다.

필요하신분은 받아가세요.

jachin의 이미지

그 부분은 bookgekgom 님이 시전하셨어야 하는 부분...
====
( - -)a 이제는 학생으로 가장한 백수가 아닌 진짜 백수가 되어야겠다.

cymacyma의 이미지

가 많네요 지금보니.. 무릅꿇기도 있고.. 일본에 열등감 폭발하기도 있고요. 공로가 많으십니다.
============================
Welcome to my small Organization, volks 'ㅅ'

============================
Welcome to my small Organization, volks 'ㅅ'

댓글 달기

Filtered HTML

  • 텍스트에 BBCode 태그를 사용할 수 있습니다. URL은 자동으로 링크 됩니다.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>
  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.

BBCode

  • 텍스트에 BBCode 태그를 사용할 수 있습니다. URL은 자동으로 링크 됩니다.
  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param>
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.

Textile

  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • You can use Textile markup to format text.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>

Markdown

  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • Quick Tips:
    • Two or more spaces at a line's end = Line break
    • Double returns = Paragraph
    • *Single asterisks* or _single underscores_ = Emphasis
    • **Double** or __double__ = Strong
    • This is [a link](http://the.link.example.com "The optional title text")
    For complete details on the Markdown syntax, see the Markdown documentation and Markdown Extra documentation for tables, footnotes, and more.
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>

Plain text

  • HTML 태그를 사용할 수 없습니다.
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.
  • 줄과 단락은 자동으로 분리됩니다.
댓글 첨부 파일
이 댓글에 이미지나 파일을 업로드 합니다.
파일 크기는 8 MB보다 작아야 합니다.
허용할 파일 형식: txt pdf doc xls gif jpg jpeg mp3 png rar zip.
CAPTCHA
이것은 자동으로 스팸을 올리는 것을 막기 위해서 제공됩니다.