Memecahkan masalah memori rendah di Azure Database for MySQL - Server Fleksibel

  • Artikel

BERLAKU UNTUK: Azure Database for MySQL - Server Tunggal Database Azure untuk MySQL - Server Fleksibel

Penting

Server tunggal Azure Database for MySQL berada di jalur penghentian. Kami sangat menyarankan Agar Anda meningkatkan ke server fleksibel Azure Database for MySQL. Untuk informasi selengkapnya tentang migrasi ke server fleksibel Azure Database for MySQL, lihat Apa yang terjadi pada Server Tunggal Azure Database for MySQL?

Untuk membantu memastikan bahwa instans server fleksibel Azure Database for MySQL berkinerja optimal, sangat penting untuk memiliki alokasi dan pemanfaatan memori yang sesuai. Secara default, saat Anda membuat instans server fleksibel Azure Database for MySQL, memori fisik yang tersedia bergantung pada tingkat dan ukuran yang Anda pilih untuk beban kerja Anda. Selain itu, memori dialokasikan untuk buffer dan cache guna meningkatkan operasi database. Untuk informasi selengkapnya, lihat Cara MySQL Menggunakan Memori.

Perhatikan bahwa server fleksibel Azure Database for MySQL menggunakan memori untuk mencapai tembolok sebanyak mungkin. Akibatnya, penggunaan memori sering kali mencapai kisaran antara 80- 90% dari memori fisik instans yang tersedia. Kecuali jika ada masalah dengan kemajuan beban kerja kueri, ini bukan masalah. Namun, Anda mungkin kehabisan memori karena alasan seperti:

  • Buffer dikonfigurasi terlalu besar.
  • Sub kueri optimal sedang berjalan.
  • Kueri melakukan penggabungan dan pengurutan himpunan data besar.
  • Koneksi maksimum yang diatur di server database terlalu tinggi.

Sebagian besar memori server digunakan oleh buffer dan cache global InnoDB, yang mencakup komponen seperti innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size, dan query_cache_size.

Nilai parameter innodb_buffer_pool_size menentukan area memori tempat InnoDB menyimpan cache tabel database dan data terkait indeks. MySQL mencoba mengakomodasi tabel dan data terkait indeks sebanyak mungkin di kumpulan buffer. Kumpulan buffer yang lebih besar memerlukan lebih sedikit operasi I/O yang dialihkan ke disk.

Memantau penggunaan memori

Server fleksibel Azure Database for MySQL menyediakan berbagai metrik untuk mengukur performa instans database Anda. Untuk lebih memahami penggunaan memori untuk server database Anda, lihat metrik Persen Memori Host atau Persen Memori.

Viewing memory utilization metrics.

Jika Anda mendapati bahwa penggunaan memori tiba-tiba meningkat dan memori yang tersedia turun dengan cepat, pantau metrik lain, seperti Persentase CPU Host, Total Koneksi, dan Persen IO, untuk menentukan apakah lonjakan mendadak dalam beban kerja menjadi sumber masalahnya.

Perlu diperhatikan bahwa setiap koneksi yang dibangun dengan server database memerlukan alokasi sejumlah memori. Akibatnya, lonjakan koneksi database dapat menyebabkan kekurangan memori.

Penyebab penggunaan memori tinggi

Mari lihat beberapa penyebab lain penggunaan memori tinggi di MySQL. Penyebab ini bergantung pada karakteristik beban kerja.

Peningkatan tabel sementara

MySQL menggunakan "tabel sementara", yang merupakan jenis tabel khusus yang dirancang untuk menyimpan tataan hasil sementara. Tabel sementara dapat digunakan kembali beberapa kali selama sesi. Karena setiap tabel sementara yang dibuat bersifat lokal untuk sesi, sesi yang berbeda dapat memiliki tabel sementara yang berbeda. Dalam sistem produksi dengan banyak sesi yang melakukan kompilasi kumpulan hasil sementara yang besar, Anda harus memeriksa penghitung status global created_tmp_tables secara berkala, yang melacak jumlah tabel sementara yang dibuat selama jam sibuk. Sejumlah besar tabel sementara dalam memori dapat dengan cepat menyebabkan memori yang tersedia rendah dalam instans server fleksibel Azure Database for MySQL.

Dengan MySQL, ukuran tabel sementara ditentukan oleh nilai dua parameter, seperti yang dijelaskan dalam tabel berikut.

Parameter Keterangan
tmp_table_size Menentukan ukuran maksimum tabel sementara internal dalam memori.
max_heap_table_size Menentukan ukuran maksimum yang dapat ditumbuhkan oleh tabel MEMORY yang dibuat pengguna.

Catatan

Saat menentukan ukuran maksimum tabel sementara internal dalam memori, MySQL mempertimbangkan bagian bawah nilai yang ditetapkan untuk parameter tmp_table_size dan max_heap_table_size.

Rekomendasi

Untuk memecahkan masalah memori rendah yang terkait dengan tabel sementara, pertimbangkan rekomendasi berikut.

  • Sebelum meningkatkan nilai tmp_table_size, verifikasi apakah database Anda diindeks dengan benar, terutama untuk kolom yang terlibat dalam gabungan dan dikelompokkan menurut operasi. Menggunakan indeks yang sesuai pada tabel yang mendasarnya akan membatasi jumlah tabel sementara yang dibuat. Meningkatkan nilai parameter ini dan parameter max_heap_table_size tanpa memverifikasi indeks Anda dapat memungkinkan kueri yang tidak efisien berjalan tanpa indeks dan membuat lebih banyak tabel sementara daripada yang diperlukan.
  • Sesuaikan nilai parameter max_heap_table_size dan tmp_table_size untuk mengatasi kebutuhan beban kerja Anda.
  • Jika nilai yang Anda tetapkan untuk parameter max_heap_table_size dan tmp_table_size terlalu rendah, tabel sementara dapat meluap ke penyimpanan secara berkala, menambahkan latensi ke kueri Anda. Anda dapat melacak tabel sementara yang meluap ke disk menggunakan penghitung status global created_tmp_disk_tables. Dengan membandingkan nilai variabel created_tmp_disk_tables dan created_tmp_tables, Anda melihat jumlah tabel sementara internal pada disk yang telah dibuat dengan jumlah total tabel sementara internal yang dibuat.

Cache tabel

Sebagai sistem multi-utas, MySQL mempertahankan cache pendeskripsi file tabel sehingga tabel dapat dibuka bersamaan secara independen oleh beberapa sesi. MySQL menggunakan sejumlah memori dan pendeskripsi file OS untuk mempertahankan cache tabel ini. Variabel table_open_cache menentukan ukuran cache tabel.

Rekomendasi

Untuk memecahkan masalah memori rendah yang terkait dengan cache tabel, pertimbangkan rekomendasi berikut.

  • Parameter table_open_cache menentukan jumlah tabel terbuka untuk semua utas. Meningkatkan nilai ini akan meningkatkan jumlah pendeskripsi file yang diperlukan mysqld. Anda dapat menentukan apakah cache tabel perlu ditingkatkan dengan memeriksa variabel status opened_tables di penghitung status global yang ditampilkan. Tingkatkan nilai parameter ini secara bertahap untuk mengakomodasi beban kerja Anda.
  • Pengaturan table_open_cache terlalu rendah dapat menyebabkan server fleksibel Azure Database for MySQL menghabiskan lebih banyak waktu dalam membuka dan menutup tabel yang diperlukan untuk pemrosesan kueri.
  • Mengatur nilai ini terlalu tinggi dapat menyebabkan penggunaan lebih banyak memori dan sistem operasi yang menjalankan pendeskripsi file yang mengarah ke koneksi yang ditolak atau gagal memproses kueri.

Buffer lain dan cache kueri

Saat memecahkan masalah yang terkait dengan memori rendah, Anda dapat memanfaatkan beberapa buffer dan cache lagi untuk membantu penyelesaiannya.

Net buffer (net_buffer_length)

Net buffer adalah ukuran untuk buffer koneksi dan utas untuk setiap utas klien dan dapat tumbuh ke nilai yang ditentukan untuk max_allowed_packet. Jika pernyataan kueri besar, misalnya, semua sisipan/pembaruan memiliki nilai yang sangat besar, maka meningkatkan nilai parameter net_buffer_length akan membantu meningkatkan performa.

Buffer gabungan (join_buffer_size)

Buffer gabungan dialokasikan untuk menembolokan baris tabel saat gabungan tidak dapat menggunakan indeks. Jika database Anda memiliki banyak gabungan yang dijalankan tanpa indeks, pertimbangkan untuk menambahkan indeks untuk gabungan yang lebih cepat. Jika Anda tidak dapat menambahkan indeks, pertimbangkan untuk meningkatkan nilai parameter join_buffer_size, yang menentukan jumlah memori yang dialokasikan per koneksi.

Buffer pengurutan (sort_buffer_size)

Buffer pengurutan digunakan untuk melakukan pengurutan terhadap beberapa kueri ORDER BY dan GROUP BY. Jika Anda melihat banyak Sort_merge_passes per detik dalam output SHOW GLOBAL STATUS, pertimbangkan untuk meningkatkan nilai sort_buffer_size guna mempercepat operasi ORDER BY atau GROUP BY yang tidak dapat ditingkatkan menggunakan pengoptimalan kueri atau pengindeksan yang lebih baik.

Hindari meningkatkan nilai sort_buffer_size secara acak kecuali Anda memiliki informasi terkait yang menunjukkan sebaliknya. Memori untuk buffer ini ditetapkan per koneksi. Dalam dokumentasi MySQL, artikel Variabel Sistem Server menyebutkan bahwa di Linux, ada dua ambang batas, 256 KB dan 2 MB, dan yang menggunakan nilai yang lebih besar dapat memperlambat alokasi memori secara signifikan. Maka dari itu, hindari meningkatkan nilai sort_buffer_size melebihi 2M, karena penalti performa akan lebih besar daripada manfaat apa pun.

Cache kueri (query_cache_size)

Cache kueri adalah area memori yang digunakan untuk penembolokan hasil kueri. Parameter query_cache_size menentukan jumlah memori yang dialokasikan untuk penembolokan hasil kueri. Secara default, cache kueri dinonaktifkan. Selain itu, cache kueri tidak digunakan lagi di MySQL versi 5.7.20 dan dihapus di MySQL versi 8.0. Jika cache kueri saat ini diaktifkan dalam solusi Anda, sebelum menonaktifkannya, pastikan bahwa tidak ada kueri yang mengandalkannya.

Menghitung rasio hit buffer cache

Rasio hit buffer cache penting di lingkungan server fleksibel Azure Database for MySQL untuk memahami apakah kumpulan buffer dapat mengakomodasi permintaan beban kerja atau tidak, dan sebagai aturan umum praktis, ini adalah praktik yang baik untuk selalu memiliki rasio hit singgahan kumpulan buffer lebih dari 99%.

Untuk menghitung rasio hit kumpulan buffer InnoDB untuk permintaan baca, Anda dapat menjalankan SHOW GLOBAL STATUS untuk mengambil penghitung "Innodb_buffer_pool_read_requests" dan "Innodb_buffer_pool_reads" lalu menghitung nilainya menggunakan rumus yang ditunjukkan di bawah ini.

InnoDB Buffer pool hit ratio = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

Pertimbangkan contoh berikut.

mysql> show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 197   |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like "innodb_buffer_pool_read_requests";
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| Innodb_buffer_pool_read_requests | 22479167 |
+----------------------------------+----------+
1 row in set (0.00 sec)

Dengan menggunakan nilai di atas, menghitung rasio hit kumpulan buffer InnoDB untuk permintaan baca menghasilkan hasil berikut:

InnoDB Buffer pool hit ratio = 22479167/(22479167+197) * 100 

Buffer hit ratio = 99.99%

Selain memilih pernyataan rasio hit buffer cache, untuk setiap pernyataan DML, penulisan ke Kumpulan Buffer InnoDB terjadi di latar belakang. Namun, jika perlu membaca atau membuat halaman dan tidak ada halaman bersih yang tersedia, Anda juga perlu menunggu halaman dibersihkan terlebih dahulu.

Penghitung Innodb_buffer_pool_wait_free menghitung berapa kali hal ini terjadi. Innodb_buffer_pool_wait_free yang lebih besar dari 0 adalah indikator kuat bahwa Kumpulan Buffer InnoDB terlalu kecil dan peningkatan ukuran kumpulan buffer atau ukuran instans diperlukan untuk mengakomodasi penulisan yang masuk ke database.

Rekomendasi

  • Pastikan database Anda memiliki cukup sumber daya yang dialokasikan untuk menjalankan kueri. Terkadang, Anda mungkin perlu meningkatkan ukuran instans untuk mendapatkan lebih banyak memori fisik sehingga buffer dan cache dapat mengakomodasi beban kerja Anda.
  • Hindari transaksi besar atau jangka panjang dengan memecahnya menjadi transaksi yang lebih kecil.
  • Gunakan peringatan "Persentase Memori Host" agar Anda mendapatkan pemberitahuan jika sistem melebihi salah satu ambang yang ditentukan.
  • Gunakan Wawasan Performa Kueri atau Buku Kerja Azure untuk mengidentifikasi kueri yang bermasalah atau berjalan lambat, lalu optimalkan.
  • Untuk server database produksi, kumpulkan diagnostik secara berkala untuk memastikan bahwa semuanya berjalan dengan lancar. Jika tidak, pecahkan masalah dan atasi masalah apa pun yang Anda identifikasi.

Langkah berikutnya

Untuk menemukan jawaban serekan atas pertanyaan Anda yang paling penting atau memposting atau menjawab pertanyaan, kunjungi Stack Overflow.