Cara MySQL menggunakan memori

MySQL mengalokasikan buffer global pada saat sistem server dimulai dan buffer ini digunakan bersama oleh semua koneksi. Mayoritas memori MySQL dipakai oleh buffer global, misalnya innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size, dll.

Gabungan buffer InnoDB biasanya merupakan pemakai memori terbesar dalam instance MySQL. Konfigurasi ini menggunakan parameter innodb_buffer_pool_size. Protokol ini digunakan untuk meng-cache data dan indeks tabel, buffer perubahan, indeks hash adaptif, dan struktur internal lainnya. Cloud SQL untuk MySQL, penawaran MySQL yang dikelola Google Cloud, mengonfigurasi innodb_buffer_pool_size hingga 72% memori instance, bergantung pada ukuran instance. 

InnoDB mencadangkan memori tambahan untuk buffer dan struktur data terkait, dengan total memori yang dialokasikan sekitar 10% lebih besar dari ukuran gabungan buffer yang ditentukan. Anda dapat memeriksa penggunaan memori gabungan buffer InnoDB di output show engine innodb status\G.

mysql> show engine innodb status\G

…

----------------------

GABUNGAN DAN MEMORI BUFFER

----------------------

Total memori besar yang dialokasikan 11511349248

…

Buffer log InnoDB digunakan untuk menyimpan perubahan yang akan ditulis ke file log InnoDB redo di disk. API ini dikonfigurasi menggunakan innodb_log_buffer_size. Nilai default komunitas MySQL adalah 16 MB, demikian pula nilai default komunitas Cloud SQL untuk MySQL.

Buffer kunci digunakan oleh MySQL untuk meng-cache indeks MyISAM di memori. Kunci ini dikonfigurasi menggunakan key_buffer_size. Nilai default komunitas MySQL adalah 8 MB, demikian pula nilai default komunitas Cloud SQL untuk MySQL. Cloud SQL untuk MySQL tidak mendukung tabel MyISAM. Oleh karena itu, Anda dapat membiarkannya menggunakan default.

Variabel konfigurasi query_cache_size menentukan jumlah memori yang dialokasikan untuk meng-cache hasil kueri. Fitur ini dinonaktifkan secara default di MySQL Community 5.7 dan Cloud SQL untuk MySQL 5.7. 

Cache kueri diketahui memiliki masalah skalabilitas yang serius, oleh karena itu, tidak digunakan lagi di MySQL 5.7.20 dan dihapus di MySQL 8.0. Jika Anda masih menggunakannya untuk instance MySQL 5.7, periksa apakah fitur ini benar-benar berguna untuk beban kerja Anda. Silakan baca blog ini untuk mengetahui informasi selengkapnya. 

MySQL mengalokasikan cache global yang dibagikan ke semua koneksi dan dialokasikan secara dinamis, dan variabel konfigurasi menentukan batas maksimum untuk koneksi tersebut.

MySQL menggunakan cache tabel untuk mempercepat pembukaan tabel. Cache tabel MySQL dipisahkan menjadi dua bagian, yakni cache tabel terbuka dan cache definisi tabel, yang masing-masing dikonfigurasi menggunakan table_open_cache dan table_definition_cache. 

Table_open_cache adalah cache memori untuk menyimpan deskriptor file dari tabel yang terbuka oleh semua thread yang terhubung. Meningkatkan nilai ini akan meningkatkan jumlah deskriptor file yang diperlukan program mysqld, yang juga dikenal sebagai server MySQL. Pastikan sistem operasi Anda dapat menangani jumlah deskriptor file terbuka yang ditunjukkan oleh setelan table_open_cache.

Beberapa sesi klien dapat mengakses tabel yang diberikan secara bersamaan dan tabel akan dibuka secara terpisah oleh setiap sesi klien yang bersamaan. Inilah alasan Anda mungkin melihat jumlah tabel terbuka lebih tinggi dari jumlah tabel di server. Jika cache tabel penuh, server akan melepaskan tabel yang saat ini tidak digunakan, dimulai dari tabel yang paling lama tidak digunakan.

Table_definition_cache adalah cache memori untuk menyimpan definisi tabel. Cache ini bersifat global dan dibagikan di antara semua koneksi.

CloudSQL untuk MySQL 5.7 masing-masing menggunakan nilai default 2000 dan 1400 untuk table_open_cache dan table_definition_cache.

CloudSQL untuk MySQL 8.0 masing-masing menggunakan nilai default 4000 dan 2000 untuk table_open_cache dan table_definition_cache.

Untuk setiap koneksi klien, MySQL menetapkan thread khusus yang mengeksekusi semua kueri dan menampilkan hasilnya ke klien hingga klien memutuskan koneksi. MySQL meng-cache thread agar tidak perlu membuat dan menghancurkan thread untuk setiap koneksi. Jumlah thread dalam cache thread dikonfigurasi menggunakan variabel thread_cache_size.

CloudSQL untuk MySQL menggunakan 48 sebagai default untuk thread_cache_size.

InnoDB memiliki cache sendiri untuk menyimpan definisi tabel, yang berbeda dari cache buka tabel dan cache definisi tabel. Anda dapat memeriksa memori yang dialokasikan untuk kamus data InnoDB di output show engine innodb status\G.

----------------------

GABUNGAN DAN MEMORI BUFFER

----------------------

…

Memori kamus yang dialokasikan 65816817

Setelan table_definition_cache menetapkan batas yang bisa dilewati pada jumlah instance tabel dalam cache kamus data InnoDB. Apabila jumlah instance tabel dalam cache kamus data InnoDB melebihi batas table_definition_cache, mekanisme LRU mulai menandai instance tabel untuk dikeluarkan dan pada akhirnya menghapusnya dari cache ini. 

Apakah ini berarti jumlah instance tabel dalam cache kamus data InnoDB akan selalu lebih rendah dari batas table_definition_cache? Bukan begitu, instance tabel yang memiliki hubungan kunci asing tidak ditempatkan pada daftar LRU. Instance tabel tersebut tetap di-cache sehingga jumlahnya melebihi batas table_definition_cache yang menyebabkan penggunaan memori tambahan. Memori yang digunakan oleh tabel dengan hubungan kunci asing dirilis hanya pada peristiwa shutdown/mulai ulang MySQL. Masalah ini terjadi pada MySQL 5.7 dan 8.0 serta terdapat bug terverifikasi yang diketahui.

Jika instance MySQL Anda memiliki banyak tabel dengan hubungan kunci asing, cache kamus data InnoDB dapat menggunakan memori hingga beberapa GB. Hal ini sering diabaikan saat mengonfigurasi buffer/cache MySQL dan dapat menjadi salah satu penyebab terjadinya masalah penggunaan memori tinggi atau kehabisan memori (OOM) yang tidak terduga.

Contoh output menunjukkan instance kecil yang menggunakan 4,16 GB untuk cache kamus data InnoDB.

$ mysql -e "show engine innodb status\G" | grep -i memory

GABUNGAN DAN MEMORI BUFFER

Total memori besar yang dialokasikan 7696023552

Memori kamus yang dialokasikan 4465193358

Fitur lain dari MySQL yang menghabiskan memori adalah buffer sesi. Buffer ini dialokasikan per sesi dan dalam beberapa kasus, beberapa instance dari buffer tersebut dapat dialokasikan untuk satu kueri (secara khusus, join_buffer_size).

• sort_buffer_size 
• join_buffer_size
• read_buffer_size
• read_rnd_buffer_size

Buffer ini hanya dialokasikan ketika kueri membutuhkannya (untuk pengurutan, gabungan, pemindaian indeks/tabel lengkap, dan lainnya). Namun, saat diperlukan, buffer tersebut dialokasikan ke ukuran penuhnya meskipun bagian yang dibutuhkan sangat kecil. Menyetel buffer ini ke nilai yang tinggi dapat mengakibatkan memori yang terbuang.

Nilai default komunitas MySQL sama dengan nilai default Cloud SQL untuk MySQL.

MySQL menggunakan cache log biner untuk menyimpan perubahan yang dibuat pada log biner saat transaksi berjalan. Ukuran file dikonfigurasi menggunakan binlog_cache_size. Log ini dialokasikan untuk setiap klien jika logging biner diaktifkan (log_bin=ON).

Nilai default untuk binlog_cache_size adalah sama di Komunitas MySQL dan Cloud SQL untuk MySQL.

MySQL membuat tabel sementara internal untuk menyimpan hasil perantara saat memroses beberapa jenis kueri seperti GROUP BY, ORDER BY, DISTINCT, dan UNION. Tabel sementara internal ini dibuat di memori terlebih dahulu kemudian dikonversi ke tabel on-disk ketika ukuran maksimum tercapai. Ukuran maksimum tabel sementara internal ditentukan sebagai ukuran minimum variabel tmp_table_size dan max_heap_table_size. 

Nilai default untuk tmp_table_size dan max_heap_table_size adalah sama di komunitas MySQL dan Cloud SQL untuk MySQL.

Catatan: Sebagai buffer per sesi dan tabel sementara dalam memori yang mengalokasikan memori secara terpisah untuk setiap koneksi, penggunaan memori secara keseluruhan bisa jadi sangat tinggi jika sejumlah besar koneksi membutuhkannya. Sebaiknya jangan tetapkan nilai ini terlalu tinggi, lakukan eksperimen untuk menemukan nilai terbaik bagi workload Anda.

Setiap thread memerlukan sedikit memori untuk mengelola koneksi klien. Variabel berikut mengontrol ukurannya.

• thread_stack: Ukuran stack untuk setiap thread, default-nya adalah 256 KB.
• net_buffer_length: Setiap klien dikaitkan dengan buffer koneksi dan buffer hasil sebesar net_buffer_length. Variabel ini dapat bertambah lagi hingga ukuran max_allowed_packet.

Jika performance_schema diaktifkan, tindakan ini akan membantu memantau eksekusi server MySQL di tingkat rendah. Performance_schema mengalokasikan memori secara dinamis dan hanya dikosongkan saat terjadi penonaktifan/pengaktifan ulang MySQL.

Cloud SQL untuk MySQL memungkinkan performance_schema di instance yang memiliki RAM sebesar 15 GB atau lebih. Hal ini akan diaktifkan secara default mulai dari versi MySQL 8.0.26. Performance_schema dinonaktifkan secara default untuk versi MySQL 5.6, 5.7, dan 8.0.18. Fitur ini dapat diaktifkan menggunakan flag database.

Cloud SQL untuk MySQL secara otomatis mengonfigurasi parameter terkait memori untuk mendapatkan performa yang sangat baik. Instance Anda masih dapat mengalami masalah OOM jika workload memerlukan cache yang lebih besar untuk mendukung sejumlah besar tabel dan/atau koneksi. Banyak thread yang mengalokasikan buffer sesi secara bersamaan juga dapat menyebabkan masalah terkait memori. Makin banyak memori yang dialokasikan untuk buffer/cache global, makin sedikit memori yang tersedia untuk koneksi, buffer sesi, dan sebaliknya, menemukan keseimbangan yang baik adalah kuncinya.

Jika workload Anda memerlukan buffer sesi/cache yang lebih besar, Anda dapat mengurangi ukuran buffer global dengan innodb_buffer_pool_size. Anda dapat mengubah parameter konfigurasi instance Cloud SQL untuk MySQL menggunakan flag database. Jika masih mengalami masalah OOM atau performa, Anda dapat mengupgrade ukuran instance untuk meningkatkan memori.