Kueri alokasi memori shared dan session-specific pada RDS untuk MySQL - ApsaraDB RDS - Alibaba Cloud - ApsaraDB RDS

Memori instans RDS dibagi menjadi dua kategori: shared memory dan session-specific private memory. Shared memory dialokasikan sekali saat startup instans dan digunakan bersama oleh semua koneksi. Session-specific private memory dialokasikan per koneksi sesuai kebutuhan—setiap kueri aktif yang memerlukan pengurutan, penggabungan (joining), atau pembacaan data langsung mengklaim ukuran buffer penuhnya. Pada kondisi konkurensi tinggi, alokasi per koneksi ini merupakan penyebab utama error out-of-memory (OOM) serta alih bencana (failover) antara primary dan secondary.

Cara menghitung memori

Rumus berikut menunjukkan cara memperkirakan total penggunaan memori:

Total memory ≈ Shared memory
             + max_connections × per-session memory per connection

Komponen	Parameter
Shared memory	`innodb_buffer_pool_size` + `innodb_log_buffer_size` + `innodb_additional_mem_pool_size` + `key_buffer_size` + `query_cache_size`
Per-session memory	`sort_buffer_size` + `read_buffer_size` + `read_rnd_buffer_size` + `join_buffer_size` + `binlog_cache_size` + `tmp_table_size`

Shared memory

Jalankan kueri berikut untuk memeriksa alokasi shared memory:

SHOW VARIABLES WHERE Variable_name IN (
  'innodb_buffer_pool_size',
  'innodb_log_buffer_size',
  'innodb_additional_mem_pool_size',
  'key_buffer_size',
  'query_cache_size'
);

Output berikut berasal dari instans 240 MB:

+---------------------------------+-----------------+
| Variable_name                   | Value           |
+---------------------------------+-----------------+
| innodb_additional_mem_pool_size | 2097152         |
| innodb_buffer_pool_size         | 67108864        |
| innodb_log_buffer_size          | 1048576         |
| key_buffer_size                 | 16777216        |
| query_cache_size                | 0               |
+---------------------------------+-----------------+

Parameter

Parameter	Nilai default pada RDS	Deskripsi
`innodb_buffer_pool_size`	64 MB (instans 240 MB)	Kolam buffer InnoDB. Menyimpan halaman data, halaman indeks, halaman undo, insert buffers, adaptive hash indexes, lock, dan kamus data. Semua operasi baca/tulis SQL dilakukan pada kolam buffer, bukan file data fisik — data dituliskan ke disk belakangan melalui checkpoint. Kolam buffer yang lebih besar meningkatkan performa baca/tulis tetapi memperpanjang waktu pemulihan kesalahan.
`innodb_log_buffer_size`	1 MB	Buffer log InnoDB. Menyimpan redo log sebelum disimpan ke disk. Proses flushing dipicu setiap detik oleh master thread, pada setiap commit transaksi, dan ketika ruang buffer tersedia turun di bawah separuh ukuran totalnya. Nilai 1 MB sudah cukup untuk sebagian besar beban kerja.
`innodb_additional_mem_pool_size`	2 MB	Menyimpan struktur data internal InnoDB. Tingkatkan nilai ini jika instans Anda memiliki jumlah tabel yang besar.
`key_buffer_size`	16 MB	Menyimpan cache indeks tabel MyISAM. Untuk tabel MyISAM, cache indeks disimpan di `key_buffer`, sedangkan cache data disimpan di memori sistem operasi. Diatur ke 16 MB untuk semua instans RDS.
`query_cache_size`	0 (dinonaktifkan)	Menyimpan cache hasil kueri berdasarkan hash pernyataan SQL. Dinonaktifkan pada instans RDS. Cache ini tidak valid setiap kali tabel dimodifikasi, sehingga tidak cocok untuk beban kerja dengan banyak operasi tulis.

Session-specific private memory

Berbeda dengan shared memory, buffer sesi tidak dialokasikan sebelumnya saat startup. Setiap nilai parameter dialokasikan secara penuh ketika kueri membutuhkannya. Dengan banyak koneksi bersamaan, alokasi kumulatif dapat melebihi kapasitas memori yang tersedia, sehingga memicu error OOM.

Jalankan kueri berikut untuk memeriksa alokasi memori sesi:

SHOW VARIABLES WHERE Variable_name IN (
  'read_buffer_size',
  'read_rnd_buffer_size',
  'sort_buffer_size',
  'join_buffer_size',
  'binlog_cache_size',
  'tmp_table_size'
);

Contoh output:

+-------------------------+-----------------+
| Variable_name           | Value           |
+-------------------------+-----------------+
| binlog_cache_size       | 262144          |
| join_buffer_size        | 262144          |
| read_buffer_size        | 262144          |
| read_rnd_buffer_size    | 262144          |
| sort_buffer_size        | 262144          |
| tmp_table_size          | 262144          |
+-------------------------+-----------------+

Parameter

Parameter	Nilai default pada RDS	Dapat dimodifikasi di konsol	Deskripsi
`read_buffer_size`	256 KB	Tidak	Buffer pemindaian sekuensial. Dipindai sebelum pembacaan fisik untuk mengurangi I/O.
`read_rnd_buffer_size`	256 KB	Tidak	Buffer pemindaian acak. Digunakan ketika baris dibaca dalam urutan terurut setelah pemindaian indeks.
`sort_buffer_size`	256 KB	Tidak	Buffer pengurutan untuk operasi `ORDER BY` dan `GROUP BY`. Jika data yang diurutkan melebihi ukuran ini, MySQL membuat tabel temporary di disk. Pada Linux, mengatur nilai ini di atas 2 MB mengubah alokasi memori dari `malloc()` ke `mmap()`, yang mengurangi efisiensi.
`join_buffer_size`	256 KB	Tidak	Buffer operasi join. ApsaraDB RDS for MySQL hanya mendukung algoritma nested loop join. Tabel non-driving disimpan dalam buffer ini untuk menghindari kontensi pada kolam buffer.
`binlog_cache_size`	256 KB	Tidak	Cache log biner. Menyimpan log transaksi sebelum commit; disimpan ke file log biner di disk setelah commit.
`tmp_table_size`	256 KB	Ya	Ukuran maksimum tabel temporary di memori. Jika suatu kueri membuat tabel temporary yang melebihi batas ini, MySQL mengonversinya menjadi tabel MyISAM di disk. Jika Anda melihat error berikut, tingkatkan nilai ini: `[Err] 1114 - The table '/home/mysql/data3081/tmp/#sql_6197_2' is full`

Catatan: Hanya tmp_table_size yang dapat dimodifikasi di konsol RDS. Parameter buffer sesi lainnya tidak dapat diubah melalui konsol.