일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | |||||
3 | 4 | 5 | 6 | 7 | 8 | 9 |
10 | 11 | 12 | 13 | 14 | 15 | 16 |
17 | 18 | 19 | 20 | 21 | 22 | 23 |
24 | 25 | 26 | 27 | 28 | 29 | 30 |
- 신쥬쿠
- paypay
- 시마지로
- 돼지갈비
- 시스템관리
- one tab buy
- 스테이크
- TOY
- fish
- Shimajirou
- 米沢、팽이
- 칸칸
- しまじろう
- 여름
- 일본
- 전철
- 리눅스
- 원탭바이
- Sekai Entertainment
- 자동차
- 돈까스
- 토익
- 사이타마
- 코라쿠엔
- 명령어
- 동경 모터쇼
- youtuber
- 영단어
- 점심
- 라면
- Today
- Total
IT Japan
[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 |
||
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.
Command-Line Format |
--innodb_read_io_threads=# |
|
Option-File Format |
innodb_read_io_threads |
|
Option Sets Variable |
||
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.
The number of log write requests.
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”.
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.
The number writes done to the InnoDB buffer pool.
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 + 바로 직전행도 잠김
'MySQL' 카테고리의 다른 글
[mySQL5.5] 10장. FullText & Row Storage Format등 (0) | 2016.03.23 |
---|---|
[mySQL5.5] 09장. InnoDB (0) | 2016.03.23 |
Foreign Key Constraints (0) | 2016.03.23 |
[mySQL5.5] 08장. Transaction & Locking (0) | 2016.03.23 |
[mySQL5.5] 06장. Data Types (0) | 2016.03.23 |