Cara menggunakan tuning mysql 8

Mengoptimalkan MySQL dapat membantu server agar load time-nya tidak terlalu tinggi. Optimasi MySQL Database ini sama saja seperti tuning Database Tuning, cara ini berlaku juga untuk MariaDB.

Database

Database adalah tempat penyimpanan datamu secara online dalam suatu sistem. Jika menggunakan CentOS, maka PHPMyAdmin adalah software atau perangkat lunak yang menangani operasi MySQL ataupun MariaDB.

MySQLTuner

MySQLTuner adalah script yang ditulis dalam bahasa pemrograman Perl untuk memberikan rekomendasi pengaturan dan konfigurasi MySQL untuk meningkatkan performa database. Tools ini sangat membantu untuk kamu yang tidak paham dengan parameter apa saja yang perlu diubah atau ditambahkan untuk meningkatkan performa database.

Optimasi Database MySQL Menggunakan MySQLTuner

Step 1 – Download MySQLTuner

$ wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

Unduh script MySQLTuner terlebih dulu.

Step 2 – Ubah Permission

$ chmod +x mysqltuner.pl

Ubah permission tools tersebut agar dapat dieksekusi atau dijalankan.

Step 3 – Jalankan MySQLTuner

$ ./mysqltuner.pl

Output-nya akan seperti di bawah ini.

Ada banyak rekomendasi seperti dari segi Performance Metrics, Log File, Storage Engine, Security, Analysis Performance, dan CVE Security. Rekomendasi pengaturan optimasi database-nya dapat kamu lihat di paling bawah.

Salin pada bagian query_cache_size (=0) sampai innodb_buffer_pool_instance (-1) lalu tempel (paste) ke Notepad atau Text Editor.

Step 4 – Backup my.cnf

$ cp /etc/my.cnf ~/my.cnf-backup

Disarankan untuk melakukan backup sebelum melakukan perubahan pada file my.cnf.

Step 5 – Ubah my.cnf

$ nano /etc/my.cnf

Jika sudah melakukan backup my.cnf, pastekan script yang tadi di copy ke notepad atau text editor ke dalam my.cnf.

Step 6 – Reboot MySQL

$ service mysql restart

Restart service MySQL untuk menerapkan perubahan yang dilakukan pada file my.cnf.

Kemudian ulangi kembali dengan menjalankan script MySQLTuner untuk memastikan apakah masih ada rekomendasi yang perlu disesuaikan atau tidak.

Simpulan

MySQLTuner bukan tools yang melakukan optimasi tweak secara langsung, namun dengan memberikan rekomendasi atau saran saja. Rekomendasi MySQLTuner akan berbeda di setiap server, karena server akan membaca resource VPS seperti RAM dan CPU usage terlebih dulu.

Cara optimasi database MySQL menggunakan MySQLTuner ini bisa berguna bagi para pengguna VPS atau Server yang baru saja belajar. Demikian artikel ini, jangan sungkan untuk meninggalkan ide-ide topik yang ingin kamu baca di blog Dewaweb. Semoga artikel ini membantu!

Pada artikel sebelumnya, sudah dibahas tentang teori database tuning (baca : MySQL Database Tuning). Kali ini, akan dijelaskan mengenai implementasi atau praktek dari database tuning. Seperti apakah database tuning ? Berikut ini cara dan penjelasannya

Tuning Pada Query

Sebelum dilakukan pada query pada database, lebih baik jika kita mengetahui deskripsi kolom dari tabel yang akan digunakan dalam query. Misalnya jika kita akan melakukan query pada tabel film. Maka untuk mendapatkan deskripsi dari tabel film, kita bisa menggunakan perintah EXPLAIN.

Dengan perintah EXPLAIN, bisa ditampilkan deskripsi tiap kolom dari tabel, tipe data, nilai default bahkan hasil dari query SELECT. Perintah EXPLAIN merupakan sinonim dari perintah DESCRIBE.

Beberapa teknik optimasi query

• query sql menjadi lebih cepat jika Anda menggunakan nama kolom dalam pernyataan SELECT yang sebenarnya bukan dari '*'. Contoh: Tuliskan query sebagai SELECT id, first_name, last_name, age, subject FROM student_details; Daripada: SELECT * FROM student_details;
• Gunakan EXISTS bukan DISTINCT ketika menggunakan bergabung yang melibatkan tabel memiliki hubungan satu-ke-banyak. Contoh: Tuliskan query sebagai SELECT d.dept_id, d.dept FROM dept d WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept); Daripada: SELECT DISTINCT d.dept_id, d.dept FROM dept d,employee e WHERE e.dept = e.dept;
• Untuk menulis query yang memberikan kinerja yang efisien mengikuti aturan umum SQL standar.
• Jika kita memiliki tabel yang berukuran cukup besar, lakukan optimisasi dengan perintah OPTIMIZE TABLE

Jika kita memiliki tabel yang korup, lakukan perintah REPAIR TABLE. Namun perintah ini hanya berkerja pada tabel MyISAM dan Archive.

Jika kita ingin melakukan checking pada tabel, lakukan perintah CHECK TABLE

Kadang dalam suatu aplikasi kita melakukan query select data pada tabel yang sama dan dilakukan berulang-ulang, hal ini akan sangan membebani MYSQL. Dalam MYSQL terdapat fitur yang bagus untuk memepercepat query dengan mengaktifkan query cache.

Apabila fitur ini diaktifkan setiap query akan di cache di memory dan ini akan meningkatkan query performance. Untuk mengecek fitur query cache, gunakan perintah

Untuk mengaktifkan query cache, gunakan perintah

(ada beberapa nilai yang mungkin : 0 (disable / off), 1 (enable / on) and 2 (on demand).

untuk menngatur besar query cache, gunakan perintah

Tuning pada Parameter MySQL Server

Pembahasan kali ini sangat berhubungan dengan sistem kerja MySQL Server. Oleh karena itu, jika kita melakukan perubahan pada sistem ini, perubahan ini dapat dirasakan saat kita mengoperasikan apapun yang berkaitan dengan MySQL Server. Namun, yang perlu diingat, pekerjaan ini hanya bisa dilakukan oleh database administrator.

Tuning parameter mempercepat kinerja MySQL Server database. Database MySQL tidak akan lambat saat diakses. Pada MySQL Server, banyak parameter yang menentukan kinerja server untuk mengolah basis data. Parameter ini bisa kita lihat dengan memasukkan perintah SHOW VARIABLES.

Untuk melakukan tuning pada MySQL Server, kita bisa mengedit beberapa parameter pada file “my.cnf”(platform Linux) atau “my.ini”(platform Windows).

Pada pembahasan kali ini menggunakan contoh file my.cnf pada platform Linux (Ubuntu 10.04). Dalam file tersebut, terdapat banyak parameter yang bisa digunakan. Namun hanya beberapa parameter yang bisa meningkatkan kinerja MySQL Server.

Berikut ini beberapa parameter yang bisa meningkatkan kinerja MySQL Server

• Query cache size : digunakan untuk menjalankan query berulang-ulang
• Key buffer size : untuk mengalokasikan indeks
• Table cache : melakukan cache query dan tabel
• Sort buffer : melakukan percepatan sorting
• read rnd buffer size : dialokasikan tiap thread, untuk mempercepat kinerja saat query pada barisan yang urut.
• Thread cache :
• Log Slow Query

untuk memulainya, buka file my.cnf / my.ini. Sebelumnya, pastikan menggunakan akses dengan root atau administrator.

Kemudian cari parameter yang telah dibahas sebelumnya. Misalnya :

query_cache_size=16M ubah menjadi query_cache_size=32M
ket_buffer = 32M ubah menjadi ket_buffer = 32M
table_cache=64 ubah menjadi table_cache=128
sort_buffer=16M ubah menjadi sort_buffer=64M
read_buffer=16M ubah menjadi read_buffer=64M
thread_cache=8 ubah menjadi thread_cache=32

jangan lupa menyimpan perubahan tersebut. Kemudian restart MySQL Server.

Benchmarking

Setelah kita melakukan tuning pada MySQL, alangkah baiknya jika kita melakukan uji kinerja MySQL. Disini kita akan menggunakan aplikasi mysqltuner pada platform Linux.

Disini akan tampil statistik, dan ukuran kinerja dari MySQL Server yang terinstal. Program ini juga menampilakan rekomendasi untuk perbaikan kinerja.