mysql、mysqlコンテナ(dockerのDBコンテナ)でのメモリ使用量を抑える方法。VPSだとメモリが足りなくてOOMが頻発する。

<h2>注意</h2>
<p>この記事は初心者が書いてるので、鵜呑みにしないでください。</p>
<h2>環境</h2>
<ul>
<li>Windows 10 Home</li>
<li>WSL2</li>
<li>この記事は全てローカルのPCでの話をしています。デプロイはしていません。</li>
<li>実際にメモリ使用量を抑えたいのはVPS上ですが、ローカル環境でもやることはほとんど変わらないので、ローカル環境で説明します。</li>
</ul>
<h2>背景</h2>
<p>VPSで、mysqlそのものやdockerのmysqlコンテナを使うと、メモリ使用量が多くて、頻繁にコンテナが落ちる。私はXerver VPSを使っているが、2GBしかないので、かなりギリギリである。</p>
<h2>結論</h2>
custom.cnfファイルを新たに作り、performance_schema=0を記載して、mysqlそのものとmysqlコンテナの設定を変更する。/etc/mysql/mysql.conf.d/や/etc/mysql/conf.d/にcustom.cnfを配置する。

```custom.cnf
[mysqld]
performance_schema = 0
```
<h2>まずはMysqlそのものの設定を変更する</h2>
<h3>現在のメモリ使用量の確認</h3>
<p>WSL2(ubuntu)を使います。現在、mysqlはLinux仮想環境全体の2.4%のメモリを使っています。</p>

```WSL2(ubuntu)
$ ps aux
USER         PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
mysql        246  2.4  2.4 2373416 405144 ?      Ssl  10:22   0:00 /usr/sbin/mysqld
```
<h3>現在のmysqlの設定の確認</h3>
<p>下記のように、mysqlのなかに潜り込んだ後、設定を確認します。現在は、performance_schemaがON（performance_schema=1）になっていることが分かります。</p>

```WSL2(ubuntu)
$ sudo mysql -u root
...
mysql> show variables like "%performance%";
+----------------------------------------------------------+-------+
| Variable_name                                            | Value |
+----------------------------------------------------------+-------+
| performance_schema                                       | ON    |
```
<h3>mysqlの設定ファイルの場所の確認</h3>
<p>下記のmysql.cnfの中身を見ると、mysqlは設定ファイルとして、/etc/mysql/conf.d/フォルダと/etc/mysql/mysql.conf.d/フォルダにあるファイルを読み込んでいることが分かります。なので、自ら作成したcustom.cnfファイルをここに配置してあげれば、設定ファイルとして読み込まれるということです。</p>


```WSL2(ubuntu)
/etc/mysql$ cat mysql.cnf
...
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
```

```custom.cnf
[mysqld]
performance_schema = 0
```
<h3>custom.cnfを適切な場所に作成する</h3>
<p>下記のように、custom.cnfを作成して配置しました。/etc/mysql/mysql.conf.dのフォルダの中に、custom.cnfがあるようにします。</p>

```WSL2(ubuntu)
/etc/mysql/mysql.conf.d$ ls -l
total 8
-rw-r--r-- 1 root root  132 Mar 11  2021 mysql.cnf
-rw-r--r-- 1 root root 2220 Mar 11  2021 mysqld.cnf
/etc/mysql/mysql.conf.d$ sudo touch custom.cnf
/etc/mysql/mysql.conf.d$ ls -l
total 8
-rw-r--r-- 1 root root    0 Oct 14 11:05 custom.cnf
-rw-r--r-- 1 root root  132 Mar 11  2021 mysql.cnf
-rw-r--r-- 1 root root 2220 Mar 11  2021 mysqld.cnf
/etc/mysql/mysql.conf.d$ sudo vi custom.cnf
/etc/mysql/mysql.conf.d$ cat custom.cnf
[mysqld]
performance_schema = 0
```

<h3>mysqlを再起動して確認</h3>
<p>下記ようにmysqlを再起動します。その後、mysqlに潜り込んで、設定を確認します。performance_schemaがOFF(performance_schema=0)になっていることが分かります。</p>

```WSL2(ubuntu)
$ sudo systemctl restart mysql
$ sudo mysql -u root
...
mysql> show variables like "%performance%";
+----------------------------------------------------------+-------+
| Variable_name                                            | Value |
+----------------------------------------------------------+-------+
| performance_schema                                       | OFF   |
```

<h3>mysqlのメモリ使用量を確認</h3>
<p>下記のように、Linux仮想環境に占めるmysqlのメモリ使用量を確認すると、1.0%になっています。設定変更前の2.4%に比べて減少しました。成功です。</p>

```WSL2(ubuntu)
$ ps aux
USER         PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
mysql       1269  0.7  1.0 2213608 178176 ?      Ssl  11:10   0:01 /usr/sbin/mysqld
```


<h2>次にdockerのmysqlコンテナの設定を同じように変更する</h2>
<h3>ディレクトリ構造とファイルの中身の確認</h3>
<p>WSL2(ubuntu)を使います。現在、下記のようなディレクトリ構造になっています。http://localhost:9001/にアクセスすると、「hello hello hello goodbye goodbye」という文字列が表示されるウェブアプリです。また、http://localhost:9001/testにアクセスすると、DBからid=1のusernameが取得されて、表示されるようになっています。</p>

```WSL2(ubuntu)
/mnt/c/.../MysqlTest$ ls -l
total 0
... Dockerfile
... app.py
... docker-compose.yml
... requirements.txt
```
```app.py
from flask import Flask, jsonify
import pymysql
import os

app = Flask(__name__)

@app.route('/')
def hello():
    return "hello hello hello goodbye goodbye"

@app.route('/test')
def test():
    conn = pymysql.connect(
        host=os.getenv("DB_HOST"),
        user=os.getenv("DB_USER"),
        password=os.getenv("DB_USER_PASS"),
        database=os.getenv("DB_NAME")
    )
    cursor = conn.cursor()
    cursor.execute("SELECT username FROM user_table WHERE id = 1")
    username = cursor.fetchone()
    conn.close()
    return jsonify({'username': username[0] if username else "No user found"})

if __name__ == "__main__":
    app.run(host="0.0.0.0", port=5000)
```
```docker-compose.yml
version: '3.9'

services:
  db:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: root_password
      MYSQL_DATABASE: test_db
      MYSQL_USER: test_user
      MYSQL_PASSWORD: test_user_password
    volumes:
      - db_data:/var/lib/mysql

  app:
    build: .
    ports:
      - "9001:5000"
    depends_on:
      - db
    environment:
      - DB_HOST=db
      - DB_USER=test_user
      - DB_USER_PASS=test_user_password
      - DB_NAME=test_db

volumes:
  db_data: {}
```
```Dockerfile
FROM python:3.9

WORKDIR /app

COPY requirements.txt requirements.txt
RUN pip install -r requirements.txt

COPY . .

CMD ["python", "app.py"]
```
```requirements.txt
Flask==2.0.2
PyMySQL==1.0.2
Werkzeug==2.2.2
cryptography==36.0.0
```

<h3>dockerコンテナを起動する</h3>
<p>下記のディレクトリに移動する。</p>

```WSL2(ubuntu)
/mnt/c/.../MysqlTest$ ls -l
total 0
... Dockerfile
... app.py
... docker-compose.yml
... requirements.txt
```
<p>下記のようにコマンドを打って、dockerコンテナを起動する。</p>

```WSL2(ubuntu)
/mnt/c/.../MysqlTest$ docker-compose build
/mnt/c/.../MysqlTest$ docker-compose up
```
<p>下記のようにdockerのwebコンテナとdbコンテナが起動しています。</p>

```WSL2(ubuntu)
$ docker ps
CONTAINER ID   IMAGE           COMMAND                  CREATED          STATUS         PORTS                    NAMES
586f2b138bb6   mysqltest-app   "python app.py"          54 seconds ago   Up 4 seconds   0.0.0.0:9001->5000/tcp   mysqltest-app-1
dcc6d5adf34d   mysql:8.0       "docker-entrypoint.s…"   26 minutes ago   Up 5 seconds   3306/tcp, 33060/tcp      mysqltest-db-1
```

<h3>dbコンテナに入って確認を行う</h3>
<p>次にdbコンテナに潜り込みます。この時、パスワードは、docker-compose.ymlに書いてあるもの（"root_password"）を使います。</p>

```WSL2(ubuntu)
$ docker exec -it dcc6d5adf34d bash
bash-4.4# mysql -u root -p
```
次にtest_userが作成されていることを確認します。このユーザは、web(Flask)コンテナがdbコンテナにアクセスするために必要です。今のタイミングで、sqlコマンドを使ってユーザを作成してもいいのですが、今回は、docker-compose.ymlに記載することで、自動的にユーザ（test_user）を作成しました。なので、test_userは、すでに下記のように作成してあります。
```WSL2(ubuntu)
mysql> show grants for test_user;
+---------------------------------------------------------+
| Grants for test_user@%                                  |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`%`                   |
| GRANT ALL PRIVILEGES ON `test\_db`.* TO `test_user`@`%` |
+---------------------------------------------------------+
2 rows in set (0.00 sec)
```
```WSL2(ubuntu)
mysql> select user from mysql.user;
+------------------+
| user             |
+------------------+
| root             |
| test_user        |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
6 rows in set (0.00 sec)
```
<h3>使うテーブルを作成。そして適当に行を追加。</h3>
<p>下記のようにテーブル（user_table）を作成しました。列は、idとusernameの二列です。idはAUTO_INCREMENTなので、自動で埋まります。そして、「id=1, username="messi"」という行を、一行追加しました。</p>

```WSL2(ubuntu)
mysql> CREATE TABLE user_table (id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(255)NOT NULL);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into user_table(username) values ("messi");
Query OK, 1 row affected (0.00 sec)
```

<h3>dockerが正常に機能しているかどうか確かめる</h3>
<p>http://localhost:9001/にアクセスすると、「hello hello hello goodbye goodbye」という文字列が表示されます。http://localhost:9001/testにアクセスすると、「{username:"messi"}」が表示されるはずです。こうなっていれば、正常に、dockerのwebコンテナとdbコンテナが機能しています。</p>

<h3>現在のメモリ使用量を確認</h3>
<p>下記を見ると、db（mysql）コンテナは、db（mysql）コンテナが使えるメモリの最大量の2.23%を使っています。</p>

```WSL2(ubuntu)
$ docker stats
CONTAINER ID   NAME              CPU %     MEM USAGE / LIMIT     MEM %     NET I/O       BLOCK I/O   PIDS
586f2b138bb6   mysqltest-app-1   0.02%     19.82MiB / 15.58GiB   0.12%     976B / 0B     0B / 0B     1
dcc6d5adf34d   mysqltest-db-1    0.84%     356MiB / 15.58GiB     2.23%     1.09kB / 0B   0B / 0B     39
```

<h3>現在のmysqlコンテナの設定を確認</h3>
<p>先ほどと同じように、dbコンテナに潜り込んでください。下記を見ると、mysqlコンテナは、performance_schemaがON（performance_schema=1）であることが分かります。</p>

```WSL2(ubuntu)
mysql> show variables like "%perform%";
+----------------------------------------------------------+-------+
| Variable_name                                            | Value |
+----------------------------------------------------------+-------+
| performance_schema                                       | ON    |
```


<h3>新しい設定ファイル（custom.cnf）の作成</h3>
<p>下記のようにファイルを作成、修正してください。docker-compose.ymlに「- ./custom.cnf:/etc/mysql/conf.d/custom.cnf」を記載することで、custom.cnfが、dockerコンテナにマウントされます。custom.cnfは、docker-compose.ymlなどと同じ階層においてください。今回は、全てのファイルが同じ階層になります。</p>

```custom.cnf
[mysqld]
performance_schema = 0
```
```docker-compose.yml
version: '3.9'

services:
  db:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: root_password
      MYSQL_DATABASE: test_db
      MYSQL_USER: test_user
      MYSQL_PASSWORD: test_user_password
    volumes:
      - db_data:/var/lib/mysql
      - ./custom.cnf:/etc/mysql/conf.d/custom.cnf  #ここを追加

  app:
    build: .
    ports:
      - "9001:5000"
    depends_on:
      - db
    environment:
      - DB_HOST=db
      - DB_USER=test_user
      - DB_USER_PASS=test_user_password
      - DB_NAME=test_db

volumes:
  db_data: {}
```
<p>ここで、windowsエクスプローラーで、custom.cnfのプロパティに入り、読み取り専用にチェックをつけてください。これをしないと、「mysql: [Warning] World-writable config file '/etc/mysql/conf.d/custom.cnf' is ignored.」というエラーメッセージが後で表示されて、custom.cnfの設定が適用されない事態に陥ります。注意してください。ただ、以前VPS上で同じことを行った時は、このエラーが起きた記憶はないので、もしかしたら、ローカルPC（windows）でWSL2を使って行うと、このエラーが出るのかもしれません。</p>

<h3>新しい設定ファイル（custom.cnf）の適用</h3>
<p>下記のコマンドをプロジェクトのルートディレクトリで実行してください。こうすることで、ファイルの変更が適用されます。</p>

```custom.cnf
/mnt/c/.../MysqlTest$ docker-compose down
/mnt/c/.../MysqlTest$ docker-compose build
/mnt/c/.../MysqlTest$ docker-compose up
```

<h3>確認</h3>
<p>以前と同じ手順で、db(mysql)コンテナに潜り込んで、mysqlの設定を確認してください。performance_schemaがOFF（performance_schema=0）になっていれば、無事に変更が適用されています。</p>

```WSL2
mysql> show variables like "%performance%";
+----------------------------------------------------------+-------+
| Variable_name                                            | Value |
+----------------------------------------------------------+-------+
| performance_schema                                       | OFF   |
```
下記を見ると、mysqlコンテナは、そのコンテナが使える最大量の0.84%のメモリを使っていることが分かります。設定の変更前は、2.23%だったので、メモリ使用量が減少していることが分かります。成功です。

```WSL2
$ docker stats
CONTAINER ID   NAME              CPU %     MEM USAGE / LIMIT     MEM %     NET I/O       BLOCK I/O   PIDS
8f3078709fc4   mysqltest-app-1   0.00%     20.96MiB / 15.58GiB   0.13%     1.28kB / 0B   0B / 0B     1
28c1235143ec   mysqltest-db-1    0.67%     134.4MiB / 15.58GiB   0.84%     1.39kB / 0B   0B / 0B     40
```

<h2>参考</h2>
<ul>
<li>GPT-4</li>
<li>https://stackoverflow.com/questions/10676753/reducing-memory-consumption-of-mysql-on-ubuntuaws-micro-instance</li>
<li>https://qiita.com/rabbitbeef/items/14433a2c0a6f85c3b476</li>
<li>https://dba.stackexchange.com/questions/64055/how-to-reduce-mysql-memory-used</li>
<li>http://www.tocker.ca/configuring-mysql-to-use-minimal-memory.html</li>
</ul>