# Tuning Mysql

## Setup

### Enable Mysql Log Settings

# Enable general log
general_log: 1

# Enable slow query log
slow_query_log: 1
long_query_time: 0.3
slow_query_log_file: /var/lib/mysql/slowquery.log

# Enable not using indexes log
log_queries_not_using_indexes: 1

### Check settings

show warnings;

## Log File

### Watch the slow query log

vim /var/lib/mysql/slowquery.log

### Example Data

# Time: 2019-05-24T03:50:58.368497Z
# User@Host: web[web] @  [172.21.0.2]  Id:    15
# Query_time: 0.812303  Lock_time: 0.000081 Rows_sent: 1  Rows_examined: 9509357
SET timestamp=1558669858;
select * from user where id = 1;

### Description

Execute Time: # Time: 2019-05-24T03:50:58.368497Z
Execute Host: # User@Host: web[web] @  [172.21.0.2]  Id:    15
Execute Information: # Query_time: 1.812303  Lock_time: 0.000081 Rows_sent: 1  Rows_examined: 9509357
Execute Time: SET timestamp=1558669858;
SQL: SELECT * FROM user WHERE id = 100000;

## Tool: Mysql Dump Slow

### Format slow log

mysqldumpslow /var/lib/mysql/slowquery.log

### Example Data

Count: 2  Time=1.76s (3s)  Lock=0.00s (0s)  Rows=25.0 (50), nba[nba]@[172.21.0.2]
  SELECT * FROM user WHERE id = 100000;

### Description

Count: Number of Records
Time: Long Query Time (Sum of Long Query Time)
Lock: Lock Time (Sum of Lock Time)
Rows: Return Rows (Sum of Return Rows)

### How to Usage

mysqldumpslow --help

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

...

-s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
    al: average lock time
    ar: average rows sent
    at: average query time
     c: count
     l: lock time
     r: rows sent
     t: query time
-t NUM       just show the top n queries

...

### Example

# Just show the top 10 queries with sort by count
mysqldumpslow -s c -t 10 /var/lib/mysql/slowquery.log

# Just show the top 5 queries with sort by query time
mysqldumpslow -s at -t 5 /var/lib/mysql/slowquery.log