Optimasi MySQL – Data administrator adalah seseorang yang bertanggung jawab dalam mengelola dan menjaga keamanan sistem manajemen data dalam sebuah perusahaan. Biasanya mereka merancang, mengembangkan, memantau kinerja database, melakukan backup data, dan mengatur pembaharuan database. Karena database adalah sumber utama informasi dari proses bisnis perusahaan, maka peran mereka sangat penting.
Di era digital ini, ada teknologi yang disebut dengan MySQL. MySQL adalah sebuah sistem yang dapat membantu manajemen basis data relasional yang biasanya digunakan untuk menyimpan, mengatur, dan mengakses data dalam aplikasi web. MySQL memiliki fitur-fitur seperti pengaturan transaksi, dukungan foreign key, akses user, dan penguncian yang dapat membantu dalam manajemen dan pengembangan aplikasi. Contoh penggunaan MySQL dalam aplikasi web meliputi:
Mengelola data pengguna, seperti informasi login dan profil pengguna
Menyimpan data produk, harga, dan persediaan dalam aplikasi e-commerce
Menyimpan data transaksi keuangan dan pembayaran dalam aplikasi finansial
Mengelola data konten, seperti posting blog, artikel, dan gambar pada situs web
Secara default, kinerja MySQL sudah baik. Namun untuk dapat menangani database yang memiliki kapasitas besar, performa MySQL dengan pengaturan default masih kurang maksimal. Kinerja MySQL dapat dimaksimalkan dan bekerja dengan efisien dengan mengoptimasi pengaturannya melalui beberapa praktik. Yang mana praktik untuk performa terbaik MySQL akan dibahas dalam artikel ini.
Apa Akibat Tidak Melakukan Optimasi MySQL?
Sebelum menerapkan pengoptimalan MySQL, tentu Anda harus mengetahui apa saja akibat jika tidak mengoptimasi MySQL. Beberapa kendala akan muncul jika tidak menerapkan pengoptimalan MySQL, seperti:
1. Kinerja yang buruk yang akan mengakibatkan lamanya waktu respon pada permintaan user yang membuat mereka menjadi tidak nyaman.
2. Kapasitas terbatas dan tidak mencukupi kebutuhan bisnis yang bisa mengakibatkan penggunaan sumber daya yang tidak efisien.
3. Keamanan menjadi rentan akan serangan siber seperti hacking atau injection yang dapat mengakibatkan kebocoran dan penyalahgunaan data oleh orang yang memiliki tujuan jahat.
4. Meningkatkan potensi kehilangan data atau downtime yang lama sehingga menimbulkan kerugian bisnis pada perusahaan dan hilangnya loyalitas pelanggan.
Beberapa kendala tersebut terjadi karena ketidakseimbangan sumber daya hardware, penggunaan MyISAM engine yang tidak efisien, kurang sesuainya konfigurasi server, dan kerja kueri yang tidak optimal. Oleh karena itu, MySQL perlu untuk dioptimalkan.
- Menyeimbangkan 4 Hardware Resource Utama (Storage, Processor, Memory, Network)
Empat hardware resource utama yang dimaksud adalah storage (penyimpanan), processor (prosesor), memory (memori), dan network (jaringan). Pastikan bahwa sumber daya hardware tidak terlalu banyak atau terlalu sedikit, sehingga kinerja MySQL tetap optimal. Berikut improvisasi optimal yang bisa diterapkan disetiap resource utama hardware:
Storage (penyimpanan). Agar kinerja storage meningkat, lakukan upgrade HDD ke SSD. Untuk memantau tingkat input/output disk, gunakan tools seperti iotop/sar dari sysstat package.
Processor (prosesor). Apabila CPU mengalami hambatan, maka perlu dilakukan upgrade. Untuk melihat kondisi CPU, Linux top command dapat digunakan untuk merinci bagaimana sumber daya telah digunakan.
Memory (memori). Memori mewakili jumlah total RAM di server penyimpanan database MySQL. Untuk meningkatkan kinerjanya, lakukan penyesuaian cache pada memori. Saat kehabisan memori, server akan menyimpan data cache (seperti hard drive) untuk bertindak sebagai memori. Padahal database caching dapat memperlambat kinerja MySQL.
Network (jaringan). Memantau traffic network dan memastikan memiliki infrastruktur yang memadai untuk mengelola beban data merupakan hal yang penting. Membebani network dengan rasio yang besar dapat menyebabkan latency, dropped packets, dan bahkan pemadaman server.
- Penggunaan InnoDB dan bukan MyISAM
InnoDB dan MyISAM merupakan jenis storage engine yang digunakan dalam sistem manajemen database MySQL. MyISAM merupakan gaya database lama yang digunakan pada beberapa database MySQL sehingga kurang efisien dengan InnoDB yang dapat mendukung fitur lebih canggih dan memiliki mekanisme optimasi bawaan.
Mengapa lebih disarankan untuk menggunakan InnoDB daripada MyISAM? Berikut adalah penjelasan mengenai perbedaan keduanya:
| InnoDB | MyISAM | |
| Transaksi dan Konsistensi Data | mendukung transaksi dan konsistensi data | tidak mendukung transaksi dan tidak memberikan tingkat konsistensi data yang sama. |
| Locking dan Concurrent Access | menggunakan penguncian baris (row-level locking), yang memungkinkan pengguna untuk melakukan operasi read dan write secara konkuren pada baris-baris yang berbeda dalam tabel yang sama. | jika satu pengguna sedang menulis (write) ke tabel, pengguna lain tidak dapat membaca (read) atau menulis ke tabel yang sama pada saat yang bersamaan. |
| Pemulihan dan Redundansi | menggunakan log transaksi untuk mencatat perubahan data yang terjadi, serta mendukung replikasi dan mirroring | tidak memiliki fitur-fitur yang ada di InnoDB |
| Fitur-fitur Lanjutan | dukungan untuk foreign key (kunci asing), sehingga dapat mengatur hubungan antara tabel. indeks yang lebih canggih untuk mengoptimalkan kueri. | tidak mendukung foreign key dan indeks yang canggih. Lemah untuk penggunaan kompleks |
| Dukungan dan Pengembangan | lebih sering diperbarui dengan fitur-fitur baru dan perbaikan bug. telah menjadi mesin penyimpanan default untuk MySQL saat ini | lebih jarang diperbarui dan dikembangkan, karena sudah tidak menjadi mesin penyimpanan default dalam MySQL. |
Tabel tersebut sangat jelas menunjukkan bahwa InnoDB jauh lebih baik untuk digunakan karena menyediakan fitur-fitur lebih canggih yang banyak dibutuhkan saat ini daripada fitur yang disediakan oleh MyISAM. Namun, perlu diingat bahwa setiap perubahan dalam mesin penyimpanan harus dipertimbangkan dengan cermat dan diuji dengan baik untuk memastikan kompatibilitas dan kinerja yang optimal untuk aplikasi MySQL.
- Konfigurasi Server MySQL
Konfigurasi server adalah pengaturan dan penyesuaian yang dilakukan untuk mengoptimalkan kinerja, keamanan, dan skalabilitas database. Dalam MySQL, aspek penting konfigurasi tersebut adalah pengaturan “file my.cnf” yang merupakan konfigurasi utama MySQL yang isinya adalah pengaturan untuk mengendalikan berbagai aspek server MySQL. Beberapa pengaturan yang perlu diperhatikan dalam file my.cnf antara lain:
Query Cache (query_cache_size): Pengaturan untuk menentukan ukuran cache dari queri MySQL yang menunggu untuk dijalankan yang ada pada penyimpanan data dalam memori.
Jumlah Koneksi (max_connections): Harus dikonfigurasi dengan bijaksana. Jika jumlah koneksi terlalu rendah maka akan terjadi penolakan koneksi oleh server. Sebaliknya, jika jumlah koneksi terlalu tinggi maka server akan terbebani dan dapat mengurangi kinerja secara keseluruhan.
Konfigurasi Buffer untuk InnoDB (innodb_buffer_pool_size): Menggunakan buffer untuk menyimpan data yang sering digunakan dalam memori untuk mengurangi akses ke disk yang mahal. Jika memiliki potongan data yang besar, maka aspek ini perlu untuk ditingkatkan.
Menetapkan Kapasitas (innodb_io_capacity): Bagian ini menetapkan tingkat input/output dari perangkat penyimpanan. Sesuaikan nilai ini agar lebih cocok dengan perangkat keras atau hardware yang digunakan.
- EXPLAIN function
EXPLAIN dalam MySQL dapat memperlihatkan kepada kita bagaimana MySQL merencanakan dan menjalankan operasi database, serta memberikan informasi rinci tentang langkah-langkah yang diambil oleh MySQL dalam memproses sebuah query. Hasil keluaran dari EXPLAIN berbentuk tabel atau output yang berisi informasi tentang bagaimana MySQL akan menjalankan query, seperti urutan operasi, tipe operasi, tipe indeks yang digunakan, jumlah baris yang dihasilkan, dan waktu yang diestimasi untuk menjalankan query tersebut.
Beberapa informasi yang diperoleh dari hasil output EXPLAIN adalah sebagai berikut:
“Id” : nomor urut langkah-langkah yang dijalankan dalam eksekusi query.
“Select_type” : jenis operasi SELECT yang digunakan oleh MySQL.
“Table” : nama tabel yang digunakan dalam operasi query.
“Type” : tipe akses yang digunakan oleh MySQL untuk mengakses tabel.
“Possible_keys” : indeks-indeks yang mungkin digunakan oleh MySQL untuk mengoptimalkan query.
“Key” : indeks yang benar-benar digunakan oleh MySQL untuk menjalankan query.
“Rows” : jumlah baris yang diestimasi akan diproses oleh MySQL dalam eksekusi query.
“Extra” : menunjukkan informasi tambahan
Menganalisis dan mengoptimalkan query yang kompleks atau lambat dalam MySQL dapat dilakukan saat menggunakan EXPLAIN. Hal ini tentu dapat meningkatkan kinerja dan efisiensi kinerja query dalam MySQL.
- ORDER BY Appropriately
“ORDER BY” merupakan sebuah klausa yang digunakan untuk mengurutkan hasil berdasarkan kolom yang ditentukan. Praktik ini dapat digunakan untuk mengurutkan berdasarkan dua kolom sekaligus. Klausa ini digunakan dalam pernyataan SELECT untuk mengatur urutan.
Berikut adalah sintaksis dasar dari “ORDER BY”
“Kolom1, kolom2,…”
Merupakan daftar kolom yang ingin ditampilkan dalam hasil query.
“nama_tabel”
Merupakan nama tabel yang ingin diambil datanya.
“kolom_urutan”
merupakan kolom yang digunakan sebagai acuan untuk mengurutkan hasil query.
“ASC” atau “DESC”
merupakan opsi untuk menentukan apakah hasil query akan diurutkan secara menaik (ASC) atau menurun (DESC) berdasarkan nilai pada kolom_urutan.
- Optimasi Queries
Query merupakan permintaan untuk menampilkan data dari database untuk diolah lebih lanjut. Dengan mengoptimalkan query dalam MySQL, efisiensi kerja dalam mengeksekusi query dapat meningkat sehingga waktu dan sumber daya sistem dapat digunakan dengan efektif dan efisien. Beberapa upaya untuk mengoptimalkan kinerja query agar lebih efisien adalah sebagai berikut:
1. Menggunakan indeks
2. Menyusun query yang efisien dengan mengoptimalkan penggunaan klausa
3. Memonitor dan menganalisis query dengan menggunakan tools seperti MySQL Query Analyzer atau EXPLAIN
4. Menggunakan caching untuk menyimpan hasil query atau data yang sering digunakan dalam memori
5. Mengoptimalkan konfigurasi MySQL seperti ukuran buffer, pengaturan cache, dan konfigurasi memori
6. Menghindari overloading query untuk mengurangi beban pada server database
7. Memperbarui versi MySQL untuk meningkatkan performa dan keamanan.
Beberapa upaya dan cara diatas dapat membantu untuk melakukan optimasi MySQL sehingga pengolahan atau manajemen data dapat berlangsung dengan cepat. Hal ini akan membantu data administrator untuk mengolah data perusahaan dengan efisien. Jika Anda tertarik untuk mempelajarinya lebih dalam, Anda dapat mengikuti IT Training Center bersama AMD Academy atau menggunakan Jasa Konsultan IT dan Software House terpercaya seperti PT Arkatama Multi Solusindo.
Content Writer: Fatihatur Rizqi Maulidiyah (DTS Batch 3)
