Tuning Variabel Sistem MySQL untuk Kinerja Tinggi

Bagi sebagian besar pengembang aplikasi, basis data adalah altar dewa-dewa iblis yang paling baik tidak disetujui. Tetapi tidak harus seperti itu!


Hal lain dianggap sama, tingkat kenyamanan yang dimiliki pengembang dengan basis data yang mendasari menentukan tingkat senioritas mereka. Sedikit basis data dan sedikit pengalaman pengkodean = pengembang junior; sedikit basis data dan pengalaman pengkodean yang baik = pengembang tingkat menengah; database yang baik dan pengalaman coding yang baik = pengembang senior.

Ini adalah kenyataan pahit yang bahkan dihabiskan 6-8 tahun untuk menjelaskan seluk-beluk pengoptimal kueri dan lebih suka melihat ke surga ketika ditanya tentang penyetelan basis data.

Mengapa?

Anehnya, alasannya bukan kemalasan (meskipun di beberapa bagian itu).

Intinya adalah bahwa database adalah kekuatan mereka sendiri untuk bersaing. Bahkan secara tradisional, ketika hanya ada jenis database relasional yang harus dihadapi, menguasainya adalah keajaiban dan jalur karier sendiri; hari ini, kita memiliki begitu banyak jenis basis data sehingga mustahil untuk mengharapkan satu jiwa fana untuk menguasai segalanya.

Yang mengatakan, ada peluang bagus bahwa Anda masih senang dengan database relasional atau bagian dari tim yang memiliki produk yang berjalan pada database relasional memuaskan untuk waktu yang sangat lama. Dan dalam sembilan dari sepuluh kasus, Anda menggunakan MySQL (atau MariaDB). Untuk kasus-kasus ini, menyelam sedikit lebih dalam di bawah kap menghasilkan manfaat besar dalam meningkatkan kinerja aplikasi dan setiap bit layak dipelajari.

Ingin tahu? Mari selami!

Tidak penasaran? Yah, ikutilah, karena karier Anda bergantung padanya! ��

Optimalkan cache kueri MySQL

Hampir semua optimisasi di bidang komputer mengarah pada caching. Di satu sisi, CPU mempertahankan beberapa tingkat cache untuk mempercepat komputasinya, dan di sisi lain, aplikasi web secara agresif menggunakan solusi caching seperti Redis ke server hasil yang diperhitungkan kepada pengguna daripada memukul database setiap kali.

Tapi hei, bahkan database MySQL yang buruk memiliki cache kueri sendiri! Artinya, setiap kali Anda menanyakan sesuatu, dan datanya masih basi, MySQL akan menyajikan hasil yang di-cache ini daripada menjalankan kueri lagi, membuat aplikasi lebih cepat lebih cepat.

Anda dapat memeriksa apakah ada cache permintaan (catatan, tersedia, tidak diaktifkan) di database Anda dengan menjalankan query ini di konsol database:

MariaDB [(tidak ada)]> TAMPILKAN VARIABEL SEPERTI ‘have_query_cache’;
+——————+——-+
| Variable_name | Nilai |
+——————+——-+
| have_query_cache | YA |
+——————+——-+

Jadi, Anda dapat melihat bahwa saya menjalankan MariaDB dan bahwa saya memiliki cache kueri yang tersedia untuk dihidupkan. Sangat tidak mungkin Anda mematikannya jika Anda menggunakan pemasangan MySQL standar.

Sekarang mari kita lihat apakah cache query saya benar-benar dihidupkan:

MariaDB [(tidak ada)]> TAMPILKAN VARIABEL SEPERTI ‘query_cache_type’;
+——————+——-+
| Variable_name | Nilai |
+——————+——-+
| query_cache_type | ON |
+——————+——-+

Ya saya lakukan. Tetapi jika Anda tidak melakukannya, Anda dapat menyalakannya dengan mengatakan:

MariaDB [(tidak ada)]> SET GLOBAL query_cache_type = ON;

Menariknya, variabel ini juga menerima nilai ketiga yang menunjukkan “on-demand,” yang berarti MySQL hanya akan menembolok permintaan yang kami sampaikan, tetapi kami tidak akan membahasnya di sini.

Dengan ini, Anda memiliki caching permintaan dan telah mengambil langkah pertama menuju pengaturan MySQL yang lebih kuat! Saya katakan langkah pertama karena ketika menyalakannya adalah peningkatan besar, kita perlu menyetel caching kueri agar sesuai dengan pengaturan kita. Jadi mari kita belajar untuk melakukannya.

Variabel lain yang menarik di sini adalah query_cache_size, yang fungsinya cukup jelas:

MariaDB [(tidak ada)]> TAMPILKAN VARIABEL SEPERTI ‘query_cache_size’;
+——————+———-+
| Variable_name | Nilai |
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+

Jadi, saya memiliki cache permintaan sekitar ukuran 16 MB. Perhatikan bahwa meskipun caching kueri dihidupkan, tetapi ukuran ini nol, caching tidak aktif. Itu sebabnya memeriksa hanya satu variabel tidak cukup. Sekarang, Anda harus menetapkan ukuran cache kueri, tetapi berapa seharusnya? Pertama, harap perhatikan bahwa fitur caching permintaan akan membutuhkan 4 KB untuk menyimpan metadata-nya, jadi apa pun yang Anda pilih harus di atas itu.

Katakanlah Anda menetapkan ukuran cache kueri menjadi 500 KB:

MariaDB [(tidak ada)]> SET GLOBAL query_cache_size = 500000;

Apakah melakukan ini cukup banyak? Ya, tidak, karena bagaimana mesin query akan benar-benar berkinerja tergantung pada beberapa hal lagi:

  • Pertama, variabel query_cache_size harus cukup besar untuk menampung hasil kueri Anda. Jika terlalu kecil, tidak ada yang akan di-cache.
  • Kedua, jika query_cache_size diatur ke angka terlalu tinggi, akan ada dua jenis masalah: 1) Mesin harus melakukan penyimpanan ekstra dan menemukan hasil permintaan di area memori besar ini. 2) Jika sebagian besar kueri menghasilkan ukuran yang jauh lebih kecil, cache akan terfragmentasi, dan manfaat menggunakan cache akan hilang..

Bagaimana Anda tahu bahwa cache semakin terfragmentasi? Periksa jumlah total blok dalam cache seperti ini:

MariaDB [(tidak ada)]> tampilkan status seperti ‘Qcache_total_blocks’;
+———————+——-+
| Variable_name | Nilai |
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

Jika jumlahnya sangat tinggi, cache terfragmentasi dan perlu dibilas.

Jadi, untuk menghindari masalah ini, pastikan bahwa ukuran query_cache_size dipilih dengan bijak. Jika Anda merasa frustrasi karena saya tidak meninggalkan Anda dengan nomor konkret di sini, saya khawatir itulah yang terjadi ketika Anda pindah melewati pengembangan dan masuk ke bidang teknik. Anda harus melihat ke dalam aplikasi yang sedang Anda jalankan dan melihat apa ukuran kueri untuk hasil kueri yang penting dan kemudian mengatur nomor ini. Dan bahkan kemudian Anda mungkin akhirnya membuat kesalahan. ��

Threading, kolam utas, menunggu, dan batas waktu

Ini mungkin bagian paling menarik dari cara kerja MySQL dan membuatnya dengan benar berarti membuat aplikasi Anda beberapa kali lebih cepat!

Threading

MySQL adalah server multi-utas. Itu berarti, setiap kali ada koneksi baru ke server MySQL, itu membuka utas baru dengan data koneksi dan memberikan pegangan ke klien (jika Anda bertanya-tanya apa itu utas, lihat ini). Klien kemudian mengirim semua pertanyaan melalui utas ini dan menerima hasil. Hal ini mengarahkan kami untuk mengajukan pertanyaan alami: berapa banyak utas yang dapat diputar MySQL? Jawabannya ada di bagian selanjutnya.

Kolam utas

Tidak ada program dalam sistem komputer yang dapat membuka utas sebanyak yang diinginkan. Alasannya ada dua: 1) Threads cost memory (RAM), dan sistem operasi tidak akan memungkinkan Anda untuk mengamuk dan memakan semuanya. 2) Mengelola, katakanlah, sejuta utas adalah tugas besar sendirian, dan jika server MySQL dapat membuat banyak utas, ia akan mati saat mencoba menangani overhead.

Untuk menghindari masalah ini, MySQL dilengkapi dengan kumpulan utas – sejumlah utas tetap yang merupakan bagian dari kumpulan sebelumnya. Permintaan koneksi baru menyebabkan MySQL mengambil salah satu utas ini dan mengembalikan data koneksi, dan jika semua utas habis, koneksi baru ditolak secara alami. Mari kita lihat seberapa besar kumpulan thread:

ariaDB [(tidak ada)]> tampilkan variabel seperti ‘thread_pool_size’;
+——————+——-+
| Variable_name | Nilai |
+——————+——-+
| thread_pool_size | 4 |
+——————+——-+

Jadi, mesin saya memungkinkan maksimum empat koneksi sekaligus. Sangat menarik untuk dicatat bahwa angka 4 berasal dari fakta bahwa saya memiliki prosesor empat inti, yang berarti komputer saya hanya dapat menjalankan 4 tugas paralel pada satu waktu (saya berbicara tentang tugas yang benar-benar paralel di sini, bukan yang bersamaan). Idealnya, ini adalah batas yang seharusnya mendorong nilai dari thread_pool_size, tetapi pada mesin yang lebih besar, meningkatkan manfaatnya sampai titik tertentu. Jika Anda ingin tidak membuat semua koneksi baru menunggu dan boleh melakukan hit kinerja (sekali lagi, ini adalah area yang dapat Anda nilai terbaik berdasarkan kinerja aplikasi Anda yang sedang dimuat), menabraknya hingga 8 mungkin merupakan ide yang baik.

Namun, menetapkannya di atas 16 adalah ide yang buruk kecuali Anda memiliki mesin 32-core, karena kinerja menurun secara signifikan. Lubang kelinci dari utas kolam di MySQL berjalan dalam, tetapi jika Anda tertarik, ini dia diskusi yang lebih rinci.

Menunggu dan kehabisan waktu

Setelah utas dibuat dan dilampirkan ke klien, itu akan menjadi pemborosan sumber daya jika klien tidak mengirim pertanyaan selama beberapa detik (atau menit) berikutnya. Akibatnya, MySQL mengakhiri koneksi setelah beberapa saat tidak aktif. Ini dikendalikan oleh variabel wait_timeout:

MariaDB [(tidak ada)]> tampilkan variabel seperti ‘tunggu%’;
+—————+——-+
| Variable_name | Nilai |
+—————+——-+
| wait_timeout | 28800 |
+—————+——-+

Nilai yang dihasilkan dalam hitungan detik. Jadi ya, secara default MySQL diatur untuk menunggu 8+ jam sebelum kabel itu terputus! Ini mungkin bagus jika Anda memiliki pertanyaan yang sudah berjalan lama dan benar-benar ingin menunggu mereka (tetapi bahkan kemudian, delapan jam tidak masuk akal!) Tetapi mengerikan dalam kebanyakan kasus. Ketika kueri dijalankan, nilai ini diatur ke 0 (artinya selamanya), tetapi secara umum, ini harus ditetapkan ke nilai yang sangat rendah (5 detik, misalnya, atau mungkin bahkan kurang) untuk membebaskan koneksi untuk proses lain.

Tuning tabel sementara

Mari kita mulai dengan apa itu tabel sementara di MySQL.

Misalkan kita memiliki MySQL yang secara struktural terlihat seperti ini: TABLE A UNION (TABEL B INNER GABUNG C). Artinya, kami tertarik untuk bergabung dengan tabel B dan C, dan kemudian melakukan penyatuan hasil dengan tabel A. Sekarang, MySQL pertama-tama akan melanjutkan untuk bergabung dengan tabel B dan C, tetapi sebelum dapat melakukan penyatuan, diperlukan untuk menyimpan data ini di suatu tempat. Di sinilah tabel sementara masuk – MySQL menggunakannya untuk menyimpan data pada tahap perantara dalam kueri kompleks untuk sementara, dan setelah kueri berakhir, tabel sementara ini akan dibuang.

Sekarang pertanyaannya adalah: mengapa kita harus repot-repot dengan semua ini?

Hanya karena tabel sementara, hanya hasil kueri, adalah data yang digunakan oleh MySQL dalam perhitungan, dan kecepatan aksesnya (di antara batasan lainnya) akan menentukan seberapa cepat kueri dijalankan. Misalnya, menyimpan tabel sementara dalam RAM akan beberapa kali lebih cepat daripada menyimpannya di disk.

Ada dua variabel yang mengontrol perilaku ini:

MariaDB [(tidak ada)]> tampilkan variabel seperti ‘MariaDB [(tidak ada)]> tampilkan variabel seperti ‘tmp_table_size’;
+—————-+———-+

| Variable_name | Nilai |

+—————-+———-+

| tmp_table_size | 16777216 |

+—————-+———-+
‘;
+———————+———-+
| Variable_name | Nilai |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(tidak ada)]> tampilkan variabel seperti ‘tmp_table_size’;
+—————-+———-+
| Variable_name | Nilai |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

Yang pertama, max_heap_table_size, memberi tahu kami berapa banyak RAM yang dapat digunakan oleh tabel MySQL (“heap” di sini merujuk pada struktur data yang digunakan dalam alokasi dan manajemen RAM – baca selengkapnya sini), sedangkan yang kedua, tmp_table_size, menunjukkan ukuran maksimum tabel sementara. Dalam kasus saya, keduanya diatur ke 16 MB, meskipun intinya saya mencoba untuk membuatnya bahwa hanya meningkatkan tmp_table_size tidak akan berfungsi secara keseluruhan, MySQL masih akan dibatasi oleh max_table_heap_size.

Sekarang, intinya: jika tabel sementara yang dibuat lebih besar dari batas yang diizinkan oleh variabel-variabel ini, MySQL akan dipaksa untuk menuliskannya ke hard disk, sehingga kinerjanya sangat buruk. Pekerjaan kami sekarang sederhana: lakukan yang terbaik untuk menebak ukuran data paling akurat untuk tabel sementara dan menyesuaikan variabel-variabel ini ke batas itu. Namun, saya ingin mengingatkan terhadap absurditas: menetapkan batas ini ke 16 GB (dengan asumsi Anda memiliki RAM sebanyak ini) ketika sebagian besar tabel sementara Anda berukuran kurang dari 24 MB adalah kebodohan – Anda hanya membuang-buang RAM yang bisa ‘ telah digunakan oleh permintaan atau bagian lain dari sistem (cache, misalnya).

Kesimpulan

Tidak mungkin untuk mencakup semua variabel sistem dalam satu artikel, atau bahkan semua yang penting dalam satu artikel ketika dokumentasi MySQL itu sendiri mencakup beberapa ribu kata. Sementara kami membahas beberapa variabel universal di sini, saya mendorong Anda untuk melihat variabel sistem untuk mesin yang Anda gunakan (InnoDB atau MyISAM).

Hasil saya yang paling diinginkan untuk menulis artikel ini adalah bagi Anda untuk mengambil tiga hal:

  1. MySQL adalah perangkat lunak khas yang bekerja dalam batas yang ditentukan oleh sistem operasi. Ini bukan program misterius yang melakukan yang Tuhan tahu apa dan tidak mungkin dijinakkan. Selain itu, untungnya, tidak terlalu sulit untuk memahami bagaimana pengaturan dan dikendalikan oleh variabel sistemnya.
  2.  Tidak ada pengaturan tunggal yang akan membuat instalasi MySQL Anda memperbesar. Anda tidak punya pilihan selain melihat ke dalam sistem Anda berjalan (ingat, optimasi datang setelah aplikasi dalam produksi, bukan sebelumnya), membuat tebakan dan pengukuran terbaik, dan hidup dengan kenyataan bahwa itu tidak akan pernah menjadi sempurna.
  3. Menyetel variabel bukan satu-satunya cara untuk mengoptimalkan MySQL – kueri penulisan yang efisien adalah masalah besar lainnya, tetapi itu adalah sesuatu yang akan saya bahas di artikel lain. Tetapi intinya adalah, bahkan jika Anda telah melakukan analisis seperti dewa dan menyetel parameter ini sebaik mungkin, masih mungkin bagi Anda untuk menghentikan semuanya..

Apa variabel sistem favorit Anda untuk penyetelan? ��

TAGS:

  • Basis data

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Like this post? Please share to your friends:
    Adblock
    detector
    map