MySQLの力を検証していく
MySQLはオープンソースのリレーショナルデータベース管理システムです。シェアも高く、様々なシステムに使われていますよね。
そこで、今回はMySQLの負荷検証をしてみました。
検証内容
Vagrantを使用してVirtualBoxから仮想マシンを作成し、MySQLをインストールして、ダミーデータ(一千万レコード)を操作(SELECT,INSERT,DELETE,UPDATE)します。
環境
image https://app.vagrantup.com/ubuntu/boxes/focal64
MySQL 8.0.28
Python 3.8.10
CPU 1core
Memory 2048mb
環境構築にあたって作成したスクリプトはgithubにてMIT LICENSEで公開しています。
MySQLとPythonをインストールし、MySQLでユーザーを新しく用意した上で、ダミーデータを1000件（名前、時間、パスワード、年齢）を作成し、それを増幅(INSERT SELECT)させ、準備は完了です。
import mysql.connector
import random
import datetime
import hashlib
conn = mysql.connector.connect(
host='localhost',
user='tester',
password='getwild',
database='test'
)
cursor = conn.cursor()
def random_name():
names = ['めぐみ', 'あきら', 'かねだ', 'いちろう', 'はなこ', 'つむぎ', 'はるか', 'あおい', 'たろう']
return names[random.randrange(0, 8)]
def random_time():
year = random.randrange(2018, 2022)
month = random.randrange(1, 12)
day = random.randrange(1, 29)
return datetime.date(year, month, day)
def random_password():
return hashlib.sha256(str.encode(str(random.randrange(0, 100)))).hexdigest()
for i in range(0, 10):
cursor.execute('INSERT INTO testtable (`name`, `age`, `times`, `pass`) values ("{}", "{}", "{}", "{}")'.format(
random_name(),
random.randrange(0, 90),
random_time(),
random_password()
))
conn.commit()
ダミーデータはこんな感じ。
| num | name | times | pass | age |
|---|---|---|---|---|
| 0 | あきら | 2018-07-03-00:00:00 | ldaskjfklsa;nvjakuf | 13 |
検証するコードはこんなかんじ
SELECT * FROM TESTTABLE;
INSERT INTO TESTTABLE (name,times,pass,age) SELECT name,times,pass,age FROM TESTTABLE;
DELETE FROM TESTTABLE;
TRUNCATE TABLE TESTTABLE;
UPDATE TESTTABLE SET name = 'いちろー' where num=1;
検証
SELECT * FROM TESTTABLE
開始から数秒後、kswapd0がcpu80%前後を使用。調べてみると、SWAPをするときなどに使われるものらしく、メモリーが足りないんだなぁと思ったが、実はMySQLのメモリー使用率は26%あたりやった。
七分あたり立ったところで、killされて終了。
UPDATE TESTTABLE SET name = 'いちろー' where num=1
0.01secで終了。
UPDATE TESTTABLE SET name = 'いちろー';
CPU使用率90%、メモリー使用率25%が続き3min25secで無事終了。
INSERT INTO TESTTABLE (name,times,pass,age) SELECT name,times,pass,age FROM TESTTABLE;
CPU使用率95%、メモリー使用率25%、1min47secで終了。意外と実行が短かったです。
DELETE FROM TESTTABLE;
2min24secで、負荷は似たような感じ。
TRUNCATE TABLE TESTTABLE;
0.13secで、負荷は殆どなかった。
考察・感想
全体的にCPUの使用率は頭打ちしているな〜という感じですが、メモリーは余裕ありそうだったのでもっと使ってほしいなぁという気持ち。
SELECT文はまぁそうだよなという結果になりました。
DELETE文は、ロールバックなどに対応している（やり直しが効く）ということもあって、TRUNCATE文とは相反する結果になりました。
普段テストをする時は多くても50レコードあたりなので、大量のデータを想定しない設計をしていましたが、このように大量のダミーデータをつかった検証を行ってみると、なかなかおもしろい結果が返ってきてよい検証になったなと思います。
TODO
- SQLiteやPostgresといった他のDBの検証も行ってみたい。
- スペックの違いも検証していきたい。
Discussion