| September 20, 2013 |  Posted In: MySQL

InnoDB performance optimization basicsNote: There is an updated post on this topic here.

I recently stumbled upon a post that Peter Zaitsev published back in 2007 titled “Innodb Performance Optimization Basics.” It’s a great post and reading it inspired me to examine what’s changed in the nearly six years that have followed in terms of MySQL, Percona Server – as well as in all of the other now-available infrastructures.

And a lot has in fact changed! In this post I am going to highlight most of the InnoDB parameters critical for InnoDB – specifically from a performance perspective. I’m a support engineer and I can tell you that Percona Support gets many questions related to the right sizing of basic InnoDB parameters.
So hopefully this post will help others with similar questions and issues.

For larger datasets, nowadays memory counted in hundreds of giga- and even in terabytes is not surprising. MySQL requires significant memory amounts in order to provide optimal performance. By caching hot datasets, indexes, and ongoing changes, InnoDB is able to provide faster response times and utilize disk IO in a much more optimal way. From a CPU standpoint, faster processors with many cores provide better throughput. CPUs with 32/64 cores or more are becoming common now, and the latest MySQL versions are able to utilize them much better then before. In terms of storage, SSD disks are replacing traditional spindles with great success, offering the best performance for the money. RAID 10 is still the most recommended level for most workloads, but first make sure your RAID controller is able to utilize the SSD drive’s performance and will not become the actual bottleneck. There are also many PCI-e Flash drives out there if you need even more IOPS.

Operating System:
Linux is the most common operating system for high performance MySQL servers. Make sure to use modern filesystems, like EXT4 or XFS on Linux, combined with the most recent kernel. Each of them has it’s own limits and advantages: for example XFS is fast in deleting large files, while EXT4 can provide better performance on fast SSD drives. Benchmark before you decide. Check this blog post to see how EXT4 can outperform 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. The default I/O scheduler in Linux is Completely Fair Queuing (CFQ), while Noop/Deadline will be much better in most cases.. Setting swappiness to zero is generally recommended for the MySQL dedicated host, which will lower the tendency of swapping. Make sure the MySQL host does not run out of memory. Swapping is bad for MySQL and defeats the purpose of caching in memory. To learn more about swapping, check this blog post

MySQL Innodb Settings 
From 5.5 InnoDB is the default engine, so these parameters are even more important for performance than before. The most important ones are:

  • innodb_buffer_pool_size: InnoDB relies heavily on the buffer pool and should be set correctly, so be sure to allocate enough memory to it. Typically a good value is 70%-80% of available memory. More precisely, if you have RAM bigger than your dataset setting it bit larger should be appropriate with that keep in account of your database growth and re-adjust innodb buffer pool size accordingly. Further, there is improvement in code for InnoDB buffer scalability if you are using Percona Server 5.1 or Percona Server 5.5 You can read more about it here.
  • innodb_buffer_pool_instances: Multiple innodb buffer pools introduced in InnoDB 1.1 and MySQL 5.5. In MySQL 5.5 the default value for it was 1 which is changed to 8 as new default value in MySQL 5.6. Minimum innodb_buffer_pool_instances should be lie between 1 (minimum) & 64 (maximum). Enabling innodb_buffer_pool_instances is useful in highly concurrent workload as it may reduce contention of the global mutexes.
  • Dump/Restore Buffer Pool: This feature speed up restarts by saving and restoring the contents of the buffer pool. This feature is first introduced in Percona Server 5.5 you can read about it here. Also Vadim benchmark this feature You can read more about it in this post. Oracle MySQL also introduced it in version 5.6, To automatically dump the database at startup and shutdown set innodb_buffer_pool_dump_at_shutdown & innodb_buffer_pool_load_at_startup parameters to ON.
  • innodb_log_file_size: Large enough InnoDB transaction logs are crucial for good, stable write performance. But also larger log files means that recovery process will slower in case of crash. However this is not such big issue since great improvements in 5.5. Default value has been changed in MySQL 5.6 to 50 MB from 5 MB (old default), but it’s still too small size for many workloads. Also, in MySQL 5.6, if innodb_log_file_size is changed between restarts then MySQL will automatically resize the logs to match the new desired size during the startup process. Combined log file size is increased to almost 512 GB in MySQL 5.6 from 4 GB. To get the optimal logfile size please check this blog post.
  • innodb_log_buffer_size: Innodb writes changed data record into lt’s log buffer, which kept in memory and it saves disk I/O for large transactions as it not need to write the log of changes to disk before transaction commit. 4 MB – 8 MB is good start unless you write a lot of huge blobs.
  • innodb_flush_log_at_trx_commit: When innodb_flush_log_at_trx_commit is set to 1 the log buffer is flushed on every transaction commit to the log file on disk and provides maximum data integrity but it also has performance impact. Setting it to 2 means log buffer is flushed to OS file cache on every transaction commit. The implication of 2 is optimal and improve performance if you are not concerning ACID and can lose transactions for last second or two in case of OS crashes.
  • innodb_thread_concurrency: With improvements to the InnoDB engine, it is recommended to allow the engine to control the concurrency by keeping it to default value (which is zero). If you see concurrency issues, you can tune this variable. A recommended value is 2 times the number of CPUs plus the number of disks. It’s dynamic variable means it can set without restarting MySQL server.
  • innodb_flush_method: DIRECT_IO relieves I/O pressure. Direct I/O is not cached, If it set to O_DIRECT avoids double buffering with buffer pool and filesystem cache. Given that you have hardware RAID controller and battery-backed write cache.
  • innodb_file_per_table: innodb_file_per_table is ON by default from MySQL 5.6. This is usually recommended as it avoids having a huge shared tablespace and as it allows you to reclaim space when you drop or truncate a table. Separate tablespace also benefits for Xtrabackup partial backup scheme.

Along with that, there are lot of enhancements for InnoDB, specifically in Percona Server 5.5 and in Oracle MySQL 5.6. Persistent optimizer statistics is one of the features first introduced in Percona Server 5.5 that requires the enabling of the innodb_use_sys_stats_table in XtraDB. You can read more about it here. This feature is now included in Oracle MySQL 5.6, too. In MySQL 5.6 persistent stats are stored in two new tables: mysql.innodb_index_stats and mysql.innodb_table_stats. Through this query plans are much more accurate and consistent. You can read more about it in documentation. Also Percona Server 5.5 introduced a Thread Pool feature which is ported from MariaDB. You can read more about it in this documentation. On a related note, I recommend reading this blog post from Vadim on the Thread Pool feature.

Percona Server free and open source. An enhanced drop in Oracle MySQL replacement and some of the mentioned features are only applicable to Percona Server.

There are bunch of other options which you may want to tune but in this post we focus only InnoDB specifically.

Application tuning for Innodb:
Especially when coming from a MyISAM background, there will be some changes you would like to make 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 should 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).

We covered almost all basic and important InnoDB parameters, OS related tweaking and hardware for optimal MySQL server performance. By setting all mentioned variables appropriately certainly help to boost overall MySQL server performance. 

More Resources


eBooks (free to download)

Database Tools

웹호스팅을 운영하면서 일반 사용자도 함수 및 프로시저 권한을 달라는 요청을 받았습니다

당연히 기본으로 권한이 포함되어 있는지 알았더니 이부분은 따로 설정을 해줘야 생성이 가능합니다

일반적인 웹호스팅 사용자  대부분이 프로시저나 함수등을 등록해서 사용하는 분들이 없기 때문에  모르고 있던 내용이기도 하다


1. 프로시저 및 함수 생성 권한 확인
SHOW VARIABLES LIKE '%log_bin_trust_function_creators%'; 
스크린샷, 2015-01-27 15:08:05

mysql 디폴트 값은 OFF 상태 입니다

2. 프로시저 및 함수 생성 권한 주기
SET GLOBAL log_bin_trust_function_creators = 1;
스크린샷, 2015-01-27 15:08:53
위와 같이 쿼리를 실행하면 Value 값이 ON 으로 변경이 됩니다

3. 생성된 함수나 프로시저를 확인하는 쿼리 입니다
SELECT * FROM information_schema.ROUTINES;

이제 일반 사용자들도 함수 및 프로시저를 생성 할 수가 있습니다

service mysqld restart 하면 당연히 global set 도 돌아오게 되는데 이제 permanent 하게 추가를 하려면 아래와 같이한다.

#vi /etc/my.cnf



MySQL 5.5.34에서 테스트하였습니다.
MySQL 기본 스토리지 엔진 변경

1 방법 1: 임시 적용[편집]

  • mysqld 재시작 없이 바로 적용 가능
  • 단, mysqld를 재시작하면 변경 전의 설정으로 원복된다...
SET default_storage_engine=엔진이름;
→ 엔진이름은 MyISAM, InnoDB 중 선택
mysql> SELECT engine, support FROM information_schema.engines WHERE support='DEFAULT';
| engine | support |
| InnoDB | DEFAULT |
1 row in set (0.00 sec)
mysql> SET default_storage_engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT engine, support FROM information_schema.engines WHERE support='DEFAULT';
| engine | support |
1 row in set (0.00 sec)

2 방법 2: 영구 적용[편집]

vi /etc/my.cnf
→ [mysqld] 아래 부분 적당한 곳에 설정 추가
[root@zetawiki ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

3 같이 보기[편집]

4 참고[편집]

MySQL 현재 접속자 보기 및 MYSQL 모니터링 방법

MySQL Database의 경우 Oracle 이나 MS SQL Server에 비해서 대용량의 자료를 처리하는 경우가 적기에 튜닝에 필요성이 적은 것 같습니다. 그러나 웹이라는 환경은 많은 사용자가 동시에 접속을 할 수 있기에 항상 모니터링과 최적화는 기본이라고 생각합니다. 
본 강좌에서는 기본적인 모니터링 방법과 Connection과 Memory 부분에 대한 튜닝 방법을 소개하도록 하겠습니다.

출처 : http://www.albumbang.com/board/board_view.jsp?board_name=free&no=139

가. 모니터링 및 초기화 명령어
  • show status - MySQL 데이타베이스의 현재 상황
  • show Processlist - MySQL 프로세스 목록
  • show variables - 설정 가능한 모든 변수 목록
  • flush logs - MySQL의 로그파일 초기화
  • flush status - MySQL 상태정보 초기화
  • flush thread - 쓰레드 캐시에 저장된 쓰레드 초기화
  • flush tables - MySQL에 캐싱된 테이블 초기화
  • flush privileges - 권한정보 재 설정
나. Connection 튜닝
1. status 
  • Aborted_clients - 클라이언트 프로그램이 비 정상적으로 종료된 수
  • Aborted_connects - MySQL 서버에 접속이 실패된 수
  • Max_used_connections - 최대로 동시에 접속한 수
  • Threads_cached - Thread Cache의 Thread 수
  • Threads_connected - 현재 연결된 Thread 수
  • Threads_created - 접속을 위해 생성된 Thread 수
  • Threads_running - Sleeping 되어 있지 않은 Thread 수
2. system variables
  • wait_timeout - 종료전까지 요청이 없이 기다리는 시간 ( TCP/IP 연결, Shell 상의 접속이 아닌 경우 )
  • thread_cache_size - thread 재 사용을 위한 Thread Cache 수로써, Cache 에 있는 Thread 수보다 접속이 많으면 새롭게 Thread를 생성한다.
  • max_connections - 최대 동시 접속 가능 수
그외에 status 또는 system variables 값은 참고의 Mysql 메뉴얼을 참조해 주십시요.
mysql> show variables like '%max_connection%';
| Variable_name   | Value |
| max_connections | 100   |
1 row in set (0.00 sec)

mysql> show status like '%connect%';
| Variable_name        | Value   |
| Aborted_connects     | 3782    |
| Connections          | 2961108 |
| Max_used_connections | 90      |
| Threads_connected    | 1       |
4 rows in set (0.01 sec)

mysql> show status like '%clients%';
| Variable_name   | Value |
| Aborted_clients | 2160  |
1 row in set (0.00 sec)

mysql> show status like '%thread%';
| Variable_name          | Value |
| Delayed_insert_threads | 0     |
| Slow_launch_threads    | 0     |
| Threads_cached         | 7     |
| Threads_connected      | 1     |
| Threads_created        | 1364  |
| Threads_running        | 1     |
6 rows in set (0.00 sec)

Cache Miss Rate(%) =  Threads_created / Connections * 100
Connection Miss Rate(%) = Aborted_connects / Connections * 100
Connection Usage(%) = Threads_connected / max_connections * 100

위의 경우는 Cache Miss Rate(%) = 0.05%, Connection Miss Rate(%) = 0.12%, Connection Usage(%) = 1%
3. 튜닝
  • Connection Usage(%)가 100% 라면 max_connections 수를 증가시켜 주십시요. Connection 수가 부족할 경우 Too Many Connection Error 가 발생합니다.
  • DB 서버의 접속이 많은 경우는 wait_timeout 을 최대한 적게 (10~20 정도를 추천) 설정하여 불필요한 연결을 빨리 정리하는 것이 좋습니다. 그러나 Connection Miss Rate(%) 가 1% 이상이 된다면 wait_timeout 을 좀 더 길게 잡는 것이 좋습니다.
  • Cache Miss Rate(%) 가 높다면 thread_cache_size를 기본값인 8 보다 높게 설정하는 것이 좋습니다. 일반적으로 threads_connected 가 Peak-time 시 보다 약간 낮은 수치로 설정하는 것이 좋습니다.
  • MySQL 서버는 외부로 부터 접속 요청을 받을 경우 인증을 위해 IP 주소를 호스트네임으로 바꾸는 과정을 수행하여 접속시에 불필요한 부하가 발생하게 됩니다. skip-name-resolve를 설정하시고 접속시에 IP 기반으로 접속을 하게 되면 hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속을 하실 수 있습니다.
다. Memory 튜닝
1. status
  • key_block_unused - Key Cache에서 사용되고 있지 않은 Block 수
  • key_reads - Key Block 읽기 요청시 Disk을 읽은 수
  • key_read_requests - Key Block 읽기 요청수
2. system variables
  • key_buffer_size - 인덱스를 메모리에 저장하는 버퍼의 크기
  • table_cache - 전체 쓰레드가 사용할 오픈 가능한 테이블 수
  • myisam_sort_buffer_size - 테이블 repair,Alter table,load data에 사용되는 버퍼 메모리 크기
  • join_buffer_size - 조인을 위한 메모리 버퍼 크기
  • record_buffer - 순차적인 검색을 위해 사용되는 메모리 버퍼 크기
  • record_rnd_buffer - order by 절을 사용할 경우 디스크 사용을 피하기 위하여 사용하는 메모리 버퍼 크기
  • sort_buffer - order by 와 group by에 사용되는 메모리 버퍼 크기
  • tmp_table_size - group by 시 디스크를 사용하지 않고 임시 테이블을 만들기 위해 사용되는 메모리 크기
  • key_cache_block_size - block 의 크기(bytes, 기본값 1024)
mysql> show status like '%key%';
| Variable_name          | Value     |
| Com_preload_keys       | 0         |
| Com_show_keys          | 2945      |
| Handler_read_key       | 365020739 |
| Key_blocks_not_flushed | 0         |
| Key_blocks_unused      | 222601    |
| Key_blocks_used        | 231960    |
| Key_read_requests      | 847204435 |
| Key_reads              | 4195954   |
| Key_write_requests     | 25034738  |
| Key_writes             | 16452136  |
10 rows in set (0.00 sec)

Key Buffer Usage = 1 - ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)Key_reads/Key_read_requests Rate(%) =  Key_reads/Key_read_requests * 100
Key_reads/Key_read_requests Relative Rate(%) = (1- ^Key_reads/^Key_read_requests) * 100

* ^Key_Reads = Current Key_Rreads - Previous Key_Reads

3. 튜닝
  • key_buffer_size는 총 메모리 크기의 25% 정도의 크기로 설정하는 것이 좋습니다.
  • Key_reads/Key_read_requests Rate(%)은 일반적으로 1%보다 적습니다. 1% 보다 높다면 Key Cache가 아닌 디스크를 읽은 경우가 많다고 판단할 수 있습니다. 또한 Key_reads/Key_reads_requests Relative Rate(%) 값이 지속적으로 90% 이상일 경우는 key_buffer_size가 효율적으로 설정되어 있다고 생각하시면 됩니다. 하지만 데이터베이스가 엄청나게 크거나 여러 데이터를 골고루 많이 읽는 데이터베이스라면 아무리 많은 양의 키 캐시를 설정해도 90% 이상의 적중률을 얻을 수는 없습니다.
라. 적용
system variables은 my.cnf 또는 my.ini 파일을 수정 후 MySQL Server 를 재시작 해 주십시요.
[www@smson www]$ vi /etc/my.cnf  # The MySQL server
port            = 3306
socket          = /tmp/mysql.sock
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
wait_timeout = 120


[root@smson mysql]# /usr/local/mysql/share/mysql/mysql.server restart

마. 참고

'DB TUNE' 카테고리의 다른 글

MySQL stored procedure permission control  (0) 2018.05.05
MySQL 기본 스토리지 엔진 변경  (0) 2018.05.04
[MySQLD] Tunner  (0) 2018.04.19
[MySQLD] innodb_file_per_table  (0) 2018.04.19
MySQL 튜닝 값을 탐지해주는 유용한 툴  (0) 2018.04.19

+ Recent posts