[mySQL5.5] 09장. InnoDB Storage Engine-inst

n  InnoDB Storage Engine

①   다중 buffer pools

innodb_buffer_pool_instances : buffer pool의 개수, 1 (the default) up to 64 (the maximum).

innodb_buffer_pool_size  : buffer pool의 크기,다중풀 설정인 경우: 적어도 1GB이상은 되어야 성능상의 이점이 있다.

Each buffer pool manages its own free lists, flush lists,

LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.

This option only takes effect when you set the innodb_buffer_pool_size to a size of 1 gigabyte or more. The total size you specify is divided up among all the buffer pools

 

 

13.3.14.2.1. InnoDB Standard Monitor and Lock Monitor Output

 

mysql> SHOW ENGINE INNODB STATUS\G

 

 

• FILE I/O innodb_write_io_threads

Command-Line Format	--innodb_write_io_threads=#
Option-File Format	innodb_write_io_threads
Option Sets Variable	Yes, innodb_write_io_threads
Variable Name	innodb_write_io_threads
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	numeric
	Default	4
	Range	1 .. 64

• The number of I/O threads for write operations in InnoDB. The default value is 4.

 

• innodb_read_io_threads

Command-Line Format	--innodb_read_io_threads=#
Option-File Format	innodb_read_io_threads
Option Sets Variable	Yes, innodb_read_io_threads
Variable Name	innodb_read_io_threads
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	numeric
	Default	4
	Range	1 .. 64

• The number of I/O threads for read operations in InnoDB. The default value is 4.

 

LOG

This section displays information about the InnoDB log. The contents include the current log sequence number, how far the log has been flushed to disk, and the position at which InnoDB last took a checkpoint. (See Section 13.3.7.3, “InnoDB Checkpoints”.) The section also displays information about pending writes and write performance statistics.

 *** How Checkpoint Processing Works

InnoDB implements a checkpoint mechanism known as “fuzzy” checkpointing. InnoDB flushes modified database pages from the buffer pool in small batches. There is no need to flush the buffer pool in one single batch, which would in practice stop processing of user SQL statements during the checkpointing process.

During crash recovery, InnoDB looks for a checkpoint label written to the log files. It knows that all modifications to the database before the label are present in the disk image of the database. Then InnoDB scans the log files forward from the checkpoint, applying the logged modifications to the database.

InnoDB writes to its log files on a rotating basis. It also writes checkpoint information to the first log file at each checkpoint. All committed modifications that make the database pages in the buffer pool different from the images on disk must be available in the log files in case InnoDB has to do a recovery. This means that when InnoDB starts to reuse a log file, it has to make sure that the database page images on disk contain the modifications logged in the log file that InnoDB is going to reuse. In other words, InnoDB must create a checkpoint and this often involves flushing of modified database pages to disk.

• innodb_log_buffer_size : The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.
• innodb_log_file_size : The size in bytes of each log file in a log group. The combined size of log files must be less than 4GB. The default value is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash.
• innodb_log_files_in_group : The number of log files in the log group. InnoDB writes to the files in a circular fashion. The default (and recommended) value is 2.
• Innodb_log_waits

The number of times that the log buffer was too small and a wait was required for it to be flushed before continuing.

• Innodb_log_write_requests

The number of log write requests.

• Innodb_log_writes

The number of physical writes to the log file.

•  
•  

 

BUFFER POOL AND MEMORY

This section gives you statistics on pages read and written. You can calculate from these numbers how many data file I/O operations your queries currently are doing.

For additional information about the operation of the buffer pool, see Section 7.9.1, “The InnoDB Buffer Pool”.

• Innodb_buffer_pool_wait_free

Normally, writes to the InnoDB buffer pool happen in the background. However, if it is necessary to read or create a page and no clean pages are available, it is also necessary to wait for pages to be flushed first. This counter counts instances of these waits. If the buffer pool size has been set properly, this value should be small.

• Innodb_buffer_pool_write_requests

The number writes done to the InnoDB buffer pool.

• Innodb_data_fsyncs

The number of fsync() operations so far.

 

 

n  InnoDB Locks
Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record.

 Next-key locking combines index-row locking with gap locking.

InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters.

Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order. : index_lock + 바로 직전행도 잠김