mysql innodb buffer pool size

상황

버전 : mysql  Ver 15.1 Distrib 10.1.29-MariaDB, for Linux (x86_64) using readline 5.1

메모리 용량 : ~]# free

             total       used       free     shared    buffers     cached

Mem:      65856248   65592836     263412          4      92876     181636

-/+ buffers/cache:   65318324     537924

Swap:      8389624    6001720    2387904

위와같이 물리 메모리 전부 사용하고 스왑메모리까지 사용하고 있다.

우선 innodb buffer pool size 란 간단하게 innodb용 캐시 사이즈라고 생각하면 될듯하다.

https://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_buffer_pool_instance 참고

cache

The general term for any memory area that stores copies of data for frequent or high-speed retrieval. In InnoDB, the primary kind of cache structure is the buffer pool.

buffer

A memory or disk area used for temporary storage. Data is buffered in memory so that it can be written to disk efficiently, with a few large I/O operations rather than many small ones. Data is buffered on disk for greater reliability, so that it can be recovered even when a crash or other failure occurs at the worst possible time. The main types of buffers used by InnoDB are the buffer pool, the doublewrite buffer, and the change buffer.

buffer pool

The memory area that holds cached InnoDB data for both tables and indexes. For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache, using a variation of the LRU algorithm. On systems with large memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances.

Several InnoDB status variables, INFORMATION_SCHEMA tables, and performance_schema tables help to monitor the internal workings of the buffer pool. Starting in MySQL 5.6, you can avoid a lengthy warmup period after restarting the server, particularly for instances with large buffer pools, by saving the buffer pool state at server shutdown and restoring the buffer pool to the same state at server startup. See Saving and Restoring the Buffer Pool State.

innodb_buffer_pool_size

Property	Value
Command-Line Format	--innodb-buffer-pool-size=#
System Variable	innodb_buffer_pool_size
Scope	Global
Dynamic	No
Type	Integer
Default Value	134217728
Minimum Value	5242880
Maximum Value (64-bit platforms)	2**64-1
Maximum Value (32-bit platforms)	2**32-1

The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. The default value is 134217728 bytes (128MB). The maximum value depends on the CPU architecture; the maximum is 4294967295 (2³²-1) on 32-bit systems and 18446744073709551615 (2⁶⁴-1) on 64-bit systems. On 32-bit systems, the CPU architecture and operating system may impose a lower practical maximum size than the stated maximum. When the size of the buffer pool is greater than 1GB, setting innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy server.

A larger buffer pool requires less disk I/O to access the same table data more than once. On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size. Be aware of the following potential issues when configuring buffer pool size, and be prepared to scale back the size of the buffer pool if necessary.

• Competition for physical memory can cause paging in the operating system.

• InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified buffer pool size.

• Address space for the buffer pool must be contiguous, which can be an issue on Windows systems with DLLs that load at specific addresses.

• The time to initialize the buffer pool is roughly proportional to its size. On instances with large buffer pools, initialization time might be significant.

참 IT 용어라는게.. 용어 ? 하여튼 buffer 하나 이해하는데 시간 적잖이 걸렸다. 그냥 뭔가 감은 오는데 제대로 이해하려면 확실히 이것저것 공부가 많이 된다.

대충 설명하자면 데이터를 디스크에 쓸 때 한번에 10개의 데이터를 한번에 쓰기위해 buffer라는 기능을 사용한다. 라고 이해했다 

###innodb_buffer_pool_instances 이거는 buffer pool size 를 몇개의 쓰레드로 나눌지. buffer pool size 가 1GB 이상일때만 사용가능

내용 요약하자면
1.설정 안할경우 기본값은 128MB
2.최대값은 32bit의 경우 4GB(그러나 실제로는 더 낮을 수 있다고 한다.), 64bit의 경우 16엑사?바이트(첨봄, 테라>페타>엑사)
3.DB서버로만 이용할경우 물리 메모리의 80%를 권장한다.
4.할당값보다 10% 추가로 할당됨(디스크 파티션 나눌 때 10%정도 빠지는거랑 같은거)

공식홈페이지에 보면 innodb buffer 에대한 여러가지 기능(더블라이트나 뭐 하이튼 겁내 별게 많음)들을 사용하면 성능향상에 도움이 될 꺼같은데 언제 써야 도움이 되는지를 모르겠네...

하여튼 버퍼가 뭔지 알았으니까 이제 버퍼 사이즈를 조정할껀데.. 조정하는 이유를 먼저 알아야한다.(조정할 필요가 없는데 조정하면 안되니까)

조정하는이유는 메모리가 가득차서 스왑메모리영역까지 사용해버린다. 스왑사용하면 속도가 느려지니까 스왑사용안되도록 조치를 해야한다.(정상적으로 메모리가 사용이 되는거라 메모리부족한거면 메모리를 늘려야한다.) 보니까 버퍼 풀 사이즈가 큰거같다. 54GB로 잡혀있다. 이걸 줄여도 될까 자세히 한번 알아봐야겠따.

MariaDB [(none)]> SHOW ENGINE INNODB STATUS\G

SHOW ENGINE INNODB STATUS\G 명령어로 엔진의 상태를 좀 봐봐야겠다.
(innodb의 상태를 저장해주는 innodb 모니터링이라는게 있는데 이걸 불러오는 명령어가 show engine innodb status 이다.즉 innodb 상태 보는 명령어. 내가 지금 봐야하는 buffer pool memory 섹션외에도 디텍티드 데드락섹션, 트랜잭션 세션이나 세마포어 섹션을 확인하여 데드락의 원인을 파악할 수 있다. 이거 다하고 공부해야지)

나는 buffer pool and memory 섹션을 봐야한다.

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 60728279040; in additional pool allocated 0    ### 약 56GB 정도 할당된걸 확인할 수 있음

Total memory allocated by read views 4192

Internal hash tables (constant factor + variable factor)

    Adaptive hash index 1547957664      (917988664 + 629969000)

    Page hash           7172584 (buffer pool 0 only)

    Dictionary cache    231761997       (229498768 + 2263229)

    File system         1032976         (812272 + 220704)

    Lock system         143445008       (143436728 + 8280)

    Recovery system     0       (0 + 0)

Dictionary memory allocated 2263229    ###딕셔너리에 할당된 메모리

Buffer pool size        3538936

Buffer pool size, bytes 57981927424

Free buffers            8189     
##나는 지금 요부분이 중요하다.
서버의 메모리가 부족하여 스왑메모리가 사용됐고 이걸 방지하기위해 불필요하게 사용되는 메모리는 없는지 찾고있는 상황이였고 디비의 innodb buffer poolsize가 전체 메모리의 약 85~90프로를 차지하고 있다. 따라서 innodb buffer pool size 를 줄여야 하는데.. 줄여도 되는 상황일까 ? 
free buffers 가 8189 이다. 

Database pages          3492297

Old database pages      1288984

Modified db pages       141927

Percent of dirty pages(LRU & free pages): 4.054

Max dirty pages percent: 75.000

Pending reads 0

Pending writes: LRU 0, flush list 117, single page 0

Pages made young 1465646, not young 3638134

6.33 youngs/s, 1.00 non-youngs/s

Pages read 3411009, created 1266390, written 13049586

0.67 reads/s, 19.33 creates/s, 228.46 writes/s

Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 3492297, unzip_LRU len: 0

I/O sum[100408]:cur[2856], unzip sum[0]:cur[0]

결론

free buffer가 없으니 그냥 냅뒀따가 메모리 추가해야겠다. 
buffer pool size 의 쓰레드를 8개로 나눠서 사용하고 있는데 이거를 좀 더 잘게 나눠보고싶다. 근데 실 사용 서버이고 내가 갑이 아니라서 괜히 문제생기면 안되니까 걍 메모리 추가해야겠따.