注意

この記事は初心者が書いてるので、鵜呑みにしないでください。

環境

Windows 10 Home
WSL2
この記事は全てローカルのPCでの話をしています。デプロイはしていません。
実際にメモリ使用量を抑えたいのはVPS上ですが、ローカル環境でもやることはほとんど変わらないので、ローカル環境で説明します。

背景

VPSで、mysqlそのものやdockerのmysqlコンテナを使うと、メモリ使用量が多くて、頻繁にコンテナが落ちる。私はXerver VPSを使っているが、2GBしかないので、かなりギリギリである。

結論

custom.cnfファイルを新たに作り、performance_schema=0を記載して、mysqlそのものとmysqlコンテナの設定を変更する。/etc/mysql/mysql.conf.d/や/etc/mysql/conf.d/にcustom.cnfを配置する。

custom.cnf

[mysqld]
performance_schema = 0

まずはMysqlそのものの設定を変更する

現在のメモリ使用量の確認

WSL2(ubuntu)を使います。現在、mysqlはLinux仮想環境全体の2.4%のメモリを使っています。

$ 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

現在のmysqlの設定の確認

下記のように、mysqlのなかに潜り込んだ後、設定を確認します。現在は、performance_schemaがON（performance_schema=1）になっていることが分かります。

$ sudo mysql -u root
...
mysql> show variables like "%performance%";
+----------------------------------------------------------+-------+
| Variable_name                                            | Value |
+----------------------------------------------------------+-------+
| performance_schema                                       | ON    |

mysqlの設定ファイルの場所の確認

下記のmysql.cnfの中身を見ると、mysqlは設定ファイルとして、/etc/mysql/conf.d/フォルダと/etc/mysql/mysql.conf.d/フォルダにあるファイルを読み込んでいることが分かります。なので、自ら作成したcustom.cnfファイルをここに配置してあげれば、設定ファイルとして読み込まれるということです。

/etc/mysql$ cat mysql.cnf
...
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

custom.cnf

[mysqld]
performance_schema = 0

custom.cnfを適切な場所に作成する

下記のように、custom.cnfを作成して配置しました。/etc/mysql/mysql.conf.dのフォルダの中に、custom.cnfがあるようにします。

/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

mysqlを再起動して確認

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

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

mysqlのメモリ使用量を確認

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

$ 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

次にdockerのmysqlコンテナの設定を同じように変更する

ディレクトリ構造とファイルの中身の確認

WSL2(ubuntu)を使います。現在、下記のようなディレクトリ構造になっています。http://localhost:9001/にアクセスすると、「hello hello hello goodbye goodbye」という文字列が表示されるウェブアプリです。また、http://localhost:9001/testにアクセスすると、DBからid=1のusernameが取得されて、表示されるようになっています。

/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: {}

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

dockerコンテナを起動する

下記のディレクトリに移動する。

/mnt/c/.../MysqlTest$ ls -l
total 0
... Dockerfile
... app.py
... docker-compose.yml
... requirements.txt

下記のようにコマンドを打って、dockerコンテナを起動する。

/mnt/c/.../MysqlTest$ docker-compose build
/mnt/c/.../MysqlTest$ docker-compose up

下記のようにdockerのwebコンテナとdbコンテナが起動しています。

$ 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

dbコンテナに入って確認を行う

次にdbコンテナに潜り込みます。この時、パスワードは、docker-compose.ymlに書いてあるもの（"root_password"）を使います。

$ 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は、すでに下記のように作成してあります。

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)

mysql> select user from mysql.user;
+------------------+
| user             |
+------------------+
| root             |
| test_user        |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
6 rows in set (0.00 sec)

使うテーブルを作成。そして適当に行を追加。

下記のようにテーブル（user_table）を作成しました。列は、idとusernameの二列です。idはAUTO_INCREMENTなので、自動で埋まります。そして、「id=1, username="messi"」という行を、一行追加しました。

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)

dockerが正常に機能しているかどうか確かめる

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

現在のメモリ使用量を確認

下記を見ると、db（mysql）コンテナは、db（mysql）コンテナが使えるメモリの最大量の2.23%を使っています。

$ 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

現在のmysqlコンテナの設定を確認

先ほどと同じように、dbコンテナに潜り込んでください。下記を見ると、mysqlコンテナは、performance_schemaがON（performance_schema=1）であることが分かります。

mysql> show variables like "%perform%";
+----------------------------------------------------------+-------+
| Variable_name                                            | Value |
+----------------------------------------------------------+-------+
| performance_schema                                       | ON    |

新しい設定ファイル（custom.cnf）の作成

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

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: {}

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

新しい設定ファイル（custom.cnf）の適用

下記のコマンドをプロジェクトのルートディレクトリで実行してください。こうすることで、ファイルの変更が適用されます。

custom.cnf

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

確認

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

mysql> show variables like "%performance%";
+----------------------------------------------------------+-------+
| Variable_name                                            | Value |
+----------------------------------------------------------+-------+
| performance_schema                                       | OFF   |

下記を見ると、mysqlコンテナは、そのコンテナが使える最大量の0.84%のメモリを使っていることが分かります。設定の変更前は、2.23%だったので、メモリ使用量が減少していることが分かります。成功です。

$ 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

参考

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