• Innodb Performance Optimization Basics

  • InnoDB 성능 최적화 기본기

  • Interviewing people for our Job Openings I like to ask them a basic question – if you have a server with 16GB of RAM which will be dedicated for MySQL with large Innodb database using typical Web workload what settings you would adjust and interestingly enough most people fail to come up with anything reasonable. So I decided to publish the answer I would like to hear extending it with basics of Hardware OS And Application optimization.
  • (역주: 2007년 글이므로, 근래의 InnoDB 최적화와는 포인트가 약간 어긋날 수 있으니, 감안하여 보시면 되겠습니다.)

    면접자들을 인터뷰하며 자주 물어보는 기본적인 질문으로, 16GB의 램을 가진 MySQL 전용 서버로 일반적인 웹 서비스를 위한 대용량 InnoDB 데이터베이스를 운용한다면, 어떻게 설정할 것인가를 물어보는데, 대부분의 사람이 납득 가능한 답변을 하나도 내놓지 못해 떨어지는 편이다. 그래서 내가 듣고 싶었던 답을 하드웨어, 운영체제와 애플리케이션 최적화까지 포함하여 적어보기로 했다.
  • I call this Innodb Performance Optimization Basics so these are general guidelines which work well for wide range of applications, though the optimal settings of course depend on the workload.
  • 부하에 따라 최적의 설정은 달라질 수 있겠지만, 대부분 이 일반적인 가이드라인에 따라 적용할 수 있으므로, 이를 'InnoDB 성능 최적화 기본기'라 하겠다.
  • Hardware
  • 하드웨어
  • If you have large Innodb database size Memory is paramount. 16G-32G is the cost efficient value these days. From CPU standpoint 2*Dual Core CPUs seems to do very well, while with even just two Quad Core CPUs scalability issues can be observed on many workloads. Though this depends on the application a lot. The third is IO Subsystem – directly attached storage with plenty of spindles and RAID with battery backed up cache is a good bet. Typically you can get 6-8 hard drives in the standard case and often it is enough, while sometimes you may need more. Also note new 2.5″ SAS hard drives. They are tiny but often faster than bigger ones. RAID10 works well for data storage and for read-mostly cases when you still would like some redundancy RAID5 can work pretty well as well but beware of random writes to RAID5.
  • 대용량 InnoDB 데이터베이스를 운용한다면, 메모리 용량이 관건이다. 요즘엔 16-32GB 정도의 메모리가 가격대 성능비가 좋고, 애플리케이션의 부하에 따라 2개의 쿼드코어 CPU로도 확장에 대한 이슈가 발생하기도 하지만, 2개의 듀얼코어 CPU 정도면 꽤 좋은 편이다. 다음은 입출력 시스템인데, RAID를 적용한 여러 개의 하드드라이브에 배터리로 백업되는 캐시가 장착되어 있다면 좋은 선택이다. 보통은 6-8개의 하드드라이브로 충분하지만 원한다면 더 추가할 수도 있고, 2.5" SAS 드라이브도 생각해볼 수 있다. 2.5" 드라이브들은 용량은 작지만 큰 녀석들에 비해 빠르다는 장점이 있다. RAID10 구성이 데이터 저장을 위해 적절하고, 데이터 중복은 필요하고 읽기 요청이 대부분인 경우에는 RAID5도 잘 동작하지만 RAID5 구성시 무작위 쓰기는 유념해야 한다.
  • Operating System 
  • 운영체제
  • First – run 64bit operating system. We still see people running 32bit Linux on 64bit capable boxes with plenty of memory. Do not do this. If using Linux setup LVM for database directory to get more efficient backup. EXT3 file system works OK in most cases, though if you’re running in particular roadblocks with it try XFS. You can use noatime and nodiratime options if you’re using innodb_file_per_table and a lot of tables though benefit of these is minor. Also make sure you wrestle OS so it would not swap out MySQL out of memory.
  • 무조건 64bit 시스템을 써야 한다. 아직도 메모리가 충분한 64bit 하드웨어에 32bit 리눅스를 쓰는 경우를 종종 본다. 제발 좀! 그러지 말았으면 한다. LVM을 사용한다면 백업의 효율을 높일 수 있다. 대부분의 경우에는 EXT3 파일 시스템만으로 충분하지만, 특별한 경우에는 XFS도 도움이 된다. innodb_file_per_table 옵션을 사용하고 테이블이 많은 경우 noatime이나 nodiratime 옵션을 사용할 수 있지만 성능 향상은 미미하다. 그리고 메모리 부족으로 인해 MySQL이 스왑되지 않도록 OS를 운용해야 한다.
  • MySQL Innodb Settings 
  • MySQL InnoDB 설정
  • The most important ones are:
  • 중요한 설정들은 다음과 같다.
  • innodb_buffer_pool_size 70-80% of memory is a safe bet. I set it to 12G on 16GB box.
  • innodb_buffer_pool_size : 메모리 용량의 70-80% 정도면 좋다. 나는 보통 16GB 메모리를 가진 서버에 12G 정도를 설정한다.
  • innodb_log_file_size – This depends on your recovery speed needs but 256M seems to be a good balance between reasonable recovery time and good performance
  • innodb_log_file_size : 복원 시간에 영향을 주므로, 256M 정도면 성능과 복원 시간 사이의 적절한 균형점이라 볼 수 있다.
  • innodb_log_buffer_size=4M 4M is good for most cases unless you’re piping large blobs to Innodb in this case increase it a bit.
  • innodb_log_buffer_size=4M : 4M 정도가 적당한데, BLOB를 마구 써댄다면 좀 높이는 것도 괜찮다.
  • innodb_flush_log_at_trx_commit=2 If you’re not concern about ACID and can loose transactions for last second or two in case of full OS crash than set this value. It can dramatic effect especially on a lot of short write transactions.
  • innodb_flush_log_at_trx_commit=2 : ACID에 크게 염려하지 않거나, OS가 죽어버려 1-2초 정도의 트랜잭션을 잃어버리는 것을 받아들일 수 있다면, 2로 설정한다. 짧은 쓰기 트랜잭션이 많은 경우 극적인 성능 향상 효과가 있다.
  • innodb_thread_concurrency=8 Even with current Innodb Scalability Fixes having limited concurrency helps. The actual number may be higher or lower depending on your application and default which is 8 is decent start
  • innodb_thread_concurrency=8 : InnoDB Scalability Fixes를 적용한 경우에도, 동시성을 제한하는 것이 도움이 된다. 8을 기준으로 애플리케이션에 따라 적당히 조정하여 사용한다.
  • note icon
  • innodb_flush_method=O_DIRECT Avoid double buffering and reduce swap pressure, in most cases this setting improves performance. Though be careful if you do not have battery backed up RAID cache as when write IO may suffer.
  • innodb_flush_method=O_DIRECT : 더블 버퍼링과 스와핑을 줄여 대부분의 경우에 성능이 향상된다. 그러나 배터리가 있는 RAID 캐시를 사용하지 않는 경우 write IO 성능이 떨어질 수 있으므로 조심하라.
  • innodb_file_per_table – If you do not have too many tables use this option, so you will not have uncontrolled innodb main tablespace growth which you can’t reclaim. This option was added in MySQL 4.1 and now stable enough to use.
  • innodb_file_per_table : 테이블이 엄청 많지 않다면 이 옵션을 사용함으로써, 기본 테이블스페이스가 감당하기 힘들 정도로 커지는 경우를 방지할 수 있다. MySQL 4.1 때부터 추가된 옵션이고, 이제는 안심하고 써도 된다.
  • Also check if your application can run in READ-COMMITED isolation mode – if it does – set it to be default as transaction-isolation=READ-COMMITTED. This option has some performance benefits, especially in locking in 5.0 and even more to come with MySQL 5.1 and row level replication.
  • 애플리케이션이 READ-COMMITTED isolation 모드로 작동 가능한지 확인하여, transaction-isolation=READ-COMMITTED 옵션을 적용할 수 있다. 이 옵션은 특히 5.0 버전의 락 기능 향상에 도움이 되며, 그 이상으로 5.1버전의 행 단위 리플리케이션에 큰 영향을 준다.
  • Application tuning for Innodb
  • InnoDB를 위한 애플리케이션 튜닝
  • Especially when coming from MyISAM background there would be some changes you would like to do with your application. First make sure you’re using transactions when doing updates, both for sake of consistency and to get better performance. Next if your application has any writes be prepared to handle deadlocks which may happen. Third you would like to review your table structure and see how you can get advantage of Innodb properties – clustering by primary key, having primary key in all indexes (so keep primary key short), fast lookups by primary keys (try to use it in joins), large unpacked indexes (try to be easy on indexes).
  • MyISAM을 사용하던 애플리케이션을 InnoDB로 옮기는 경우에 몇 가지 유의할 점이 있다. 먼저, 일관성(consistency)와 성능향상을 위해 트랜잭션을 사용하도록 해야 한다. 그리고 데드락을 유발할 수 있는 쓰기 작업이 있는지 확인해야 한다. 세번째로 테이블의 구조를 분석해, InnoDB의 특징에 따라 이득볼 수 있는 부분을 찾아본다. Primary Key를 이용한 클러스터링, 모든 인덱스에 PK 포함하기(그러므로 PK를 작게 유지해야함), PK를 통한 빠른 찾아보기 (join에서 쓰기 위해), 방만하게 구성된 인덱스 (인덱스좀 살살 다뤄라) 등이 있다.
  • With these basic innodb performance tunings you will be better of when majority of Innodb users which take MySQL with defaults run it on hardware without battery backed up cache with no OS changes and have no changes done to application which was written keeping MyISAM tables in mind.
  • 대부분의 InnoDB 사용자들이 MyISAM을 염두에 두고 작성한 애플리케이션과 함께 OS 튜닝도 하지 않고, 배터리로 백업되는 하드웨어도 없는 상태에서 MySQL을 기본 설정값으로 구동하고 있을터이니, 이 정도의 기본 튜닝만으로도 성능 향상을 기대할 수 있을 것이다.
8 Comments
gywndi

gywndi • Sep 11th, 2012

잘 읽었습니다. ^^ innodb_flush_log_at_trx_commit 부분이 조금 이상하다 싶었었는데.. 아래 분께서 멋지게 설명해주셨네요. ^^
2bura

2bura • Sep 11th, 2012

이 글에 대한 마지막 간섭ㅋ
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html

innodb_flush_log_at_trx_commit

0: 트랜잭션 커밋 시에 로그 버퍼를 로그 파일에 바로 기록하지 않고 초당 1회씩 기록함.
1: 트랜잭션 커밋 시에 로그 버퍼를 로그 파일에 기록하고 바로 플러시함. (기본값, ACID 준수를 위해 필요)
2: 트랜잭션 커밋 시에 로그 버퍼를 로그 파일에 기록함. 디스크 플러시 작업은 초당 1회. 그러나 스케줄링 상황에 따라 반드시 초당 1회 동작한다는 보장은 없음.

0: Mysqld가 죽으면 1-2초 트랜잭션 로그를 잃을 가능성이 있음.
2: (파일에는 기록했으니까) 운영체제가 전원 문제 등으로 사망시에 1-2초 로그가 날아갈 수 있음.

innodb 크래시 복구는 이 값에 영향을 받지 않으므로 정상동작함.
복제 구성 시에 InnoDB와 트랜잭션을 사용한다면, 가능한한 내구성(ACID의 D)과 일관성(C)을 최대로 하기 위해 마스터 서버의 my.cnf에 innodb_flush_log_at_trx_commit=1, sync_binlog=1을 넣을 것

주의: 운영체제 또는 컨트롤러 등에 따라 디스크에 비우는 작업을 실제로 하지 않았는데 했다고 구라를 치는 경우가 있음. 이 경우는 설정을 1로 해놔도 100% 내구성을 보장하기 어려움. BBWC 있는 컨트롤러를 쓰고 마음의 평화를 얻으세요. (뒷문장 생략)

(아래는 사족)
운영체제의 쓰기 캐시는 켜지 말 것. 글 보면서 뭔가 이상하다 싶었는데 ("이걸 왜 걱정하지") 윈도우 서버의 기본값은 아예 꺼져 있음.
배터리 달린 캐시가 붙은 RAID 컨트롤러에서 제대로 받아주는게 아니면 쓰기 캐시는 장애시 데이터 손실이 발생할 수 있음
2bura

2bura • Sep 11th, 2012

You can use noatime and nodiratime options if you’re using innodb_file_per_table and a lot of tables though benefit of these is minor
innodb_file_per_table 옵션을 사용하고 테이블이 많은 경우 noatime이나 nodiratime 옵션을 사용할 수 있지만 성능 향상은 미미하다.

It can dramatic effect especially on a lot of short write transactions.
짧은 쓰기 트랜잭션이 많은 경우 극적인 성능 향상 효과가 있다.

Though be careful if you do not have battery backed up RAID cache as when write IO may suffer.
그러나 배터리가 있는 RAID 캐시를 사용하지 않는 경우 write IO 성능이 떨어질 수 있으므로 조심하라.

large unpacked indexes (try to be easy on indexes).
방만하게 구성된 인덱스 (인덱스좀 살살 다뤄라)

아니 수정법 몰라요..
  • lqez • Sep 11th, 2012

    지적해주신 부분 모두 수정했습니다. 직접 올리신 글과 같이 start translation 하고 수정해주시면 됩니다 ㅋ

  • lqez • Sep 11th, 2012

    O_DIRECT에 대한 설명, 맞습니다. OS의 캐시 전략을 사용하지 않겠다는 의미이고, http://www.mimul.com/pebble/default/2012/05/25/1337938684138.html 에 잘 설명되어 있습니다.

  • 2bura • Sep 11th, 2012

    왜냐면 Battery Backed / Flash Backed가 아닌 경우 RAID 컨트롤러 캐시는 Read-Only로 동작하기 때문. O_DIRECT를 쓰면 OS의 버퍼링(= 잘 구성된 DB서버라면 메모리 낭비겠지용)을 거치지 않고 그냥 바로 하드웨어에 쓰는가본데, 캐시가 없으니 데이터를 다 못받아주고 느려지기 때문인가봄.

2bura

2bura • Sep 11th, 2012

RAID10 works well for data storage and for read-mostly cases when you still would like some redundancy RAID5 can work pretty well as well but beware of random writes to RAID5.
RAID10 구성이 데이터 저장을 위해 적절하고, 데이터 중복은 필요하고 읽기 요청이 대부분인 경우에는 RAID5도 잘 동작하지만 RAID5 구성시 무작위 쓰기는 유념해야 한다.
  • lqez • Sep 11th, 2012

    부라님 감사합니다 (_ _) 바로 수정해주셨으면 더 좋았을텐데요 OTL