Tips pengoptimalan performa untuk MySQL

Konfigurasi hardware merupakan pertimbangan penting untuk performa database. Penting untuk memiliki pemahaman yang baik tentang jumlah pengguna aplikasi yang aktif dan serentak, ukuran database dan indeks, serta latensi yang diharapkan dari aplikasi atau layanan Anda sebelum menentukan konfigurasi hardware. Berikut adalah beberapa pertimbangan hardware yang penting:

Daya pemrosesan adalah salah satu faktor terpenting dalam sistem database yang berperforma baik. Jumlah koneksi/pengguna/thread serentak menentukan jumlah core yang diperlukan untuk memproses permintaan database. CPU yang dialokasikan ke database harus dapat menangani beban kerja normal + beban kerja puncak (ekstrim) agar aplikasi dapat berjalan pada level optimal.

Dalam kasus Cloud SQL, penawaran MySQL yang terkelola sepenuhnya dari Google Cloud, CPU dialokasikan dalam bentuk CPU virtual (vCPU). Jumlah vCPU yang dialokasikan ke database pada akhirnya menentukan jumlah memori dan throughput jaringan untuk instance database karena setiap vCPU memiliki jumlah memori maksimum yang dialokasikan untuk vCPU tersebut dan bahkan throughput jaringan bervariasi berdasarkan jumlah vCPU. Cloud SQL menyediakan fleksibilitas untuk menskalakan jumlah vCPU untuk instance Anda sehingga pemenuhan persyaratan throughput jaringan dan memori aplikasi Anda menjadi lebih mudah.

Pertimbangan penting untuk menentukan jumlah memori yang akan dialokasikan ke database adalah memastikan bahwa set pekerjaan sesuai dengan kumpulan buffer. Set pekerjaan adalah data yang secara aktif digunakan oleh database setiap saat. Memori yang dialokasikan harus cukup untuk menyimpan set pekerjaan ini atau data yang sering diakses, yang biasanya terdiri dari data database, indeks, buffer sesi, cache kamus, dan tabel hash. Salah satu cara untuk memeriksa bahwa memori yang cukup telah dialokasikan adalah dengan memeriksa status pembacaan disk dalam database. Idealnya, pembacaan disk harus kurang atau berada sedikit di bawah kondisi beban kerja normal.

Jika alokasi memori tidak mencukupi untuk instance, instance dapat mengalami masalah “Out of Memory” yang akan menyebabkan instance database memulai ulang dan menyebabkan periode nonaktif database atau aplikasi. 

Penyimpanan database adalah komponen lain yang berperan penting dalam pengoptimalan performa. Cloud SQL menawarkan 2 jenis Penyimpanan 

• SSD (default) 
• HDD

SSD memberikan performa dan throughput yang jauh lebih baik daripada HDD. Oleh karena itu, selalu pilih SSD untuk performa yang lebih baik, khususnya untuk workload produksi. 

Operasi input/output per detik (IOP) untuk baca & tulis yang dialokasikan ke instance bergantung pada jumlah penyimpanan yang dialokasikan saat membuat instance. Semakin besar ukuran disk, semakin besar pula IOP baca dan tulisnya. Oleh karena itu, sebaiknya buat instance dengan ukuran data yang lebih besar untuk performa IOP yang lebih baik. Screenshot Konsol Google Cloud berikut menampilkan ringkasan resource (termasuk kapasitas maksimum) yang dialokasikan ke instance database pada saat pembuatan, sehingga membantu pengguna mengonfirmasi dan memahami dengan tepat bagaimana database mereka akan dikonfigurasi setelah mereka membuat instance database tersebut.

Salah satu cara untuk mengurangi latensi jaringan adalah dengan memilih region Instance yang paling dekat dengan aplikasi atau layanan. Cloud SQL untuk MySQL tersedia di semua region Google Cloud sehingga memudahkan pengguna untuk membuat instance database sedekat mungkin dengan pengguna akhir.

Cloud SQL menawarkan cara mudah untuk meningkatkan atau memperkecil skala resource (CPU, memori, atau penyimpanan) yang ditetapkan ke instance database. Hal ini dapat berguna untuk beban kerja dengan kebutuhan resource yang beragam. Misalnya, pengguna dapat meningkatkan (menambah skala) resource di saat meningkatnya kebutuhan akan beban kerja, lalu memperkecil skala resource saat situasi tersebut berakhir.

Bagian ini berisi praktik terbaik untuk konfigurasi database MySQL guna meningkatkan performa.

Pilih versi MySQL terbaru saat membuat database baru. Versi terbaru memiliki perbaikan bug dan pengoptimalan untuk performa yang lebih baik dibandingkan versi lama. Cloud SQL menyediakan MySQL versi terbaru yang tersedia di pasar dan menjadikannya versi default saat membuat database baru. Detail selengkapnya tentang versi MySQL yang didukung di Cloud SQL.

Untuk instance MySQL, InnoDB adalah satu-satunya mesin penyimpanan yang didukung. Ukuran kumpulan buffer innodb adalah parameter pertama yang perlu ditentukan pengguna untuk performa yang optimal. Kumpulan buffer adalah area memori yang dialokasikan untuk menyimpan cache tabel, cache indeks, data yang dimodifikasi sebelum penghapusan, dan struktur internal lainnya seperti Adaptive Hash Index (AHI). 

Cloud SQL menentukan nilai default sekitar (~72%) dari memori Instance yang akan dialokasikan untuk kumpulan buffer InnoDB, berdasarkan ukuran instance (nilai default bervariasi sesuai ukuran instance). Detail selengkapnya tentang setelan kumpulan buffer pada berbagai ukuran instance. Cloud SQL memberikan fleksibilitas untuk mengubah ukuran kumpulan buffer sesuai dengan kebutuhan aplikasi Anda menggunakan flag database. 

Kumpulan buffer harus berukuran sedemikian rupa agar memori bebas yang memadai tersedia pada instance untuk buffer sesi, cache kamus, tabel performance_schema (jika diaktifkan) selain kumpulan buffer InnoDB.

Pengguna dapat memeriksa pembacaan disk yang terjadi dari instance untuk mengidentifikasi jumlah data yang dibaca dari disk vs pembacaan yang terpenuhi dari kumpulan buffer. Jika jumlah pembacaan disk lebih banyak, maka meningkatkan ukuran kumpulan buffer dan memori instance akan meningkatkan performa kueri baca.

File log InnoDB atau log pengulangan mencatat perubahan data pada data tabel. Ukuran file log InnoDB menentukan ukuran file log pengulangan tunggal. 

Beban kerja penulisan yang berat dengan ukuran log pengulangan yang lebih besar akan memberikan lebih banyak ruang untuk operasi tulis tanpa harus sering melakukan aktivitas pengosongan checkpoint dan menghemat I/O disk, sehingga meningkatkan performa penulisan. Ukuran total log pengulangan, yang dapat dihitung sebagai (innodb_log_file_size * innodb_log_file_size) seharusnya cukup untuk mengakomodasi setidaknya 1-2 jam penulisan data selama periode sibuk akses ke database.

Nilai default yang ditentukan oleh Cloud SQL adalah 512 MB. Cloud SQL juga memberikan fleksibilitas untuk meningkatkan ukuran file log InnoDB menggunakan flag database. 

CATATAN: Meningkatkan nilai ukuran file log InnoDB akan meningkatkan waktu pemulihan error.

Tanda innodb_flush_log_at_trx_commit mengontrol seberapa sering data log dikosongkan ke disk dan apakah akan dihapus untuk setiap commit transaksi atau tidak. 

Performa penulisan pada replika baca dapat ditingkatkan dengan mengubah nilai innodb_flush_log_at_trx_commit menjadi 0 atau 2.

Cloud SQL tidak mendukung perubahan pada setelan ketahanan pada Cloud SQL Utama. Namun, Cloud SQL mengizinkan perubahan tanda pada replika baca. Mengurangi ketahanan pada replika baca akan meningkatkan performa tulis pada replika. Hal ini membantu mengatasi keterlambatan replikasi pada replika. Pelajari lebih lanjut innodb_flush_log_at_trx_commit.

Ukuran buffer log InnoDB adalah jumlah buffer yang digunakan InnoDB untuk menulis dalam file log (Redo log). 

Jika transaksi (penyisipan, update, atau penghapusan) dalam database berukuran besar dan buffer yang digunakan lebih dari 16 MB, maka InnoDB perlu melakukan disk IO sebelum melakukan transaksi, dan hal ini memengaruhi performa. Untuk menghindari Disk IO, tingkatkan nilai innodb_log_buffer_size.

Cloud SQL menentukan nilai default sebesar 16 MB untuk ukuran buffer log InnoDB. Variabel status MySQL innodb_log_waits menunjukkan berapa kali innodb_log_buffer_size berukuran kecil dan InnoDB harus menunggu hingga pengosongan terjadi sebelum melakukan transaksi. Jika nilai untuk innodb_log_waits lebih besar dari 0 dan meningkat, maka tingkatkan nilai innodb_log_buffer_size menggunakan flag database untuk performa yang lebih baik. Nilai innodb_log_buffer_size dan innodb_log_waits dapat diidentifikasi dengan menjalankan kueri berikut di shell MySQL (CLI). Kueri ini menunjukkan nilai variabel status dan variabel global di MySQL.

TAMPILKAN VARIABEL GLOBAL SEPERTI 'innodb_log_buffer_size';

TAMPILKAN STATUS GLOBAL SEPERTI 'innodb_log_waits';

Kapasitas IO InnoDB menentukan jumlah IOP yang tersedia untuk tugas latar belakang (seperti penghapusan halaman dari kumpulan buffer dan menggabungkan data dari buffer perubahan).  

Cloud SQL menetapkan nilai default sebesar 5.000 untuk innodb_io_capacity dan 10.000 untuk innodb_io_capacity. 

Setelan default ini paling cocok untuk sebagian besar beban kerja, tetapi jika beban kerja Anda memiliki jumlah operasi tulis atau perubahan yang belum diterapkan pada instance yang tinggi, dan jika Anda memiliki cukup IOPS yang tersedia pada instance, pertimbangkan untuk meningkatkan innodb_io_capacity dan innodb_io_capacity. Nilai perubahan yang diterapkan dapat ditemukan dengan menggunakan kueri berikut di shell MySQL:

mysql -e 'tampilkan status mesin InnoDB \G;' | grep Ibuf

Buffer sesi adalah memori yang dialokasikan untuk sesi individu. Jika aplikasi atau kueri Anda menyertakan banyak penyisipan, update, pengurutan, gabungan, dan memerlukan buffer yang lebih tinggi, maka menentukan nilai buffer yang tinggi saat menjalankan kueri di sesi tertentu akan menghindari overhead performa. Pengguna bisa mencegah alokasi buffer yang berlebihan pada level global yang menambah nilai untuk semua koneksi dan meningkatkan total penggunaan memori instance pada akhirnya. Mengubah nilai default untuk buffer berikut akan membantu meningkatkan performa kueri. Nilai-nilai ini dapat diubah menggunakan flag database.

sort_buffer_size,

join_buffer_size,

tmp_table_size,

max_heap_table_size

Perlu diperhatikan bahwa ini adalah nilai buffer per sesi, sehingga meningkatkan batas dapat memengaruhi semua koneksi dan pada akhirnya dapat menyebabkan peningkatan penggunaan memori secara keseluruhan.

Jika Anda memiliki terlalu banyak (lebih dari ribuan) tabel dalam instance database (dalam satu atau beberapa database), tingkatkan nilai table_open_cache dan table_open_cache untuk mempercepat pembukaan tabel.

Table_definition_cache mempercepat pembukaan tabel dan hanya memiliki satu entri per tabel. Cache definisi tabel membutuhkan lebih sedikit ruang dan tidak menggunakan deskriptor file. Jika jumlah instance tabel dalam cache objek kamus melebihi batas table_definition_cache, mekanisme LRU akan mulai menandai instance tabel untuk penggusuran dan akhirnya menghapusnya dari cache objek kamus untuk memberi ruang bagi definisi tabel baru. Proses ini dilakukan setiap kali ruang tabel baru dibuka. Hanya ruang tabel tidak aktif yang ditutup. Proses penghapusan ini akan memperlambat pembukaan tabel.

Table_open_cache menentukan jumlah tabel terbuka untuk semua thread. Anda dapat memeriksa apakah cache tabel perlu ditingkatkan dengan memeriksa variabel status Opened_tables. Jika nilai Opened_tables besar dan Anda jarang menggunakan FLUSH TABLES, maka pertimbangkan untuk meningkatkan nilai variabel table_open_cache.

Table_open_cache dan Table_open_cache dapat ditetapkan ke jumlah tabel sebenarnya dalam instance. Pelajari lebih lanjut pemberi rekomendasi tabel terbuka Cloud SQL dalam jumlah banyak.

Catatan: Cloud SQL memberikan fleksibilitas untuk mengubah nilai ini.

Mendefinisikan kunci utama ke tabel akan menyusun data secara fisik, sehingga mempercepat pencarian, pengambilan, dan penyortiran kumpulan data dan meningkatkan performa.

Sebaiknya kunci utama yang bertambah secara otomatis bernilai bilangan bulat ideal untuk sistem OLTP.

Tidak adanya kunci utama juga merupakan salah satu alasan utama keterlambatan replikasi atau jeda replikasi dalam skenario replikasi berbasis baris.

Pembuatan indeks membantu pengambilan data yang lebih cepat sehingga meningkatkan performa kueri baca. Buat indeks untuk kolom yang digunakan di klausa LOKASI, URUTKAN MENURUT, dan KELOMPOKKAN MENURUT. 

CATATAN: Terlalu banyak indeks atau adanya indeks yang tidak digunakan juga dapat mengganggu performa database.

Jalankan uji performa atau tolok ukur untuk melihat apakah konfigurasi sudah optimal atau dapat ditingkatkan lebih lanjut dengan menyesuaikan konfigurasi untuk hardware, database MySQL, atau desain skema. Ubah parameter satu per satu dan tinjau hasil tolok ukur untuk melihat apakah ada peningkatan.

Penggabungan koneksi adalah teknik membuat dan mengelola kumpulan koneksi yang siap digunakan oleh proses apa pun yang membutuhkannya. Penggabungan koneksi dapat meningkatkan performa aplikasi Anda secara signifikan, sekaligus mengurangi penggunaan resource secara keseluruhan. Tinjau detail tentang cara mengelola koneksi dari aplikasi, termasuk jumlah koneksi dan waktu tunggu.

Replika baca (beberapa, di seluruh zona) dapat digunakan untuk memindahkan beban kerja baca dari instance utama. Hal ini mengurangi overhead atau beban pada instance utama sehingga meningkatkan performa instance utama. Selain itu, resource lainnya tersedia untuk kueri baca di replika baca.  

ProxySQL, proxy MySQL open source berperforma tinggi yang mampu merutekan kueri database, dapat digunakan untuk menskalakan database Cloud SQL untuk MySQL secara horizontal.

Kueri yang berjalan selama beberapa menit atau jam diketahui dapat menyebabkan penurunan performa. 

• Log urung digunakan untuk menyimpan versi lama dari baris yang diubah guna melakukan rollback transaksi dan juga memberikan pembacaan (snapshot data) yang konsisten dalam transaksi. Log urung ini disimpan dalam bentuk daftar tertaut dengan versi terbaru yang menunjuk ke yang lebih lama, yang selanjutnya mengarah ke yang lebih lama dan seterusnya. Transaksi yang berjalan lama cenderung menunda penghapusan log urungkan dan oleh karena itu meningkatkan daftar log urungkan. InnoDB harus melalui banyak log urung dan daftar tertaut yang panjang, sehingga mengurangi performa. 
• Kueri yang berjalan lama juga menghabiskan resource (seperti memori, buffer, kunci), yang tidak dibebaskan untuk waktu yang lama dan memengaruhi kueri lainnya karena kurangnya resource.

Terlalu banyak perubahan kumpulan data (memperbarui, menghapus, menyisipkan) dalam satu transaksi akan menahan resource (kunci, buffer) untuk terlalu banyak kumpulan data. Perintah ini mungkin menambahkan buffer log yang mengakibatkan IO Disk. Kueri yang tersisa harus menunggu hingga resource atau kunci dirilis. Hal ini mengakibatkan terlalu banyak data ke dalam kumpulan buffer, sehingga mencegah penggunaan kumpulan buffer lebih lanjut. Rollback transaksi sebesar itu juga menurunkan performa database. Untuk mengatasinya, sebaiknya bagi transaksi besar menjadi transaksi kecil dan transaksi yang berjalan lebih cepat.

Selalu optimalkan kueri untuk mendapatkan hasil terbaik, yaitu, lebih sedikit resource dan eksekusi yang lebih cepat. Tinjau rekomendasi untuk Penyesuaian kueri MySQL.