Ambil sedikit data dari mysql

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 mysqldump:

$ 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 where (dan penggunaan fungsi internal MySQL seperti IF dan DATE misalnya)
• menyortir dengan Order By
• 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 WHERE. 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 Memcached atau Redis.

Dengan Memcache kamu dapat dengan mudah menyimpan konten kuerimu dengan yang berikut, misalnya:

<?php
$cache = new Memcache;
$cache->connect('localhost',11211);
$cacheResult = $cache->get('key-name');
if($cacheResult){
    //.. no need to query
    $result = $cacheResult;
} else {
  //.. run your query
     $mysqli = mysqli('p:localhost','username','password','table'); //prepend p: to hostname for persistancy
     $sql = 'SELECT * FROM posts 
     		LEFT JOIN userInfo using (UID) WHERE posts.post_type = 'post' || posts.post_type = 'article' 
     ORDER BY column LIMIT 50';
     $result = $mysqli->query($sql);
     $memc->set('key-name', $result->fetch_array(), MEMCACHE_COMPRESSED,86400);
}

//Pass the $cacheResult to template
$template->assign('posts', $cacheResult);

?>

Sekarang contoh kueri LEFT JOIN 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 p: 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 blob, misalnya, sangat tidak disarankan; menyimpan nama file dalam kolom dengan tipe data varchar 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 FOREIGN KEY 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 ORDER BY di MySQL.
• Gunakan jenis indeks UNIQUE untuk dataset unik dan gunakan ON DUPLICATE KEY UPDATE untuk menyimpan datetime atau unix timestamp  yang diperbarui misalnya saat terakhir kali barisnya diperiksa.
• Gunakan tipe data INT 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:

1. Analisis (pencatatan log kueri yang lambat, pemeriksaan, dan analisa desain tabel)
2. Persyaratan kinerja (berapa banyak pengguna, apa permintaannya)
3. 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 EXPLAIN. Memanfaatkan ini dalam kuerimu sebelum SELECT akan memberimu output berikut:

mysql> EXPLAIN SELECT * FROM `wp_posts` WHERE `post_type`='post';
+----+-------------+----------+------+------------------+------------------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys    | key              | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+------------------+------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | wp_posts | ref  | type_status_date | type_status_date | 82      | const |    2 | Using where |
+----+-------------+----------+------+------------------+------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Kolom yang tercantum masing-masing menyimpan informasi yang berguna tentang kueri yang sedang dijalankan. Kolom yang perlu kamu perhatikan adalah possible_keys dan Extra.

possible_keys 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 Extra akan menunjukkan apakah WHERE atau ORDER BY bersyarat digunakan. Yang paling penting untuk diperhatikan adalah jika Using Filesort muncul. Perhatikan contoh berikut:

EXPLAIN SELECT main_text
FROM posts
WHERE user = 'myUsername' &&
status = '1' && (
    status_spam_user = 'no_spam'
    || (
        status_spam_user = 'neutral' &&
        status_spam_system = 'neutral'
    )
)
ORDER BY datum DESC
LIMIT 6430 , 10

Jenis kueri ini bisa masuk ke disk karena ada kondisi di mana, yang terjadi jika kita melihat pada EXPLAIN:

id  select_type     table       type    possible_keys                       key         key_len     ref     rows    Extra
1   SIMPLE          posts  ref     index_user,index_status    index_user  32          const   7800    Using where; Using filesort

Jadi kueri ini memiliki kemungkinan untuk menggunakan dua indeks dan saat ini sedang memukul disk karena Using fileort di Extra.

Apa yang Using Fileort 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 Extra penting lainnya yang harus dihindari adalah Using temporary, 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 Using Filesort kita harus memastikan MySQL menggunakan INDEX. Ini memiliki beberapa possible_keys 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 FORCE INDEX, USE INDEX dan IGNORE INDEX dalam kuerimu. Kamu dapat membaca lebih lanjut tentang petunjuk indeks di manual MySQL.

Untuk melihat kunci-kunci tabel, gunakan perintah SHOW INDEX.

Kamu dapat menentukan beberapa petunjuk untuk pengoptimal agar digunakan, misalnya:

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;
 ```
 
Running an `EXPLAIN` will show you which index was used in the final outcome. So to fix the previous example we will add the `USE INDEX` as so:

```sql
EXPLAIN SELECT main_text
FROM posts USE INDEX (index_user)
WHERE user = 'myUsername' &&
status = '1' && (
    status_spam_user = 'no_spam'
    || (
        status_spam_user = 'neutral' &&
        status_spam_system = 'neutral'
    )
)
ORDER BY datum DESC
LIMIT 6430 , 10

Sekarang MySQL memiliki index_status dari tabel untuk digunakan, kueri itu tetap.

id  select_type     table       type    possible_keys                       key         key_len     ref     rows    Extra
1   SIMPLE          posts  ref     index_user,index_status    index_user  32          const   7800    Using where

Bersama menjelaskan adalah kata kunci yang menggambarkan. Dengan DESCRIBE kamu dapat melihat informasi tabel sebagai berikut:

mysql> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+

Menambahkan Indeks

Kamu membuat indeks di MySQL dengan sintaks CREATE INDEX. Ada beberapa rasa dari indeks. FULLTEXT digunakan untuk pencarian teks lengkap, dan kemudian ada jenis UNIQUE untuk memastikan data tetap unik.

Untuk menambahkan indeks ke tabelmu, gunakan sintaks berikut ini misalnya:

mysql> CREATE INDEX idx_start_of_username ON `users` (username(10));

Ini akan membuat indeks pada tabel users, yang akan menggunakan 10 huruf pertama dari kolom username, yang merupakan tipe data varchar.

Dalam hal ini, pencarian apa pun yang membutuhkan WHERE 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 EXPLAIN.

Sebagai contoh, jika kita perlu mereferensikan dua kolom di dalam WHERE kita bersyarat, kunci komposit akan ideal.

mysql> CREATE INDEX idx_composite ON users (username, active);

Di sini kunci ini dibuat pada kolom username dari contoh sebelumnya dan kolom active, tipe data ENUM yang menandakan apakah akun penggunanya aktif. Jadi sekarang ketika kueri data untuk WHERE username valid dan akun active=1, 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 set profiling=1, dan kemudian mengeksekusi kuerimu dan melihat hasil dari show profiles.

Dengan PDO, ini adalah cuplikan kode yang tidak hanya itu:

$db->query('set profiling=1'); 
$db->query('select headline, body, tags from posts');
$rs = $db->query('show profiles');
$db->query('set profiling=0'); // Disable profiling after the query has been run

$records = $rs->fetchAll(PDO::FETCH_ASSOC); // Get the results from profiling

$errmsg = $rs->errorInfo()[2]; //Catch any errors here

Jika kamu tidak menggunakan PDO, hal yang sama dapat dilakukan dengan mysqli sebagai berikut:

$db = new mysqli($host,$username,$password,$dbname);

$db->query('set profiling=1');
$db->query('select headline, body, tags from posts');
if ($result = $db->query("SHOW profiles", MYSQLI_USE_RESULT)) {
    while ($row = $result->fetch_row()) {
        var_dump($row);
    }
    $result->close();
}

if ($result = $db->query("show profile for query 1", MYSQLI_USE_RESULT)) {
    while ($row = $result->fetch_row()) {
        var_dump($row);
    }
    $result->close();
}

$db->query('set profiling=0');

Ini akan mengembalikan kepadamu data profil, yang akan mencakup waktu eksekusi dalam nilai kedua dari array asosiatif:

array(3) { 
	[0]=>  string(1) "1" 
    [1]=>  string(10) "0.00024300" 
    [2]=>  string(17) "select headline, body, tags from posts" 
    }

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 DEBUG di basis data aplikasi database abstraction layer / framework database driver, dan kemudian kamu dapat mulai mengaudit dengan mengaktifkan kasus profil dan mengeluarkan hasilnya dengan var_dump / print_r. 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 log_slow_queries dan dapat menentukan file dengan global slow_query_log_file. Ini dapat dilakukan dalam prompt runtime sebagai berikut:

set global log_slow_queries = 1;
set global slow_query_log_file = /dev/slow_query.log;

Kamu dapat mengatur ini secara tetap di file konfigurasi /etc/my.cnf atau my.ini untuk server-mu.

log_slow_queries = 1; 
slow_query_log_file = /dev/slow_query.log;

Setelah melakukan perubahan ini, kamu harus me-restart server MySQL, misalnya. service mysql restart pada sistem Linux.

Di MySQL 5.6.1 yang lebih baru, log_slow_queries tidak digunakan lagi dan slow_query_log digunakan sebagai gantinya. Mengaktifkan TABLE sebagai tipe output memungkinkan pengalaman debugging yang jauh lebih baik dan dapat dilakukan sebagai berikut di MySQL 5.6.1 dan yang lebih baru:

log_output = TABLE;
log_queries_not_using_indexes = 1;
long_query_time = 1

long_query_time 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 TABEL.

Ini akan masuk ke tabel mysql.slow_log dan mysql.general_log dalam MySQL.

Untuk menonaktifkan pencatatan log, set log_output ke NONE.

log_queries_not_using_indexes 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 SHOW PROCESSLIST atau melalui tabel information_schema.PROCESSLIST secara langsung, misal. select * from information_schema.PROCESSLIST;.

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 pt-query-digest adalah tool baris perintah untuk menganalisis log kueri, daftar proses atau tcpdumps.

Kamu dapat menggunakan pt-query-digest dengan cara-cara berikut:

Menganalisis file *.log (dikeluarkan dari pencatatan log kuerimu yang lambat misalnya):

$ pt-query-digest slow.log

Laporkan kueri yang paling lambat dari host1 secara real time (sangat berguna!):

$ pt-query-digest --processlist h=host1

Gunakan tcpdump untuk melaporkan kueri yang paling lambat dari data protokol MySQL:

$ tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt

$ pt-query-digest --type tcpdump mysql.tcp.txt

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:

$ pt-query-digest --review h=host2 --no-report slow.log

Untuk mempelajari cara menggunakan tool pt-query-digest 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 RRDTool seperti Cacti 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 ab. Ketika kamu menjalankan benchmark-mu, kamu harusnya melihat SHOW PROCESSLIST, dan juga memungkinkan logging dan memantau dengan tool-tool sistem seperti top, free dan iostat. 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 ab, pastikan kamu memiliki paket yang diinstal, misalnya:

#centos users
$ sudo yum install ab
#debian / ubuntu users
$ sudo apt-get install ab

Sekarang kamu dapat memulai dengan menguji aplikasimu, misalnya:

$ ab -k -c 350 -n 20000 my-domain.com/

-k berarti untuk keep-alive koneksi, dan -c 350 adalah jumlah koneksi bersamaan, yaitu jumlah orang/klien yang akan mengunjungi situs sekaligus. Akhirnya -n 20000 adalah jumlah permintaan yang akan dilakukan my-domain.com.

Jadi dengan menjalankan perintah di atas, kamu akan menekan http://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 Memcache 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 Memcached 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 long_query_time jika kamu menggunakan metode kueri log. Jika tidak, setelah memeriksa semua kuerimu dengan pembuatan profil (se profilling=1), 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. ORDER BY menggunakan fileort.

mysql> explain select * from products where products.price > 4 and products.stock > 0 order by name;
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | products | ALL  | NULL          | NULL | NULL    | NULL | 1142 | Using where; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+

Menghindari file pada ini tidak mungkin karena ORDER BY name. Tidak peduli apa permutasi indeks yang kamu gunakan, yang terbaik yang akan kamu dapatkan adalah Using where; Using Filesort di kolom Extra mu Untuk mengoptimalkan ini, simpan hasilnya di Memcache, atau lakukan pemesanan di lapisan logika aplikasimu.

2. Menggunakan ORDER BY di WHERE dan LEFT JOIN

ORDER BY memiliki jumlah kueri signifikan pada kueri. Misalnya, berikut ini adalah gabungan LEFT JOIN dari tabel products dan tabel categories dengan menggunakan ID integer. Ketika memesan dihapus, begitu juga filesorting.

mysql> explain select products.* from products use index (idx_price) left join categories using (catID) where products.price > 4 and catID = 4 ORDER BY stock ASC limit 10;
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-----------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra                       |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | products   | ALL   | idx_price     | NULL    | NULL    | NULL  |  986 | Using where; Using filesort |
|  1 | SIMPLE      | categories | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index                 |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-----------------------------+
2 rows in set (0.00 sec)

mysql> explain select products.* from products use index (idx_price) left join categories using (catID) where products.price > 4 and catID = 4;
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | products   | ALL   | idx_price     | NULL    | NULL    | NULL  |  986 | Using where |
|  1 | SIMPLE      | categories | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

Ketika itu bisa dihindari, cobalah untuk tidak menggunakan ORDER BY. Jika benar-benar harus digunakan, pesan pada kunci indeks saja.

3. Order By 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 FULLTEXT

Menggunakan kueri LIKE adalah cara paling lambat untuk melakukan pencocokan teks lengkap pada datamu. Menerapkan pencarian teks lengkap dan menuai manfaat fitur cemerlang MySQL ini seperti:

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

5. Memilih sejumlah besar baris yang tidak perlu

Melupakan LIMIT 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 LEFT JOIN, 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 pt-query-digest dan EXPLAIN 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.