7 kunci untuk kinerja MySQL yang lebih baik

Peter Zaitsev adalah salah satu pendiri dan CEO  Percona .

Salah satu cara kami mengukur aplikasi adalah melalui kinerja. Salah satu metrik kinerja aplikasi adalah pengalaman pengguna, yang secara umum diterjemahkan menjadi "apakah pengguna perlu menunggu lebih lama dari jumlah waktu yang wajar untuk mendapatkan apa yang mereka inginkan."

Metrik ini dapat memiliki arti yang berbeda dalam skenario yang berbeda. Untuk aplikasi belanja seluler, waktu respons tidak boleh lebih dari beberapa detik. Untuk halaman HR karyawan, tanggapan mungkin membutuhkan waktu beberapa detik lebih lama.

Kami memiliki banyak penelitian tentang bagaimana kinerja memengaruhi perilaku pengguna:

  • 79 persen pelanggan cenderung tidak kembali ke situs web yang lambat
  • 47 persen konsumen mengharapkan halaman web dimuat dalam 2 detik atau kurang
  • 40 persen pengguna meninggalkan situs web jika pemuatannya lebih dari tiga detik
  • Penundaan satu detik dalam waktu buka halaman dapat menyebabkan hilangnya 7 persen dalam konversi dan 11 persen lebih sedikit tampilan halaman

Apapun standarnya, penting untuk menjaga kinerja yang baik untuk aplikasi. Jika tidak, pengguna akan mengeluh (atau lebih buruk, pergi ke aplikasi lain). Salah satu faktor yang mempengaruhi kinerja aplikasi adalah kinerja database. Interaksi antara aplikasi, situs web, dan basis data sangat penting dalam menetapkan tingkat kinerja aplikasi.

Komponen utama dari interaksi ini adalah bagaimana aplikasi meminta database dan bagaimana database menanggapi permintaan. Dengan ukuran apa pun, MySQL adalah salah satu sistem manajemen basis data paling populer. Semakin banyak perusahaan yang beralih ke MySQL (dan database open source lainnya) sebagai solusi database di lingkungan produksi mereka.

Ada banyak metode untuk mengonfigurasi MySQL yang dapat membantu memastikan bahwa database Anda merespons kueri dengan cepat, dan dengan jumlah penurunan kinerja aplikasi yang minimal.

Berikut adalah beberapa tip penting untuk membantu Anda mengoptimalkan kinerja database MySQL Anda.

Kunci pengoptimalan MySQL # 1: Pelajari cara menggunakan EXPLAIN

Dua keputusan terpenting yang Anda buat dengan database apa pun adalah mendesain bagaimana hubungan antara entitas aplikasi dipetakan ke tabel (skema database) dan mendesain bagaimana aplikasi mendapatkan data yang mereka butuhkan dalam format yang mereka butuhkan (kueri).

Aplikasi yang rumit dapat memiliki skema dan kueri yang rumit. Jika Anda ingin mendapatkan kinerja dan skala yang dibutuhkan aplikasi Anda, Anda tidak bisa hanya mengandalkan intuisi untuk memahami bagaimana kueri akan dieksekusi.

Alih-alih menebak dan berharap, Anda harus belajar bagaimana menggunakan EXPLAINperintah tersebut. Perintah ini menunjukkan kepada Anda bagaimana kueri akan dieksekusi, dan memberi Anda wawasan tentang kinerja apa yang dapat Anda harapkan dan bagaimana kueri akan diskalakan dengan mengubah ukuran data.

Ada sejumlah alat — seperti MySQL Workbench — yang dapat memvisualisasikan EXPLAINkeluarannya untuk Anda, tetapi Anda masih perlu memahami dasar-dasarnya untuk memahaminya.

Ada dua format berbeda di mana EXPLAINperintah memberikan keluaran: format tabel kuno, dan dokumen JSON yang lebih modern dan terstruktur yang memberikan detail yang jauh lebih banyak (ditampilkan di bawah):

mysql> jelaskan format = json pilih avg (k) dari sbtest1 dimana id antara 1000 dan 2000 \ G

*************************** 1. baris ******************** *******

JELASKAN: {

  “Query_block”: {

    “Select_id”: 1,

    “Info_biaya”: {

      “Query_cost”: “762,40”

    "meja": {

      “Table_name”: “sbtest1”,

      “Access_type”: “range”,

      “Kunci_mungkin”: [

        "UTAMA"

      ],

      “Key”: “PRIMARY”,

      “Used_key_parts”: [

        "Indo"

      ],

      “Key_length”: “4”,

      “Row_examined_per_scan”: 1874,

      “Row_produced_per_join”: 1874,

      "Difilter": "100.00",

      “Info_biaya”: {

        “Biaya_baca”: “387,60”,

        “Eval_cost”: “374,80”,

        “Prefix_cost”: “762,40”,

        “Data_read_per_join”: “351K”

      },

      “Used_columns”: [

        "Indo",

        "K"

      ],

      “Attachment_condition”: “(` sbtest`.`sbtest1`.`id` antara 1000 dan 2000) ”

    }

  }

}

Satu komponen yang harus Anda lihat adalah "biaya kueri". Biaya kueri mengacu pada seberapa mahal MySQL mempertimbangkan kueri khusus ini dalam hal keseluruhan biaya eksekusi kueri, dan didasarkan pada banyak faktor yang berbeda.

Kueri sederhana biasanya memiliki biaya kueri kurang dari 1.000. Kueri dengan biaya antara 1.000 dan 100.000 dianggap kueri berbiaya menengah, dan umumnya cepat jika Anda hanya menjalankan ratusan kueri semacam itu per detik (bukan puluhan ribu).  

Kueri dengan biaya lebih dari 100.000 adalah kueri mahal. Seringkali kueri ini masih akan berjalan cepat ketika Anda adalah pengguna tunggal di sistem, tetapi Anda harus memikirkan dengan hati-hati tentang seberapa sering Anda menggunakan kueri tersebut dalam aplikasi interaktif Anda (terutama saat jumlah pengguna bertambah).

Tentu saja ini adalah angka kinerja kasarnya, tetapi mereka menunjukkan prinsip umum. Sistem Anda mungkin menangani beban kerja kueri dengan lebih baik atau lebih buruk, bergantung pada arsitektur dan konfigurasinya.

Kepala di antara faktor-faktor yang menentukan biaya kueri adalah apakah kueri menggunakan indeks dengan benar. The EXPLAINperintah dapat memberitahu Anda jika query tidak menggunakan indeks (biasanya karena bagaimana indeks dibuat dalam database, atau bagaimana query itu sendiri direkayasa). Inilah mengapa sangat penting untuk belajar menggunakan EXPLAIN.

Kunci pengoptimalan MySQL # 2: Buat indeks yang benar

Indeks meningkatkan kinerja kueri dengan mengurangi jumlah data dalam database yang harus dipindai oleh kueri. Indeks di MySQL digunakan untuk mempercepat akses dalam database dan membantu menegakkan batasan database (seperti UNIQUEdan FOREIGN KEY).

Indeks database sangat mirip dengan indeks buku. Mereka disimpan di lokasinya sendiri, dan berisi informasi yang sudah ada di database utama. Mereka adalah metode referensi atau peta ke tempat data berada. Indeks tidak mengubah data apa pun dalam database. Mereka hanya menunjuk ke lokasi data.

Tidak ada indeks yang selalu tepat untuk beban kerja apa pun. Anda harus selalu melihat indeks dalam konteks kueri yang dijalankan sistem.

Basis data yang diindeks dengan baik tidak hanya berjalan lebih cepat, tetapi bahkan satu indeks yang hilang dapat memperlambat basis data untuk merayapi. Gunakan EXPLAIN(seperti yang disarankan sebelumnya) untuk menemukan indeks yang hilang dan menambahkannya. Tapi hati-hati: Jangan tambahkan indeks yang tidak Anda butuhkan! Indeks yang tidak perlu memperlambat database (lihat presentasi saya tentang praktik terbaik pengindeksan MySQL).

Kunci pengoptimalan MySQL # 3: Tidak ada default!

Seperti perangkat lunak lainnya, MySQL memiliki banyak pengaturan yang dapat dikonfigurasi yang dapat digunakan untuk mengubah perilaku (dan pada akhirnya, kinerja). Dan seperti perangkat lunak lainnya, banyak dari pengaturan yang dapat dikonfigurasi ini diabaikan oleh administrator dan akhirnya digunakan dalam mode default mereka.

Untuk mendapatkan performa terbaik dari MySQL, penting untuk memahami pengaturan MySQL yang dapat dikonfigurasi dan — yang lebih penting — mengaturnya agar berfungsi paling baik untuk lingkungan database Anda.

Secara default, MySQL disetel untuk instalasi pengembangan skala kecil, bukan untuk skala produksi. Anda biasanya ingin mengonfigurasi MySQL untuk menggunakan semua sumber daya memori yang tersedia, serta untuk mengizinkan jumlah koneksi yang dibutuhkan aplikasi Anda. 

Berikut adalah tiga pengaturan penyesuaian kinerja MySQL yang harus selalu Anda periksa dengan cermat:

innodb_buffer_pool_size: Kolam buffer adalah tempat data dan indeks disimpan dalam cache. Ini adalah alasan utama untuk menggunakan sistem dengan jumlah RAM yang besar sebagai server database Anda. Jika Anda hanya menjalankan mesin penyimpanan InnoDB, Anda biasanya mengalokasikan sekitar 80 persen dari memori Anda untuk kumpulan buffer. Jika Anda menjalankan kueri yang sangat rumit, atau Anda memiliki koneksi database serentak dalam jumlah yang sangat besar, atau Anda memiliki jumlah tabel yang sangat besar, Anda mungkin perlu menurunkan nilai ini untuk mengalokasikan lebih banyak memori untuk tujuan lain.

Saat Anda menyetel ukuran kumpulan buffer InnoDB, Anda perlu memastikan Anda tidak menyetelnya terlalu besar atau ini akan menyebabkan pertukaran. Ini benar-benar mematikan kinerja database Anda. Cara mudah untuk memeriksanya adalah dengan melihat Aktivitas Swapping di grafik System Overview di Percona Monitoring and Management: 

Percona

Seperti yang ditunjukkan grafik ini, beberapa pertukaran sering kali baik-baik saja. Namun, jika Anda melihat aktivitas pertukaran berkelanjutan sebesar 1MB per detik atau lebih, Anda perlu mengurangi ukuran kumpulan buffer (atau penggunaan memori lainnya).

Jika Anda tidak mendapatkan nilainya innodb_buffer_pool_sizedengan benar saat pertama kali pergi, jangan khawatir. Dimulai dengan MySQL 5.7 Anda dapat mengubah ukuran kumpulan buffer InnoDB secara dinamis, tanpa memulai ulang server database.  

innodb_log_file_size: Ini adalah ukuran satu file log InnoDB. Secara default, InnoDB menggunakan dua nilai sehingga Anda dapat menggandakan angka ini untuk mendapatkan ukuran ruang log pengulangan melingkar yang digunakan InnoDB untuk memastikan transaksi Anda tahan lama. Ini juga mengoptimalkan penerapan perubahan ke database. Pengaturan innodb_log_file_sizeadalah pertanyaan trade-off. Semakin besar ruang pengulangan yang Anda alokasikan, semakin baik kinerja yang akan Anda capai untuk beban kerja intensif tulis, namun semakin lama waktu pemulihan kerusakan jika sistem Anda mengalami kehilangan daya atau masalah lain. 

Bagaimana Anda tahu jika kinerja MySQL Anda dibatasi oleh ukuran file log InnoDB Anda saat ini? Anda dapat mengetahui dengan melihat berapa banyak ruang redo log yang dapat digunakan sebenarnya digunakan. Cara termudah adalah dengan melihat dasbor Percona Monitoring and Management InnoDB Metrics. Pada grafik di bawah ini, ukuran file log InnoDB tidak cukup besar, karena ruang yang digunakan mendorong sangat dekat dengan seberapa banyak ruang redo log yang tersedia (ditunjukkan dengan garis merah). Ukuran file log Anda setidaknya harus 20 persen lebih besar dari jumlah ruang yang digunakan untuk menjaga kinerja sistem Anda secara optimal. 

Percona

max_connections: Aplikasi skala besar sering kali membutuhkan lebih dari jumlah koneksi default. Tidak seperti variabel lain, jika Anda tidak menyetelnya dengan benar, Anda tidak akan mengalami masalah kinerja (per se). Sebaliknya, jika jumlah koneksi tidak mencukupi untuk kebutuhan aplikasi Anda, aplikasi Anda tidak akan dapat terhubung ke database (yang terlihat seperti waktu henti bagi pengguna Anda). Penting untuk memastikan variabel ini benar.

Sulit untuk mengetahui berapa banyak koneksi yang Anda perlukan untuk aplikasi kompleks dengan banyak komponen yang berjalan di beberapa server. Untungnya, MySQL membuatnya sangat mudah untuk melihat berapa banyak koneksi yang digunakan pada operasi puncak. Biasanya Anda ingin memastikan setidaknya ada selisih 30 persen antara jumlah maksimum koneksi yang digunakan aplikasi Anda dan jumlah maksimum koneksi yang tersedia. Cara mudah untuk melihat angka-angka ini adalah dengan menggunakan MySQL Connections Graph di dashboard MySQL Overview di Percona Monitoring and Management. Grafik di bawah ini menunjukkan sistem yang sehat, di mana ada banyak koneksi tambahan yang tersedia. 

Percona

Satu hal yang perlu diingat adalah jika database Anda berjalan lambat, aplikasi sering kali membuat koneksi dalam jumlah yang berlebihan. Dalam kasus seperti itu, Anda harus bekerja pada masalah kinerja database daripada sekadar mengizinkan lebih banyak koneksi. Lebih banyak koneksi dapat memperburuk masalah kinerja yang mendasarinya.

(Catatan: Ketika Anda menyetel max_connectionsvariabel secara signifikan lebih tinggi dari nilai default, Anda sering kali perlu mempertimbangkan untuk meningkatkan parameter lain seperti ukuran cache tabel dan jumlah file terbuka yang diizinkan MySQL. Namun, hal ini melampaui cakupan artikel ini. .) 

Kunci optimasi MySQL # 4: Simpan database di memori

Kami telah melihat transisi ke solid state drive (SSD) dalam beberapa tahun terakhir. Meskipun SSD jauh lebih cepat daripada hard drive yang berputar, mereka tetap tidak sebanding dengan ketersediaan data dalam RAM. Perbedaan ini tidak hanya berasal dari kinerja penyimpanan itu sendiri, tetapi juga dari pekerjaan tambahan yang harus dilakukan database saat mengambil data dari penyimpanan disk atau SSD. 

Dengan peningkatan perangkat keras baru-baru ini, semakin mungkin untuk mendapatkan database Anda di memori — baik Anda menjalankan di cloud atau mengelola perangkat keras Anda sendiri. 

Kabar yang lebih baik lagi adalah Anda tidak perlu memasukkan semua database Anda ke dalam memori untuk mendapatkan sebagian besar manfaat kinerja dalam memori. Anda hanya perlu memasukkan kumpulan data kerja ke dalam memori — data yang paling sering diakses.

Anda mungkin pernah melihat beberapa artikel yang memberikan beberapa angka spesifik tentang bagian mana dari database yang harus Anda simpan dalam memori, mulai dari 10 persen hingga 33 persen. Faktanya, tidak ada angka “satu ukuran cocok untuk semua”. Jumlah data yang akan dimasukkan ke dalam memori untuk keuntungan kinerja terbaik berkaitan dengan beban kerja. Daripada mencari nomor "ajaib" tertentu, Anda harus memeriksa seberapa banyak I / O database berjalan pada kondisi mapannya (biasanya beberapa jam setelah dimulai). Lihat bacaan, karena bacaan bisa sepenuhnya dihilangkan jika database Anda ada di memori. Menulis akan selalu perlu terjadi, berapa pun jumlah memori yang Anda miliki. 

Di bawah ini Anda dapat melihat I / O terjadi di Grafik InnoDB I / O di dasbor Metrik InnoDB dari Pemantauan dan Manajemen Percona.

Percona

Dalam grafik di atas, Anda melihat lonjakan setinggi 2.000 operasi I / O per detik, yang menunjukkan bahwa (setidaknya untuk beberapa bagian beban kerja) set kerja database tidak cocok dengan memori.  

Kunci pengoptimalan MySQL # 5: Gunakan penyimpanan SSD

Jika database Anda tidak muat dalam memori (dan meskipun demikian), Anda masih memerlukan penyimpanan cepat untuk menangani penulisan dan untuk menghindari masalah kinerja saat database memanas (tepat setelah restart). Penyimpanan yang cepat saat ini berarti SSD.