• 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 정도를 설정한다.

• UPDATE: If you’re looking for more details, check out detailed guide on tuning innodb buffer pool
• 더 자세하게 알아보고 싶으면 tuning innodb buffer pool 을 참고하라.

• 

  http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

• 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을 기준으로 애플리케이션에 따라 적당히 조정하여 사용한다.

• 

  InnoDB Scalability Fixes : http://www.mysqlperformanceblog.com/2006/11/14/bug-fix-of-innodb-scalability-problem/

• 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버전의 행 단위 리플리케이션에 큰 영향을 준다.

• There are bunch of other options you may want to tune but lets focus only on Innodb ones today. You can check about tuning other options here or read one of our MySQL Presentations.
• 그 외에도 튜닝에 필요한 다양한 옵션이 있지만, 이 정도로 정리해볼 수 있다. 다른 옵션에 대해서는 다음 링크를 참조하라.
  링크 : http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
  링크 : http://www.mysqlperformanceblog.com/mysql-performance-presentations/

 

 

 

 

 

 

my.cnf를 열어서 innodb_buffer_pool_size 값을 고친다. 리눅스라면 아래 명령으로 vi 편집기로 열어서 고칠 수 있다.

# vi /etc/my.cnf

 

[mysqld]

…
innodb_buffer_pool_size = 64M
…

 

  innodb_buffer_pool_size 항목값에서 MySQL의 임시 기억 공간인 버퍼 풀 메모리 크기를 지정한다. 이 값으로 전체 메모리의 50~80%를 권하기도 하지만, 메모리가 넉넉하지 않은 환경이라면 이 값을 20% 이하로 줄여야 할 때도 있다. 버퍼 풀 메모리가 너무 크면 하드디스크를 가상 메모리로 쓰는 작업(스와핑)이 일어나 매우 느려지는 원인이 될 수 있기 때문이다.

  이 항목값이 어떻게 적용되었는지는 MySQL 로그 파일(/var/log/mysqld.log)에서 볼 수 있다.

130618 00:18:43 InnoDB: Initializing buffer pool, size = 64.0M

 

  다음 MySQL 명령으로 버퍼 풀 메모리가 얼마나 쓰이고 있는지를 볼 수 있다.

mysql> SHOW STATUS LIKE '%innodb_buffer_pool%';

  아래처럼 Innodb_buffer_pool_bytes_data 값에 버퍼풀 메모리가 쓰이고 있는 크기가 바이트(byte) 값으로 나타난다.