Pendahuluan
MySQL adalah aplikasi database open-source populer yang menyimpan dan menyusun data dengan cara yang berarti dan mudah diakses. Dengan aplikasi besar, jumlah data yang banyak dapat menyebabkan masalah kinerja.
Panduan ini memberikan beberapa tip penyetelan tentang cara meningkatkan kinerja database MySQL .
Prasyarat
- Sistem Linux dengan MySQL terinstal dan berjalan, Centos atau Ubuntu
- Database yang sudah ada
- Kredensial administrator untuk sistem operasi dan database
Penyesuaian Kinerja Sistem MySQL
Di tingkat sistem, Anda akan menyesuaikan opsi perangkat keras dan perangkat lunak untuk meningkatkan kinerja MySQL.
1. Seimbangkan Empat Sumber Daya Perangkat Keras Utama
Penyimpanan
Luangkan waktu sejenak untuk mengevaluasi penyimpanan Anda. Jika Anda menggunakan hard disk drive (HDD) tradisional, Anda dapat meningkatkan ke solid-state drive (SSD) untuk peningkatan kinerja.
Gunakan alat seperti iotop atau sar dari sysstat paket untuk memantau tingkat input/output disk Anda. Jika penggunaan disk jauh lebih tinggi daripada penggunaan sumber daya lain, pertimbangkan untuk menambahkan lebih banyak penyimpanan atau meningkatkan ke penyimpanan yang lebih cepat.
Prosesor
Prosesor biasanya dianggap sebagai ukuran seberapa cepat sistem Anda. Gunakan Linux atas perintah untuk perincian tentang bagaimana sumber daya Anda digunakan. Perhatikan proses MySQL dan persentase penggunaan prosesor yang dibutuhkan.
Prosesor lebih mahal untuk diupgrade, tetapi jika CPU Anda mengalami hambatan, upgrade mungkin diperlukan.
Memori
Memori mewakili jumlah total RAM di server penyimpanan database MySQL Anda. Anda dapat menyesuaikan cache memori (lebih lanjut tentang itu nanti) untuk meningkatkan kinerja . Jika Anda tidak memiliki cukup memori, atau jika memori yang ada tidak dioptimalkan, Anda dapat merusak kinerja Anda alih-alih meningkatkannya.
Seperti kemacetan lainnya, jika server Anda terus-menerus kehabisan memori, Anda dapat meningkatkan dengan menambahkan lebih banyak. Jika Anda kehabisan memori, server Anda akan menyimpan penyimpanan data cache (seperti hard drive) untuk bertindak sebagai memori. Caching basis data memperlambat kinerja Anda.
Jaringan
Penting untuk memantau lalu lintas jaringan untuk memastikan Anda memiliki infrastruktur yang memadai untuk mengelola beban.
Membebani jaringan Anda dapat menyebabkan latensi, paket yang hilang, dan bahkan pemadaman server. Pastikan Anda memiliki bandwidth jaringan yang cukup untuk mengakomodasi tingkat lalu lintas database yang normal.
2. Gunakan InnoDB, Bukan MyISAM
MyISAM adalah gaya database lama yang digunakan untuk beberapa database MySQL. Ini adalah desain database yang kurang efisien. InnoDB yang lebih baru mendukung fitur yang lebih canggih dan memiliki mekanisme pengoptimalan bawaan.
InnoDB menggunakan indeks berkerumun dan menyimpan data di halaman, yang disimpan dalam blok fisik berurutan. Jika nilai terlalu besar untuk sebuah halaman, InnoDB memindahkannya ke lokasi lain, lalu mengindeks nilainya. Fitur ini membantu menyimpan data yang relevan di tempat yang sama pada perangkat penyimpanan, artinya hard drive fisik membutuhkan lebih sedikit waktu untuk mengakses data.
3. Gunakan MySQL Versi Terbaru
Menggunakan versi terbaru tidak selalu layak untuk database lama dan lama. Tetapi bila memungkinkan, Anda harus memeriksa versi MySQL yang digunakan dan meningkatkan ke versi terbaru.
Bagian dari pengembangan yang sedang berlangsung termasuk peningkatan kinerja. Beberapa penyesuaian kinerja umum mungkin dianggap usang oleh versi MySQL yang lebih baru. Secara umum, selalu lebih baik menggunakan peningkatan kinerja MySQL asli daripada skrip dan file konfigurasi.
Penyetelan Kinerja MySQL Perangkat Lunak
Penyetelan kinerja SQL adalah proses memaksimalkan kecepatan kueri pada basis data relasional. Tugas biasanya melibatkan banyak alat dan teknik.
Metode ini melibatkan:
- Mengubah file konfigurasi MySQL.
- Menulis kueri basis data yang lebih efisien.
- Menyusun database untuk mengambil data secara lebih efisien.
4. Pertimbangkan Menggunakan Alat Peningkatan Kinerja Otomatis
Seperti kebanyakan perangkat lunak, tidak semua alat bekerja pada semua versi MySQL. Kami akan memeriksa tiga utilitas untuk mengevaluasi database MySQL Anda dan merekomendasikan perubahan untuk meningkatkan kinerja.
Yang pertama adalah tuning-primer. Alat ini sedikit lebih tua, dirancang untuk MySQL 5.5 – 5.7. Itu dapat menganalisis database Anda dan menyarankan pengaturan untuk meningkatkan kinerja. Misalnya, Anda mungkin disarankan untuk menaikkan query_cache_size parameter jika sistem Anda tidak dapat memproses kueri dengan cukup cepat untuk menjaga cache tetap bersih.
Alat penyetelan kedua, yang berguna untuk sebagian besar database SQL modern, adalah MySQLTuner. Skrip ini (mysqltuner.pl ) ditulis dalam Perl. Seperti tuning-primer, ia menganalisis konfigurasi database Anda untuk mencari kemacetan dan inefisiensi. Keluaran menunjukkan metrik dan rekomendasi:
Di bagian atas output, Anda dapat melihat versi alat MySQLTuner dan database Anda.
Script bekerja dengan MySQL 8.x. Rekomendasi file log adalah yang pertama dalam daftar, tetapi jika Anda menggulir ke bawah, Anda dapat melihat rekomendasi umum untuk meningkatkan kinerja MySQL.
Utilitas ketiga, yang mungkin sudah Anda miliki, adalah phpMyAdmin Advisor . Seperti dua utilitas lainnya, ini mengevaluasi database Anda dan merekomendasikan penyesuaian. Jika Anda sudah menggunakan phpMyAdmin, Advisor adalah alat yang berguna yang dapat Anda gunakan dalam GUI.
5. Optimalkan Kueri
Kueri adalah permintaan berkode untuk mencari database untuk data yang cocok dengan nilai tertentu. Ada beberapa operator kueri yang, menurut sifatnya, membutuhkan waktu lama untuk dijalankan. Teknik penyetelan performa SQL membantu mengoptimalkan kueri untuk waktu proses yang lebih baik.
Mendeteksi kueri dengan waktu eksekusi yang buruk adalah salah satu tugas utama penyetelan kinerja. Kueri yang biasanya diterapkan pada kumpulan data besar lambat dan menempati basis data. Oleh karena itu, tabel tidak tersedia untuk tugas lain.
Misalnya, database OLTP memerlukan transaksi cepat dan pemrosesan kueri yang efektif. Menjalankan kueri yang tidak efisien akan memblokir penggunaan database dan menghentikan pembaruan informasi.
Jika lingkungan Anda bergantung pada kueri otomatis seperti pemicu , mereka mungkin memengaruhi kinerja. Periksa dan hentikan proses MySQL yang mungkin menumpuk tepat waktu.
6. Gunakan Indeks Jika Sesuai
Banyak kueri basis data menggunakan struktur yang mirip dengan ini:
SELECT … WHEREKueri ini melibatkan evaluasi, pemfilteran, dan pengambilan hasil. Anda dapat merestrukturisasi ini dengan menambahkan satu set kecil indeks untuk tabel terkait. Kueri dapat diarahkan ke indeks untuk mempercepat kueri.
7. Fungsi dalam Predikat
Hindari menggunakan fungsi dalam predikat kueri. Misalnya:
SELECT * FROM MYTABLE WHERE UPPER(COL1)='123'Copy
UPPER notasi membuat fungsi, yang harus beroperasi selama SELECT operasi. Ini menggandakan pekerjaan yang dilakukan kueri, dan Anda harus menghindarinya jika memungkinkan.
8. Hindari % Wildcard dalam Predikat
Saat mencari melalui data tekstual, wildcard membantu melakukan pencarian yang lebih luas. Misalnya, untuk memilih semua nama yang dimulai dengan ch , buat indeks pada kolom nama dan jalankan:
SELECT * FROM person WHERE name LIKE "ch%"Kueri memindai indeks, membuat kueri berbiaya rendah:
Namun, melakukan pencarian nama menggunakan karakter pengganti di awal meningkatkan biaya kueri secara signifikan karena pemindaian pengindeksan tidak berlaku untuk ujung string:
Sebuah wildcard di awal pencarian tidak berlaku pengindeksan. Sebagai gantinya, pemindaian tabel penuh mencari melalui setiap baris satu per satu, meningkatkan biaya kueri dalam prosesnya. Dalam kueri contoh, menggunakan karakter pengganti di bagian akhir membantu mengurangi biaya kueri karena melewati lebih sedikit baris tabel.
Cara untuk mencari ujung string adalah dengan membalikkan string, mengindeks string yang dibalik dan melihat karakter awal. Menempatkan wildcard di akhir sekarang mencari awal dari string terbalik, membuat pencarian lebih efisien.
9. Tentukan Kolom di Fungsi SELECT
Ekspresi yang umum digunakan untuk kueri analitik dan eksplorasi adalah SELECT * . Memilih lebih dari yang Anda butuhkan menghasilkan kehilangan kinerja dan redundansi yang tidak perlu. Jika Anda menentukan kolom yang Anda butuhkan, kueri Anda tidak perlu memindai kolom yang tidak relevan.
Jika semua kolom diperlukan, tidak ada cara lain untuk melakukannya. Namun, sebagian besar persyaratan bisnis tidak memerlukan semua kolom yang tersedia dalam kumpulan data. Pertimbangkan untuk memilih kolom tertentu saja.
Untuk meringkas, hindari menggunakan:
SELECT * FROM tableSebagai gantinya, coba:
SELECT column1, column2 FROM table10. Gunakan ORDER BY dengan tepat
ORDER BY ekspresi mengurutkan hasil berdasarkan kolom yang ditentukan. Ini dapat digunakan untuk mengurutkan berdasarkan dua kolom sekaligus. Ini harus diurutkan dalam urutan yang sama, naik atau turun.
Jika Anda mencoba mengurutkan kolom yang berbeda dalam urutan yang berbeda, itu akan memperlambat kinerja. Anda dapat menggabungkan ini dengan indeks untuk mempercepat penyortiran.
11. GROUP BY Alih-alih SELECT DISTINCT
PILIH BERBEDA kueri berguna saat mencoba menyingkirkan nilai duplikat. Namun, pernyataan tersebut membutuhkan sejumlah besar kekuatan pemrosesan.
Jika memungkinkan, hindari menggunakan SELECT DISTINCT , karena sangat tidak efisien dan terkadang membingungkan. Misalnya, jika tabel mencantumkan informasi tentang pelanggan dengan struktur berikut:
| id | nama | namabelakang | alamat | kota | status | zip |
|---|---|---|---|---|---|---|
| 0 | John | Smith | 652 Jalan Bunga | Los Angeles | CA | 90017 |
| 1 | John | Smith | 1215 Ocean Boulevard | Los Angeles | CA | 90802 |
| 2 | Martha | Matthews | 3104 Pico Boulevard | Los Angeles | CA | 90019 |
| 3 | Martha | Jones | 2712 Venesia Boulevard | Los Angeles | CA | 90019 |
Menjalankan kueri berikut mengembalikan empat hasil:
SELECT DISTINCT name, address FROM person
Pernyataan itu sepertinya harus mengembalikan daftar nama yang berbeda bersama dengan alamat mereka. Sebaliknya, kueri melihat keduanya kolom nama dan alamat. Meskipun ada dua pasang pelanggan dengan nama yang sama, alamat mereka berbeda.
Untuk memfilter nama duplikat dan mengembalikan alamat, coba gunakan GROUP BY pernyataan:
SELECT name, address FROM person GROUP BY name
Hasilnya mengembalikan nama pertama yang berbeda bersama dengan alamatnya, membuat pernyataan itu tidak terlalu ambigu. Untuk mengelompokkan menurut alamat unik, GROUP BY parameter hanya akan berubah ke alamat dan mengembalikan hasil yang sama seperti DISTINCT pernyataan lebih cepat.
Untuk meringkas, hindari menggunakan:
SELECT DISTINCT column1, column2 FROM tableSebagai gantinya, coba gunakan:
SELECT column1, column2 FROM table GROUP BY column112. GABUNG, DI MANA, BERSATU, BERBEDA
Cobalah untuk menggunakan gabungan dalam bila memungkinkan. Gabungan luar melihat data tambahan di luar kolom yang ditentukan. Tidak apa-apa jika Anda memerlukan data itu, tetapi membuang-buang kinerja untuk menyertakan data yang tidak diperlukan.
Menggunakan INNER JOIN adalah pendekatan standar untuk menggabungkan tabel. Sebagian besar mesin basis data menerima penggunaan WHERE demikian juga. Misalnya, dua kueri berikut menghasilkan hasil yang sama:
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.idDibandingkan dengan:
SELECT * FROM table1, table2 WHERE table1.id = table2.idSecara teori, mereka juga memiliki runtime yang sama.
Pilihan apakah akan menggunakan JOIN atau WHERE query tergantung pada mesin database. Meskipun sebagian besar mesin memiliki waktu proses yang sama untuk kedua metode tersebut, dalam beberapa sistem basis data yang satu berjalan lebih cepat dari yang lain.
UNION dan DISTINCT perintah terkadang disertakan dalam kueri. Seperti gabungan luar, boleh saja menggunakan ekspresi ini jika diperlukan. Namun, mereka menambahkan penyortiran dan pembacaan database tambahan. Jika Anda tidak membutuhkannya, lebih baik cari ekspresi yang lebih efisien.
13. Gunakan Fungsi EXPLAIN
Basis data MySQL modern menyertakan EXPLAIN fungsi.
Menambahkan EXPLAIN ekspresi ke awal kueri akan membaca dan mengevaluasi kueri. Jika ada ekspresi yang tidak efisien atau struktur yang membingungkan, EXPLAIN dapat membantu Anda menemukan mereka. Anda kemudian dapat menyesuaikan ungkapan kueri Anda untuk menghindari pemindaian tabel yang tidak disengaja atau pencapaian kinerja lainnya.
14. Konfigurasi Server MySQL
Konfigurasi ini melibatkan perubahan pada /etc/mysql/my.cnf . Anda mengajukan. Lanjutkan dengan hati-hati dan buat perubahan kecil pada satu waktu.
query_cache_size – Menentukan ukuran cache dari kueri MySQL yang menunggu untuk dijalankan. Rekomendasinya adalah mulai dengan nilai kecil sekitar 10MB dan kemudian meningkat menjadi tidak lebih dari 100-200MB. Dengan terlalu banyak kueri yang di-cache, Anda dapat mengalami serangkaian kueri “Menunggu kunci cache.” Jika kueri Anda terus dicadangkan, prosedur yang lebih baik adalah menggunakan EXPLAIN untuk mengevaluasi setiap kueri dan menemukan cara untuk membuatnya lebih efisien.
max_connection – Mengacu pada jumlah koneksi yang diizinkan ke dalam database. Jika Anda mendapatkan kesalahan saat mengutip “Terlalu banyak koneksi, ” meningkatkan nilai ini dapat membantu.
innodb_buffer_pool_size – Pengaturan ini mengalokasikan memori sistem sebagai cache data untuk database Anda. Jika Anda memiliki potongan data yang besar, tingkatkan nilai ini. Catat RAM yang diperlukan untuk menjalankan sumber daya sistem lainnya.
innodb_io_capacity – Variabel ini menetapkan tingkat input/output dari perangkat penyimpanan Anda. Ini terkait langsung dengan jenis dan kecepatan drive penyimpanan Anda. HDD 5400-rpm akan memiliki kapasitas yang jauh lebih rendah daripada SSD kelas atas atau Intel Optane. Anda dapat menyesuaikan nilai ini agar lebih cocok dengan perangkat keras Anda.