10 tip kinerja penting untuk MySQL

Seperti dengan semua database relasional, MySQL dapat menjadi binatang yang rumit, yang dapat merayap berhenti pada saat itu juga, meninggalkan aplikasi Anda dalam kesulitan dan bisnis Anda dipertaruhkan.

Sebenarnya, kesalahan umum mendasari sebagian besar masalah kinerja MySQL. Untuk memastikan server MySQL Anda bekerja dengan kecepatan tinggi, memberikan kinerja yang stabil dan konsisten, penting untuk menghilangkan kesalahan ini, yang sering tertutupi oleh beberapa kehalusan dalam beban kerja Anda atau jebakan konfigurasi.

Untungnya, banyak masalah kinerja MySQL yang ternyata memiliki solusi serupa, membuat pemecahan masalah dan menyesuaikan MySQL menjadi tugas yang dapat dikelola.

Berikut adalah 10 tip untuk mendapatkan kinerja yang luar biasa dari MySQL.

Tip kinerja MySQL No. 1: Buat profil beban kerja Anda

Cara terbaik untuk memahami bagaimana server Anda menghabiskan waktunya adalah dengan membuat profil beban kerja server. Dengan membuat profil beban kerja Anda, Anda dapat menampilkan kueri yang paling mahal untuk penyetelan lebih lanjut. Di sini, waktu adalah metrik yang paling penting karena saat Anda mengeluarkan kueri pada server, Anda tidak terlalu peduli tentang apa pun kecuali seberapa cepat kueri itu selesai.

Cara terbaik untuk membuat profil beban kerja Anda adalah dengan alat seperti penganalisis kueri MySQL Enterprise Monitor atau pt-query-digest dari Percona Toolkit. Alat-alat ini menangkap kueri yang dijalankan server dan mengembalikan tabel tugas yang diurutkan berdasarkan urutan waktu respons yang menurun, secara instan menampilkan tugas-tugas yang paling mahal dan memakan waktu ke atas sehingga Anda dapat melihat di mana harus memfokuskan upaya Anda.

Alat profil beban kerja mengelompokkan kueri serupa, memungkinkan Anda melihat kueri yang lambat, serta kueri yang cepat tetapi dieksekusi berkali-kali.

Tip kinerja MySQL No. 2: Pahami empat sumber daya dasar

Untuk berfungsi, server database membutuhkan empat sumber daya dasar: CPU, memori, disk, dan jaringan. Jika salah satunya lemah, tidak menentu, atau kelebihan beban, server database kemungkinan besar akan berkinerja buruk.

Memahami sumber daya fundamental penting dalam dua bidang tertentu: memilih perangkat keras dan memecahkan masalah.

Saat memilih perangkat keras untuk MySQL, pastikan semua komponen berperforma baik. Sama pentingnya, seimbangkan mereka dengan cukup baik satu sama lain. Seringkali, organisasi akan memilih server dengan CPU dan disk cepat tetapi kekurangan memori. Dalam beberapa kasus, menambahkan memori adalah cara yang murah untuk meningkatkan kinerja berdasarkan urutan besarnya, terutama pada beban kerja yang terikat disk. Ini mungkin tampak berlawanan dengan intuisi, tetapi dalam banyak kasus disk terlalu banyak digunakan karena tidak ada cukup memori untuk menampung kumpulan data kerja server.

Contoh bagus lainnya dari keseimbangan ini berkaitan dengan CPU. Dalam kebanyakan kasus, MySQL akan bekerja dengan baik dengan CPU yang cepat karena setiap kueri berjalan dalam satu utas dan tidak dapat diparalelkan di seluruh CPU.

Dalam hal pemecahan masalah, periksa kinerja dan pemanfaatan keempat sumber daya, dengan hati-hati dalam menentukan apakah kinerjanya buruk atau hanya diminta untuk melakukan terlalu banyak pekerjaan. Pengetahuan ini dapat membantu menyelesaikan masalah dengan cepat.

Tip kinerja MySQL No. 3: Jangan gunakan MySQL sebagai antrian

Antrian dan pola akses seperti antrian dapat menyelinap ke dalam aplikasi Anda tanpa Anda sadari. Misalnya, jika Anda menyetel status item sehingga proses pekerja tertentu dapat mengklaimnya sebelum bertindak, maka Anda tanpa disadari membuat antrian. Menandai email sebagai tidak terkirim, mengirimnya, lalu menandainya sebagai terkirim adalah contoh umum.

Antrian menyebabkan masalah karena dua alasan utama: Mereka membuat serial beban kerja Anda, mencegah tugas dilakukan secara paralel, dan mereka sering menghasilkan tabel yang berisi pekerjaan dalam proses serta data historis dari pekerjaan yang telah lama diproses. Keduanya menambahkan latensi ke aplikasi dan memuat ke MySQL.

Tip kinerja MySQL No. 4: Filter hasil dengan yang termurah terlebih dahulu

Cara terbaik untuk mengoptimalkan MySQL adalah dengan melakukan pekerjaan yang murah dan tidak tepat terlebih dahulu, kemudian kerja keras dan akurat pada kumpulan data yang lebih kecil dan dihasilkan.

Misalnya, Anda mencari sesuatu dalam radius tertentu dari suatu titik geografis. Alat pertama di banyak kotak alat pemrogram adalah rumus lingkaran besar (Haversine) untuk menghitung jarak di sepanjang permukaan bola. Masalah dengan teknik ini adalah rumus tersebut memerlukan banyak operasi trigonometri, yang sangat intensif CPU. Penghitungan lingkaran besar cenderung berjalan lambat dan membuat penggunaan CPU mesin meroket.

Sebelum menerapkan rumus lingkaran besar, pare data Anda menjadi bagian kecil dari total, dan pangkas kumpulan yang dihasilkan menjadi lingkaran yang tepat. Kotak yang berisi lingkaran (tepat atau tidak tepat) adalah cara mudah untuk melakukannya. Dengan begitu, dunia di luar kotak tidak akan pernah terpukul dengan semua fungsi trigonometri yang mahal itu.

Tip kinerja MySQL No. 5: Ketahui dua perangkap kematian skalabilitas

Skalabilitas tidak sejelas yang Anda yakini. Faktanya, ada definisi matematis yang tepat tentang skalabilitas yang dinyatakan sebagai persamaan. Persamaan ini menyoroti mengapa sistem tidak berskala sebaik yang seharusnya.

Ambil Hukum Skalabilitas Universal, definisi yang berguna dalam mengekspresikan dan mengukur karakteristik skalabilitas sistem. Ini menjelaskan masalah penskalaan dalam dua biaya fundamental: serialisasi dan crosstalk.

Proses paralel yang harus dihentikan agar sesuatu yang serial berlangsung secara inheren terbatas dalam skalabilitasnya. Demikian pula, jika proses paralel perlu mengobrol satu sama lain sepanjang waktu untuk mengoordinasikan pekerjaan mereka, mereka membatasi satu sama lain.

Hindari serialisasi dan crosstalk, dan aplikasi Anda akan berskala jauh lebih baik. Apa yang diterjemahkan ke dalam MySQL? Ini bervariasi, tetapi beberapa contoh akan menghindari kunci eksklusif pada baris. Antrian, poin No. 3 di atas, cenderung berskala buruk karena alasan ini.

Tip kinerja MySQL No. 6: Jangan terlalu fokus pada konfigurasi

DBA cenderung menghabiskan banyak waktu untuk menyesuaikan konfigurasi. Hasilnya biasanya bukan peningkatan yang besar dan terkadang bahkan bisa sangat merusak. Saya telah melihat banyak server "dioptimalkan" yang mogok terus-menerus, kehabisan memori, dan berkinerja buruk ketika beban kerja menjadi sedikit lebih intens.

Default yang dikirimkan dengan MySQL adalah one-size-fit-none dan sangat usang, tetapi Anda tidak perlu mengkonfigurasi semuanya. Lebih baik mendapatkan dasar yang benar dan mengubah pengaturan lain hanya jika diperlukan. Dalam kebanyakan kasus, Anda bisa mendapatkan 95 persen dari kinerja puncak server dengan mengatur sekitar 10 opsi dengan benar. Beberapa situasi di mana ini tidak berlaku akan menjadi kasus tepi yang unik untuk keadaan Anda.

Dalam kebanyakan kasus, alat "penyetelan" server tidak direkomendasikan karena cenderung memberikan pedoman yang tidak masuk akal untuk kasus tertentu. Beberapa bahkan memiliki saran berbahaya dan tidak akurat yang dikodekan di dalamnya - seperti rasio cache hit dan rumus konsumsi memori. Ini tidak pernah benar, dan mereka menjadi semakin tidak benar seiring berjalannya waktu.

Tip kinerja MySQL No. 7: Hati-hati dengan kueri penomoran halaman

Aplikasi yang memberi nomor halaman cenderung membuat server bertekuk lutut. Dalam menampilkan halaman hasil, dengan link untuk menuju ke halaman berikutnya, aplikasi ini biasanya mengelompokkan dan mengurutkan dengan cara yang tidak dapat menggunakan indeks, dan mereka menggunakan LIMITdan offsetyang menyebabkan server melakukan banyak pekerjaan menghasilkan, lalu membuang baris.

Pengoptimalan sering kali dapat ditemukan di antarmuka pengguna itu sendiri. Alih-alih menunjukkan jumlah persis halaman dalam hasil dan link ke setiap halaman satu per satu, Anda dapat menampilkan link ke halaman berikutnya. Anda juga dapat mencegah orang membuka halaman terlalu jauh dari halaman pertama.

Di sisi kueri, daripada menggunakan LIMITwith offset, Anda dapat memilih satu baris lagi dari yang Anda butuhkan, dan saat pengguna mengklik link "halaman berikutnya", Anda dapat menetapkan baris terakhir tersebut sebagai titik awal untuk rangkaian hasil berikutnya. Misalnya, jika pengguna melihat halaman dengan baris 101 sampai 120, Anda akan memilih baris 121 juga; untuk merender halaman berikutnya, Anda akan meminta server untuk baris yang lebih besar dari atau sama dengan 121, batas 21.

Tip kinerja MySQL No. 8: Simpan statistik dengan bersemangat, waspada dengan enggan

Pemantauan dan peringatan itu penting, tetapi apa yang terjadi dengan sistem pemantauan biasa? Ini mulai mengirim positif palsu, dan administrator sistem menyiapkan aturan pemfilteran email untuk menghentikan kebisingan. Segera sistem pemantauan Anda benar-benar tidak berguna.

Saya suka memikirkan tentang pemantauan dengan dua cara: menangkap metrik dan mengingatkan. Sangat penting untuk menangkap dan menyimpan semua metrik yang Anda bisa karena Anda akan senang memilikinya saat Anda mencoba mencari tahu apa yang berubah dalam sistem. Suatu hari, masalah aneh akan muncul, dan Anda akan menyukai kemampuan untuk menunjuk ke grafik dan menunjukkan perubahan pada beban kerja server.

Sebaliknya, ada kecenderungan untuk terlalu waspada. Orang sering waspada pada hal-hal seperti rasio buffer hit atau jumlah tabel sementara yang dibuat per detik. Masalahnya adalah tidak ada ambang batas yang baik untuk rasio seperti itu. Ambang batas yang tepat tidak hanya berbeda dari server ke server, tetapi dari jam ke jam saat beban kerja Anda berubah.

Akibatnya, waspadai seperlunya dan hanya pada kondisi yang menunjukkan masalah yang pasti dan dapat ditindaklanjuti. Rasio hit buffer yang rendah tidak dapat ditindaklanjuti, juga tidak menunjukkan masalah nyata, tetapi server yang tidak merespons upaya koneksi adalah masalah aktual yang perlu diselesaikan.

Tip kinerja MySQL No. 9: Pelajari tiga aturan pengindeksan

Pengindeksan mungkin adalah topik yang paling disalahpahami dalam database karena ada begitu banyak cara untuk membuat bingung tentang cara kerja indeks dan cara server menggunakannya. Dibutuhkan banyak usaha untuk benar-benar memahami apa yang sedang terjadi.

Indeks, jika dirancang dengan benar, memiliki tiga tujuan penting dalam server database:

  1. Indeks memungkinkan server menemukan grup baris yang berdekatan, bukan baris tunggal. Banyak orang mengira tujuan indeks adalah untuk menemukan baris individu, tetapi menemukan baris tunggal mengarah ke operasi disk acak, yang lambat. Jauh lebih baik menemukan kelompok baris, yang semuanya atau sebagian besar menarik, daripada menemukan baris satu per satu.
  2. Indeks memungkinkan server menghindari pengurutan dengan membaca baris dalam urutan yang diinginkan. Penyortiran itu mahal. Membaca baris dalam urutan yang diinginkan jauh lebih cepat.
  3. Indeks memungkinkan server memenuhi seluruh kueri dari indeks saja, menghindari kebutuhan untuk mengakses tabel sama sekali. Ini juga dikenal sebagai indeks penutup atau kueri khusus indeks.

Jika Anda dapat merancang indeks dan kueri Anda untuk memanfaatkan tiga peluang ini, Anda dapat membuat kueri Anda beberapa kali lipat lebih cepat.

Tip kinerja MySQL No. 10: Manfaatkan keahlian rekan-rekan Anda

Jangan mencoba melakukannya sendiri. Jika Anda sedang memikirkan suatu masalah dan melakukan apa yang menurut Anda logis dan masuk akal, itu bagus. Ini akan berhasil sekitar 19 kali dari 20 kali. Di lain waktu, Anda akan masuk ke lubang kelinci yang akan sangat mahal dan memakan waktu, tepatnya karena solusi yang Anda coba tampaknya masuk akal.

Bangun jaringan sumber daya terkait MySQL - dan ini lebih dari sekadar kumpulan alat dan panduan pemecahan masalah. Ada beberapa orang yang sangat berpengetahuan mengintai di milis, forum, situs Q&A, dan sebagainya. Konferensi, pameran dagang, dan acara kelompok pengguna lokal memberikan peluang berharga untuk mendapatkan wawasan dan membangun hubungan dengan rekan yang dapat membantu Anda dalam keadaan darurat.

Bagi mereka yang mencari alat untuk melengkapi tips ini, Anda dapat melihat Percona Configuration Wizard untuk MySQL, Percona Query Advisor untuk MySQL, dan Percona Monitoring Plugins. (Catatan: Anda harus membuat akun Percona untuk mengakses dua tautan pertama tersebut. Gratis.) Wizard konfigurasi dapat membantu Anda menghasilkan file my.cnf dasar untuk server baru yang lebih unggul dari file sampel yang dikirimkan dengan server. Penasihat kueri akan menganalisis SQL Anda untuk membantu mendeteksi pola yang berpotensi buruk seperti kueri penomoran halaman (No. 7). Plugin Pemantauan Percona adalah seperangkat plugin pemantauan dan grafik untuk membantu Anda menyimpan statistik dengan bersemangat dan waspada dengan enggan (No. 8). Semua alat ini tersedia secara gratis.