mysql-성능튜닝 #4(innodb)

 

<InnoDB 튜닝>
1. 관련 변수
- Innodb_buffer_pool_size (기본값 8Mb) : InnoDB가 사용하는 메모리 버퍼의 사이즈, OS Cache 보다 훨씬 효율적으로 메모리를 사용하여 Write 성능에 큰 영향을 미친다.
- Innodb_additional_mem_pool : Data Dictionary를 저장하기 위해서 사용되며 필요한 경우 자동으로 증가한다
- Innodb_log_file_size : InnoDB redo로그 파일 크기,  Write 성능에 영향을 미친다. 크기에 따라 InnoDB의 복구 시간이 증가한다.
- Innodb_log_files_in_group : 로그 그룹 안에 포함될 로그 파일의 수
- Innodb_log_buffer_size : 로그 버퍼 크기. 1초에 한번씩 flush 됨
- Innodb_flush_log_at_trx_commit (기본값은 1)
  : 0으로 설정하면 1초에 한번씩 디스크에 기록하고 싱크한다.
  : 1로 설정하면 commit이 발생할 때마다 디스크에 싱크한다.
  : 2로 설정하면 commit할 때마다 디스크에 기록하나 싱크는 1초에 한번만 한다.
  : 0으로 설정할 경우 여러 트랜잭션을 1초마다 한번씩 모아서 디스크에 기록함으로써 인서트 성능을 대폭 향상시키나 서버가 갑자기 다운될 경우 1초간의 트랜잭션은 상실된다.
- Innodb_thread_concurrency (기본값은 8) : InnoDB가 동시 사용가능한 최대 시스템 쓰래드 수
- foreign_key_checks/unique_checks :  InnoDB는 데이터 로드 시 기본적으로 외부키 및 키의 유일성에 대한 체크를 함께 한다. 이런 부분때문에 속도가 저하될 수 있다.
- innodb_fast_shutdown : Shutdown시 InnoDB가 내부적으로 수행하게 되는 내부 메모리 구조 정리 작업과 Insert 버퍼 정리 작업을 안한다.
- innodb_status_file: mysql 시작 옵션을  주어 InnoDB는 정기적으로 데이터의 결과를 datadir/innodb status.pid라는 파일로 저장할 수 있다.
mysql> SHOW STATUS LIKE 'innodb%'
    -> ;
+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Innodb_buffer_pool_pages_data     | 32925      |
| Innodb_buffer_pool_pages_dirty    | 0          |
| Innodb_buffer_pool_pages_flushed  | 2590193    |
| Innodb_buffer_pool_pages_free     | 32609      |
| Innodb_buffer_pool_pages_latched  | 0          |
| Innodb_buffer_pool_pages_misc     | 2          |
| Innodb_buffer_pool_pages_total    | 65536      |
| Innodb_buffer_pool_read_ahead_rnd | 0          |
| Innodb_buffer_pool_read_ahead_seq | 0          |
| Innodb_buffer_pool_read_requests  | 347929888  |
| Innodb_buffer_pool_reads          | 0          |
| Innodb_buffer_pool_wait_free      | 0          |
| Innodb_buffer_pool_write_requests | 34301551   |
| Innodb_data_fsyncs                | 960103     |
| Innodb_data_pending_fsyncs        | 0          |
| Innodb_data_pending_reads         | 0          |
| Innodb_data_pending_writes        | 0          |
| Innodb_data_read                  | 0          |
| Innodb_data_reads                 | 0          |
| Innodb_data_writes                | 2275182    |
| Innodb_data_written               | 2474565120 |
| Innodb_dblwr_pages_written        | 2590020    |
| Innodb_dblwr_writes               | 160386     |
| Innodb_log_waits                  | 0          |
| Innodb_log_write_requests         | 15814690   |
| Innodb_log_writes                 | 426023     |
| Innodb_os_log_fsyncs              | 536863     |
| Innodb_os_log_pending_fsyncs      | 0          |
| Innodb_os_log_pending_writes      | 0          |
| Innodb_os_log_written             | 3444500992 |
| Innodb_page_size                  | 16384      |
| Innodb_pages_created              | 32925      |
| Innodb_pages_read                 | 0          |
| Innodb_pages_written              | 2590193    |
| Innodb_row_lock_current_waits     | 0          |
| Innodb_row_lock_time              | 0          |
| Innodb_row_lock_time_avg          | 0          |
| Innodb_row_lock_time_max          | 0          |
| Innodb_row_lock_waits             | 0          |
| Innodb_rows_deleted               | 10         |
| Innodb_rows_inserted              | 108029     |
| Innodb_rows_read                  | 51046757   |
| Innodb_rows_updated               | 3966034    |
+-----------------------------------+------------+
mysql> show engine innodb status;
....
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1198565838; in additional pool allocated 10850304
Buffer pool size   65536
Free buffers       32609
Database pages     32925
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 0, created 32925, written 2590211
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000

2. 이슈

InnoDB의 flush를 지정할 수 있다. unix계통에서는 fsync사용한다.