10 petua prestasi penting untuk MySQL

Seperti semua pangkalan data perhubungan, MySQL dapat terbukti menjadi binatang yang rumit, yang dapat merangkak berhenti seketika, membiarkan aplikasi anda terus berjalan dan perniagaan anda terus berjalan.

Yang benar adalah, kesalahan biasa mendasari kebanyakan masalah prestasi MySQL. Untuk memastikan pelayan MySQL anda bersuara dengan kelajuan tertinggi, memberikan prestasi yang stabil dan konsisten, penting untuk menghilangkan kesilapan ini, yang sering dikaburkan oleh beberapa kehalusan dalam beban kerja anda atau perangkap konfigurasi.

Nasib baik, banyak masalah prestasi MySQL ternyata mempunyai penyelesaian yang serupa, menjadikan penyelesaian masalah dan menala MySQL sebagai tugas yang dapat dikendalikan.

Berikut adalah 10 petua untuk mendapatkan prestasi hebat dari MySQL.

Petua prestasi MySQL No. 1: Profil beban kerja anda

Kaedah terbaik untuk memahami bagaimana pelayan anda menghabiskan waktunya adalah dengan membuat profil beban kerja pelayan. Dengan memprofilkan beban kerja anda, anda dapat mendedahkan pertanyaan yang paling mahal untuk penalaan selanjutnya. Di sini, masa adalah metrik yang paling penting kerana semasa anda mengeluarkan pertanyaan terhadap pelayan, anda tidak begitu peduli dengan apa pun kecuali seberapa cepat penyelesaiannya.

Cara terbaik untuk mempromosikan beban kerja anda adalah dengan alat seperti penganalisis pertanyaan MySQL Enterprise Monitor atau pt-query-digest dari Percona Toolkit. Alat-alat ini menangkap pertanyaan yang dijalankan oleh pelayan dan mengembalikan jadual tugas yang disusun dengan mengurangkan susunan masa tindak balas, serta-merta menggerakkan tugas yang paling mahal dan memakan masa ke puncak sehingga anda dapat melihat di mana memfokuskan usaha anda.

Alat profil beban kerja mengumpulkan pertanyaan yang serupa, membolehkan anda melihat pertanyaan yang lambat, serta pertanyaan yang cepat tetapi dilaksanakan berkali-kali.

Petua prestasi MySQL No. 2: Memahami empat sumber asas

Untuk berfungsi, pelayan pangkalan data memerlukan empat sumber asas: CPU, memori, cakera, dan rangkaian. Sekiranya salah satu daripadanya lemah, tidak menentu, atau berlebihan, maka pelayan pangkalan data cenderung berprestasi buruk.

Memahami sumber asas penting dalam dua bidang tertentu: memilih perkakasan dan menyelesaikan masalah.

Semasa memilih perkakasan untuk MySQL, pastikan komponen berprestasi baik di sekitar. Sama pentingnya, seimbangkan antara mereka dengan baik. Selalunya, organisasi akan memilih pelayan dengan CPU dan cakera yang pantas tetapi yang kekurangan memori. Dalam beberapa kes, menambahkan memori adalah cara murah untuk meningkatkan prestasi mengikut pesanan besarnya, terutama pada beban kerja yang terikat pada cakera. Ini mungkin kelihatan berlawanan dengan intuisi, tetapi dalam banyak kes disk terlalu banyak digunakan kerana memori tidak cukup untuk menyimpan set data pelayan.

Contoh baik keseimbangan ini berkaitan dengan CPU. Dalam kebanyakan kes, MySQL akan berfungsi dengan baik dengan CPU pantas kerana setiap pertanyaan berjalan dalam satu utas dan tidak dapat diselaraskan di seluruh CPU.

Ketika menyelesai masalah, periksa kinerja dan penggunaan keempat sumber tersebut, dengan hati-hati untuk menentukan apakah mereka berkinerja buruk atau hanya diminta untuk melakukan terlalu banyak pekerjaan. Pengetahuan ini dapat membantu menyelesaikan masalah dengan cepat.

Petua prestasi MySQL No. 3: Jangan gunakan MySQL sebagai barisan

Antrian dan corak akses seperti antrian dapat menyelinap ke aplikasi anda tanpa anda sedari. Contohnya, jika anda menetapkan status item supaya proses pekerja tertentu dapat menuntut sebelum bertindak, anda tidak sengaja membuat barisan. Menandakan e-mel sebagai tidak dihantar, menghantarnya, kemudian menandakannya sebagai dihantar adalah contoh biasa.

Antrian menimbulkan masalah kerana dua sebab utama: Mereka menyusun beban kerja anda, mencegah tugas dilakukan secara selari, dan mereka sering menghasilkan jadual yang berisi kerja dalam proses serta data sejarah dari pekerjaan yang telah diproses lama. Kedua-duanya menambah latensi ke aplikasi dan memuatkan ke MySQL.

Petua prestasi MySQL No. 4: Tapis hasil dengan yang termurah terlebih dahulu

Kaedah terbaik untuk mengoptimumkan MySQL adalah dengan melakukan kerja yang murah dan tidak tepat terlebih dahulu, kemudian kerja keras dan tepat pada sekumpulan data yang lebih kecil dan dihasilkan.

Sebagai contoh, anggap anda sedang mencari sesuatu dalam radius titik geografi tertentu. Alat pertama dalam kotak alat banyak pengaturcara adalah formula lingkaran besar (Haversine) untuk mengira jarak sepanjang permukaan sfera. Masalah dengan teknik ini ialah formula memerlukan banyak operasi trigonometri, yang sangat memerlukan CPU. Pengiraan bulatan besar cenderung berjalan perlahan dan membuat penggunaan CPU mesin melambung tinggi.

Sebelum menerapkan formula bulatan-hebat, tuliskan catatan anda ke subset kecil dari jumlah keseluruhan, dan potong set yang dihasilkan ke lingkaran tepat. Petak yang mengandungi bulatan (tepat atau tidak tepat) adalah cara mudah untuk melakukan ini. Dengan cara itu, dunia di luar dataran tidak akan terpukul dengan semua fungsi trig yang mahal itu.

Petua prestasi MySQL No. 5: Ketahui dua perangkap kematian skalabiliti

Skalabiliti tidak samar-samar seperti yang anda percayai. Sebenarnya, terdapat definisi matematik tepat mengenai skalabilitas yang dinyatakan sebagai persamaan. Persamaan ini menyoroti mengapa sistem tidak mengikut skala sebagaimana mestinya.

Ikuti Undang-undang Skalabilitas Universal, definisi yang berguna dalam menyatakan dan mengukur ciri-ciri skalabiliti sistem. Ia menjelaskan masalah penskalaan dari segi dua kos asas: serialisasi dan crosstalk.

Proses selari yang mesti dihentikan agar sesuatu yang bersiri berlaku sememangnya terhad dalam skalabilitasnya. Begitu juga, jika proses selari perlu berbual antara satu sama lain sepanjang masa untuk menyelaraskan kerja mereka, mereka saling membataskan.

Elakkan siri dan crosstalk, dan aplikasi anda akan bertambah baik Apa yang diterjemahkan ini ke dalam MySQL? Ini berbeza-beza, tetapi beberapa contohnya adalah mengelakkan penguncian baris eksklusif. Antrian, titik No. 3 di atas, cenderung tidak sesuai dengan skala ini.

Petua prestasi MySQL No. 6: Jangan terlalu fokus pada konfigurasi

DBA cenderung menghabiskan banyak masa untuk mengubah konfigurasi. Hasilnya biasanya bukan peningkatan yang besar dan kadangkala boleh sangat merosakkan. Saya telah melihat banyak pelayan yang "dioptimumkan" yang selalu hancur, kehabisan ingatan, dan berkinerja buruk ketika beban kerja menjadi lebih kuat.

Lalai yang dihantar dengan MySQL adalah satu ukuran-sesuai-tidak ada dan ketinggalan zaman, tetapi anda tidak perlu mengkonfigurasi semuanya. Lebih baik mendapatkan asas yang betul dan menukar tetapan lain hanya jika diperlukan. Dalam kebanyakan kes, anda dapat memperoleh 95 peratus prestasi puncak pelayan dengan menetapkan kira-kira 10 pilihan dengan betul. Beberapa situasi di mana ini tidak berlaku akan menjadi kes yang unik untuk keadaan anda.

Dalam kebanyakan kes, alat "penyetelan" pelayan tidak disarankan kerana cenderung memberikan panduan yang tidak masuk akal untuk kes tertentu. Sebilangan malah mempunyai nasihat berbahaya dan tidak tepat yang dikodkan ke dalamnya - seperti nisbah hit cache dan formula penggunaan memori. Ini tidak pernah betul, dan menjadi semakin tidak betul kerana masa telah berlalu.

Petua prestasi MySQL No. 7: Perhatikan pertanyaan penomboran

Aplikasi yang melakukan penomboran cenderung menjadikan pelayan berlutut. Dalam menunjukkan halaman hasil kepada anda, dengan pautan untuk pergi ke halaman seterusnya, aplikasi ini biasanya mengelompokkan dan menyusun cara yang tidak dapat menggunakan indeks, dan mereka menggunakan LIMITdan offsetyang menyebabkan pelayan melakukan banyak kerja menghasilkan, kemudian membuang baris.

Pengoptimuman sering dijumpai di antara muka pengguna itu sendiri. Daripada menunjukkan bilangan halaman yang tepat dalam hasil dan pautan ke setiap halaman secara berasingan, anda hanya boleh menunjukkan pautan ke halaman seterusnya. Anda juga boleh mengelakkan orang pergi ke halaman yang terlalu jauh dari halaman pertama.

Di sisi pertanyaan, alih-alih menggunakan LIMITdengan offset, Anda dapat memilih satu baris lagi daripada yang anda perlukan, dan apabila pengguna mengklik pautan "halaman berikutnya", Anda dapat menetapkan baris terakhir itu sebagai titik awal untuk set hasil berikutnya. Sebagai contoh, jika pengguna melihat halaman dengan baris 101 hingga 120, anda juga akan memilih baris 121; untuk membuat halaman seterusnya, anda akan meminta pelayan untuk baris yang lebih besar daripada atau sama dengan 121, had 21.

Petua prestasi MySQL No. 8: Simpan statistik dengan bersemangat, waspada dengan berat hati

Pemantauan dan amaran sangat penting, tetapi apa yang terjadi pada sistem pemantauan khas? Ini mula mengirim positif palsu, dan pentadbir sistem menetapkan peraturan penapisan e-mel untuk menghentikan kebisingan. Tidak lama lagi sistem pemantauan anda tidak berguna lagi.

Saya suka memikirkan pemantauan dalam dua cara: menangkap metrik dan memberi amaran. Sangat penting untuk menangkap dan menyimpan semua metrik yang mungkin anda dapat kerana anda akan senang menggunakannya ketika anda cuba mengetahui apa yang berubah dalam sistem. Suatu hari, masalah aneh akan muncul, dan anda akan menyukai kemampuan untuk menunjuk grafik dan menunjukkan perubahan dalam beban kerja pelayan.

Sebaliknya, terdapat kecenderungan untuk memberi amaran berlebihan Orang sering berjaga-jaga mengenai perkara seperti nisbah hit buffer atau jumlah jadual sementara yang dibuat sesaat. Masalahnya adalah bahawa tidak ada ambang yang baik untuk nisbah tersebut. Ambang batas yang betul tidak hanya berbeza dari pelayan ke pelayan, tetapi dari jam ke jam apabila beban kerja anda berubah.

Akibatnya, berjaga-jaga dengan berhati-hati dan hanya pada keadaan yang menunjukkan masalah yang pasti dapat ditindaklanjuti. Nisbah klik penyangga yang rendah tidak dapat ditindaklanjuti, juga tidak menunjukkan masalah yang sebenarnya, tetapi pelayan yang tidak bertindak balas terhadap percubaan sambungan adalah masalah sebenarnya yang perlu diselesaikan.

Petua prestasi MySQL No. 9: Ketahui tiga peraturan pengindeksan

Pengindeksan mungkin merupakan topik yang paling disalahpahami dalam pangkalan data kerana terdapat banyak cara untuk menjadi bingung tentang bagaimana indeks berfungsi dan bagaimana pelayan menggunakannya. Perlu banyak usaha untuk benar-benar memahami apa yang berlaku.

Indeks, apabila dirancang dengan betul, melayani tiga tujuan penting dalam pelayan pangkalan data:

  1. Indeks membolehkan pelayan mencari kumpulan baris bersebelahan dan bukannya baris tunggal. Ramai orang berpendapat tujuan indeks adalah mencari baris individu, tetapi mencari baris tunggal membawa kepada operasi cakera rawak, yang lambat. Jauh lebih baik untuk mencari kumpulan baris, semuanya atau yang paling menarik, daripada mencari baris satu demi satu.
  2. Indeks membiarkan pelayan mengelakkan penyusunan dengan membaca baris mengikut urutan yang diinginkan. Menyusun adalah mahal. Membaca baris mengikut urutan yang diinginkan jauh lebih pantas.
  3. Indeks membiarkan pelayan memenuhi keseluruhan pertanyaan dari indeks sahaja, mengelakkan keperluan untuk mengakses jadual sama sekali. Ini dikenali sebagai indeks penutup atau pertanyaan khusus indeks.

Sekiranya anda dapat merancang indeks dan pertanyaan anda untuk memanfaatkan tiga peluang ini, anda dapat membuat pertanyaan anda dengan beberapa pesanan yang lebih besar.

Petua prestasi MySQL No. 10: Manfaatkan kepakaran rakan sebaya anda

Jangan cuba pergi sendiri. Sekiranya anda memikirkan masalah dan melakukan perkara yang kelihatan logik dan masuk akal bagi anda, itu bagus. Ini akan berfungsi kira-kira 19 kali daripada 20. Pada masa yang lain, anda akan memasuki lubang arnab yang akan sangat mahal dan memakan masa, tepatnya kerana penyelesaian yang anda cuba nampaknya masuk akal.

Bina rangkaian sumber yang berkaitan dengan MySQL - dan ini melampaui panduan alat dan penyelesaian masalah. Terdapat beberapa orang yang sangat berpengetahuan mengintai senarai surat, forum, laman web Soal Jawab dan sebagainya. Persidangan, pameran dagang, dan acara kumpulan pengguna tempatan memberikan peluang berharga untuk memperoleh pandangan dan membina hubungan dengan rakan sebaya yang dapat membantu anda dalam beberapa saat.

Bagi mereka yang mencari alat untuk melengkapkan petua ini, anda boleh melihat Percona Configuration Wizard untuk MySQL, Percona Query Advisor untuk MySQL, dan Percona Monitoring Plugins. (Catatan: Anda perlu membuat akaun Percona untuk mengakses dua pautan pertama itu. Percuma.) Wizard konfigurasi dapat membantu anda menghasilkan fail my.cnf asas untuk pelayan baru yang lebih unggul daripada fail sampel yang dihantar dengan pelayan. Penasihat pertanyaan akan menganalisis SQL anda untuk membantu mengesan corak berpotensi buruk seperti pertanyaan penomboran (No. 7). Plugin Pemantauan Percona adalah satu set pemalam pemantauan dan grafik untuk membantu anda menyimpan statistik dengan bersemangat dan berjaga-jaga dengan enggan (No. 8). Semua alat ini boleh didapati secara percuma.