Bagaimana untuk menetapkan Innodb_buffer_pool_size dalam MySQL

Kaedah tetapan Innodb_buffer_pool_size

Data cache jadual InnoDB, indeks dan penimbal tambahan lain disimpan dalam kawasan memori dalam kumpulan penimbal. Saiz kolam penimbal adalah penting untuk prestasi sistem. Kumpulan penimbal yang lebih besar boleh mengurangkan I/O cakera untuk berbilang akses kepada data jadual yang sama. Adalah wajar untuk menetapkan saiz kumpulan penimbal kepada 80% daripada saiz memori fizikal komputer pada pelayan pangkalan data khusus

Penerangan parameter berkaitan kumpulan penimbal

1) Parameter pembolehubah sistem

• Innodb_page_size

• Saiz halaman InnoDB (lalai ialah 16KB). Terdapat banyak nilai yang terkandung dalam halaman, dan saiz halaman membolehkannya dengan mudah ditukar kepada bait.

• Innodb_buffer_pool_chunk_size

• innodb_buffer_pool_chunk_size mentakrifkan saiz bongkah untuk operasi pensaiz semula kumpulan penimbal InnoDB. Lalai ialah 128M. Nilai maksimum boleh ditetapkan innodb_buffer_pool_size / innodb_buffer_pool_instances

• innodb_buffer_pool_instances

• Bilangan kawasan kumpulan penimbal InnoDB dibahagikan kepada.

• Innodb_buffer_pool_pages_data

• Bilangan halaman data yang terkandung dalam kumpulan penimbal InnoDB. Nombor ini termasuk halaman kotor dan halaman bersih. Apabila menggunakan jadual termampat, nilai data Innodb_buffer_pool_pages_data mungkin lebih besar daripada Innodb_buffer_pool_pages_total (Pepijat #59550).

• Innodb_buffer_pool_pages_total

• Jumlah saiz kumpulan penimbal InnoDB dalam halaman. Apabila menggunakan jadual termampat, nilai Innodb_buffer_pool_pages_data yang dilaporkan mungkin lebih besar daripada Innodb_buffer_pool_pages_total (Pepijat #59550) 🎜>

Bilangan permintaan untuk memuat semula halaman daripada kumpulan penimbal.

• Innodb_buffer_pool_pages_flushed

• mewakili bilangan permintaan untuk membaca logik daripada ingatan.

• Innodb_buffer_pool_read_requests

• Bilangan bacaan logik yang InnoDB tidak dapat dipenuhi daripada kumpulan penimbal dan mesti dibaca terus daripada cakera.

• Innodb_buffer_pool_reads

• Biasanya, menulis kepada kumpulan penimbal InnoDB berlaku di latar belakang. Apabila InnoDB perlu membaca atau mencipta halaman dan tiada halaman bersih tersedia, InnoDB mula-mula membuang beberapa halaman yang kotor dan menunggu operasi selesai. Kaunter ini mengira kejadian menunggu ini. Jika ditetapkan dengan betul, innodb_buffer_pool_size hendaklah nilai yang lebih kecil.

• Tetapkan parameter berkaitan kumpulan penimbal dengan munasabah

  Innodb_buffer_pool_wait_free

  1 innodb_buffer_pool_size ditetapkan kepada 80% daripada memori sistem secara lalai, dan kemudian dilaraskan mengikut peraturan berikut. agar sesuai dengan situasi sebenar

• 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 innodb_buffer_pool_instances ditetapkan kepada 8 secara lalai, minimum 1, maksimum 64

Untuk sistem dengan kumpulan penimbal dalam berbilang julat -gigabait, dengan mengurangkan Concurrency boleh dipertingkatkan dengan membahagikan kumpulan penimbal kepada keadaan berasingan disebabkan perbalahan untuk urutan yang berbeza untuk membaca dan menulis halaman cache. Ciri ini biasanya digunakan pada sistem dengan saiz kolam penimbal dalam julat berbilang GB. Berbilang contoh kumpulan penimbal dikonfigurasikan menggunakan pilihan konfigurasi innodb_buffer_pool_instances Anda juga boleh melaraskan nilai innodb_buffer_pool_size.

Banyak permintaan data boleh dipenuhi dengan mendapatkan semula daripada memori, terutamanya jika kumpulan penimbal InnoDB adalah besar. Anda mungkin menghadapi kesesakan dengan berbilang benang yang cuba mengakses kumpulan penimbal sekaligus. Anda boleh mendayakan berbilang kumpulan penimbal untuk meminimumkan perbalahan ini. Halaman dalam kumpulan penimbal disimpan atau dibaca dengan memetakan setiap halaman secara rawak ke lokasi dalam kumpulan penimbal menggunakan fungsi cincang. Setiap kumpulan penimbal mengurus senarai percumanya sendiri, senarai siram, LRU dan semua struktur data lain yang disambungkan ke kumpulan penimbal, dan dilindungi oleh mutex kolam penimbalnya sendiri. Untuk mendayakan berbilang contoh kumpulan penimbal, tetapkan pilihan konfigurasi innodb_buffer_pool_instances kepada nilai yang lebih besar daripada 1 (lalai), sehingga maksimum 64 (maksimum). Pilihan ini hanya berkuat kuasa apabila innodb_buffer_pool_size ditetapkan kepada 1GB atau lebih besar. Jumlah saiz yang anda tentukan akan diperuntukkan kepada semua kumpulan penimbal. Untuk kecekapan terbaik, nyatakan gabungan innodb_buffer_pool_instances dan innodb_buffer_pool_size supaya setiap contoh kumpulan penimbal ialah sekurang-kurangnya 1GB.

Ringkasan:

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

Rujukan:

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
Tetapkan parameter innodb_buffer_pool_size

• Penggunaan Mengenai saiz memori indeks cache dan data, lebih banyak lebih baik, sudah tentu membaca dan menulis data dalam ingatan adalah sangat cepat, yang mengurangkan membaca dan menulis ke cakera.

Hanya apabila data diserahkan atau syarat pusat pemeriksaan dipenuhi, data memori dibuang ke cakera sekali gus.

• Walau bagaimanapun, memori juga digunakan oleh proses lain sistem pengendalian atau pangkalan data Berdasarkan pengalaman, adalah disyorkan untuk menetapkan saiz innodb-buffer-pool kepada 75% daripada jumlah memori tersedia bagi pelayan. . Jika tidak ditetapkan dengan betul, penggunaan memori mungkin terbuang atau berlebihan.

  对于繁忙的服务器， 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)

Atas ialah kandungan terperinci Bagaimana untuk menetapkan Innodb_buffer_pool_size dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!