# Tools
良さそうなツール
## インフラ担当
* アクセス解析　alp https://github.com/tkuchiki/alp
* リソースモニタリング netdata
* netdataのconfigをいじらないといけない https://gato.intaa.net/archives/10701
* ログ解析 alp https://nishinatoshiharu.com/install-alp-to-nginx/

### netdataのinstall

sudo apt-get install netdata -y
systemctl restart netdata

### alp

wget https://github.com/tkuchiki/alp/releases/download/v1.0.1/alp_linux_amd64.zip
apt install unzip

## DB

+-----------------------+
| Tables_in_isucari     |
+-----------------------+
| categories            |
| configs               |
| items                 |
| shippings             |
| transaction_evidences |
| users                 |
+-----------------------+

### categories

mysql> desc categories;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| parent_id     | int(10) unsigned | NO   |     | NULL    |                |
| category_name | varchar(191)     | NO   |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+

### configs

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(191) | NO   | PRI | NULL    |       |
| val   | varchar(255) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

### items

mysql> desc items;
+-------------+------------------------------------------------------+------+-----+-------------------+----------------+
| Field       | Type                                                 | Null | Key | Default           | Extra          |
+-------------+------------------------------------------------------+------+-----+-------------------+----------------+
| id          | bigint(20)                                           | NO   | PRI | NULL              | auto_increment |
| seller_id   | bigint(20)                                           | NO   |     | NULL              |                |
| buyer_id    | bigint(20)                                           | NO   |     | 0                 |                |
| status      | enum('on_sale','trading','sold_out','stop','cancel') | NO   |     | NULL              |                |
| name        | varchar(191)                                         | NO   |     | NULL              |                |
| price       | int(10) unsigned                                     | NO   |     | NULL              |                |
| description | text                                                 | NO   |     | NULL              |                |
| image_name  | varchar(191)                                         | NO   |     | NULL              |                |
| category_id | int(10) unsigned                                     | NO   | MUL | NULL              |                |
| created_at  | datetime                                             | NO   |     | CURRENT_TIMESTAMP |                |
| updated_at  | datetime                                             | NO   |     | CURRENT_TIMESTAMP |                |
+-------------+------------------------------------------------------+------+-----+-------------------+----------------+

### shippings

mysql> mysql> desc shippings;
+-------------------------+-------------------------------------------------+------+-----+-------------------+-------+
| Field                   | Type                                            | Null | Key | Default           | Extra |
+-------------------------+-------------------------------------------------+------+-----+-------------------+-------+
| transaction_evidence_id | bigint(20)                                      | NO   | PRI | NULL              |       |
| status                  | enum('initial','wait_pickup','shipping','done') | NO   |     | NULL              |       |
| item_name               | varchar(191)                                    | NO   |     | NULL              |       |
| item_id                 | bigint(20)                                      | NO   |     | NULL              |       |
| reserve_id              | varchar(191)                                    | NO   |     | NULL              |       |
| reserve_time            | bigint(20)                                      | NO   |     | NULL              |       |
| to_address              | varchar(191)                                    | NO   |     | NULL              |       |
| to_name                 | varchar(191)                                    | NO   |     | NULL              |       |
| from_address            | varchar(191)                                    | NO   |     | NULL              |       |
| from_name               | varchar(191)                                    | NO   |     | NULL              |       |
| img_binary              | mediumblob                                      | NO   |     | NULL              |       |
| created_at              | datetime                                        | NO   |     | CURRENT_TIMESTAMP |       |
| updated_at              | datetime                                        | NO   |     | CURRENT_TIMESTAMP |       |
+-------------------------+-------------------------------------------------+------+-----+-------------------+-------+

### transaction_evidences

mysql> desc transaction_evidences;
+-----------------------+------------------------------------------+------+-----+-------------------+----------------+
| Field                 | Type                                     | Null | Key | Default           | Extra          |
+-----------------------+------------------------------------------+------+-----+-------------------+----------------+
| id                    | bigint(20)                               | NO   | PRI | NULL              | auto_increment |
| seller_id             | bigint(20)                               | NO   |     | NULL              |                |
| buyer_id              | bigint(20)                               | NO   |     | NULL              |                |
| status                | enum('wait_shipping','wait_done','done') | NO   |     | NULL              |                |
| item_id               | bigint(20)                               | NO   | UNI | NULL              |                |
| item_name             | varchar(191)                             | NO   |     | NULL              |                |
| item_price            | int(10) unsigned                         | NO   |     | NULL              |                |
| item_description      | text                                     | NO   |     | NULL              |                |
| item_category_id      | int(10) unsigned                         | NO   |     | NULL              |                |
| item_root_category_id | int(10) unsigned                         | NO   |     | NULL              |                |
| created_at            | datetime                                 | NO   |     | CURRENT_TIMESTAMP |                |
| updated_at            | datetime                                 | NO   |     | CURRENT_TIMESTAMP |                |
+-----------------------+------------------------------------------+------+-----+-------------------+----------------+

### users

mysql> desc users;
+-----------------+------------------+------+-----+---------------------+----------------+
| Field           | Type             | Null | Key | Default             | Extra          |
+-----------------+------------------+------+-----+---------------------+----------------+
| id              | bigint(20)       | NO   | PRI | NULL                | auto_increment |
| account_name    | varchar(128)     | NO   | UNI | NULL                |                |
| hashed_password | varbinary(191)   | NO   |     | NULL                |                |
| address         | varchar(191)     | NO   |     | NULL                |                |
| num_sell_items  | int(10) unsigned | NO   |     | 0                   |                |
| last_bump       | datetime         | NO   |     | 2000-01-01 00:00:00 |                |
| created_at      | datetime         | NO   |     | CURRENT_TIMESTAMP   |                |

### nginx
- 再起動

nginx -s reload

- gitディレクトリからシンボリックリンクを貼る

# nginxファイルを移動
mv /etc/nginx/nginx.conf /home/isucon/${appname}/webapp/go/nginx/nginx.conf

# シンボリックリンク
ln -s /home/isucon/${appname}/webapp/go/nginx/nginx.conf /etc/nginx/nginx.conf

## アプリケーション担当
* Goのベンチマーク pprof
* MySqlのSlowQueryを見つける https://tech.studyplus.co.jp/entry/2018/05/14/120029

### MySQLクエリ解析

#### INDEX
`alter table isucari.items add index index01(seller_id, status);`

#### クエリ解析実行スニペット

$ tcpdump -s 65535 -x -nn -q -tttt -i lo -c 10000 port 3306 > /home/isucon/isucari/dump.pcap
# ベンチマーク流す　-c の値を変えると受け取るパケット数を変えられる
$ pt-query-digest --type=tcpdump --limit 50 /home/isucon/isucari/dump.pcap > /home/isucon/isucari/pt-query-digest-result.txt

$ cat /home/isucon/isucari/pt-query-digest-result.txt | less

`my.cnf`のシンボリックリンク化

# my.cnfファイルを移動
mv /etc/mysql/my.cnf /home/isucon/${appname}/webapp/go/mysql/my.cnf

# シンボリックリンク
ln -s /home/isucon/${appname}/webapp/go/mysql/my.cnf /etc/mysql/my.cnf

下記を`my.cnf`に追加

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 0

pt-query-digestをインストール

sudo apt-get install percona-toolkit
sudo pt-query-digest --limit 10 /var/log/mysql/mysql-slow.log

# ↓↓↓別のインストール方法↓↓↓
wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/debian/bionic/x86_64/percona-toolkit_3.0.13-1.bionic_amd64.deb
sudo apt install libio-socket-ssl-perl libdbd-mysql-perl libdbi-perl libnet-ssleay-perl
sudo dpkg -i percona-toolkit_3.0.13-1.bionic_amd64.deb
sudo pt-query-digest --limit 10 /var/log/mysql/mysql-slow.log

ログのパーミッションがおかしかったら

sudo chmod 755 /var/log/mysql/

[tcpdump と pt-query-digest でクエリ分析](https://qiita.com/isaoshimizu/items/805413c05f81dc36b95c)

# Snippet
clone

/home/isucon/appname/webapp/go

# ssh config

Host github.com
    User git
    IdentityFile ~/.ssh/isucon9.rsa

# deploy

これを自分たちなりにいじる

#!/bin/bash
set -ex
IPADDR=$1
USERNAME=$USER
ssh isucon@$IPADDR "/home/isucon/notify.sh $USERNAME 'deploying...' && cd /home/isucon/deploy && git pull && ~/.local/perl/bin/carton install && sudo systemctl restart mysql && sudo service memcached restart && sudo systemctl restart isuxi.perl && sudo systemctl restart nginx && sudo sysctl -p && /home/isucon/notify.sh $USERNAME 'deploy done'"

## slack通知
- `GO111MODULE=off GO111MODULE=on go get github.com/catatsuy/notify_slack/cmd/notify_slack`
- echo hoge | notify_slack -c config.toml

[slack]
url = "https://hooks.slack.com/services/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"

## 最後にやること
mysqlのスロークエリログを切る
nginxのアクセスログを切る