Mengoptimalkan konsumsi memori tinggi dalam instances

Merupakan masalah umum jika instance menggunakan banyak memori atau mengalami masalah 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, buffering, atau cache yang digunakan oleh sesi tersebut dan dikontrol oleh parameter sesi MySQL. Parameter utama mencakup:

  • 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.

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 atur 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

Menggunakan Metrics Explorer untuk mengidentifikasi penggunaan memori

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

Jika memori gabungan kurang dari 5% dalam database/memory/components.cache dan database/memory/components.free, risiko peristiwa OOM akan tinggi. Untuk memantau penggunaan memori dan mencegah peristiwa OOM, kita memberikan rekomendasi yang sebaiknya disiapkan kebijakan pemberitahuan dengan kondisi batas metrik sebesar 95% atau lebih 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
Hingga 100 GB 95%
100 GB hingga 200 GB 96%
200 GB hingga 300 GB 97%
Lebih dari 300 GB 98%

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 buffering 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 saat ini 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 flag di instance Cloud SQL untuk MySQL, Anda dapat menggunakan Google Cloud Console 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.