Mengoptimalkan penggunaan memori tinggi dalam instance

Merupakan masalah umum jika instance menggunakan banyak memori atau mengalami peristiwa kehabisan memori (OOM). Instance database yang berjalan dengan pemakaian memori tinggi sering menyebabkan masalah performa, terhenti, atau bahkan periode nonaktif database.

Beberapa blok memori MySQL digunakan secara global. Ini berarti bahwa semua beban kerja kueri berbagi lokasi memori, ditempati sepanjang waktu, dan dirilis hanya ketika proses MySQL berhenti. Beberapa blok memori berbasis sesi, yang berarti bahwa segera setelah sesi ditutup, memori yang digunakan oleh sesi tersebut juga dirilis kembali ke sistem.

Setiap kali ada penggunaan memori yang tinggi oleh instance Cloud SQL untuk MySQL, Cloud SQL merekomendasikan agar Anda mengidentifikasi kueri atau proses yang menggunakan banyak memori dan melepaskannya. Konsumsi memori MySQL dibagi menjadi tiga bagian utama:

  • Thread dan konsumsi memori proses
  • Konsumsi memori buffer
  • Konsumsi memori cache

Thread dan konsumsi memori proses

Setiap sesi pengguna menggunakan memori, bergantung pada kueri yang berjalan, buffer, atau cache yang digunakan oleh sesi tersebut dan dikontrol oleh parameter sesi MySQL. Parameter utamanya meliputi:

  • thread_stack
  • net_buffer_length
  • read_buffer_size
  • read_rnd_buffer_size
  • sort_buffer_size
  • join_buffer_size
  • max_heap_table_size
  • tmp_table_size

Jika ada N jumlah kueri yang berjalan pada waktu tertentu, setiap kueri akan menggunakan memori sesuai dengan parameter ini selama sesi tersebut.

Konsumsi memori buffer

Bagian memori ini umum untuk semua kueri dan dikontrol oleh parameter seperti innodb_buffer_pool_size, innodb_log_buffer_size, dan key_buffer_size.

Kumpulan buffer InnoDB, yang dikonfigurasi oleh flag innodb_buffer_pool_size, menggunakan sejumlah besar memori di instance Cloud SQL untuk MySQL Anda dan berfungsi sebagai cache untuk meningkatkan performa. Untuk mengurangi risiko peristiwa kehabisan memori (OOM), Anda dapat mengaktifkan pool buffer terkelola.

Konsumsi memori cache

Memori cache menyertakan cache kueri, yang digunakan untuk menyimpan kueri dan hasilnya agar pengambilan data kueri berikutnya yang sama lebih cepat. Aktivitas ini juga mencakup cache binlog untuk menyimpan perubahan yang dilakukan pada log biner saat transaksi berjalan, dan dikontrol oleh binlog_cache_size.

Konsumsi memori lainnya

Memori juga digunakan oleh operasi penggabungan dan pengurutan Jika kueri Anda menggunakan operasi gabungan atau pengurutan, kueri tersebut akan menggunakan memori berdasarkan join_buffer_size dan sort_buffer_size.

Selain itu, jika Anda mengaktifkan skema performa, ini akan menghabiskan memori. Untuk memeriksa penggunaan memori oleh skema performa, gunakan kueri berikut:

SELECT *
FROM
  performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

Ada banyak instrumen yang tersedia di MySQL yang dapat Anda siapkan untuk memantau penggunaan memori melalui skema performa. Untuk mempelajari lebih lanjut, lihat dokumentasi MySQL.

Parameter terkait MyISAM untuk penyisipan data massal adalah bulk_insert_buffer_size.

Untuk mempelajari cara MySQL menggunakan memori, lihat dokumentasi MySQL.

Rekomendasi

Bagian berikut menawarkan beberapa rekomendasi untuk penggunaan memori yang optimal.

Mengaktifkan kumpulan buffer terkelola

Mengaktifkan kumpulan buffer terkelola membantu Anda mengurangi konsumsi memori kumpulan buffer InnoDB (atau innodb_buffer_pool_size) saat memori instance tinggi. Pengurangan ini mengosongkan memori yang kemudian dapat digunakan oleh proses database lainnya. Jika penggunaan memori instance Anda tinggi, instance Anda dapat mengalami peristiwa kehabisan memori (OOM). Sebaiknya aktifkan managed buffer pool di instance Anda untuk membantu mencegah peristiwa OOM.

Saat penggunaan memori stabil pada nilai yang lebih rendah selama 10 menit atau lebih, MySQL akan menaikkan nilai innodb_buffer_pool_size secara bertahap ke nilai aslinya. Anda juga dapat meningkatkan nilai tanda innodb_buffer_pool_size ke nilai yang dipilih setelah penggunaan memori stabil.

Kriteria kelayakan

Anda tidak dapat mengaktifkan pool buffer terkelola untuk instance core bersama, atau untuk MySQL 5.6 atau MySQL 5.7.

Aktifkan fitur

Untuk mengaktifkan pool buffer terkelola untuk instance Anda, tetapkan flag innodb_cloudsql_managed_buffer_pool ke on. Untuk mengetahui informasi selengkapnya tentang menyetel flag database, lihat Menyetel flag database.

Mengubah nilai flag innodb_cloudsql_managed_buffer_pool tidak memerlukan mulai ulang instance Cloud SQL.

Jika Anda telah mengaktifkan managed buffer pool dan konsumsi memori instance Anda melebihi persentase batas default dari memori yang dialokasikan, Cloud SQL akan mulai mengurangi ukuran innodb_buffer_pool_size. Persentase nilai minimum default ini bervariasi antara 90% dan 97%, bergantung pada kapasitas RAM instance Anda. Untuk mengubah nilai minimum, tetapkan flag innodb_cloudsql_managed_buffer_pool_threshold_pct ke nilai persentase yang berbeda. Misalnya, untuk menyesuaikan nilai minimum menjadi 97%, gunakan perintah berikut:

gcloud sql instances patch INSTANCE_NAME \
   --database-flags=EXISTING_FLAGS,innodb_cloudsql_managed_buffer_pool=on,\
     innodb_cloudsql_managed_buffer_pool_threshold_pct=97

Anda dapat menetapkan flag innodb_cloudsql_managed_buffer_pool_threshold_pct ke nilai bilangan bulat antara 50 dan 99. Mengubah nilai batas penggunaan memori tidak memerlukan mulai ulang instance Cloud SQL.

Logika penyesuaian

Managed buffer pool tidak mengecilkan innodb_buffer_pool_size ke ukuran minimum tetap yang telah ditentukan sebelumnya. Sebagai gantinya, ukuran dikurangi secara iteratif dan dinamis hingga total pemanfaatan memori instance kembali di bawah persentase nilai minimum yang dikonfigurasi (innodb_cloudsql_managed_buffer_pool_threshold_pct). Ukuran kumpulan buffer dikurangi dengan menyesuaikan nilai flag innodb_buffer_pool_size, dengan memanfaatkan fungsi pengubahan ukuran kumpulan buffer bawaan InnoDB.

Untuk mencegah innodb_buffer_pool_size menyusut ke ukuran yang akan sangat memengaruhi performa saat penggunaan memori tetap tinggi meskipun telah dikurangi, fitur ini menggunakan batas keamanan internal. Nilai mewakili persentase total memori instance yang harus dialokasikan ke kumpulan buffer.

Ukuran penampung MySQL Ukuran kumpulan buffer minimum
1025–2048 MB 35%
2049–6528 MB 30%
6529–11315 MB 40%
11316–22630 MB 45%
Ukuran lainnya (default) 50%

Jumlah penurunan innodb_buffer_pool_size bergantung pada kapasitas memori instance database Anda. Tabel berikut menunjukkan penurunan persentase untuk setiap ukuran penampung:

Ukuran penampung MySQL Penurunan persentase
1025–2048 MB 15%
2049–6528 MB 11%
6529–11315 MB 8%
11316–22630 MB 6%
Ukuran lainnya (default) 5%

Setelah menghitung nilai baru yang dikurangi, pool buffer terkelola akan membulatkan innodb_buffer_pool_size ke kelipatan terdekat dari nilai innodb_buffer_pool_instances dan innodb_buffer_pool_chunk_size.

Saat kumpulan buffer terkelola melakukan penyesuaian pada nilai innodb_buffer_pool_size, perubahan tidak tercermin di konsol Google Cloud . Untuk melihat nilai innodb_buffer_pool_size saat ini saat kumpulan buffer terkelola diaktifkan, Anda dapat menggunakan klien MySQL:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

Batasan

Mengurangi ukuran kumpulan buffer tidak dapat mencegah OOM dalam semua kasus. Misalnya, beberapa beban kerja mungkin menggunakan memori secara tidak berkelanjutan atau meningkat secara tiba-tiba, beberapa instance Cloud SQL mungkin kurang disediakan, atau buffer pool mungkin belum di-warm up. Cloud SQL mungkin tidak dapat mengosongkan memori dengan cukup cepat untuk mengakomodasi perubahan mendadak dalam workload memori. Selain itu, Cloud SQL tidak dapat mengakomodasi nilai flag memori lainnya yang salah dikonfigurasi.

Pemantauan

Anda dapat memantau pool buffer terkelola di log error MySQL. Di Logs Explorer, Anda dapat memfilter log mysql.err untuk entri dengan awalan Managed Buffer Pool Plugin: atau Tuner Plugin: guna menemukan peristiwa penyesuaian terbaru.

Saat pertama kali dimulai, kumpulan buffer terkelola akan memancarkan log seperti berikut:

[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: MySQL Instance memory limit: 29533, Current MySQL memory usage: 2663641088, Max Allowed MySQL memory usage: 30732730368 ...

Log berikut menunjukkan contoh penurunan otomatis pada innodb_buffer_pool_size:

[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: Decreasing InnoDB Buffer Pool Size.
[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: Updated innodb_buffer_pool_size=805306368 bytes.

Anda dapat mengonfigurasi metrik berbasis log untuk melacak peristiwa penyesuaian pool buffer terkelola dari waktu ke waktu.

Menggunakan Metrics Explorer untuk mengidentifikasi penggunaan memori

Anda dapat meninjau penggunaan memori instance dengan metrik database/memory/components.usage di Metrics Explorer.

Secara umum, jika Anda memiliki memori gabungan kurang dari 10% dalam database/memory/components.cache dan database/memory/components.free, risiko peristiwa OOM akan tinggi. Untuk memantau penggunaan memori dan mencegah peristiwa OOM, sebaiknya siapkan kebijakan pemberitahuan dengan kondisi batas metrik di database/memory/components.usage.

Tabel berikut menunjukkan hubungan antara memori instance dan nilai minimum pemberitahuan yang direkomendasikan:

Memori instance Nilai minimum pemberitahuan yang direkomendasikan
Kurang dari atau sama dengan 16 GB 90%
Lebih besar dari 16 GB 95%

Menghitung konsumsi memori

Hitung penggunaan memori maksimum oleh database MySQL Anda untuk memilih jenis instance yang sesuai untuk database MySQL Anda. Gunakan formula berikut:

Penggunaan memori MySQL maksimum = innodb_buffer_pool_size +innodb_additional_mem_pool_size +innodb_log_buffer_size +tmp_table_size +key_buffer_size + ((read_buffer_size +read_rnd_buffer_size +sort_buffer_size +join_buffer_size ) xmax_connections )

Berikut adalah parameter yang digunakan dalam formula tersebut:

  • innodb_buffer_pool_size: Ukuran dalam byte kumpulan buffer, area memori tempat InnoDB meng-cache tabel dan mengindeks data.
  • innodb_additional_mem_pool_size: Ukuran dalam byte kumpulan memori yang digunakan InnoDB untuk menyimpan informasi kamus data dan struktur data internal lainnya.
  • innodb_log_buffer_size: Ukuran dalam byte buffering yang digunakan InnoDB untuk menulis ke file log di disk.
  • tmp_table_size: Ukuran maksimum tabel sementara dalam memori internal yang dibuat oleh mesin penyimpanan MEMORY dan, mulai MySQL 8.0.28, mesin penyimpanan TempTable.
  • key_buffer_size: Ukuran buffering yang digunakan untuk blok indeks. Blok indeks untuk tabel MyISAM di-buffer dan dibagikan oleh semua thread.
  • read_buffer_size: Setiap thread yang melakukan pemindaian berurutan untuk tabel MyISAM mengalokasikan buffering dengan ukuran ini (dalam byte) untuk setiap tabel yang dipindai.
  • read_rnd_buffer_size: Variabel ini digunakan untuk membaca dari tabel MyISAM, untuk semua mesin penyimpanan, dan untuk pengoptimalan Pembacaan Multi-Rentang.
  • sort_buffer_size: Setiap sesi yang harus melakukan pengurutan mengalokasikan buffering dengan ukuran ini. sort_buffer_size tidak bersifat khusus untuk mesin penyimpanan apa pun dan berlaku secara umum untuk pengoptimalan.
  • join_buffer_size: Ukuran minimum buffer yang digunakan untuk pemindaian indeks biasa, pemindaian indeks rentang, dan penggabungan yang tidak menggunakan indeks sehingga melakukan pemindaian tabel penuh.
  • max_connections: Jumlah maksimum koneksi klien simultan yang diizinkan.

Memecahkan masalah konsumsi memori yang tinggi

  • Jalankan SHOW PROCESSLIST untuk melihat kueri yang sedang berlangsung yang menggunakan memori. Fungsi ini menampilkan semua thread yang terhubung serta pernyataan SQL yang sedang berjalan dan mencoba mengoptimalkannya. Perhatikan kolom status dan durasi.

    mysql> SHOW [FULL] PROCESSLIST;

  • Periksa SHOW ENGINE INNODB STATUS di bagian ini BUFFER POOL AND MEMORY untuk melihat kumpulan buffer saat ini dan penggunaan memori, yang dapat membantu Anda menetapkan ukuran kumpulan buffer.

    mysql> SHOW ENGINE INNODB STATUS \G
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 398063986; in additional pool allocated 0
Dictionary memory allocated 12056
Buffer pool size 89129
Free buffers 45671
Database pages 1367
Old database pages 0
Modified db pages 0

  • Gunakan perintah SHOW variables MySQL untuk memeriksa nilai penghitung yang memberikan informasi seperti jumlah tabel sementara, jumlah thread, jumlah cache tabel, halaman kotor, tabel terbuka, dan penggunaan kumpulan buffer.

    mysql> SHOW variables like 'VARIABLE_NAME'

Terapkan perubahan

Setelah Anda menganalisis penggunaan memori oleh berbagai komponen, tetapkan flag in your MySQL database. Untuk mengubah tanda di instance Cloud SQL untuk MySQL, Anda dapat menggunakan Konsol Google Cloud atau gcloud CLI. Untuk mengubah nilai tanda menggunakan konsol Google Cloud , edit bagian Tanda, pilih tanda, lalu masukkan nilai baru.

Terakhir, jika penggunaan memori masih tinggi dan Anda merasa menjalankan kueri serta nilai flag telah dioptimalkan, maka pertimbangkan untuk meningkatkan ukuran instance untuk menghindari OOM.

Langkah berikutnya