Cara menggunakan cara melihat log/mysql

Sometimes you need to monitor all the queries sent to the MySQL server during a period of time. MySQL Server provides a way to show the general query log and the slow query log, if those logs are enabled. Possible destinations for log entries are log files or the general_log and slow_log tables in the mysql system database.

Table of Contents

• Jenis Query
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Fungsi Slow Query Log
• Cara Mengaktifkan Slow Query pada Server
• Analisis Slow Query
• Contoh Slow Queries
• Masalah Pada Saat Mengaktifkan Slow Query Log

In this post, we are going to enable general log and slow query log and show them in tables in MySQL.

1. First, check if you already have the two tables slow_log and general_log existing in the mysql database.

If not, you have to create them, note that you have to create in the mysql database.

• To create the general_log table:

  CREATE TABLE `general_log` (
   `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `command_type` varchar(64) NOT NULL,
   `argument` mediumtext NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

• And the slow_log table

  CREATE TABLE `slow_log` (
   `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `query_time` time NOT NULL,
   `lock_time` time NOT NULL,
   `rows_sent` int(11) NOT NULL,
   `rows_examined` int(11) NOT NULL,
   `db` varchar(512) NOT NULL,
   `last_insert_id` int(11) NOT NULL,
   `insert_id` int(11) NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `sql_text` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

The slow query log consists of SQL statements that take more than long_query_time seconds to execute and require at leastmin_examined_row_limit rows to be examined. The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization.

The minimum and default values of long_query_time are 0 and 10, respectively. The value can be specified to a resolution of microseconds.

2. Enable Query logging on the database

SET global general_log = 1;
SET global log_output = 'table';

3. Now you can view the log by running this query:

SELECT
    *
FROM
    mysql.general_log;

4. If you want to disable query logging on the database, run this query:

SET global general_log = 0;

Using TablePlus, you can enable the console log from the GUI.

To turn on/off the log, click on the console log button near the top right, or use shortcut key Cmd + Shift + C.

You can also choose to log data queries, meta queries, or all queries.

New to TablePlus? It’s a modern, native tool with an elegant GUI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.

Download TablePlus here. It’s free anyway!

Tentu kita tahu bahwa penggunaan query ini sangat penting jika Anda menggunakan database yang dihubungkan ke website. Fungsi query ini akan menampilkan data dari database dengan cara melakukan penarikan data menggunakan script php. Sedangkan Slow Query biasanya ditampilkan jika ada proses query yang lambat.

Jenis Query

Query sendiri ada 3 macam yang umumnya digunakan untuk memanipulasi ataupun mendefinisikan pada sebuah database.

1. Data Definition Language (DDL)

   Bahasa atau perintah pada SQL yang digunakan untuk mendefinisikan data dengan cara membuat, menghapus dan merubah data. Ada beberapa sintaks pada DDL ini, seperti CREATE, DROP dan ALTER.

2. Data Manipulation Language (DML)

   Bahasa atau perintah pada SQL yang digunakan untuk menambah atau mengubah nilai (value) pada suatu table di database. Ada beberapa sintaks pada DML ini, seperti INSERT, UPDATE, DELETE dan SELECT.

3. Data Control Language (DCL)

   Bahasa atau perintah pada SQL yang digunakan untuk melakukan kontrol pada database. Ada beberapa sintaks pada DCL ini, seperti GRANT dan REVOKE.

Baca Juga: Cara Instalasi dan Setup Database Odoo di Ubuntu 16.04

Fungsi Slow Query Log

Slow Query adalah suatu pernyataan SQL yang membutuhkan lebih long_query_time untuk dapat dieksekusi. Log ini juga dapat digunakan untuk menemukan query-query lambat supaya dapat dioptimasi.

Untuk mengetahui ada berapa banyak slow queries dapat gunakan command dibawah ini.

$ mysqladmin status
Uptime: 703 Threads: 10 Questions: 24010 Slow queries: 27 Opens: 358 Flush tables: 1 Open tables: 352 Queries per second avg: 34.15

Cara Mengaktifkan Slow Query pada Server

Edit file my.cnf dengan menggunakan command dibawah ini.

$ nano /etc/my.cnf

Tambahkan beberapa script berikut dibawah [mysqld] section.

slow_query_log = 1 
long_query_time = 5
slow_query_log_file = /var/log/mysql/slow-query.log
log_queries_not_using_indexes

Pastikan Anda sudah memiliki directory “mysql” di dalam /var/log/, jika tidak memiliki maka perlu membuatnya terlebih dahulu.

Note:

Slow_query_log = 1. Artinya adalah log slow query ini enabled.

Long_query_time = 5. Artinya adalah waktu yang dibutuhkan oleh Query SQL untuk melakukan eksekusi dalam hitungan detik.

Slow_query_log_file = /var/log/mysql/slow-query.log. Artinya adalah path atau lokasi penempatan log slow query tersebut.

Log_queries_not_using_indexes. Artinya adalah untuk memberitahu MySQL untuk melakukan log ke semua query yang tidak mengunakan indeks untuk membatasi jumlah baris/row yang discan.

Buat file slow-query.log di dalam directory /var/log/mysql/ dan rubah ownership menggunakan mysql dengan command dibawah ini.

$ cd /var/log/mysql/
$ touch slow-query.log
$ chown mysql:mysql slow-query.log

Restart MySQL Service dengan command dibawah ini:

$ service mysql restart

Atau gunakan command dibawah ini jika menggunakan WHM – cPanel:

$ /scripts/restartsrv_mysql

Setelah direstart, maka slow query akan otomatis tercatat (logging) di file slow-query.log. Gunakan command dibawah ini untuk memantau atau melihat apa saja yang di-log.

$ tail -f /var/log/mysql/slow-query.log

Baca Juga: Cara Membuat Database di XAMPP

Analisis Slow Query

Untuk melakukan analisis slow query dapat menggunakan command mysqldumpslow.

$ mysqldumpslow -a /var/log/slow-query.log

Contoh Slow Queries

Di bawah ini adalah contoh slow queries yang sudah ter-log di dalam file slow-query.log.

Baca Juga: Panduan Lengkap Cara Membuat Database WordPress di MYSQL

Masalah Pada Saat Mengaktifkan Slow Query Log

Slow query lognya tidak running, dimana tidak ada query yang ter-log. Lalu Anda melakukan restart mysql dan ada error dibawah ini.

[ERROR] Could not use /var/log/mysql/slow-query.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

Cara menyelesaikan masalahnya adalah dengan cara melihat permission pada directory /mysql/ dan file slow-query.log. Pastikan directory menggunakan permission 0755 dan file 0644.

Kemudian restart mysql nya kembali dan cek, pada file lognya, seharusnya sudah mulai di-log slow querynya.

Kalau semua slow queries-nya sudah di optimasi dan sudah tidak menampilkan slow queries pada mysqladmin status, maka log-nya perlu di disabled kembali. Dengan merubah nilai 1 menjadi 0 pada slow_query_log di file my.cnf.

Kesimpulan

Dengan menggunakan slow query log ini dapat memudahkan dalam melakukan optimasi, karena akan dilihatkan query apa saja yang lambat diproses.

Optimasi dilakukan dengan banyak cara, ada beberapa aplikasi berbayar yang memberikan layanan untuk proses ini. Seperti contohnya adalah SolarWinds, RedGate dan everSQL.

Jangan sungkan untuk meninggalkan ide-ide topik yang ingin Anda baca di blog Dewaweb. Semoga artikel ini membantu!