15 Tip Penyetelan dan Pengoptimalan Kinerja MySQL/MariaDB yang Berguna

MySQL adalah Sistem Manajemen Basis Data Relasional sumber terbuka yang kuat atau singkatnya RDBMS. Itu dirilis kembali pada tahun 1995 (20 tahun). Ia menggunakan Bahasa Kueri Terstruktur yang mungkin merupakan pilihan paling populer untuk mengelola konten dalam database. Versi MySQL terbaru adalah 5.6.25 dan dirilis pada 29 Mei 2015.

Fakta menarik tentang MySQL adalah fakta bahwa nama tersebut berasal dari putri Michael Widenius (pencipta MySQL) My. Meskipun ada banyak fakta menarik tentang MySQL, artikel ini dimaksudkan untuk menunjukkan beberapa praktik berguna untuk membantu Anda mengelola server MySQL Anda.

Pada bulan April 2009 proyek MySQL dibeli oleh Oracle. Hasilnya, fork komunitas MySQL bernama MariaDB telah dibuat. Alasan utama pembuatan fork ini adalah untuk menjaga agar proyek tetap bebas di bawah Lisensi Publik Umum.

Saat ini MySQL dan MariaDB adalah salah satu RDBMS yang paling sering (jika bukan yang paling) sering digunakan untuk aplikasi web seperti WordPress, Joomla, Magento dan lainnya.

Artikel ini akan menunjukkan kepada Anda beberapa tips dasar namun bermanfaat tentang cara mengoptimalkan kinerja MySQL/MariaDB. Harap diingat bahwa artikel ini mengasumsikan Anda sudah menginstal MySQL atau MariaDB. Jika Anda masih bertanya-tanya bagaimana cara menginstalnya di sistem Anda, Anda dapat mengikuti panduan ekstensif kami di sini:

  1. Memasang LAMP di RHEL/CentOS 7
  2. Memasang LAMP di Fedora 22
  3. Menyiapkan LAMP di Ubuntu 15.04
  4. Menginstal MariaDB di Debian 8
  5. Instal MariaDB di Gentoo Linux
  6. Instal MariaDB di Arch Linux

Penting: Sebelum kita mulai – jangan menerima saran ini begitu saja. Setiap pengaturan MySQL bersifat unik dan memerlukan pemikiran tambahan, sebelum melakukan perubahan apa pun.

Hal-hal yang perlu Anda ketahui:

  1. File konfigurasi MySQL/MariaDB terletak di /etc/my.cnf. Setiap kali Anda memodifikasi file ini, Anda perlu me-restart layanan MySQL agar perubahan baru dapat diterapkan.
  2. Untuk penulisan artikel ini MySQL versi 5.6 telah digunakan sebagai template.

1. Aktifkan file per tabel InnoDB

Pertama, penting untuk menjelaskan bahwa InnoDB adalah mesin penyimpanan. MySQL dan MariaDB menggunakan InnoDB sebagai mesin penyimpanan default. Di masa lalu MySQL digunakan untuk menyimpan tabel dan indeks database dalam tablespace sistem. Pendekatan ini dimaksudkan untuk server yang tujuan utamanya adalah pemrosesan basis data dan disk penyimpanannya tidak digunakan untuk tujuan lain apa pun.

InnoDB memberikan pendekatan yang lebih fleksibel dan setiap informasi database disimpan dalam file data .ibd. Setiap file .ibd mewakili tablespacenya sendiri. Dengan begitu, operasi database seperti “TRUNCATE ” dapat diselesaikan lebih cepat dan Anda juga dapat mendapatkan kembali ruang yang tidak terpakai saat menghapus atau memotong tabel database.

Keuntungan lain dari konfigurasi ini adalah kenyataan bahwa Anda dapat menyimpan beberapa tabel database di perangkat penyimpanan terpisah. Hal ini dapat sangat meningkatkan beban I/O pada disk Anda.

innodb_file_per_table diaktifkan secara default di MySQL 5.6 dan yang lebih baru. Anda dapat melihatnya di file /etc/my.cnf. Arahannya terlihat seperti ini:

innodb_file_per_table=1

2. Simpan Data Database MySQL pada Partisi Terpisah

Catatan: Penyiapan ini hanya berfungsi dengan MySQL, namun tidak dengan MariaDB.

Terkadang proses baca/tulis OS dapat memperlambat kinerja server MySQL Anda, terutama jika terletak pada hard drive yang sama. Sebagai gantinya, saya akan merekomendasikan menggunakan hard drive terpisah (sebaiknya SSD) untuk layanan MySQL.

Untuk menyelesaikannya, Anda perlu memasang drive baru ke komputer/server Anda. Untuk tujuan artikel ini, saya berasumsi bahwa drive tersebut akan berada di bawah /dev/sdb.

Langkah selanjutnya adalah menyiapkan drive baru:

fdisk /dev/sdb

Sekarang tekan “n ” untuk membuat partisi baru. Selanjutnya tekan “p ” untuk menjadikan partisi baru sebagai partisi utama. Setelah itu, atur nomor partisi dari 1-4. Setelah itu Anda akan memilih ukuran partisi. Tekan enter di sini. Pada langkah selanjutnya Anda perlu mengkonfigurasi ukuran partisi.

Jika Anda ingin menggunakan seluruh disk, tekan enter sekali lagi. Jika tidak, Anda dapat mengatur ukuran partisi baru secara manual. Jika sudah siap tekan “w ” untuk menulis perubahan. Sekarang kita perlu membuat sistem file untuk partisi baru kita. Ini dapat dengan mudah dilakukan dengan:

mkfs.ext4 /dev/sdb1

Sekarang kita akan memasang partisi baru kita di sebuah folder. Saya menamai folder saya “ssd ” dan dibuat di direktori root:

mkdir /ssd/

Kami siap untuk me-mount partisi baru yang baru saja kami buat di folder baru:

mount /dev/sdb1  /ssd/

Anda dapat melakukan mount saat startup dengan menambahkan baris berikut di file /etc/fstab.

/dev/sdb1 /ssd ext3 defaults 0 0

Sekarang Anda siap untuk memindahkan MySQL ke disk baru. Pertama-tama hentikan layanan MySQL dengan:

service mysqld stop

Saya sarankan Anda menghentikan Apache/nginx juga untuk mencegah segala upaya menulis di database:

service httpd stop
service nginx stop

Sekarang salin seluruh direktori MySQL di drive baru:

cp /var/lib/mysql /ssd/ -Rp

Ini mungkin memakan waktu cukup lama tergantung pada situs database MySQL Anda. Setelah proses ini selesai, ganti nama direktori MySQL:

mv /var/lib/mysql /var/lib/mysql-backup

Selanjutnya kita akan membuat symlink.

ln -s /ssd/mysql /var/lib/mysql

Sekarang Anda siap untuk memulai MySQL dan layanan web Anda:

service mysqld start
service httpd start
service nginx start

Pada titik ini database MySQL Anda akan diakses dari drive baru.