---
# System prepended metadata

title: Tuning Mysql

---

# 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
```
