Mysql innodb_buffer_pool_size 관련 오류

어느날 갑자기 디비 서버접속이 안되는 경우가 발생

mysql의 error.log를 살펴본 결과 아래와 같은 로그가 발생함.

2018-11-28 08:12:00 7805 [Note] Plugin 'FEDERATED' is disabled.

2018-11-28 08:12:00 7805 [ERROR] Function 'innodb' already exists

2018-11-28 08:12:00 7805 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.

2018-11-28 08:12:00 7805 [ERROR] Function 'federated' already exists

2018-11-28 08:12:00 7805 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.

2018-11-28 08:12:00 7805 [ERROR] Function 'blackhole' already exists

2018-11-28 08:12:00 7805 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.

2018-11-28 08:12:00 7805 [ERROR] Function 'archive' already exists

2018-11-28 08:12:00 7805 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.

2018-11-28 08:12:00 7805 [Note] InnoDB: Using atomics to ref count buffer pool pages

2018-11-28 08:12:00 7805 [Note] InnoDB: The InnoDB memory heap is disabled

2018-11-28 08:12:00 7805 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2018-11-28 08:12:00 7805 [Note] InnoDB: Memory barrier is not used

2018-11-28 08:12:00 7805 [Note] InnoDB: Compressed tables use zlib 1.2.8

2018-11-28 08:12:00 7805 [Note] InnoDB: Using Linux native AIO

2018-11-28 08:12:00 7805 [Note] InnoDB: Using CPU crc32 instructions

2018-11-28 08:12:00 7805 [Note] InnoDB: Initializing buffer pool, size = 40.0G

2018-11-28 08:12:03 7805 [Note] InnoDB: Completed initialization of buffer pool

2018-11-28 08:12:03 7805 [Note] InnoDB: Highest supported file format is Barracuda.

2018-11-28 08:12:03 7805 [Note] InnoDB: Log scan progressed past the checkpoint lsn 4519765000655

2018-11-28 08:12:03 7805 [Note] InnoDB: Database was not shutdown normally!

2018-11-28 08:12:03 7805 [Note] InnoDB: Starting crash recovery.

2018-11-28 08:12:03 7805 [Note] InnoDB: Reading tablespace information from the .ibd files...

2018-11-28 08:12:03 7805 [Note] InnoDB: Restoring possible half-written data pages

2018-11-28 08:12:03 7805 [Note] InnoDB: from the doublewrite buffer...

열심히 구글링을 해보니 db가 갑자기 부하가 걸릴경우 innodb_buffer_pool_size에 설정된 값을 사용하게 되는데, 이게 허용치가 넘어서서 오류나는 경우가 있다고함.

따라서 innodb_buffer_pool_size를 수정하기로함. 

innodb_buffer_pool_size        =  서버 메모리의 최대 50% 까지 설정 하도록 권고는 되어있음 (보통 25% 로 설정)

#수정하고 MySql 재시작하기.

======

5.7 이상의 버전에서 부터는 재시작 없이 하는 방법이 있습니다.

SET GLOBAL 명령어를 통해 설정하기 (console)

## 1GB

SET GLOBAL innodb_buffer_pool_size=1073741824;