Saat ini, pengembang terus mengembangkan dan menerapkan aplikasi menggunakan arsitektur LAMP (Linux®, Apache, MySQL, dan PHP/Perl). Namun, administrator server sering kali memiliki sedikit kendali atas aplikasi itu sendiri karena aplikasi tersebut ditulis oleh orang lain. Seri tiga bagian ini membahas sejumlah masalah konfigurasi server yang dapat memengaruhi kinerja aplikasi. Artikel ini, bagian ketiga dan terakhir dari seri ini, akan fokus pada penyetelan lapisan database untuk efisiensi maksimum.
Mengenai penyetelan MySQL,
ada tiga metode untuk mempercepat kecepatan berjalan server MySQL. Urutan efisiensi dari rendah ke tinggi adalah:
Ganti perangkat keras yang bermasalah. Sesuaikan pengaturan proses MySQL. Optimalkan kueri.
Mengganti perangkat keras yang bermasalah seringkali menjadi pertimbangan pertama kita, terutama karena database dapat menghabiskan banyak sumber daya. Namun solusi ini hanya berlaku sejauh ini. Faktanya, Anda sering kali dapat menggandakan kecepatan unit pemrosesan pusat (CPU) atau disk, dan meningkatkan memori sebanyak 4 hingga 8 kali lipat.
Metode kedua adalah menyetel server MySQL (juga dikenal sebagai mysqld). Menyetel proses ini berarti mengalokasikan memori dengan tepat dan memberi tahu mysqld jenis beban apa yang akan ditanggungnya. Mempercepat pengoperasian disk tidak sepenting mengurangi jumlah akses disk yang diperlukan. Demikian pula, memastikan bahwa proses MySQL beroperasi dengan benar berarti proses tersebut menghabiskan lebih banyak waktu untuk melayani kueri dibandingkan pada tugas-tugas latar belakang seperti bekerja dengan tabel disk sementara atau membuka dan menutup file. Menyetel mysqld adalah fokus artikel ini.
Pendekatan terbaik adalah memastikan bahwa kueri telah dioptimalkan. Ini berarti indeks yang sesuai diterapkan pada tabel dan kueri ditulis sedemikian rupa sehingga memanfaatkan kemampuan MySQL sepenuhnya. Meskipun artikel ini tidak membahas penyetelan kueri (topik yang telah dibahas di banyak buku), artikel ini mengonfigurasi mysqld untuk melaporkan kueri yang mungkin memerlukan penyetelan.
Meskipun perintah telah ditetapkan untuk tugas-tugas ini, Anda masih perlu memperhatikan pengaturan perangkat keras dan mysqld untuk menyempurnakan kueri dengan benar. Tidak apa-apa jika mesin lambat, saya telah melihat mesin yang sangat cepat gagal karena beban berat saat menjalankan kueri yang dirancang dengan baik karena mysqld sibuk dengan banyak pekerjaan yang sibuk dan tidak dapat melayani kueri.
Mencatat kueri yang lambat
Di server SQL, tabel data disimpan di disk. Indeks memberi server cara untuk menemukan baris data tertentu dalam tabel tanpa harus mencari seluruh tabel. Jika seluruh tabel harus dicari, hal ini disebut pemindaian tabel. Secara umum, Anda mungkin hanya ingin mendapatkan sebagian data dalam tabel, sehingga pemindaian tabel secara penuh akan membuang banyak I/O disk, dan oleh karena itu juga memakan banyak waktu. Masalah ini menjadi lebih buruk ketika data harus digabungkan, karena beberapa baris data pada kedua sisi gabungan harus dibandingkan.
Tentu saja, pemindaian tabel tidak selalu menimbulkan masalah; terkadang membaca keseluruhan tabel lebih efisien daripada memilih sebagian data (perencana kueri dalam proses server digunakan untuk membuat keputusan ini). Jika indeks digunakan secara tidak efisien, atau tidak dapat digunakan sama sekali, hal ini akan memperlambat kueri, dan masalah ini akan menjadi lebih signifikan seiring dengan meningkatnya beban di server dan ukuran tabel. Kueri yang membutuhkan waktu lebih lama untuk dieksekusi daripada rentang waktu tertentu disebut kueri lambat.
Anda dapat mengonfigurasi mysqld untuk mencatat kueri lambat ini ke log kueri lambat yang diberi nama dengan tepat. Administrator kemudian akan meninjau log ini untuk membantu mereka menentukan bagian mana dari aplikasi yang memerlukan penyelidikan lebih lanjut. Listing 1 menunjukkan konfigurasi yang perlu dilakukan di my.cnf untuk mengaktifkan pencatatan kueri yang lambat.
Listing 1. Mengaktifkan log kueri lambat MySQL
[mysqld]; aktifkan log kueri lambat, kueri log-lambat 10 detik default; kueri log yang memakan waktu lebih dari 5 detiklong_query_time = 5; kueri-tidak-menggunakan-indeks
Ketiga pengaturan ini digunakan bersama untuk mencatat kueri yang memerlukan waktu lebih dari 5 detik untuk dieksekusi dan tidak menggunakan indeks. Harap perhatikan peringatan tentang log-queries-not-using-indexes: Anda harus menggunakan MySQL 4.1 atau lebih tinggi. Log kueri lambat disimpan di direktori data MySQL dan diberi nama hostname-slow.log. Jika Anda ingin menggunakan nama atau jalur yang berbeda, Anda dapat menggunakan log-slow-queries = /new/path/to/file di my.cnf untuk mencapainya.
Membaca log kueri lambat paling baik dilakukan melalui perintah mysqldumpslow. Dengan menentukan jalur ke file log, Anda dapat melihat daftar kueri lambat yang diurutkan, beserta berapa kali kueri tersebut muncul di file log. Fitur yang sangat berguna adalah mysqldumpslow menghapus data apa pun yang ditentukan pengguna sebelum membandingkan hasilnya, sehingga panggilan berbeda ke kueri yang sama dihitung sebagai satu; ini dapat membantu mengidentifikasi kueri yang memerlukan pekerjaan paling banyak.
Kueri cache
Banyak aplikasi LAMP sangat bergantung pada database tetapi mengeksekusi kueri yang sama berulang kali. Setiap kali query dijalankan, database harus melakukan pekerjaan yang sama - menganalisis query, menentukan cara mengeksekusinya, memuat informasi dari disk, dan mengembalikan hasilnya ke klien. MySQL memiliki fitur yang disebut query cache, yang menyimpan hasil query (yang akan digunakan nanti) di memori. Dalam banyak kasus, hal ini akan sangat meningkatkan kinerja. Namun masalahnya adalah cache kueri dinonaktifkan secara default.
Tambahkan query_cache_size = 32M ke /etc/my.conf untuk mengaktifkan cache kueri 32MB.
Memantau Cache Kueri
Setelah mengaktifkan cache kueri, penting untuk memahami apakah cache tersebut digunakan secara efektif. MySQL memiliki beberapa variabel yang dapat Anda lihat untuk memahami apa yang terjadi di cache. Listing 2 menunjukkan status cache.
Listing 2. Menampilkan statistik cache kueri
mysql> TAMPILKAN STATUS SEPERTI 'qcache%';+-------------------------+------------+ |.Nama_Variabel |.Nilai |+----------+------------+| |.Qcache_free_memori |.14640664 ||.Qcache_hits |.2581646882 ||.Qcache_inserts || 042 |+------------- -------- ---+----------------+8 baris dalam set (0,00 detik)
Penjelasan item-item tersebut ditunjukkan pada Tabel 1.
Tabel 1. Deskripsi nama variabel variabel cache query MySQL
Qcache_free_blocks Jumlah blok memori yang berdekatan dalam cache. Angka yang besar menandakan mungkin terdapat pecahan. FLUSH QUERY CACHE mendefrag cache untuk mendapatkan blok gratis.
Qcache_free_memory Membebaskan memori dalam cache.
Qcache_hits bertambah setiap kali kueri masuk ke cache.
Qcache_inserts bertambah setiap kali kueri dimasukkan. Rasio miss adalah jumlah hit dibagi dengan jumlah penyisipan; kurangi nilai ini dari 1 untuk mendapatkan hit rate. Dalam contoh di atas, sekitar 87% kueri masuk ke cache.
Qcache_lowmem_prunes Berapa kali cache kehabisan memori dan harus dibersihkan untuk memberi ruang bagi lebih banyak pertanyaan. Angka ini paling baik dilihat dalam jangka waktu yang lama; jika jumlahnya bertambah, ini mungkin menunjukkan fragmentasi yang parah atau memori yang rendah. (Free_blocks dan free_memory di atas dapat memberi tahu Anda kasusnya).
Qcache_not_cached Jumlah kueri yang tidak cocok untuk caching, biasanya karena bukan pernyataan SELECT.
Qcache_queries_in_cache Jumlah kueri (dan tanggapan) yang saat ini di-cache.
Qcache_total_blocks Jumlah blok dalam cache.
Seringkali perbedaannya dapat dilihat dengan menampilkan variabel-variabel ini dalam selang waktu beberapa detik, yang dapat membantu menentukan apakah cache digunakan secara efisien. Menjalankan FLUSH STATUS dapat mengatur ulang beberapa penghitung, yang bisa sangat membantu jika server telah berjalan selama beberapa waktu.
Sangat menggoda untuk menggunakan cache kueri yang sangat besar dan berharap untuk menyimpan semuanya dalam cache. Karena mysqld harus melakukan pemeliharaan pada cache, seperti melakukan pemangkasan ketika memori hampir habis, server mungkin macet saat mencoba mengelola cache. Biasanya, jika FLUSH QUERY CACHE memakan waktu lama, berarti cache terlalu besar.
Menerapkan Batasan
Anda dapat menerapkan batasan di mysqld untuk memastikan bahwa beban sistem tidak menyebabkan kehabisan sumber daya. Listing 3 menunjukkan beberapa pengaturan penting terkait sumber daya di my.cnf.
Listing 3. Pengaturan sumber daya MySQL
set-variable=max_connections=500set-variable=wait_timeout=10max_connect_errors = 100
Jumlah maksimum koneksi dikelola di baris pertama. Mirip dengan MaxClients di Apache, idenya adalah untuk memastikan bahwa hanya jumlah koneksi yang diizinkan oleh layanan yang dibuat. Untuk menentukan jumlah maksimum koneksi yang saat ini dibuat di server, jalankan SHOW STATUS LIKE 'max_used_connections'.
Baris 2 memberitahu mysqld untuk menghentikan koneksi apa pun yang tidak digunakan selama lebih dari 10 detik. Pada aplikasi LAMP, waktu yang dibutuhkan untuk terhubung ke database biasanya sama dengan waktu yang dibutuhkan web server untuk memproses permintaan. Terkadang jika bebannya terlalu berat, sambungan akan hang dan memakan ruang tabel sambungan. Jika Anda memiliki beberapa pengguna interaktif atau menggunakan koneksi persisten ke database, tidak disarankan untuk menyetel nilai ini lebih rendah!
Baris terakhir adalah metode yang aman. Jika sebuah host mengalami masalah saat menyambung ke server dan mencoba lagi berkali-kali sebelum menyerah, host tersebut akan terkunci dan tidak dapat dijalankan hingga setelah FLUSH HOSTS. Secara default, 10 kegagalan sudah cukup untuk menyebabkan lockout. Mengubah nilai ini menjadi 100 akan memberikan waktu yang cukup bagi server untuk pulih dari masalah. Jika koneksi tidak dapat dibuat setelah 100 kali percobaan ulang, maka menggunakan nilai yang lebih tinggi tidak akan banyak membantu dan mungkin tidak terhubung sama sekali.
Buffer dan Caching
MySQL mendukung lebih dari 100 pengaturan yang dapat disesuaikan; namun untungnya, menguasai beberapa pengaturan akan memenuhi sebagian besar kebutuhan. Untuk menemukan nilai yang benar untuk pengaturan ini, Anda dapat melihat variabel status melalui perintah SHOW STATUS, yang dapat menentukan apakah mysqld beroperasi seperti yang kita harapkan. Memori yang dialokasikan ke buffer dan cache tidak boleh melebihi memori yang tersedia di sistem, jadi penyetelan biasanya memerlukan beberapa kompromi.
Pengaturan MySQL yang dapat disetel dapat diterapkan ke seluruh proses mysqld atau ke sesi klien individual.
Pengaturan sisi server
Setiap tabel dapat direpresentasikan sebagai file pada disk, yang harus dibuka terlebih dahulu dan kemudian dibaca. Untuk mempercepat proses membaca data dari file, mysqld menyimpan cache file yang terbuka ini hingga jumlah maksimum yang ditentukan oleh table_cache di /etc/mysqld.conf. Listing 4 menunjukkan cara menampilkan aktivitas yang berhubungan dengan pembukaan tabel.
Listing 4. Menampilkan aktivitas yang membuka tabel
mysql> TAMPILKAN STATUS SEPERTI 'open%tabel';+---------------+-------+| Nama_Variabel |+-------- -------+-------+|.Open_tables |. +2 baris dalam set (0,00 detik)
Listing 4 menunjukkan bahwa saat ini terdapat 5.000 tabel yang terbuka dan 195 tabel perlu dibuka karena tidak ada deskriptor file yang tersedia di cache (karena statistik telah dihapus sebelumnya, mungkin hanya ada 5.000 tabel yang terbuka). . Jika Opened_tables meningkat dengan cepat dengan menjalankan kembali perintah SHOW STATUS, ini menunjukkan bahwa tingkat cache hit tidak mencukupi. Jika Open_tables jauh lebih kecil dari pengaturan table_cache, nilainya terlalu besar (tetapi memiliki ruang untuk berkembang bukanlah hal yang buruk). Misalnya, gunakan table_cache = 5000 untuk menyesuaikan cache tabel.
Mirip dengan cache tabel, ada juga cache untuk thread. mysqld memunculkan thread sesuai kebutuhan saat menerima koneksi. Pada server yang sibuk dimana koneksi berubah dengan cepat, thread caching untuk digunakan nanti dapat mempercepat koneksi awal.
Listing 5 menunjukkan cara menentukan apakah cukup thread yang di-cache.
Listing 5. Menampilkan statistik penggunaan thread
mysql> TAMPILKAN STATUS SEPERTI 'utas%';+----+--------+|Nama_Variabel |. ---------------+--------+|.Threads_cached ||.Threads_connected ||.Threads_dibuat ||. ---------------+--------+4 baris dalam set (0,00 detik)
Nilai penting di sini adalah Threads_created, nilai ini bertambah setiap kali mysqld perlu membuat thread baru. Jika jumlah ini meningkat dengan cepat saat menjalankan perintah SHOW STATUS berturut-turut, Anda harus mencoba meningkatkan cache thread. Misalnya, Anda dapat menggunakan thread_cache = 40 di my.cnf untuk mencapai hal ini.
Buffer kunci menampung blok indeks tabel MyISAM. Idealnya, permintaan untuk blok ini harus berasal dari memori, bukan dari disk. Listing 6 menunjukkan cara menentukan berapa banyak blok yang dibaca dari disk dan berapa banyak yang dibaca dari memori.
Listing 6. Menentukan efisiensi kata kunci
mysql> tampilkan status seperti '%key_read%';+-------------------+-----------+|Nama_Variabel |+ ----+----------+|.Permintaan_baca_kunci |.163554268 ||.Bacaan_kunci |. -----------+-----------+2 baris dalam set (0,00 detik)
Key_reads mewakili jumlah permintaan yang masuk ke disk, dan Key_read_requests adalah jumlah totalnya. Rasio kesalahan adalah jumlah permintaan baca yang mengenai disk dibagi dengan jumlah total permintaan baca - dalam hal ini sekitar 0,6 kesalahan dalam memori untuk setiap 1.000 permintaan. Jika jumlah disk hits melebihi 1 per 1.000 permintaan, Anda harus mempertimbangkan untuk meningkatkan buffer kata kunci. Misalnya, key_buffer = 384M akan menyetel buffer menjadi 384MB.
Tabel sementara dapat digunakan dalam kueri tingkat lanjut di mana data harus disimpan ke tabel sementara sebelum diproses lebih lanjut (seperti klausa GROUP BY idealnya, tabel sementara dibuat di memori); Namun jika tabel sementara menjadi terlalu besar, maka perlu ditulis ke disk. Listing 7 memberikan statistik terkait pembuatan tabel sementara.
Listing 7. Menentukan penggunaan tabel sementara
mysql> TAMPILKAN STATUS SEPERTI 'created_tmp%';+-----------+-------+|Nama_Variabel | |.+----------+-------+|.Tabel_tmp_yang dibuat || |.+------+-------+3 baris dalam set (0,00 detik)
Created_tmp_tables akan bertambah setiap kali tabel sementara digunakan; Created_tmp_disk_tables juga akan bertambah untuk tabel berbasis disk. Tidak ada aturan ketat untuk rasio ini, karena bergantung pada kueri yang terlibat. Menonton Created_tmp_disk_tables dari waktu ke waktu akan menunjukkan rasio tabel disk yang dibuat dan Anda dapat menentukan efisiensi pengaturan Anda. Baik tmp_table_size dan max_heap_table_size mengontrol ukuran maksimum tabel sementara, jadi pastikan kedua nilai disetel di my.cnf.
Pengaturan per sesi
Pengaturan berikut khusus untuk setiap sesi. Berhati-hatilah saat mengatur angka-angka ini karena jika dikalikan dengan jumlah koneksi yang mungkin ada, pilihan ini mewakili jumlah memori yang besar! Anda dapat mengubah nomor-nomor ini dalam satu sesi melalui kode, atau mengubah pengaturan ini di my.cnf untuk semua sesi.
Ketika MySQL harus mengurutkan, ia mengalokasikan buffer pengurutan untuk menampung baris data saat dibaca dari disk. Jika data yang akan diurutkan terlalu besar, data harus disimpan ke file sementara di disk dan diurutkan kembali. Jika variabel status sort_merge_passes besar, ini menunjukkan aktivitas disk. Listing 8 menunjukkan beberapa informasi penghitung status terkait penyortiran.
Listing 8. Menampilkan statistik pengurutan
mysql> TAMPILKAN STATUS SEPERTI "sortir%";+-------------------+---------+|Nama_Variabel |. -+---------+|. Sortir_gabungan |. 1 ||. Urutkan_rentang |. 79192 ||. ----+---------+4 baris dalam set (0,00 detik)
Jika sort_merge_passes besar berarti Anda perlu memperhatikan sort_buffer_size. Misalnya, sort_buffer_size = 4M menyetel buffer pengurutan ke 4MB.
MySQL juga mengalokasikan sejumlah memori untuk membaca tabel. Idealnya, indeks memberikan informasi yang cukup untuk dibaca hanya pada baris yang Anda perlukan, namun terkadang kueri (dirancang dengan buruk atau karena sifat datanya) perlu membaca sejumlah besar data dari tabel. Untuk memahami perilaku ini, Anda perlu mengetahui berapa banyak pernyataan SELECT yang dijalankan dan berapa kali baris data berikutnya dalam tabel perlu dibaca (daripada diakses langsung melalui indeks). Perintah untuk mencapai fungsi ini ditunjukkan pada Listing 9.
Listing 9. Menentukan rasio pemindaian tabel
mysql> TAMPILKAN STATUS SEPERTI "com_select";+---------------+--------+| Nilai_Variabel |+--------- ------+--------+|.Com_select |.318243 |+----------+--------+1 baris dalam set (0,00 detik) mysql> TAMPILKAN STATUS SEPERTI "handler_read_rnd_next";+--------+-----------+|Nama_Variabel | |+--------+-----------+|.Handler_read_rnd_next |. ----+----------+1 baris dalam set (0,00 detik)
Handler_read_rnd_next / Com_select menghasilkan rasio pemindaian tabel - dalam hal ini 521:1. Jika nilainya melebihi 4000, sebaiknya centang read_buffer_size, misalnya read_buffer_size = 4M. Jika jumlah ini melebihi 8 juta, inilah saatnya mendiskusikan penyesuaian pertanyaan ini dengan pengembang!
3 Alat Penting
Meskipun perintah SHOW STATUS bisa sangat berguna ketika memahami pengaturan tertentu, Anda juga memerlukan beberapa alat untuk menafsirkan sejumlah besar data yang disediakan oleh mysqld. Ada tiga alat yang menurut saya penting; Anda dapat menemukan tautan ke alat tersebut di bagian Sumber Daya.
Sebagian besar administrator sistem akrab dengan perintah teratas, yang memberikan tampilan CPU dan memori yang terus diperbarui yang digunakan oleh tugas. mytop mengemulasi top; ini memberikan tampilan semua klien yang terhubung dan kueri yang mereka jalankan. mytop juga menyediakan data langsung dan historis tentang buffer kata kunci dan efisiensi cache kueri, serta statistik kueri yang berjalan. Ini adalah alat yang berguna untuk melihat apa yang terjadi di sistem Anda (katakanlah dalam 10 detik), Anda bisa mendapatkan tampilan informasi kesehatan server dan menunjukkan koneksi apa pun yang menyebabkan masalah.
mysqlard adalah daemon yang terhubung ke server MySQL, bertanggung jawab mengumpulkan data setiap 5 menit dan menyimpannya di Database Round Robin di latar belakang. Terdapat halaman Web yang menampilkan data seperti penggunaan cache tabel, efisiensi kata kunci, klien yang terhubung, dan penggunaan tabel sementara. Meskipun mytop memberikan gambaran informasi kesehatan server, mysqlard memberikan informasi kesehatan jangka panjang. Sebagai bonus, mysqlard menggunakan beberapa informasi yang telah dikumpulkannya untuk memberikan beberapa saran tentang cara menyetel server.
Alat lain untuk mengumpulkan informasi SHOW STATUS adalah mysqlreport. Pelaporannya jauh lebih kompleks daripada mysqlard karena setiap aspek server perlu dianalisis. Ini adalah alat yang hebat untuk menyetel server Anda karena alat ini melakukan penghitungan yang sesuai pada variabel status untuk membantu menentukan masalah mana yang perlu diperbaiki.
Kesimpulan
Artikel ini memperkenalkan beberapa pengetahuan dasar tentang penyetelan MySQL dan menyimpulkan seri 3 bagian tentang penyetelan komponen LAMP. Penyetelan sebagian besar melibatkan pemahaman cara kerja komponen, menentukan apakah komponen berfungsi dengan baik, membuat beberapa penyesuaian, dan mengevaluasi ulang. Setiap komponen - Linux, Apache, PHP atau MySQL - memiliki persyaratan yang berbeda-beda. Memahami setiap komponen satu per satu dapat membantu mengurangi kemacetan yang dapat memperlambat aplikasi Anda.