* InnoDB란? * InnoDB 설정
[InnoDB란?]
InnoDB 스토리지 엔진은 대용량 데이터를 처리할때 최고의 성능을 낼 수 있도록 설계되었으며 디스크기반의 여타 다른 스토리지 엔진 보다 우수하다. InnoDB 스토리지 엔진은의 가장 두드러진 점은 트랙젝션 기능이 있다. 또한 로우 레벨 락킹이 가능하며 Foreign Key도 지원한다. InnoDB 스토리지 엔진은 자신만의 테이블 스페이스 및 버퍼 풀을 가지고 있다. 테이블 스페이스는 데이타 및 인덱스를 저장하기 위해 쓰이며 버퍼 풀은 메모리에 데이터와 인덱스를 캐싱하기 위해 활용된다. 테이블 스페이스는 여러개의 파일로 구성 할 수 있으며 테이블 단위로 테이블 스페이스를 만들어 줄 수 있다. 또한 OS의 파일 처리 시스템이 최대 2G로 되어 있다고 해도 InnoDB에서는 2G이상의 테이블 스페이스를 만들수 있다.
* InnoDB 테이블 스페이스 최대 한계치는 64TB이다.
[InnoDB설정]
MySQL 환경 설정에서 InnoDB 스토리지 엔진은 기본으로 구성되어 있다. InnoDB를 사용하지 않을 경우에는 옵션 skip-innodb를 환경 설정 파일에 추가해주면 된다. 아래 내용은 기본 환경설정 파일 중 my-huge.cnf에 대한 설정 부분이며 메모리가 1G-2G일 때 권장하는 환경 설정 파일이다.(서비스 내용에 따라 알맞는 튜닝이 필요하다.) [my-huge.cnf] 아래 내용은 기본 환경설정 파일 중 my-innodb-heavy-4G.cnf(InnoDB 전용)에 대한 설정 부분이며 메모리가 4G일 때 권장하는 환경 설정 파일이다.(서비스 내용에 따라 알맞는 튜닝이 필요하다.) [my-innodb-heavy-4G.cnf] # # Set the default transaction isolation level. Levels available are: : # *** INNODB Specific options *** # Use this option if you have a MySQL server with InnoDB support enabled # Additional memory pool that is used by InnoDB to store metadata # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # InnoDB stores data in one or more data files forming the tablespace. # Set this option if you would like the InnoDB tablespace files to be # Number of IO threads to use for async IO operations. This value is # If you run into InnoDB tablespace corruption, setting this to a nonzero # Number of threads allowed inside the InnoDB kernel. The optimal value # If set to 1, InnoDB will flush (fsync) the transaction logs to the # Speed up InnoDB shutdown. This will disable InnoDB to do a full purge # The size of the buffer InnoDB uses for buffering log data. As soon as # Size of each log file in a log group. You should set the combined size # Total number of files in the log group. A value of 2-3 is usually good # Location of the InnoDB log files. Default is the MySQL datadir. You # Maximum allowed percentage of dirty pages in the InnoDB buffer pool. # The flush method InnoDB will use for Log. The tablespace always uses # How long an InnoDB transaction should wait for a lock to be granted :
# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
:
(중략)
:
# Uncomment the following if you are using InnoDB tables
A01 #innodb_data_home_dir = /usr/local/mysql/data/
A02 #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
A03 #innodb_log_group_home_dir = /usr/local/mysql/data/
A04 #innodb_log_arch_dir = /usr/local/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
A05 #innodb_buffer_pool_size = 384M
A06 #innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
A07 #innodb_log_file_size = 100M
A08 #innodb_log_buffer_size = 8M
A09 #innodb_flush_log_at_trx_commit = 1
A10 #innodb_lock_wait_timeout = 50
:
(중략)
:
#BEGIN CONFIG INFO
#DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries
#TYPE: SYSTEM
#END CONFIG INFO
# This is a MySQL example config file for systems with 4GB of memory
# running mostly MySQL using InnoDB only tables and performing complex
# queries with few connections.
:
(중략)
:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
B01 transaction_isolation = REPEATABLE-READ
(중략)
:
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
B02 #skip-innodb
# information. If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS. As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
B03 innodb_additional_mem_pool_size = 16M
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system. Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
B04 innodb_buffer_pool_size = 2G
# If you have a single logical drive for your data, a single
# autoextending file would be good enough. In other cases, a single file
# per device is often a good choice. You can configure InnoDB to use raw
# disk partitions as well - please refer to the manual for more info
# about this.
B05 innodb_data_file_path = ibdata1:10M:autoextend
# stored in another location. By default this is the MySQL datadir.
B06 #innodb_data_home_dir = <directory>
# hardcoded to 4 on Unix, but on Windows disk I/O may benefit from a
# larger number.
B07 innodb_file_io_threads = 4
# value will likely help you to dump your tables. Start from value 1 and
# increase it until you're able to dump the table successfully.
B08 #innodb_force_recovery=1
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
B09 innodb_thread_concurrency = 16
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
B10 innodb_flush_log_at_trx_commit = 1
# and insert buffer merge on shutdown. It may increase shutdown time a
# lot, but InnoDB will have to do it on the next startup instead.
B11 #innodb_fast_shutdown
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
B12 innodb_log_buffer_size = 8M
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
B13 innodb_log_file_size = 256M
# enough.
B14 innodb_log_files_in_group = 3
# may wish to point it to a dedicated hard drive or a RAID1 volume for
# improved performance
B15 #innodb_log_group_home_dir
# If it is reached, InnoDB will start flushing them out agressively to
# not run out of clean pages at all. This is a soft limit, not
# guaranteed to be held.
B16 innodb_max_dirty_pages_pct = 90
# doublewrite flush logic. The default value is "fdatasync", another
# option is "O_DSYNC".
B17 #innodb_flush_method=O_DSYNC
# before being rolled back. InnoDB automatically detects transaction
# deadlocks in its own lock table and rolls back the transaction. If you
# use the LOCK TABLES command, or other transaction-safe storage engines
# than InnoDB in the same transaction, then a deadlock may arise which
# InnoDB cannot notice. In cases like this the timeout is useful to
# resolve the situation.
B18 innodb_lock_wait_timeout = 120
(중략)
:
* InnoDB 활성화 시 테이블 스페이스 파일과 로그 파일이 생성이 된다.
댓글