MySQL에서 Innodb_buffer_pool_size를 설정하는 방법

Innodb_buffer_pool_size 설정 방법

InnoDB 테이블, 인덱스, 기타 보조 버퍼에 대한 캐시 데이터는 버퍼 풀의 메모리 영역에 저장됩니다. 버퍼 풀의 크기는 시스템 성능에 중요합니다. 더 큰 버퍼 풀은 동일한 테이블 데이터에 대한 다중 액세스에 대한 디스크 I/O를 줄일 수 있습니다. 전용 데이터베이스 서버에서는 버퍼 풀 크기를 컴퓨터 물리 메모리 크기의 80%로 설정하는 것이 가능합니다

버퍼 풀 관련 매개변수 설명

1) 시스템 변수 매개변수

• Innodb_page_sizeInnodb_page_size

• InnoDB页面大小（默认为16KB）。页面中包含许多值，页面大小使它们可以轻松转换为字节。

• Innodb_buffer_pool_chunk_size

• innodb_buffer_pool_chunk_size 定义InnoDB缓冲池大小调整操作的块大小。默认128M。最大值可设置innodb_buffer_pool_size / innodb_buffer_pool_instances

• innodb_buffer_pool_instances

• InnoDB 缓冲池划分为的区域数。

• Innodb_buffer_pool_pages_data

• 数页在 InnoDB 缓冲池中包含的数据。该数字包括 脏页和干净页。使用压缩表时，报告的 Innodb_buffer_pool_pages_data 值可能大于 Innodb_buffer_pool_pages_total （错误＃59550）。

• Innodb_buffer_pool_pages_total

• InnoDB 缓冲池 的总大小（以页为单位）。使用压缩表时，报告的 Innodb_buffer_pool_pages_data 值可能大于 Innodb_buffer_pool_pages_total （错误＃59550）

2）运行状态变量

• Innodb_buffer_pool_pages_flushed

• 从缓冲池刷新页面的请求数 。

• Innodb_buffer_pool_read_requests

• 表示从内存中读取逻辑的请求数。

• Innodb_buffer_pool_reads

• InnoDB 不能从缓冲池满足的逻辑读取的数量，必须直接从磁盘读取。

• Innodb_buffer_pool_wait_free

• InnoDB 페이지 크기(기본 16KB). 페이지에는 많은 값이 포함되어 있으며 페이지 크기를 통해 이를 바이트로 쉽게 변환할 수 있습니다.

Innodb_buffer_pool_chunk_size

innodb_buffer_pool_chunk_size는 InnoDB 버퍼 풀 크기 조정 작업에 대한 청크 크기를 정의합니다. 기본값은 128M입니다. 최대값은 innodb_buffer_pool_size / innodb_buffer_pool_instances

innodb_buffer_pool_instances

InnoDB 버퍼 풀이 분할되는 영역 수입니다.

Innodb_buffer_pool_pages_data

InnoDB 버퍼 풀에 포함된 데이터 페이지 수입니다. 이 숫자에는 더럽고 깨끗한 페이지가 포함됩니다. 압축된 테이블을 사용할 때 보고된 Innodb_buffer_pool_pages_data 값은 Innodb_buffer_pool_pages_total(버그 #59550)보다 클 수 있습니다.

• Innodb_buffer_pool_pages_total

• InnoDB 버퍼 풀의 총 크기(페이지)입니다. 압축된 테이블을 사용할 때 보고된 Innodb_buffer_pool_pages_data 값은 Innodb_buffer_pool_pages_total(Bug #59550)보다 클 수 있습니다. (Bug #59550)
2) 실행 상태 변수

Innodb_buffer_pool_pages_flushed

페이지 새로고침 버퍼 풀 요청 수입니다. 🎜🎜🎜🎜Innodb_buffer_pool_read_requests🎜🎜🎜🎜는 메모리에서 로직을 읽기 위한 요청 수를 나타냅니다. 🎜🎜🎜🎜Innodb_buffer_pool_reads🎜🎜🎜🎜InnoDB가 버퍼 풀에서 충족할 수 없고 디스크에서 직접 읽어야 하는 논리적 읽기 수입니다. 🎜🎜🎜🎜Innodb_buffer_pool_wait_free🎜🎜🎜🎜일반적으로 InnoDB 버퍼 풀에 대한 쓰기는 백그라운드에서 발생합니다. InnoDB가 페이지를 읽거나 생성해야 하고 클린 페이지를 사용할 수 없는 경우 InnoDB는 먼저 일부 더티 페이지를 플러시하고 작업이 완료될 때까지 기다립니다. 이 카운터는 대기 중인 인스턴스를 계산합니다. 올바르게 설정된 경우 innodb_buffer_pool_size는 더 작은 값이어야 합니다. 🎜🎜🎜🎜버퍼풀 관련 매개변수를 합리적으로 설정🎜🎜🎜1. innodb_buffer_pool_size는 기본적으로 시스템 메모리의 80%로 설정되며, 이후 실제 상황에 맞게 다음 규칙에 따라 조정됩니다🎜🎜

mysql> show global status like 'Innodb_buffer_pool_pages_data';
+-------------------------------+---------+
| Variable_name                 | Value   |
+-------------------------------+---------+
| Innodb_buffer_pool_pages_data | 1894874 |
+-------------------------------+---------+
1 row in set (0.00 sec)

mysql>  show global status like 'Innodb_buffer_pool_pages_total';
+--------------------------------+---------+
| Variable_name                  | Value   |
+--------------------------------+---------+
| Innodb_buffer_pool_pages_total | 1965960 |
+--------------------------------+---------+
1 row in set (0.00 sec)

# 计算是否应该添加内存
使用率 = Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%
当结果 > 95% 则增加 innodb_buffer_pool_size
当结果 < 95% 则减少 innodb_buffer_pool_size， 可适当较少，当然独享业务机器多了也没啥问题

🎜🎜2. 기본적으로 8로 설정, 최소 1, 최대 64🎜🎜🎜수 기가바이트 범위의 버퍼 풀이 있는 시스템의 경우 버퍼 풀을 여러 개의 별도 인스턴스로 나누면 캐시 페이지를 읽고 쓰는 여러 스레드에 대한 경합을 줄여 동시성을 향상시킬 수 있습니다. 이 기능은 일반적으로 버퍼 풀 크기가 다중 GB 범위인 시스템에서 사용됩니다. innodb_buffer_pool_instances 구성 옵션을 사용하여 여러 버퍼 풀 인스턴스를 구성합니다. innodb_buffer_pool_size 값을 조정할 수도 있습니다. 🎜🎜특히 InnoDB 버퍼 풀이 큰 경우 메모리에서 검색하여 많은 데이터 요청을 충족할 수 있습니다. 여러 스레드가 동시에 버퍼 풀에 액세스하려고 하면 병목 현상이 발생할 수 있습니다. 이 경합을 최소화하기 위해 여러 버퍼 풀을 활성화할 수 있습니다. 버퍼 풀의 페이지는 해시 함수를 사용하여 각 페이지를 버퍼 풀의 위치에 무작위로 매핑하여 저장하거나 읽습니다. 각 버퍼 풀은 자체 여유 목록, 플러시 목록, LRU 및 버퍼 풀에 연결된 기타 모든 데이터 구조를 관리하며 자체 버퍼 풀 뮤텍스로 보호됩니다. 🎜🎜여러 버퍼 풀 인스턴스를 활성화하려면 innodb_buffer_pool_instances 구성 옵션을 1(기본값)보다 큰 값에서 최대 64(최대)까지 설정하세요. 이 옵션은 innodb_buffer_pool_size가 1GB 이상으로 설정된 경우에만 적용됩니다. 지정한 총 크기는 모든 버퍼 풀에 할당됩니다. 최고의 효율성을 위해서는 각 버퍼 풀 인스턴스가 최소 1GB가 되도록 innodb_buffer_pool_instances와 innodb_buffer_pool_size의 조합을 지정하십시오. 🎜🎜요약: 🎜

# cat /etc/my.cnf
[mysqld]
......
innodb_buffer_pool_size = 系统内存%80
innodb_buffer_pool_instances = 大于8的情况下，每个缓冲池实例至少1GB
......

🎜참조: 🎜🎜🎜🎜https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html🎜🎜🎜🎜https://dev.mysql.com/ doc/refman/5.7/en/server-status-variables.htm🎜🎜🎜🎜https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html🎜🎜🎜🎜 innodb_buffer_pool_size 매개변수를 인덱스와 데이터를 캐시하는 데 사용되는 메모리 크기로 설정하면 됩니다. 물론 메모리에서 데이터 읽기 및 쓰기 속도가 매우 빨라 디스크 읽기 및 쓰기가 줄어듭니다. 🎜🎜데이터가 제출되거나 체크포인트 조건이 충족되면 메모리 데이터가 디스크에 한번에 새로 고쳐집니다. 🎜🎜그러나 메모리는 운영 체제나 데이터베이스의 다른 프로세스에서도 사용됩니다. 경험에 따르면 innodb-buffer-pool-size를 서버에서 사용 가능한 총 메모리의 75%로 설정하는 것이 좋습니다. 올바르게 설정하지 않으면 메모리 사용량이 낭비되거나 과도해질 수 있습니다. 🎜

对于繁忙的服务器， buffer pool 将划分为多个实例以提高系统并发性， 减少线程间读写缓存的争用。buffer pool 的大小首先受 innodb_buffer_pool_instances 影响， 当然影响较小。

1.Innodb_buffer_pool_pages_data： Innodb buffer pool缓存池中包含数据的页的数目，包括脏页。单位是page。

show global status like 'Innodb_buffer_pool_pages_data';

2.Innodb_buffer_pool_pages_total： innodb buffer pool的页总数目。单位是page。

show global status like 'Innodb_buffer_pool_pages_total';

3.show global status like 'Innodb_page_size'; 查看@@innodb_buffer_pool_size大小，单位字节

SELECT @@innodb_buffer_pool_size/1024/1024/1024; #字节转为G

4.在线调整InnoDB缓冲池大小，如果不设置，默认为128M

set global innodb_buffer_pool_size = 4227858432; ##单位字节

计算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%

• 当结果 > 95% 则增加 innodb_buffer_pool_size， 建议使用物理内存的 75%

• 当结果 < 95% 则减少 innodb_buffer_pool_size， 建议设置大小为： Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

위 내용은 MySQL에서 Innodb_buffer_pool_size를 설정하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!