Locustを使ってPostgresqlの負荷テストをするときの備忘録

# 概要
- データベースの負荷テストを行うのにLocustを使用しようと思ったが、意外にもWEBアプリケーションの負荷テストに用いる場合の記事しか無く、実装するのにまあまあ時間が掛かったので書き残しておく。

# 参考
https://docs.locust.io/en/stable/installation.html

https://docs.locust.io/en/stable/configuration.html

# 環境構築
```terminal
# Pythonのバージョンを確認
python --version
> Python 3.7.3

# psycopg2のインストール
> pip install psycopg2

# psycopg2のインストールが失敗する場合、以下を試す
> pip install psycopg2-binary

# locustのインストール
> pip install locust
```

# 準備
以下のようにPythonで書く。


```python:locustfile.py
import psycopg2
from locust import User, task, between, TaskSet, events
import time


def create_conn():

    conn = psycopg2.connect(host="ホスト名", port=5432,
                        dbname="データベース名", user="ユーザー名",
                        password="パスワード", sslmode='disable',
                        keepalives=1,
                        keepalives_idle=30,
                        keepalives_interval=10,
                        keepalives_count=5)

    if None != conn:
        print("Connection successful")

    return conn

def execute_query(query):

    conn = create_conn()
    cur = conn.cursor()

    try:
        cur.execute(query)
        return cur.fetchall()
    except Exception as e:
        print(format(e))
        

class PostgresqlClient:

    def __getattr__(self, name):
        def wrapper(*args, **kwargs):
            start_time = time.time()
            try:
                res = execute_query(*args, **kwargs)
                print('Result ----------->' + str(res))
                events.request_success.fire(request_type="postgresql",
                                            name=name,
                                            response_time=int((time.time() - start_time) * 1000),
                                            response_length=len(res))
            except Exception as e:
                events.request_failure.fire(request_type="postgresql",
                                            name=name,
                                            response_time=int((time.time() - start_time) * 1000),
                                            exception=e)

                print('error {}'.format(e))

        return wrapper


class CustomTaskSet(TaskSet):
    
    @task(1)
    def execute_query(self):
        self.client.execute_query("select * from emp;")


class PostgresLocust(User):

    min_wait = 0
    max_wait = 0
    tasks = [CustomTaskSet]
    wait_time = between(min_wait, max_wait)


    def __init__(self, *args, **kwargs):
        super(PostgresLocust, self).__init__(*args, **kwargs)
        self.client = PostgresqlClient()

```

# テスト実行
```
> locust -f ./locustfile.py
```
web UIを使用しないで実行するときは（CSVの書き出しのみで事足りる時など）`master.conf`にオプションを書いておくと便利。
```config:master.conf
locustfile = locustfile.py
headless = true
users = 100
run-time = 12h
csv
logfile = logs.log
```
```
> locust --config=master.conf
```
# 結果

```
Result ----------->[(1, 'aaa       '), (2, 'bbb       '), (3, 'ccc       ')]
Connection successful
Result ----------->[(1, 'aaa       '), (2, 'bbb       '), (3, 'ccc       ')]
Connection successful
Result ----------->[(1, 'aaa       '), (2, 'bbb       '), (3, 'ccc       ')]
Connection successful
Result ----------->[(1, 'aaa       '), (2, 'bbb       '), (3, 'ccc       ')]
Connection successful
Result ----------->[(1, 'aaa       '), (2, 'bbb       '), (3, 'ccc       ')]
```
![image-20220228-143220.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2684448/860083c7-d143-97ec-5faf-0b2ab2a984d3.png)
![image-20220228-143313.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2684448/a108fe35-fd94-5fc0-d82e-9d51e9adbbbc.png)

### 補足
テスト実施時に下記のようなOpen Files Limitについてのwarningが出た際には、Limitを上げる。
```
System open file limit '1024' is below minimum setting '10000'.
It's not high enough for load testing, and the OS didn't allow locust to increase it by itself.
```
```
> ulimit -Sn 10001
```

[Maximum Number of Open Files Limitについて](https://github.com/locustio/locust/wiki/Installation#increasing-maximum-number-of-open-files-limit)
> Every User/HTTP connection from Locust opens a new file (technically a file descriptor). Many operating systems by default set a low limit for the maximum number of files that can be open at the same time.
Locust will try to adjust this automatically for you, but in a lot of cases your operating system will not allow it (in which case you will get a warning in the log). Instead you will have to do it manually 


# まとめ
上記のようにすることで、Postgresqlの負荷テストが実行できた。今回はPostgresqlで実施したが、データベース接続の部分を書き換えればMySQLの負荷テストも可能になると思う。