Cara memeriksa kinerja kueri di meja kerja mysql

Menurut hasil dari , hampir setengah (46%) responden mengatakan bahwa mereka menggunakan MySQL, menjadikannya teknologi basis data yang paling banyak diadopsi oleh pengembang saat ini. Popularitas ini sebagian besar disebabkan oleh fitur unik MySQL yang membantu memenuhi kebutuhan aplikasi modern dengan mudah, mulai dari proyek perangkat lunak kecil hingga sistem bisnis penting.

Karena kasus penggunaan terus berkembang menjadi lebih kompleks dan menggunakan volume data yang lebih besar, pemantauan kinerja memainkan peran yang lebih penting dalam mengelola database MySQL. Mengawasi kinerja membantu memastikan bahwa aplikasi Anda memanfaatkan sepenuhnya fleksibilitas, skalabilitas, ketersediaan, keandalan, dan keamanan tingkat tinggi yang ditawarkan MySQL

Artikel ini memberikan pengantar dan panduan terperinci untuk pemantauan kinerja MySQL. Ini menjelaskan konsep utama tentang pemantauan database MySQL, metrik kinerja penting untuk dilacak, serta langkah dan alat untuk disertakan dalam strategi pemantauan yang efektif

Alat seperti MetricFire membuat pemantauan kinerja MySQL lebih sederhana dan lebih efisien. MetricFire adalah platform pemantauan dan pengamatan berfitur lengkap yang ideal untuk melacak kinerja database MySQL. Dengan MetricFire, Anda tidak perlu mengelola dan menyimpan sendiri metrik deret waktu. Sebagai gantinya, MetricFire menarik data kinerja ke dalam basis data skala waktu yang dihosting dan menggunakannya untuk menghasilkan laporan dan dasbor yang dapat disesuaikan

Cari tahu lebih lanjut tentang bagaimana MetricFire meningkatkan proses pemantauan MySQL Anda dengan demo cepat, atau mulai uji coba gratis MetricFire hari ini

Memahami kinerja MySQL

MySQL berfungsi sebagai tulang punggung banyak aplikasi web, seluler, desktop, dan cloud saat ini. Itu dibangun dengan fokus yang kuat pada kecepatan dan keandalan sejak awal. Itulah mengapa salah satu nilai jual utama MySQL adalah sistem database yang berperforma tinggi

Tinjauan singkat MySQL

Manual Referensi MySQL resmi menggambarkan MySQL sebagai sistem manajemen basis data relasional (RDBMS) sumber terbuka multi-pengguna dan multi-utas yang cepat dan stabil. Pertama kali dirilis pada tahun 1995, MySQL kini hadir dalam dua edisi terpisah. Server Komunitas MySQL yang tersedia secara gratis dan MySQL Enterprise Edition komersial

MySQL mendukung fitur-fitur penting seperti arsitektur fleksibel (open-source, multi-threaded, mesin penyimpanan pluggable), kompatibilitas dengan standar ANSI SQL, ketersediaan dan replikasi yang tinggi (mesin replikasi bawaan, ID transaksi global, sharding, dll. ), OLTP dan transaksi (kepatuhan ACID, integritas referensial, penguncian tingkat baris, dll. ), keamanan basis data, dan kinerja tinggi

Fitur-fitur ini menjadikan MySQL back-end database yang ideal untuk aplikasi yang mengandalkan operasi terdistribusi, situs web, aplikasi web yang perlu dikirimkan dengan cepat, dan sistem yang perlu diskalakan dengan cepat sesuai permintaan

Fitur MySQL yang memengaruhi kinerja database

Dibandingkan dengan database relasional berfitur lengkap lainnya seperti PostgreSQL, MySQL menawarkan serangkaian fitur yang relatif lebih ringan, sehingga mampu memberikan kecepatan dan keandalan yang lebih baik.

Spesifikasi teknis MySQL mencakup item berikut yang mendorong kinerja database

• Grup sumber daya (digunakan untuk menetapkan utas dan mengalokasikan sumber daya)
• Mempartisi
• Dioptimalkan untuk konkurensi tinggi
• Dioptimalkan untuk hanya-baca
• Dioptimalkan untuk SSD
• Berbagai jenis indeks (B-tree, R-tree, hash, teks lengkap, dll. )
• Kumpulan utas sisi server
• Caching utas koneksi
• Diagnostik dan penelusuran SQL
• Skema kinerja dan skema sistem

Dengan fitur bawaan yang berfokus pada kinerja ini, MySQL terus menikmati reputasi sebagai solusi database yang cepat dan andal, bahkan saat opsi RDBMS lainnya mulai menyamai kinerja MySQL di bawah pengujian benchmark yang lebih baru.

Mengapa pemantauan kinerja MySQL

Basis data Anda membentuk lapisan kritis dalam tumpukan aplikasi. Segala sesuatu di aplikasi atau situs web Anda yang dibuat di atas lapisan tersebut akan bergantung pada seberapa baik kinerja database

Memantau kinerja database membantu Anda menangani kemungkinan masalah dalam aplikasi Anda terlebih dahulu sebelum masalah tersebut memengaruhi pengguna akhir. Namun selain membantu Anda mencegah dan memperbaiki masalah, pemantauan data juga memungkinkan Anda

• Identifikasi peluang untuk pengoptimalan basis data (seperti menulis ulang kueri yang kurang optimal)
• Tentukan dampak perubahan (seperti migrasi data, perubahan definisi skema, modifikasi konfigurasi, pengiriman fitur baru, dll. )
• Menyediakan server dan menghitung sumber daya sesuai kebutuhan aktual (penskalaan horizontal)
• Tentukan potensi kerentanan keamanan dan terapkan langkah-langkah keamanan yang memadai
• Temukan area untuk pertumbuhan dan peningkatan pengalaman pengguna

Singkatnya, memantau kinerja MySQL melintasi semua level kritis aplikasi atau situs web Anda, bukan hanya database itu sendiri

Metrik kinerja database MySQL utama

Sebelum kita beralih ke metrik kinerja MySQL yang berbeda untuk dilacak, mari kita buka dulu apa artinya mengukur kinerja database. Lagi pula, pada dasarnya itulah yang kami lakukan saat memantau metrik. kami mengukur aktivitas tertentu dan mencatat pengukuran ini

Mengukur kinerja database MySQL

Secara umum, metrik kinerja basis data terbagi menjadi dua jenis utama. metrik beban kerja dan metrik sumber daya. Metrik beban kerja mengukur keseluruhan kinerja database dengan mengukur jumlah pekerjaan atau keluaran yang dapat dihasilkannya dalam periode tertentu. Metrik sumber daya, sementara itu, mengukur berapa banyak perangkat keras, perangkat lunak, dan sumber daya jaringan yang digunakan database untuk berfungsi

Metrik beban kerja dan sumber daya selanjutnya terdiri dari subkategori kinerja yang berbeda. Untuk sebagian besar kasus penggunaan, kategori yang paling penting adalah

• Hasil. Metrik beban kerja yang mengukur seberapa banyak pekerjaan yang dilakukan database dalam interval waktu tertentu (mis. g. jumlah kueri, transaksi, baca, tulis, dll. )
• Latensi (Waktu Eksekusi). Metrik beban kerja yang mengukur waktu yang dibutuhkan database untuk melakukan unit kerja tertentu (mis. g. , waktu proses kueri)
• Koneksi (Konkurensi). Metrik sumber daya yang memberi tahu Anda jumlah kueri yang berjalan secara bersamaan dan tidak dapat diselesaikan tepat waktu (mis. g. , jumlah utas yang terhubung, utas yang berjalan, koneksi yang dibatalkan, dll. )
• Penyangga (Pemanfaatan). Metrik sumber daya yang mengukur penggunaan buffer (cache) (mis. g. , penggunaan buffer pool)

Nanti, kita akan melihat beberapa statistik utama untuk dipantau di bawah masing-masing kategori ini

Metrik pemantauan kinerja MySQL dapat ditanyakan dari tiga lokasi berbeda. variabel status server, skema sistem, dan skema kinerja

• Variabel status server. Ini adalah "penghitung" internal yang dikelola MySQL untuk memberikan informasi tentang operasinya. Variabel ini termasuk Questions, Slow_queries, Threads_running, Com_select, dan lainnya (lihat daftar lengkap variabel status server di sini)
• Skema kinerja. Ini adalah fitur MySQL untuk. Metrik kinerja diperoleh dengan mengkueri satu atau beberapa tabel dalam database skema_kinerja seperti tabel events_statements_summary_by_digest
• Skema sistem. Skema sys MySQL meningkatkan kegunaan skema kinerja (yang, dengan lusinan tabel, terkadang membosankan untuk dikerjakan). Skema mencakup tampilan, fungsi, dan prosedur yang membuatnya lebih nyaman untuk menganalisis data yang dikumpulkan oleh skema kinerja

Kami akan segera melihat contoh penggunaan untuk masing-masing dari ketiga fitur ini

Memilih metrik kinerja mana yang akan dipantau

Dengan potensi ratusan metrik kinerja yang tersedia untuk dipantau untuk database MySQL Anda, statistik mana yang harus Anda awasi?

• Hasil. -Pertanyaan. jumlah pernyataan yang diprakarsai oleh klien yang dijalankan oleh server

• Kueri. jumlah pernyataan yang dieksekusi oleh server (termasuk pernyataan yang dikirim klien dan pernyataan yang dieksekusi dalam prosedur tersimpan)
• Com_select. menghitung berapa banyak pernyataan SELECT yang dieksekusi, menunjukkan tingkat aktivitas hanya-baca
• Com_insert, Com_update, dan Com_delete. menunjukkan tingkat operasi tulis, biasanya diringkas menjadi satu variabel

• Slow_Queries. jumlah kueri yang melebihi long_query_time detik untuk dieksekusi
• Waktu menjalankan kueri. statistik tentang berapa lama kueri dieksekusi, tersedia dalam skema kinerja

• Aborted_connects. menghitung berapa banyak upaya gagal untuk terhubung ke server MySQL
• Threads_connected. jumlah koneksi yang saat ini terbuka
• Threads_running. menghitung berapa banyak utas yang tidak tidur

• Statistik yang dihasilkan dari menjalankan pernyataan SHOW ENGINE INNODB STATUS

Perhatikan bahwa, kecuali dinyatakan lain, metrik di atas dapat diperoleh dengan menanyakan variabel status server bawaan. Deskripsi juga diambil dari dokumentasi resmi MySQL

Mengumpulkan dan memantau metrik kinerja MySQL

Di bagian sebelumnya, kita telah melihat jenis metrik performa yang harus dipantau di database MySQL Anda. Kami sekarang akan melihat sejumlah cara dan alat untuk mengumpulkan data yang kami butuhkan

Variabel status server

Kami mengetahui bahwa MySQL melacak "penghitung" yang disebut variabel status server. Variabel status server memberi Anda informasi tentang operasi MySQL. Jumlah variabel status server bervariasi tergantung pada versi MySQL Server yang digunakan

Variabel-variabel ini dapat diakses menggunakan SHOW [GLOBAL. SESI] Pernyataan STATUS. Dengan GLOBAL, pernyataan mengembalikan nilai agregat di semua koneksi, sementara SESSION membatasi nilai hanya untuk koneksi saat ini

Misalnya, pernyataan berikut menampilkan semua variabel status server dan nilainya di semua koneksi

mysql> SHOW GLOBAL STATUS;

+-------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name                                         | Value                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Aborted_clients                                       | 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Aborted_connects                                      | 3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Acl_cache_items_count                                 | 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Binlog_cache_disk_use                                 | 11                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| Binlog_cache_use                                      | 60                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| Binlog_stmt_cache_disk_use                            | 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Binlog_stmt_cache_use                                 | 8                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Bytes_received                                        | 3843055                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| Bytes_sent                                            | 214977                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
...
| Threads_cached                                        | 1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Threads_connected                                     | 3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Threads_created                                       | 4                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Threads_running                                       | 2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Uptime                                                | 6086                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| Uptime_since_flush_status                             | 6086                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+-------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
463 rows in set (0.0047 sec)

Anda juga dapat melihat variabel status server tunggal dengan

mysql> SHOW STATUS LIKE '%Com_select%';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 10    |
+---------------+-------+
1 row in set (0.0130 sec)

Bagian sebelumnya menyebutkan beberapa variabel status server yang penting seperti Questions, Queries, Com_insert, Com_update, dll. Untuk daftar lengkap variabel yang tersedia, periksa dokumentasi untuk versi Server MySQL Anda

Skema kinerja

Skema kinerja MySQL memantau eksekusi server pada tingkat kueri individual. Di Server MySQL yang mendukung skema kinerja, tersedia sebagai database bernama performance_schema yang berisi tabel yang dapat ditanyakan dengan pernyataan SELECT biasa. Sebelum Anda dapat mulai menanyakan skema kinerja, pertama-tama Anda harus memastikan bahwa skema tersebut dipasang dan diaktifkan dengan benar

Database performance_schema mengelompokkan tabelnya berdasarkan jenis informasi yang dikandungnya. Peristiwa terkini, riwayat dan ringkasan peristiwa, instance objek, dan informasi penyiapan (konfigurasi). Untuk melihat semua tabel yang tersedia di performance_schema, jalankan pernyataan berikut

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_SCHEMA = 'performance_schema';

+------------------------------------------------------+
| TABLE_NAME                                           |
+------------------------------------------------------+
| accounts                                             |
| cond_instances                                       |
| data_lock_waits                                      |
| data_locks                                           |
| events_errors_summary_by_account_by_error            |
| events_errors_summary_by_host_by_error               |
| events_errors_summary_by_thread_by_error             |
...
| table_handles                                        |
| table_io_waits_summary_by_index_usage                |
| table_io_waits_summary_by_table                      |
| table_lock_waits_summary_by_table                    |
| threads                                              |
| user_defined_functions                               |
| user_variables_by_thread                             |
| users                                                |
| variables_by_thread                                  |
| variables_info                                       |
+------------------------------------------------------+
103 rows in set (0.1040 sec)

Sebelumnya, kami mencatat bahwa statistik kinerja utama dapat ditemukan dengan menanyakan sejumlah tabel di database performance_schema, khususnya tabel events_statements_summary_by_digest

Pernyataan di bawah mengembalikan kueri dengan waktu eksekusi terlama

mysql> SELECT digest_text, avg_timer_wait
       FROM performance_schema.events_statements_summary_by_digest
       ORDER BY avg_timer_wait DESC
       LIMIT 1;

+-----------------------------------------------+----------------+
| digest_text                                   | avg_timer_wait |
+-----------------------------------------------+----------------+
| INSERT INTO `rental` VALUES (...) /* , .. */ |   407201600000 |
+-----------------------------------------------+----------------+
1 row in set (0.0052 sec)

Harap perhatikan bahwa Anda dapat menghindari penggunaan nama skema performance_schema dalam pernyataan di atas dengan menyetel performance_schema sebagai database saat ini dengan USE performance_schema

Skema sistem

Bahkan dengan contoh kami yang cukup sederhana di atas, tidak sulit untuk membayangkan bahwa menanyakan skema kinerja dapat menjadi membosankan dengan kasus penggunaan yang lebih kompleks. Itu sebabnya MySQL memperkenalkan skema sys sebagai fitur yang dimulai pada versi 5. 7. 7

Skema sys menyediakan tampilan, prosedur tersimpan, dan fungsi tersimpan yang memudahkan untuk mengakses informasi yang terdapat dalam tabel database performance_schema

Menggunakan contoh dari dokumentasi skema sys resmi, kita melihat ini beraksi dengan tampilan host_summary_by_file_io yang merangkum file I/O yang dikelompokkan berdasarkan host dan menampilkan nilai latensi yang lebih mudah dibaca

mysql> SELECT * FROM sys.host_summary_by_file_io;

+------------+-------+------------+
| host       | ios   | io_latency |
+------------+-------+------------+
| background | 12167 | 1.48 s     |
| localhost  |  1694 | 427.99 ms  |
+------------+-------+------------+
2 rows in set (0.0049 sec)

Meja Kerja MySQL

Pendekatan yang lebih nyaman untuk menjelajahi dan menyelidiki kinerja database yang juga didukung oleh MySQL adalah dengan menggunakan Workbench GUI. MySQL Workbench menyediakan cara visual untuk merancang, mengelola, dan memantau database MySQL

Selain dasbor bawaan yang menampilkan statistik tingkat tinggi, MySQL Workbench juga memungkinkan Anda dengan cepat menelusuri metrik kinerja yang terdapat dalam skema sys

Fitur pemantauan yang disediakan MySQL di luar kotak bekerja dengan baik untuk pemeriksaan satu kali yang cepat. Tetapi ketika Anda membutuhkan pemantauan real-time berkelanjutan dari basis data produksi, fitur bawaan dan fungsionalitas dasar ini tidak akan cukup.

Di sinilah Anda perlu menggunakan alat pemantauan basis data berfitur lengkap. Platform ini terintegrasi dengan MySQL untuk mengelola pengumpulan, penyimpanan, dan pengambilan metrik kinerja, memungkinkan Anda melakukannya

• Offload tugas memantau dan menyimpan data kinerja ke layanan eksternal sehingga sumber daya database Anda tetap fokus secara eksklusif untuk melayani aplikasi
• Menganalisis dan memvisualisasikan kinerja database pada berbagai tingkat perincian
• Siapkan lansiran dan pemberitahuan untuk berbagai peristiwa dan pemicu
• Lacak performa secara terus-menerus bahkan di lingkungan produksi bervolume tinggi

MetricFire adalah contoh platform pemantauan yang tangguh dan berfitur lengkap yang membantu Anda melacak kinerja database secara terus-menerus, secara waktu nyata. Sebagai alat eksternal, MetricFire mudah diatur dan diintegrasikan dengan Server MySQL Anda. Setelah terpasang, MetricFire melakukan kerja keras mengumpulkan dan menyimpan data deret waktu, memberi Anda visualisasi/pelaporan khusus, peringatan proaktif, dan dukungan yang dikelola sepenuhnya

Salah satu fitur utama MetricFire yang menyederhanakan pemantauan MySQL adalah dukungan out-of-the-box untuk Hosted Graphite Agent (HG-Agent). Agen Grafit yang Dihosting adalah aplikasi pemantauan yang dibangun di atas alat pengumpulan metrik Diamond dan manajer proses Supervisor. Ini hanya memerlukan proses instalasi cepat untuk bangun dan berjalan, dan kemudian secara otomatis memantau server yang sedang berjalan tanpa perlu konfigurasi atau pengaturan tambahan. Setelah diinstal, HG-Agent mengumpulkan metrik sistem dan menerbitkannya ke Hosted Graphite, tempat data deret waktu disimpan, diproses, dan divisualisasikan. Dengan klien Hosted Graphite Agent dari MetricFire, pemasangan dan penyiapan HG-Agent menjadi lebih cepat

Pelajari lebih lanjut dengan memesan demo hari ini, atau mulai gunakan MetricFire secara gratis

Kesimpulan

Dalam posting ini, kami membahas hal-hal penting yang perlu Anda ketahui untuk memantau database MySQL secara efektif. Kami mempelajari fitur apa yang membuat MySQL menjadi RDBMS yang cepat dan andal, bagaimana kinerja database diukur dalam MySQL, metrik kinerja mana yang harus dilacak, dan cara menggunakan berbagai fitur dan alat untuk memantau kinerja

Dengan mempertimbangkan konsep ini, Anda sekarang siap menyusun strategi pemantauan yang solid. Rencana Anda harus mencakup pemilihan alat pemantauan yang tepat yang memenuhi kebutuhan Anda. Dengan menggabungkan Graphite yang dihosting, dan Grafana dalam platform pemantauan berfitur lengkap, MetricFire dapat menjadi alat yang tepat untuk kebutuhan pemantauan MySQL Anda. Pesan demo untuk mengetahui lebih lanjut atau daftar uji coba gratis untuk memulai MetricFire hari ini

Bagaimana cara memeriksa waktu eksekusi kueri di MySQL Workbench?

Bagaimana cara memeriksa kinerja database MySQL?

Bagaimana saya bisa melihat kueri MySQL mana yang berjalan lambat?

Bagaimana cara memeriksa kinerja prosedur tersimpan di MySQL Workbench?