시행착오/[mysql]

[mysql] innodb_buffer_pool_size 최적 크기

bled 2021. 6. 14. 11:18

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.

물리적 메모리 크기의  80% 

4기가 메모리라면 약 3.2기가로 설정 (서버가 데이터베이스만으로 사용될시)

 

[추가]

 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.

버퍼 풀 사이즈가 1기가 이상이면 innodb_buffer_pool_instances 가 1보다 크게 설정하면 좋다함.

 

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

 

MySQL :: MySQL 8.0 Reference Manual :: 15.14 InnoDB Startup Options and System Variables

 

dev.mysql.com

https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size

 

How large should be mysql innodb_buffer_pool_size?

I have a busy database with solely InnoDB tables which is about 5GB in size. The database runs on a Debian server using SSD disks and I've set max connections = 800 which sometimes saturate and gri...

dba.stackexchange.com