MySQL dan MariaDB adalah sistem manajemen basis data relasional (RDMS) yang paling banyak digunakan dalam hal hosting situs web dan sistem CMS seperti Joomla, WordPress, Drupal, dan Typo 3. Pada artikel ini, saya akan menjelaskan cara mempercepat dan mengoptimalkan Server database MySQL dan MariaDB.
Simpan data MySQL di partisi terpisah
Ketika sampai pada titik optimasi dan jaminan, selalu ide terbaik untuk menyimpan data database dalam volume terpisah. Volume khusus untuk volume penyimpanan cepat seperti SSD, NVMe. Bahkan jika sistem Anda akan gagal, Anda akan memiliki database yang aman. Karena volume partisi terdiri dari volume penyimpanan yang cepat, kinerjanya akan lebih cepat.
Setel jumlah maksimum koneksi MySQL
MySQL/MariaDB menggunakan instruksi max_connections yang menentukan berapa banyak koneksi bersamaan yang saat ini diizinkan di server. Terlalu banyak koneksi mengakibatkan konsumsi memori yang tinggi serta beban CPU yang tinggi. Untuk situs web kecil, koneksi dapat ditentukan hingga 100-200 dan yang lebih besar mungkin memerlukan 500-800 dan lebih banyak lagi. koneksi_maks dapat diubah secara dinamis menggunakan kueri SQL. Dalam contoh ini, saya telah menetapkan nilainya menjadi 200.
$ mysql -u root -p
mysql> set global max_connections=200;
Keluaran :
Aktifkan Log kueri lambat MySQL
Mencatat kueri yang membutuhkan waktu sangat lama untuk dieksekusi memudahkan pemecahan masalah database. Log kueri lambat dapat diaktifkan dengan menambahkan baris berikut di file konfigurasi MySQL/MariaDB.
slow-query-log=1 slow-query-log-file= /var/lib/mysql/mysql-slow-query.log long-query-time=1
Di mana variabel pertama mengaktifkan log kueri lambat
Variabel kedua mendefinisikan direktori file log
Variabel ketiga menentukan waktu untuk menyelesaikan kueri MySQL
Mulai ulang layanan mysql/mariadb dan pantau log
$ systemctl restart mysql
$ systemctl restart mariadb
$ tail -f /var/lib/mysql/mysql-slow-query.log
Mengatur paket maksimum yang diizinkan oleh MySQL
Data dipecah menjadi paket-paket di MySQL. Max_allowed_packet mendefinisikan ukuran maksimum paket yang dapat dikirim. Menyiapkan max_allowed_packet terlalu rendah dapat menyebabkan kueri menjadi terlalu lambat. Disarankan untuk mengatur nilai paket ke ukuran paket terbesar.
Mengatur kapasitas tabel sementara
Tmp_table_size adalah ruang maksimum yang digunakan untuk tabel memori internal. Jika ukuran tabel melebihi batas yang ditentukan, tabel akan diubah menjadi tabel MyISAM pada disk. Di MySQL/MariaDB, Anda dapat menambahkan variabel berikut dalam file konfigurasi untuk mengatur ukuran tabel sementara. Direkomendasikan untuk menyetel nilai ini pada server 64M per GB memori.
[mysqld] tmp_table_size=64M
Mulai ulang layanan mysql
$ systemctl restart mysql
$ systemctl restart mariadb
Siapkan kapasitas tabel memori maksimum.
Max_heap_table_size adalah variabel yang digunakan di MySQL untuk mengkonfigurasi kapasitas tabel memori maksimum. Ukuran kapasitas tabel memori maksimum harus sama dengan kapasitas tabel sementara untuk menghindari penulisan disk. Disarankan untuk menyetel nilai ini di server ke 64M per GB memori. Tambahkan baris berikut di file konfigurasi MySQL dan mulai ulang layanan.
[mysqld] max_heap_table_size=64M
Untuk menerapkan perubahan, mulai ulang server database.
$ systemctl restart mysql
$ systemctl restart mariadb
Nonaktifkan pencarian balik DNS untuk MySQL
Ketika koneksi baru diterima, MySQL/MariaDB akan melakukan pencarian DNS untuk menyelesaikan alamat IP pengguna. Ini dapat menyebabkan penundaan ketika konfigurasi DNS tidak valid atau ada masalah dengan server DNS. Untuk menonaktifkan pencarian DNS, tambahkan baris berikut di file konfigurasi MySQL dan mulai ulang layanan MySQL.
[mysqld] skip-name-resolve
Mulai ulang layanan:
$ systemctl restart mysql
$ systemctl restart mariadb
Hindari Menggunakan Swappiness di MySQL
Kernel Linux memindahkan sebagian memori ke partisi khusus disk yang disebut ruang "swap" ketika sistem kehabisan memori fisik. Dalam kondisi ini, sistem menulis informasi ke disk daripada mengosongkan beberapa memori. Karena memori sistem lebih cepat daripada penyimpanan disk, disarankan untuk menonaktifkan swappiness. Swappiness dapat dinonaktifkan dengan menggunakan perintah berikut.
$ sysctl -w vm.swappiness=0
Keluaran :
Meningkatkan ukuran kumpulan buffer InnoDB
MySQL/MariaDB memiliki mesin InnoDB yang memiliki kumpulan buffer untuk menyimpan dan mengindeks data dalam memori. Kumpulan buffer membantu kueri MySQL/MariaDB dieksekusi secara relatif lebih cepat. Memilih ukuran yang tepat dari kumpulan buffer InnoDB memerlukan beberapa pengetahuan tentang memori sistem. Ide terbaik adalah menyetel nilai ukuran kumpulan buffer InnoDB ke 80% dari RAM.
Contoh.
- Memori Sistem =4GB
- Ukuran Buffer Pool =3,2 GB
Tambahkan baris berikut di file konfigurasi MySQL dan mulai ulang layanan
[mysqld] Innodb_buffer_pool_size 3.2G
Mulai ulang basis data:
$ systemctl restart mysql
$ systemctl restart mariadb
Menangani ukuran cache kueri
Perintah cache kueri di MySQL/MariaDB digunakan untuk menyimpan semua kueri yang terus berulang dengan data yang sama. Disarankan untuk mengatur nilainya menjadi 64MB dan menambah waktu untuk situs web kecil. Meningkatkan nilai ukuran cache kueri ke GB tidak disarankan karena dapat menurunkan kinerja database. Tambahkan baris berikut di file my.cnf.
[mysqld] query_cache_size=64M
Periksa koneksi idle
Sumber daya dikonsumsi oleh koneksi yang tidak aktif sehingga perlu dihentikan atau disegarkan jika memungkinkan. Koneksi ini tetap dalam status "tidur" dan dapat bertahan untuk jangka waktu yang lama. Periksa koneksi yang tidak aktif menggunakan perintah berikut.
$ mysqladmin processlist -u root -p | grep “Sleep”
Kueri akan mencantumkan proses yang berada dalam kondisi tidur. Umumnya di PHP, event bisa terjadi saat menggunakan mysql_pconnect. Ini membuka koneksi MySQL, mengeksekusi kueri, menghapus otentikasi, dan membiarkan koneksi terbuka. Menggunakan wait_timeout direktif, koneksi idle dapat diinterupsi. Nilai default untuk wait_timeout adalah 28800 detik yang dapat diturunkan ke rentang waktu minimum seperti 60 detik. Tambahkan baris berikut di file my.cnf
[mysqld] wait_timeout=60
Optimalkan dan perbaiki database MySQL
Jika server dimatikan secara tidak terduga, maka ada kemungkinan tabel di MySQL/MariaDB dapat macet. Ada kemungkinan alasan lain untuk mendapatkan tabel database crash seperti mengakses database saat proses penyalinan sedang berjalan, sistem file tiba-tiba macet. Dalam situasi ini, kami memiliki alat khusus yang disebut “mysqlcheck ” yang memeriksa, memperbaiki, dan mengoptimalkan semua tabel dalam database.
Gunakan perintah berikut untuk melakukan aktivitas perbaikan dan pengoptimalan.
Untuk semua basis data:
$ mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
Untuk basis data tertentu:
$ mysqlcheck -u root -p --auto-repair --check --optimize dbname
Ganti dbname dengan nama database Anda
- Periksa kinerja MySQL/MariaDB menggunakan alat pengujian
Ini adalah praktik terbaik untuk memeriksa kinerja database MySQL/MariaDB secara teratur. Ini akan memudahkan untuk mendapatkan laporan kinerja dan titik perbaikan. Ada banyak alat yang tersedia di antaranya mysqltuner adalah yang terbaik.
Jalankan perintah berikut untuk mengunduh alat
$ wget https://github.com/major/MySQLTuner-perl/tarball/master
Buka tar file
$ tar xvzf master
Buka direktori proyek dan jalankan skrip berikut.
$ cd major-MySQLTuner-perl-7aa57fa
$ ./mysqltuner.pl
Keluaran:
Kesimpulan
Pada artikel ini, kita telah mempelajari cara mengoptimalkan MySQL/MariaDB menggunakan teknik yang berbeda. Terima kasih telah membaca.