Perbedaan antara SQL yang ditulis dengan baik dan tidak sangat luas, dan dalam produksi di situs dengan request yang tinggi menyebabkan dampak serius pada kinerja dan keandalan layanan. Dalam panduan ini saya akan membahas cara menulis kueri yang cepat dan faktor apa yang menyebabkannya berjalan lambat.
Mengapa MySQL?
Hari ini ada banyak pembicaraan tentang Big Data dan teknologi baru. NoSQL dan solusi berbasis cloud sangat bagus, tetapi banyak software web populer (seperti WordPress, phpBB, Drupal, software Forum VBulletin, dll.) yang masih berjalan di MySQL. Migrasi ke solusi baru ini mungkin tidak sesederhana hanya dengan mengoptimalkan konfigurasi yang sudah kamu miliki di produksi. Selain itu, kinerja MySQL sangat bagus, terutama versi Percona.
Jangan membuat kesalahan umum dengan membuang lebih banyak kekuatan komputasi saat berurusan dengan masalah kueri yang lambat dan beban server yang tinggi, daripada benar-benar mengatasi akar masalah yang mendasarinya. Menambahkan daya CPU, SSD, atau RAM adalah bentuk pengoptimalan jika kamu sukai, tetapi bukan itu yang akan saya bicarakan di sini. Selain itu, tanpa situs yang dioptimalkan, saat kamu tumbuh dengan perangkat keras, masalah akan berlipat ganda secara eksponensial. Jadi itu bukan solusi jangka panjang yang solid.
Menjadi baik di SQL selalu merupakan alat vital bagi pengembang web, dan dengan memperbaiki sering sesederhana hanya dengan menambahkan indeks atau sedikit memodifikasi cara tabel digunakan, itu benar-benar membantu untuk mengetahui cara menggunakan RDBMS-mu dengan baik. Dalam hal ini kita berfokus pada database open source yang sering digunakan bersama dengan PHP, dan itu adalah MySQL.
Untuk Siapa Panduan Ini?
Web Developer, Database Architect / DBA dan System Administrator yang terbiasa dengan MySQL. Jika kamu tidak terbiasa dengan MySQL sebagai pemula, maka panduan ini kemungkinan besar tidak akan masuk akal, tapi saya akan mencoba untuk tetap informatif sebisa mungkin untuk pendatang baru ke MySQL.
Backup Terlebih Dulu
Saya sarankan untuk mencoba langkah-langkah yang disediakan pada database MySQL-mu sendiri (backup semuanya terlebih dahulu!). Jika kamu tidak memiliki basis data untuk dikerjakan, contoh buat skema basis data yang disediakan jika berlaku.
Melakukan backup MySQL dapat dengan mudah melalui utilitas baris perintah <?php 07:
1$ mysqldump myTable > myTable-backup.sql
Kamu dapat mempelajari lebih lanjut tentang mysqldump.
Apa yang Membuat Kueri Lambat?
Secara singkat dan tanpa urutan kepentingan, berikut ini semua yang memainkan faktor penting dalam kinerja kueri dan server:
- tabel indeks
- klausa <?php 08 (dan penggunaan fungsi internal MySQL seperti <?php 09 dan <?php 10 misalnya)
- menyortir dengan <?php 11
- frekuensi permintaan secara bersamaan
- tipe storage engine (InnoDB, MyISAM, Memory, Blackhole)
- tidak menggunakan edisi Percona
- variabel konfigurasi server (tuning my.cnf / my.ini)
- set hasil yang besar (>1.000 baris)
- koneksi yang tidak persisten
- konfigurasi pemecahan / pengelompokkan
- desain tabel yang buruk
Kita akan membahas semua area ini dalam panduan ini. Juga, jika kamu belum menggunakannya, silakan menginstal Percona, yang merupakan pengganti drop-in untuk MySQL yang akan membawa peningkatan kinerja yang serius. Untuk melihat tolok ukur Percona vs. MySQL, lihat perbandingan ini.
Apa Itu Indeks?
Indeks digunakan oleh MySQL untuk menemukan baris dengan nilai kolom tertentu dengan cepat, misalnya di dalam <?php 12. Tanpa indeks, MySQL harus dimulai dengan baris pertama dan kemudian membaca seluruh tabel untuk menemukan baris yang relevan. Semakin besar tabel, semakin banyak biaya ini.
Jika tabel memiliki indeks untuk kolom yang dimaksud, MySQL dapat dengan cepat menentukan posisi untuk mencari di tengah file data tanpa harus melihat semua data. Ini jauh lebih cepat daripada membaca setiap baris secara berurutan.
Koneksi Yang Tidak Persisten?
Ketika bahasa scripting-mu terhubung dengan database, jika kamu telah mengkonfigurasi koneksi persisten maka ia akan dapat menggunakan kembali koneksi yang ada tanpa harus membuat yang baru. Ini optimal untuk penggunaan produksi dan harus diaktifkan.
Pengguna PHP dapat membaca lebih lanjut di PHP Manual.
Mengurangi Frekuensi Permintaan Secara Bersamaan
Cara tercepat dan paling efektif yang saya temukan untuk memperbaikinya adalah dengan memanfaatkan sepasang pasangan nilai-kunci seperti <?php 13 atau <?php 14.
Dengan <?php 15 kamu dapat dengan mudah menyimpan konten kuerimu dengan yang berikut, misalnya:
1<?php 2$cache = new Memcache; 3$cache->connect('localhost',11211); 4$cacheResult = $cache->get('key-name'); $ mysqldump myTable > myTable-backup.sql 0$ mysqldump myTable > myTable-backup.sql 1$ mysqldump myTable > myTable-backup.sql 2$ mysqldump myTable > myTable-backup.sql 3$ mysqldump myTable > myTable-backup.sql 4$ mysqldump myTable > myTable-backup.sql 5$ mysqldump myTable > myTable-backup.sql 6$ mysqldump myTable > myTable-backup.sql 7$ mysqldump myTable > myTable-backup.sql 8$ mysqldump myTable > myTable-backup.sql 910111213141516171819<?php 0<?php 1<?php 2<?php 3<?php 4<?php 5<?php 6<?php 7<?php 8<?php 92021
Sekarang contoh kueri <?php 16 hanya akan berjalan sekali setiap 86.400 detik (24 jam), sehingga mengambil sejumlah besar beban jauh dari server MySQL dan mengurangi koneksi secara bersamaan.
Catatan: Tambahkan <?php 17 ke argumen host-mu di MySQLi untuk koneksi persisten.
Pemecahan / Pengelompokkan
Ketika datamu menjadi besar atau permintaan untuk layananmu meningkat, kepanikan dapat diatur. Perbaikan cepat untuk memastikan layananmu tetap online dapat menjadi pemecahan. Tapi saya tidak merekomendasikan itu, karena pemecahan secara inheren tampaknya membuat struktur data terlalu rumit. Dan seperti yang dijelaskan dengan sangat fasih dalam artikel ini dari blog Percona, jangan memecah.
Desain Tabel Yang Buruk
Membuat skema basis data tidak terlalu sulit ketika kamu menerima beberapa aturan yang sangat baik, seperti bekerja dengan batasan dan menyadari apa yang akan efisien. Menyimpan gambar dalam database sebagai tipe data <?php 18, misalnya, sangat tidak disarankan; menyimpan nama file dalam kolom dengan tipe data <?php 19 jauh lebih unggul.
Memaastikan bahwa desainnya benar untuk penggunaan yang diperlukan sangat penting dalam membuat aplikasimu. Tetap memisahkan data tertentu (misalnya kategori dan pos) dan memastikan hubungan many-to-one atau one-to-many dapat dengan mudah dikaitkan dengan ID. Memanfaatkan fasilitas <?php 20 dari MySQL sangat ideal untuk menurunkan kebimbangan data antar tabel.
Saat membangun tabelmu, coba ingat yang berikut:
- Gunakan minimum yang kamu butuhkan untuk menyelesaikan pekerjaan; menjadi tipis dan langsung ke point-nya.
- Jangan berharap MySQL untuk melakukan logika bisnismu atau menjadi terprogram—yang harus dilakukan benar-benar sebelum penyisipan oleh bahasa scripting-mu. Misalnya, jika kamu perlu mengacak daftar, lakukan pengacakan array dalam PHP, bukan dalam <?php 21 di MySQL.
- Gunakan jenis indeks <?php 22 untuk dataset unik dan gunakan <?php 23 untuk menyimpan datetime atau unix timestamp yang diperbarui misalnya saat terakhir kali barisnya diperiksa.
- Gunakan tipe data <?php 24 untuk bilangan bulat numerik. Jika kamu tidak menentukan panjangnya, MySQL akan menghitung apa yang diperlukan itu sendiri.
Dasar-dasar Optimasi
Agar dapat mengoptimalkan secara efektif, kita harus melihat tiga kumpulan data mendasar terkait aplikasimu:
- Analisis (pencatatan log kueri yang lambat, pemeriksaan, dan analisa desain tabel)
- Persyaratan kinerja (berapa banyak pengguna, apa permintaannya)
- Kendala teknologi (kecepatan perangkat keras, meminta terlalu banyak MySQL)
Analisis dapat dilakukan dengan beberapa cara. Pertama-tama, kita akan mengambil rute yang paling langsung untuk mencari di bawah kapak kueri MySQL. Tool pertama dalam toolbox optimasimu adalah <?php 25. Memanfaatkan ini dalam kuerimu sebelum <?php 26 akan memberimu output berikut:
123225327425$ mysqldump myTable > myTable-backup.sql 0$cache = new Memcache; 1$ mysqldump myTable > myTable-backup.sql 225$ mysqldump myTable > myTable-backup.sql 4$cache = new Memcache; 5
Kolom yang tercantum masing-masing menyimpan informasi yang berguna tentang kueri yang sedang dijalankan. Kolom yang perlu kamu perhatikan adalah <?php 27 dan <?php 28.
<?php 27 akan menampilkan indeks yang engine MySQL telah sediakan untuk digunakan pada query. Terkadang kamu perlu memaksa indeks untuk memastikan kueri dijalankan dengan cara tercepat.
Kolom <?php 28 akan menunjukkan apakah <?php 12 atau <?php 21 bersyarat digunakan. Yang paling penting untuk diperhatikan adalah jika <?php 33 muncul. Perhatikan contoh berikut:
1$cache = new Memcache; 72$cache = new Memcache; 9331433$ mysqldump myTable > myTable-backup.sql 035$ mysqldump myTable > myTable-backup.sql 237$ mysqldump myTable > myTable-backup.sql 439$ mysqldump myTable > myTable-backup.sql 6$cache->connect('localhost',11211); 1$ mysqldump myTable > myTable-backup.sql 8$cache->connect('localhost',11211); 310$cache->connect('localhost',11211); 512$cache->connect('localhost',11211); 714$cache->connect('localhost',11211); 9
Jenis kueri ini bisa masuk ke disk karena ada kondisi di mana, yang terjadi jika kita melihat pada <?php 25:
141243
Jadi kueri ini memiliki kemungkinan untuk menggunakan dua indeks dan saat ini sedang memukul disk karena <?php 35 di <?php 28.
Apa yang <?php 37 lakukan didefinisikan di sini dari manual MySQL:
"MySQL harus melakukan terusan tambahan untuk mencari tahu cara mengambil baris dalam urutan yang diurutkan. Semacam ini dilakukan dengan melalui semua baris sesuai dengan tipe join dan menyimpan kunci sortir dan pointer ke baris untuk semua baris yang sesuai dengan klausa WHERE. Tombol-tombol itu kemudian diurutkan dan baris-baris itu diambil dalam urutan yang terurut."
Terusan ekstra ini akan memperlambat aplikasimu dan harus dihindari di semua biaya. Hasil <?php 28 penting lainnya yang harus dihindari adalah <?php 39, yang berarti MySQL harus membuat tabel sementara untuk kueri. Tentunya ini adalah penggunaan MySQL yang mengerikan dan harus dihindari dengan segala cara kecuali kamu tidak dapat mengoptimalkan lebih lanjut karena persyaratan data. Dalam hal ini kuerinya harus di-cache dalam Redis atau Memcache dan tidak dijalankan oleh pengguna.
Untuk memperbaiki masalah dengan <?php 33 kita harus memastikan MySQL menggunakan <?php 41. Ini memiliki beberapa <?php 27 untuk dipilih, tetapi MySQL hanya dapat menggunakan satu indeks dalam kueri akhir. Meskipun Indeks dapat berupa gabungan beberapa kolom, kebalikannya tidak benar, meskipun kamu dapat memberikan petunjuk kepada pengoptimal MySQL mengenai indeks mana yang telah kamu buat.
Indeks Petunjuk
Pengoptimal MySQL akan menggunakan statistik berdasarkan tabel kueri untuk memilih indeks terbaik pada cakupan kueri. Ia melakukannya berdasarkan logika statistik pengoptimalnya yang ada di dalamnya, meskipun dengan beberapa pilihan ini tidak selalu benar tanpa memberi petunjuk. Untuk memastikan kunci yang benar digunakan (atau tidak digunakan), gunakan kata kunci <?php 43, <?php 44 dan <?php 45 dalam kuerimu. Kamu dapat membaca lebih lanjut tentang petunjuk indeks di manual MySQL.
Untuk melihat kunci-kunci tabel, gunakan perintah <?php 46.
Kamu dapat menentukan beberapa petunjuk untuk pengoptimal agar digunakan, misalnya:
1452473494$cacheResult = $cache->get('key-name'); 1$ mysqldump myTable > myTable-backup.sql 0$cacheResult = $cache->get('key-name'); 3$ mysqldump myTable > myTable-backup.sql 2$cacheResult = $cache->get('key-name'); 5$ mysqldump myTable > myTable-backup.sql 4$cacheResult = $cache->get('key-name'); 7$ mysqldump myTable > myTable-backup.sql 6$cache = new Memcache; 7$ mysqldump myTable > myTable-backup.sql 8$ mysqldump myTable > myTable-backup.sql 0110311233143516371839<?php 0$cache->connect('localhost',11211); 1<?php 2$cache->connect('localhost',11211); 3<?php 4$cache->connect('localhost',11211); 5<?php 5$cache->connect('localhost',11211); 7<?php 7$cache->connect('localhost',11211); 9
Sekarang MySQL memiliki <?php 47 dari tabel untuk digunakan, kueri itu tetap.
1412$ mysqldump myTable > myTable-backup.sql 25
Bersama menjelaskan adalah kata kunci yang menggambarkan. Dengan <?php 48 kamu dapat melihat informasi tabel sebagai berikut:
1$ mysqldump myTable > myTable-backup.sql 272$ mysqldump myTable > myTable-backup.sql 293$ mysqldump myTable > myTable-backup.sql 314$ mysqldump myTable > myTable-backup.sql 29$ mysqldump myTable > myTable-backup.sql 0$ mysqldump myTable > myTable-backup.sql 35$ mysqldump myTable > myTable-backup.sql 2$ mysqldump myTable > myTable-backup.sql 37$ mysqldump myTable > myTable-backup.sql 4$ mysqldump myTable > myTable-backup.sql 39$ mysqldump myTable > myTable-backup.sql 6$ mysqldump myTable > myTable-backup.sql 41$ mysqldump myTable > myTable-backup.sql 8$ mysqldump myTable > myTable-backup.sql 4310$ mysqldump myTable > myTable-backup.sql 29
Menambahkan Indeks
Kamu membuat indeks di MySQL dengan sintaks <?php 49. Ada beberapa rasa dari indeks. <?php 50 digunakan untuk pencarian teks lengkap, dan kemudian ada jenis <?php 22 untuk memastikan data tetap unik.
Untuk menambahkan indeks ke tabelmu, gunakan sintaks berikut ini misalnya:
1$ mysqldump myTable > myTable-backup.sql 47
Ini akan membuat indeks pada tabel <?php 52, yang akan menggunakan 10 huruf pertama dari kolom username, yang merupakan tipe data varchar.
Dalam hal ini, pencarian apa pun yang membutuhkan <?php 12 pada nama pengguna dengan kecocokan yang berada di 10 karakter pertama akan sama dengan pencarian seluruh tabel.
Indeks Komposit
Indeks memiliki efek besar pada kecepatan yang diperlukan untuk mengembalikan data kueri. Hanya menetapkan primary key dan indeks unique umumnya tidak cukup—kunci komposit adalah tempat di mana tuning reluk yang sebenarnya terletak di MySQL, dan paling sering ini memerlukan beberapa pemeriksaan A/B dengan <?php 25.
Sebagai contoh, jika kita perlu mereferensikan dua kolom di dalam <?php 12 kita bersyarat, kunci komposit akan ideal.
1$ mysqldump myTable > myTable-backup.sql 49
Di sini kunci ini dibuat pada kolom <?php 56 dari contoh sebelumnya dan kolom <?php 57, tipe data <?php 58 yang menandakan apakah akun penggunanya aktif. Jadi sekarang ketika kueri data untuk <?php 12 username valid dan akun <?php 60, dataset sekarang dioptimalkan untuk menangani ini lebih baik.
Seberapa Cepatkah MySQL Mu?
Aktifkan profiling untuk melihat lebih dekat pada query MySQL-mu. Ini dapat dilakukan pada waktu proses melalui <?php 61, dan kemudian mengeksekusi kuerimu dan melihat hasil dari <?php 62.
Dengan PDO, ini adalah cuplikan kode yang tidak hanya itu:
1$ mysqldump myTable > myTable-backup.sql 512$ mysqldump myTable > myTable-backup.sql 533$ mysqldump myTable > myTable-backup.sql 554$ mysqldump myTable > myTable-backup.sql 57$ mysqldump myTable > myTable-backup.sql 0$ mysqldump myTable > myTable-backup.sql 2$ mysqldump myTable > myTable-backup.sql 60$ mysqldump myTable > myTable-backup.sql 4$ mysqldump myTable > myTable-backup.sql 6$ mysqldump myTable > myTable-backup.sql 63
Jika kamu tidak menggunakan PDO, hal yang sama dapat dilakukan dengan <?php 63 sebagai berikut:
1$ mysqldump myTable > myTable-backup.sql 6523$ mysqldump myTable > myTable-backup.sql 684$ mysqldump myTable > myTable-backup.sql 53$ mysqldump myTable > myTable-backup.sql 0$ mysqldump myTable > myTable-backup.sql 72$ mysqldump myTable > myTable-backup.sql 2$ mysqldump myTable > myTable-backup.sql 74$ mysqldump myTable > myTable-backup.sql 4$ mysqldump myTable > myTable-backup.sql 76$ mysqldump myTable > myTable-backup.sql 6$ mysqldump myTable > myTable-backup.sql 78$ mysqldump myTable > myTable-backup.sql 8$ mysqldump myTable > myTable-backup.sql 8010<?php 31214$ mysqldump myTable > myTable-backup.sql 8516$ mysqldump myTable > myTable-backup.sql 7418$ mysqldump myTable > myTable-backup.sql 76<?php 0$ mysqldump myTable > myTable-backup.sql 78<?php 2$ mysqldump myTable > myTable-backup.sql 80<?php 4<?php 3<?php 5<?php 7$ mysqldump myTable > myTable-backup.sql 98
Ini akan mengembalikan kepadamu data profil, yang akan mencakup waktu eksekusi dalam nilai kedua dari array asosiatif:
1100210231044106$ mysqldump myTable > myTable-backup.sql 0$ mysqldump myTable > myTable-backup.sql 78
Kueri membutuhkan waktu 0.00024300 detik untuk dapat selesai. Itu cukup cepat untuk tidak perlu khawatir. Tetapi ketika jumlah meningkat, kita harus melihat lebih dalam.
Sebagai contoh kerja, kenali aplikasimu. Tempatkan cek untuk konstanta <?php 64 di basis data aplikasi database abstraction layer / framework database driver, dan kemudian kamu dapat mulai mengaudit dengan mengaktifkan kasus profil dan mengeluarkan hasilnya dengan <?php 65 / <?php 66. Sekarang kamu akan dapat menjelajahi dan membuat profil halaman situs web-mu dengan mudah!
Sepenuhnya Mengaudit Aplikasimu
Untuk melakukan audit penuh atas kuerimu, aktifkan pencatatan log. Beberapa pengembang saya telah bekerja dengan khawatir bahwa ini adalah masalah dua sisi yang memungkinkan pencatatan log sedikit mempengaruhi kinerja, dan statistik yang kamu rekam akan sedikit lebih rendah daripada dalam kenyataan. Meskipun ini benar, banyak patokan menunjukkan itu tidak terlalu banyak perbedaan.
Untuk mengaktifkan logging di MySQL versi 5.1.6, kamu menggunakan global <?php 67 dan dapat menentukan file dengan global <?php 68. Ini dapat dilakukan dalam prompt runtime sebagai berikut:
11102112
Kamu dapat mengatur ini secara tetap di file konfigurasi <?php 69 atau <?php 70 untuk server-mu.
11142116
Setelah melakukan perubahan ini, kamu harus me-restart server MySQL, misalnya. <?php 71 pada sistem Linux.
Di MySQL 5.6.1 yang lebih baru, <?php 67 tidak digunakan lagi dan <?php 73 digunakan sebagai gantinya. Mengaktifkan <?php 74 sebagai tipe output memungkinkan pengalaman debugging yang jauh lebih baik dan dapat dilakukan sebagai berikut di MySQL 5.6.1 dan yang lebih baru:
111821203122
<?php 75 menentukan jumlah detik kueri yang lambat agar diklasifikasikan. Standarnya adalah 10 dan minimum 0. Ini dapat mengambil nilai milidetik dengan menentukan float; di sini saya telah mengaturnya menjadi 1 detik. Jadi, setiap kueri yang membutuhkan waktu lebih lama dari 1 detik akan dicatat dalam format output <?php 76.
Ini akan masuk ke tabel <?php 77 dan <?php 78 dalam <?php 79.
Untuk menonaktifkan pencatatan log, set <?php 80 ke <?php 81.
<?php 82 adalah boolean yang berguna, ketika diaktifkan bersama dengan log kueri yang lambat, berarti hanya query yang diharapkan untuk mengambil semua baris yang dicatat.
Pilihan ini tidak selalu berarti tidak ada indeks yang digunakan. Misalnya, saat kueri menggunakan pemindaian indeks lengkap, ini akan dicatat karena indeks tidak akan membatasi jumlah baris.
Pencatatan Log di Produksi?
Mengaktifkan pencatatan di situs produksi dengan lalu lintas akan cukup banyak harus dilakukan dalam waktu singkat, sambil memantau beban untuk memastikannya tidak memengaruhi layanan. Jika kamu berada di bawah beban berat dan memerlukan perbaikan mendesak, mulailah dengan mengatasi masalah pada prompt dengan <?php 83 atau melalui tabel <?php 84 secara langsung, misal. <?php 85.
Mencatat semua kueri dalam produksi dapat memberitahumu banyak hal dan merupakan praktik yang baik untuk tujuan penelitian ketika kamu mengaudit proyek, tetapi membiarkannya berjalan dengan sering selama berhari-hari tidak akan memberimu data yang lebih bermanfaat daripada paling lama 48 jam ( rata-rata, paling tidak menangkap waktu puncak penggunaan agar dapat melihat dengan baik kuerinya dan mendapatkan beberapa ide frekuensi).
Catatan: jika kamu menjalankan situs yang mengalami lonjakan lalu lintas puncak dan kemudian periode tidak banyak sama sekali (seperti situs web olahraga selama dan di luar musim), logislah dengan caramu melihat pencatatan log. Jangan anggap situs ini bekerja dengan cepat. Lakukan audit dan yang paling penting mengatur beberapa grafik.
Logging dan Percona’s pt-query-digest
Percona memiliki beberapa tool hebat yang digabungkan dengannya, dan <?php 86 adalah tool baris perintah untuk menganalisis log kueri, daftar proses atau tcpdumps.
Kamu dapat menggunakan <?php 86 dengan cara-cara berikut:
Menganalisis file *.log (dikeluarkan dari pencatatan log kuerimu yang lambat misalnya):
1124
Laporkan kueri yang paling lambat dari host1 secara real time (sangat berguna!):
1126
Gunakan tcpdump untuk melaporkan kueri yang paling lambat dari data protokol MySQL:
112823131
Akhirnya kita dapat menyimpan data kueri yang lambat dari satu host ke yang lain untuk ditinjau nantinya. Di sini kita menyimpan intisari kueri untuk slow.log ke host2:
1133
Untuk mempelajari cara menggunakan tool <?php 86 dari Percona, baca halaman manual.
Penggrafikan Kinerja MySQL dan Server
Grafik InnoDB Row Operations ini menunjukkan operasi baris InnoDB yang telah dilakukan: pembaruan, membaca, menghapus dan menyisipkan.
Ini adalah topik yang besar dan saya akan cukup menyentuhnya di panduan ini untuk memulai dengan pemantauan MySQL. Penting untuk dicatat secara umum, bagaimanapun, bahwa pemantauan semua layanan situs web-mu sangat ideal untuk benar-benar mengetahui apa kinerja dan penggunaanmu.
Untuk mencapai ini, saya sarankan menyiapkan solusi berbasis <?php 89 seperti <?php 90 dengan konfigurasi MySQL. Dapatkan template untuk Cacti dari orang-orang di Percona.
Setelah kamu menyiapkan Cacti dan dapat mulai menganalisis aplikasimu, biarkan beberapa waktu berlalu sehingga grafik dapat meningkat. Setelah beberapa hari kamu akan mulai melihat irama siang dan malam lalu lintasmu dan melihat seberapa server benar-benar sibuk.
Jika kamu mencari peringatan dan pemicu otomatis, lihat untuk mengonfigurasi monit, monitor proaktif open-source untuk sistem Unix. Dengan monit kamu dapat membuat aturan untuk servermu dan memastikanmu diberitahu ketika beban naik sehingga kamu dapat menangkapnya ketika itu terjadi.
Log Kueri Yang Lambat
Mencatat semua kueri yang lambat membutuhkan waktu lebih dari satu detik untuk menyelesaikan dapat memberi tahu kita sesuatu, tetapi juga mengetahui pertanyaan mana yang mengeksekusi ratusan kali sama pentingnya. Bahkan jika permintaan tersebut singkat untuk dieksekusi, biaya overhead permintaan tinggi masih mengambil korban di server.
Itulah mengapa tetap di sekitar ketika kamu memperbarui sesuatu dan menayangkannya secara langsung adalah waktu yang paling penting untuk setiap pekerjaan dan perubahan basis data baru. Kita selalu memiliki kebijakan di tim saya untuk tidak pernah menyinkronkan perubahan basis data fitur baru setelah hari Rabu pada proyek langsung. Itu harus dilakukan pada awal minggu, selambat-lambatnya Selasa, sehingga semua tim dapat memantau dan memberikan dukungan yang sesuai.
Sebelum menayangkan dengan kueri baru, kamu harus membuat tolok ukur dengan tool pengujian beban seperti <?php 91. Ketika kamu menjalankan benchmark-mu, kamu harusnya melihat <?php 83, dan juga memungkinkan logging dan memantau dengan tool-tool sistem seperti <?php 93, <?php 94 dan <?php 95. Ini adalah langkah penting sebelum memasukkan kueri baru apapun ke dalam produksi langsung. Namun ini bukan tes asam 100% karena traffic langsung dapat berperilaku jauh berbeda dengan tolok ukur yang dihitung.
Untuk mengukur dengan <?php 91, pastikan kamu memiliki paket yang diinstal, misalnya:
1135213731394141
Sekarang kamu dapat memulai dengan menguji aplikasimu, misalnya:
1143
<?php 97 berarti untuk <?php 98 koneksi, dan <?php 99 adalah jumlah koneksi bersamaan, yaitu jumlah orang/klien yang akan mengunjungi situs sekaligus. Akhirnya 200 adalah jumlah permintaan yang akan dilakukan 201.
Jadi dengan menjalankan perintah di atas, kamu akan menekan //my-domain.com/ dengan 350 koneksi simultan hingga 20.000 permintaan terpenuhi, dan ini akan dilakukan menggunakan header keep-alive.
Setelah proses menyelesaikan 20.000 permintaan, kamu akan menerima umpan balik tentang statistik. Ini akan memberi tahumu seberapa baik situs dilakukan di bawah tekanan yang kamu masukkan saat menggunakan parameter di atas. Ini adalah cara yang baik untuk mengetahui dalam arti otomatis jika kuerimu telah mengubah apa pun.
Benchmarking Hot vs. Cold
Jumlah permintaan dan beban server memiliki dampak besar pada kinerja, dan waktu kueri dapat terpengaruh karena hal ini. Dalam semua kamu harus mengaktifkan log kueri yang lambat untuk menangkap ini dalam produksi, dan sebagai aturan untuk pengembanganmu harus memastikan semua pertanyaan mengeksekusi dalam pecahan milidetik (0.0xx atau lebih cepat) pada server yang menganggur.
Menerapkan <?php 15 akan memiliki dampak yang dramatis pada persyaratan pemuatanmu dan akan digunakan untuk secara serius mengurangi sumber daya yang sedang digunakan memproses kueri. Pastikan kamu menggunakan <?php 13 secara efektif dan memberi tolok ukur aplikasimu dengan cache hot (dimuat dengan nilai) vs. yang cold.
Untuk menghindari keluar ke produksi dengan cache kosong, skrip pre-loader adalah cara yang baik untuk memastikan cache akan dibaca dan kamu tidak akan mendapatkan banyak sekali permintaan semua datang sekaligus ketika kembali dari downtime karena kegagalan kapasitas berlebih.
Memperbaiki Query Yang Lambat
Jadi setelah mengaktifkan pencatatan, kamu sekarang menemukan beberapa kueri yang lambat di aplikasimu. Mari memperbaikinya! Sebagai contoh, saya akan menunjukkan berbagai masalah umum yang akan kamu hadapi dan logika untuk memperbaikinya.
Jika kamu belum menemukan kueri yang lambat, mungkin periksa apa setelanmu di mana untuk <?php 75 jika kamu menggunakan metode kueri log. Jika tidak, setelah memeriksa semua kuerimu dengan pembuatan profil (se 205), buat daftar kueri yang memakan waktu lebih lama daripada pecahan milidetik untuk diselesaikan (0.000x detik) dan mari mulai pada semua itu.
Masalah Umum
Berikut ini enam masalah umum yang saya hadapi ketika mengoptimalkan query MySQL:
1. <?php 21 menggunakan fileort.
1145214731494147$ mysqldump myTable > myTable-backup.sql 0153$ mysqldump myTable > myTable-backup.sql 2147
Menghindari file pada ini tidak mungkin karena 207. Tidak peduli apa permutasi indeks yang kamu gunakan, yang terbaik yang akan kamu dapatkan adalah 208 di kolom <?php 28 mu Untuk mengoptimalkan ini, simpan hasilnya di Memcache, atau lakukan pemesanan di lapisan logika aplikasimu.
2. Menggunakan <?php 21 di <?php 12 dan <?php 16
<?php 21 memiliki jumlah kueri signifikan pada kueri. Misalnya, berikut ini adalah gabungan <?php 16 dari tabel 215 dan tabel 216 dengan menggunakan ID integer. Ketika memesan dihapus, begitu juga filesorting.
1157215931614159$ mysqldump myTable > myTable-backup.sql 0165$ mysqldump myTable > myTable-backup.sql 2167$ mysqldump myTable > myTable-backup.sql 4159$ mysqldump myTable > myTable-backup.sql 6171$ mysqldump myTable > myTable-backup.sql 81017412176141781617618182<?php 0184<?php 2176<?php 4171
Ketika itu bisa dihindari, cobalah untuk tidak menggunakan <?php 21. Jika benar-benar harus digunakan, pesan pada kunci indeks saja.
3. <?php 11 pada kolom sementara
Jangan lakukan itu. Jika kamu perlu mengumpulkan hasilmu, lakukan itu dalam logika aplikasimu; jangan melakukan penyaringan atau memesan pada tabel sementara di dalam MySQL. Ini akan sangat membutuhkan banyak sumber daya.
4. Tidak menggunakan indeks <?php 50
Menggunakan kueri 220 adalah cara paling lambat untuk melakukan pencocokan teks lengkap pada datamu. Menerapkan pencarian teks lengkap dan menuai manfaat fitur cemerlang MySQL ini seperti:
1190219231944196$ mysqldump myTable > myTable-backup.sql 0194$ mysqldump myTable > myTable-backup.sql 2<?php 00$ mysqldump myTable > myTable-backup.sql 4<?php 02$ mysqldump myTable > myTable-backup.sql 6194$ mysqldump myTable > myTable-backup.sql 8171
5. Memilih sejumlah besar baris yang tidak perlu
Melupakan 221 pada permintaan dapat secara signifikan mengubah waktu pencarian atas dataset yang besar (lebih dari satu juta baris).
6. Join yang berlebih daripada hanya membuat tabel atau view gabungan
Ketika sampai tiga atau empat tingkat <?php 16, kamu harus bertanya pada diri sendiri: "Apakah saya melakukan ini dengan benar?" Jika kamu memiliki argumen yang masuk akal mengapa permintaan ini harus demikian, misalnya itu hanya muncul di layar admin dalam permintaan yang rendah atau dalam penggunaan tampilan statistik yang lebih besar yang dapat di-cache, kemudian diproses. Tetapi jika kamu perlu mengakses datamu secara sering dengan jumlah gabungan yang besar, kamu harus melihat bagaimana menggabungkan kolom ke dalam tabel baru mungkin lebih bermanfaat, atau membuat suatu view.
Kesimpulan
Kita telah membahas dasar-dasar optimalisasi dan tool yang kita miliki untuk melakukan pekerjaan. Kita harus mengaudit dengan membuat profil, dan menggunakan tool <?php 86 dan <?php 25 terlebih dahulu untuk melihat apa yang sebenarnya terjadi, dan kemudian dari sana kita dapat mendesain yang lebih baik.
Kita juga melihat beberapa contoh kasus dan perangkap umum yang dapat kamu hadapi ketika menggunakan MySQL. Menggunakan indeks mengisyaratkan kita dapat memastikan MySQL memilih indeks yang tepat untuk pekerjaan itu dan tidak bingung, terutama jika ada beberapa kueri di atas pada tabel yang sama. Untuk melanjutkan membaca tentang topik ini, periksa proyek Percona dan blog Kinerja MySQL untuk informasi lebih lanjut.