Bagaimana cara menyetel database mysql untuk kinerja terbaik?

Pastikan Anda Menggunakan Versi MySQL Terbaru  

Jika Anda adalah basis data lawas atau lama, ini mungkin tidak dapat dilakukan. Namun jika ya, periksa MySQL versi terbaru dan tingkatkan ke (database NoSQL terbaru< . ).

Versi yang lebih baru hadir dengan peningkatan kinerja secara default, sehingga Anda tidak perlu mencari solusi lebih lanjut untuk penyetelan kinerja umum dalam masalah MySQL. Biasanya yang terbaik adalah menggunakan peningkatan kinerja MySQL default atau asli daripada scripting atau file konfigurasi

Pilih InnoDB Daripada MyISAM

MyISAM memiliki lebih sedikit fitur lanjutan daripada InnoDB. Secara keseluruhan, MyISAM kurang efisien, dengan peningkatan pengoptimalan asli yang lebih sedikit. Misalnya, InnoDB memiliki indeks berkerumun, dengan data di halaman dan blok fisik berurutan. Jika suatu nilai terlalu banyak untuk dimasukkan ke dalam satu halaman, InnoDB memigrasikannya ke lokasi lain, mengindeks nilainya — data terkait tetap berada di lokasi yang sama di hard drive, mempersingkat waktu untuk mengambilnya

Sumber Daya Perangkat Keras. Penyesuaian Kinerja Tingkat Sistem di MySQL

Prosesor

Kecepatan prosesor menunjukkan seberapa cepat mesin Anda. Perintah top akan memberi Anda wawasan tentang penggunaan CPU dan memori per proses — dengan kata lain, bagaimana sumber daya Anda digunakan. Saat menggunakan MySQL, awasi proses tertentu itu sebagai persentase penggunaan. Apa pun yang terlalu tinggi dan hambatannya kemungkinan besar adalah mesin Anda, yang berarti perlu ditingkatkan.

Penyimpanan

Menyesuaikan atau meningkatkan memori Anda akan meningkatkan total RAM di server MySQL Anda dan meningkatkan kinerja. Buka bagian konfigurasi server, di mana Anda akan melihat perintah buffer pool size untuk mengoptimalkan memori

Perangkat keras

Jika Anda menggunakan HDD untuk penyimpanan, meningkatkan ke solid-state drive dapat meningkatkan kinerja Anda. Awasi berapa banyak penggunaan disk yang digunakan MySQL dibandingkan dengan sumber daya lainnya. Tambahkan lebih banyak penyimpanan jika secara substansial tidak proporsional. Alat seperti sar atau iotop (paket sysstat) bagus untuk memantau kecepatan input vs output disk

Jaringan

Hambatan dalam bandwidth jaringan menyebabkan paket turun, latensi, atau bahkan downtime total. Anda harus dapat menyediakan lalu lintas database normal.  

Penyesuaian Kinerja Tingkat Perangkat Lunak di MySQL

Menyesuaikan skrip memungkinkan kueri basis data yang lebih efisien, file konfigurasi MySQL, dan desain basis data yang optimal

TIPS PRO. menyesuaikan konfigurasi perangkat lunak Anda dapat menyebabkan lebih banyak masalah daripada menyelesaikannya. Saya merekomendasikan hanya membuat perubahan kecil pada saat itu, mengujinya setiap saat, karena hal ini akan mempermudah mengidentifikasi masalah dan menilai dampaknya terhadap performa secara keseluruhan.

Pembuatan Profil Kode

Menggunakan alat pemrofilan kode, seperti Stackify Prefix, memungkinkan Anda membuat profil dan menguji kode saat Anda menulisnya. Dengan memvalidasi kinerja kode seperti yang tertulis, pengguna Prefix mendorong kode yang lebih baik untuk pengujian, menerima lebih sedikit tiket dukungan dari produksi, dan memiliki manajer pengembang yang lebih bahagia. Pembuatan profil dan pelacakan awalan membantu pengembang yang paling berpengalaman sekalipun menemukan kueri SQL yang lambat, pengecualian tersembunyi, dan banyak lagi.

Temukan kueri SQL berperforma buruk, kueri yang dihasilkan ORM, dan kemacetan yang sebelumnya tidak diketahui. Lacak setiap parameter panggilan SQL, rekaman yang terpengaruh, dan waktu pengunduhan. Awalan juga memudahkan untuk menemukan pola N+1 yang ditakuti.   Prefix mencakup seluruh tech stack Anda, dengan dukungan untuk. NET, Java, PHP, Node. js, Python, dan Ruby pada sistem operasi Windows dan Mac. Unduh Awalan hari ini GRATIS.

Kueri

Saat Anda meminta database, Anda memintanya untuk mengambil data spesifik yang cocok dengan nilai yang Anda masukkan. Berhati-hatilah dengan kueri otomatis yang mungkin menguras formulir di latar belakang. Awasi mereka menggunakan fungsi show processlist dan bunuh yang tidak diperlukan. Beberapa petani membutuhkan waktu lebih lama dari yang lain. Jika Anda mengizinkan proses MySQL yang tidak perlu untuk dijalankan, ini akan mengkompilasi dan mencegah pengguna lain mengakses dan mengubah data

Peningkatan Kinerja Otomatis

Rekomendasi kami untuk tiga alat peningkatan kinerja otomatis teratas adalah MySQLTuner, tuning-primer, dan PHPMyAdmin Adviser. Alat pertama, MySQLTuner, paling berguna dengan database MySQL modern. Ia menemukan bagian yang dapat dioptimalkan, merekomendasikan penyesuaian, dan kompatibel dengan MySQL 8. x. Dua lainnya melakukan fungsi serupa meskipun tuning-primer lebih tua, dibuat untuk MySQL 5. 5 sampai 5. 7.  

Penyesuaian Server

Sekali lagi, pastikan Anda hanya membuat perubahan bertahap di sini. Kita akan menyesuaikan /etc/mysql/my. cnf berkas.

innodb_buffer_pool_size —

Konfigurasi perintah memisahkan memori sistem sebagai cache data untuk database Anda. Semakin besar potongan data Anda, semakin besar nilainya. Saat melakukan perubahan, catat berapa banyak RAM yang digunakan untuk sumber daya sistem lainnya

innodb_io_capacity —

Ini menunjukkan laju input/output dari perangkat penyimpanan Anda. Batas keras di sini adalah apa pun jenis drive penyimpanan yang Anda gunakan. Tweak dapat dilakukan untuk lebih menyelaraskan database Anda dengan perangkat keras Anda

query_cache_size —

Ini mengatur ukuran cache dari kueri MySQL yang tertunda. Sebaiknya mulai dari yang kecil, sekitar 10MB, lalu tingkatkan menjadi 100-200MB, tetapi tidak lebih. Jika Anda memiliki terlalu banyak kueri dan menerima pesan “Menunggu kunci cache”, cobalah fungsi JELASKAN sebagai gantinya untuk mengevaluasi setiap kueri dan cara untuk mengoptimalkan setiap.

max_connection —

Ini menunjukkan jumlah koneksi yang diaktifkan di database. Untuk mengatasi kesalahan "Terlalu banyak sambungan", tingkatkan jumlah ini

MENJELASKAN

Basis data MySQL modern menyertakan fungsi penjelasan. Jika Anda menggunakan ekspresi ini di awal kueri, kueri akan dibaca dan dievaluasi. Jika terjadi ketidakefisienan dalam ekspresi atau anomali dalam struktur, JELASKAN dapat membantu Anda menentukannya. Anda kemudian men-tweak frase kueri untuk menghilangkan pengurasan kinerja dan pemindaian tabel yang tidak perlu.

BERGABUNG, UNI, BERBEDA

Meskipun “gabungan dalam” adalah jenis yang akan digunakan, Anda juga harus menggunakan gabungan, gabungan, dan berbeda jika berlaku. Gabungan luar mencari data asing di luar kolom terkait. Dalam beberapa kasus, data mungkin diperlukan, tetapi ini menguras kinerja yang tidak perlu untuk mencari data yang tidak relevan

DISTINCT dan UNION terkadang terlibat dalam kueri. Sekali lagi, gunakan ini jika diperlukan, tetapi perhatikan bahwa mereka memerlukan pembacaan dan penyortiran ekstra dari database. Jika tidak diperlukan, lebih baik Anda mencari perintah yang lebih efisien.

Indeks Dapat Berguna

SELECT … WHERE umumnya digunakan dalam database. Mereka menangani pemfilteran, mengambil, dan mengevaluasi hasil. Membuat kumpulan indeks kecil untuk tabel yang terhubung adalah struktur berguna yang memungkinkan kueri diarahkan ke indeks untuk mempercepatnya saat dijalankan.

Hindari Menggunakan Fungsi & Wildcard dalam Predikat Kueri

Hindari penggunaan fungsi dalam predikat kueri. Misalnya, jika Anda membuat notasi UPPER (membuat fungsi), ini akan memaksa Anda untuk beroperasi dalam  SELECT operation. Working in this order doubles the workload of the query. 

Hal yang sama berlaku untuk menggunakan karakter pengganti — karakter pengganti, %, mewakili nol karakter atau lebih. Misalnya, ekspresi ' %345 ' menyertakan semua nilai yang diakhiri dengan 345 . While the % stands for any character appearing before that string, including the absence of any proceeding character. This forces the search to do a full table scan for any of these possibilities.

Saat menggunakan operasi PILIH * , perhatikan bahwa saat memindai kolom tertentu, Anda mungkin perlu meningkatkan waktu kueri penelusuran karena .

ORDER BY menyusun hasil berdasarkan kolom yang dipilih. Ini juga berfungsi untuk menyortir dua kolom secara bersamaan. Namun, kedua kolom harus diurutkan secara homogen, dalam urutan menaik atau menurun. Jika Anda mengurutkan secara tidak homogen akan memperlambat kinerja. Tambahkan indeks ke ini untuk mempercepat penyortiran.

Membungkus

Anda harus memiliki ide yang bagus tentang cara menyetel database MySQL Anda untuk meningkatkan kinerja

Secara keseluruhan, pertimbangkan otomatisasi, menghilangkan proses kueri yang tidak perlu, hambatan dalam perangkat lunak dan sumber daya perangkat keras, dan

Bagaimana saya bisa meningkatkan kinerja database MySQL saya?

Apa strategi terbaik untuk menyempurnakan basis data?

Bagaimana Anda mengoptimalkan kinerja basis data?

Bagaimana cara menyetel kinerja SQL?