MySQL

MySQLパーティショニング

| by 優

 

MySQLで運用していると100万レコードほどになってくるとレスポンスが悪くなります。WEBサービスのチャットや履歴といったログデータを、ユーザ側で『最初から最後まで見れて当たり前』な設計をすると、ユーザが増加してチャットデータが爆発的に増えた場合にデータベースが膨らみます。そのまま全部保存でいくといつかは会話の時系列でサーバをわけることになります。

 

そもそも１年前の会話ログとか本当に必要かな？

確かにごく一部消したくない必要なチャットログもあるはずです。恋人同士の特別な記念になる会話とか、そんな時は特別な会話だけ保存して、他は期間を決めて削除されていく仕様に切り替えるといった仕様に割り切ると、データもスリムにお財布にも良いです。

 

MySQL パーティション

時間が経つにつれ価値がなくなっていくログ系データに適用しやすい、レコードを効率的に管理できるようにすることが出来るMySQLのパーティション機能のご紹介。

 

※某大規模チャットアプリも２週間～１か月程度で自動削除される仕様なはず。ユーザ参照用のDBはスリムにしておいて、管理用に別のストレージ用のDBを作って全て貯めておけば蓄積データとして活用できます。

 

パーティションの効用

  • SELECT時の全体スキャンを部分スキャンで探索できる。
    WHEREでパーティションのカラムを指定することで、適切な範囲にオプティマイザがアクセスするのでパーティション内の検索が早い。
  • DELETEに強い。パーティションで切れば大量DELETEをDROPに置き換えて実行出来るので超高速！

 

パーティション使用上の注意

  •  運用面が変わります、本当に必要かよく考えて導入する。
  • レンジパーティションの範囲に指定するカラムをPRIMARY KEYに含めておかなければいけない。その為主キーの再設定が必要になる場合があります。
  • 未指定のパーティション範囲にデータが入らなくなるので、管理業務が発生する。メンテナンス時にREORGNIZEで再編成するか、LESS THAN MAXVALUEで受け皿にする、数年単位の分を予めパーティションを切っておくなど考える必要があります。

今回使ったミニサーバ

  • MySQLバージョン5.6.34
  • スペック1CPU メモリ1GB SSD

特別に良いサーバでなくても大丈夫。

 

 

まずはテーブルの作成

 

mysql>
CREATE TABLE taiou (
  id int UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id varchar(255) NOT NULL,
  name varchar(255) NOT NULL,
  title varchar(255),
  naiyou varchar(255),
  created_date timestamp NOT NULL default current_timestamp(),
  updated_date datetime,
  PRIMARY KEY(id, created_date),
  INDEX(id)
) ENGINE=InnoDB;

 

適当にデータを10件入れます。

mysql> INSERT INTO taiou (user_id, name, title, naiyou) values
('a1',    'tanaka',    '動かない', '代替機で対応'),
('a2',    'tarou',     '山にいます。助けて', 'ヘリで対応'),
('a3',    'yuu',       'PC壊れた', '代替機で対応'),
('a4',    'suzuki',    '眠い', '寝たようです。'),
('a5',    'katoki',    '触っていないが壊れた', '電源が壊れていた'),
('a6',    'ooki',      'PCが燃えた', '消火！'),
('a7',    'ko0masan',   'コ〇さんズラ', '顧客に会えない'),
('a8',    'jibaanyan',  'にゃーけーびー', '顧客に会えない'),
('a9',    'nyankiti',  'パスワードを忘れた', 'パスワードの再発行で対応'),
('a10',    'ponta',     'こいつ、うご！', '謎のロボットに遭遇');

 

中身を見てみる

mysql> SELECT * FROM taiou;
+----+---------+----------+--------------------------------+--------------------------------------+---------------------+--------------+
| id | user_id | name     | title                          | naiyou                               | created_date        | updated_date |
+----+---------+----------+--------------------------------+--------------------------------------+---------------------+--------------+
|  1 | a1       | tanaka   | 動かない                       | 代替機で対応                         | 2016-11-28 03:13:53 | NULL         |
|  2 | a2       | tarou    | 山にいます。助けて               | ヘリで対応                         | 2016-11-28 03:13:53 | NULL         |
|  3 | a3       | yuu      | PC壊れた                       | 代替機で対応                         | 2016-11-28 03:13:53 | NULL         |
|  4 | a4       | suzuki   | 眠い                           | 寝たようです。                       | 2016-11-28 03:13:53 | NULL         |
|  5 | a5       | katoki   | 触っていないが壊れた           | 電源を入れた                         | 2016-11-28 03:13:53 | NULL         |
|  6 | a6       | ooki     | PCが燃えた                     | 消火して対応                         | 2016-11-28 03:13:53 | NULL         |
|  7 | a7       | ko0masan  | コ○さんズラ                   | 顧客に会えない                       | 2016-11-28 03:13:53 | NULL         |
|  8 | a8       | jibaanyan | にゃーけーびー                 | 顧客に会えない                       | 2016-11-28 03:13:53 | NULL         |
|  9 | a9       | nyankiti | パスワードを忘れた             | パスワードの再発行で対応             | 2016-11-28 03:13:53 | NULL         |
| 10 | a10       | ponta    | こいつ、うご！                 | 謎のロボットに遭遇                   | 2016-11-28 03:13:53 | NULL         |
+----+---------+----------+--------------------------------+--------------------------------------+---------------------+--------------+
10 rows in set (0.01 sec)

 

結合を使って1000万レコードを追加します。

mysql> INSERT INTO taiou (user_id, name, title, naiyou)
	SELECT t1.user_id, t1.name, t1.title, t1.naiyou
	FROM taiou t1, taiou t2, taiou t3, taiou t4, taiou t5, taiou t6, taiou t7;

1000万レコードは1分20秒
Query OK, 10000000 rows affected (1 min 21.40 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

レコード総数の確認を行います。

mysql> select count(*) from taiou;
+----------+
| count(*) |
+----------+
| 10000010 |
+----------+
1 row in set (2.97 sec)

1000万と10レコード入っているのがわかります。結合指定を増やせば、１億でも10億でもこのように簡単にテスト用のレコードを追加出来ます。

 

試しに100件のデータを表示してみます。

 

mysql> SELECT * FROM taiou WHERE id BETWEEN 1 and 100;
+-----+---------+----------+--------------------------------+--------------------------------------+---------------------+--------------+
| id  | user_id | name     | title                          | naiyou                               | created_date        | updated_date |
+-----+---------+----------+--------------------------------+--------------------------------------+---------------------+--------------+
|   1 | a1       | tanaka   | 動かない                       | 代替機で対応                         | 2016-11-28 03:16:37 | NULL         |
|   2 | a2       | tarou    | 山にいます。助けて               | ヘリで対応                         | 2016-11-28 03:16:37 | NULL         |
|   3 | a3       | yuu      | PC壊れた                       | 代替機で対応                         | 2016-11-28 03:16:37 | NULL         |
|   4 | a4       | suzuki   | 眠い                           | 寝たようです。                       | 2016-11-28 03:16:37 | NULL         |
|   5 | a5       | katoki   | 触っていないが壊れた           | 電源を入れた                         | 2016-11-28 03:16:37 | NULL         |
|   6 | a6       | ooki     | PCが燃えた                     | 消火して対応                         | 2016-11-28 03:16:37 | NULL         |
|   7 | a7       | ko0masan  | コ〇さんズラ                   | 顧客に会えない                       | 2016-11-28 03:16:37 | NULL         |
|   8 | a8       | jibaanyan | にゃーけーびー                 | 顧客に会えない                       | 2016-11-28 03:16:37 | NULL         |
|   9 | a9       | nyankiti | パスワードを忘れた             | パスワードの再発行で対応             | 2016-11-28 03:16:37 | NULL         |
|  10 | a10       | ponta    | こいつ、うご！                 | 謎のロボットに遭遇                   | 2016-11-28 03:16:37 | NULL         |
|  11 | a1       | tanaka   | 動かない                       | 代替機で対応                         | 2016-11-28 03:16:57 | NULL         |
|  12 | a2       | tarou    | 山にいます。助けて               | ヘリで対応                         | 2016-11-28 03:16:57 | NULL         |
|  13 | a3       | yuu      | PC壊れた                       | 代替機で対応                         | 2016-11-28 03:16:57 | NULL         |
|  14 | a4       | suzuki   | 眠い                           | 寝たようです。                       | 2016-11-28 03:16:57 | NULL         |
|  15 | a5       | katoki   | 触っていないが壊れた           | 電源を入れた                         | 2016-11-28 03:16:57 | NULL         |
|  16 | a6       | ooki     | PCが燃えた                     | 消火して対応                         | 2016-11-28 03:16:57 | NULL         |
|  17 | a7       | ko0masan  | コ〇さんズラ                   | 顧客に会えない                       | 2016-11-28 03:16:57 | NULL         |
|  18 | a8       | jibaanyan | にゃーけーびー                 | 顧客に会えない                       | 2016-11-28 03:16:57 | NULL         |
|  19 | a9       | nyankiti | パスワードを忘れた             | パスワードの再発行で対応             | 2016-11-28 03:16:57 | NULL         |
|  20 | a10       | ponta    | こいつ、うご！                 | 謎のロボットに遭遇                   | 2016-11-28 03:16:57 | NULL         |

(略)

|  97 | a7       | ko0masan  | コ〇さんズラ                   | 顧客に会えない                       | 2016-11-28 03:16:57 | NULL         |
|  98 | a8       | jibaanyan | にゃーけーびー                 | 顧客に会えない                       | 2016-11-28 03:16:57 | NULL         |
|  99 | a9       | nyankiti | パスワードを忘れた             | パスワードの再発行で対応             | 2016-11-28 03:16:57 | NULL         |
| 100 | a10       | ponta    | こいつ、うご！                 | 謎のロボットに遭遇                   | 2016-11-28 03:16:57 | NULL         |
+-----+---------+----------+--------------------------------+--------------------------------------+---------------------+--------------+
100 rows in set (0.02 sec)

 

パーティション作成

今回は100万レコードずつレンジパーティションを切ることにします。基本的にパーティションは細かくざくざく切っていって、参照時に１つのパーティション内で参照が済むようになっているとパフォーマンスがいいです。idの数や時系列でレンジで範囲指定したパーティションを設定出来ます。今回は単純にidの数でパーティションを切ります。

mysql>

ALTER TABLE taiou
PARTITION BY RANGE (id) (
PARTITION p1000000 VALUES LESS THAN (1000000)   ENGINE=InnoDB COMMENT = 'range-1-999999',
PARTITION p2000000 VALUES LESS THAN (2000000)   ENGINE=InnoDB COMMENT = 'range-1000000-1999999',
PARTITION p3000000 VALUES LESS THAN (3000000)   ENGINE=InnoDB COMMENT = 'range-2000000-2999999',
PARTITION p4000000 VALUES LESS THAN (4000000)   ENGINE=InnoDB COMMENT = 'range-3000000-3999999',
PARTITION p5000000 VALUES LESS THAN (5000000)   ENGINE=InnoDB COMMENT = 'range-4000000-4999999',
PARTITION p6000000 VALUES LESS THAN (6000000)   ENGINE=InnoDB COMMENT = 'range-5000000-5999999',
PARTITION p7000000 VALUES LESS THAN (7000000)   ENGINE=InnoDB COMMENT = 'range-6000000-6999999',
PARTITION p8000000 VALUES LESS THAN (8000000)   ENGINE=InnoDB COMMENT = 'range-7000000-7999999',
PARTITION p9000000 VALUES LESS THAN (9000000)   ENGINE=InnoDB COMMENT = 'range-8000000-8999999',
PARTITION p10000000 VALUES LESS THAN (10000000) ENGINE=InnoDB COMMENT = 'range-9000000-9999999',
PARTITION pover VALUES LESS THAN MAXVALUE
);

 

1千万件のレコードのパーティションで1分44秒かかる。

Query OK, 10000010 rows affected (1 min 43.88 sec)
Records: 10000010 Duplicates: 0 Warnings: 0

 

１億レコードの場合 ※1CPU 1GB
Query OK, 100000010 rows affected (13 min 47.08 sec)
Records: 100000010 Duplicates: 0 Warnings: 0

 

【パーティションの消し方】

パーティションを消す前に該当のパーティションに含まれる範囲のレコードをdumpしておくと安心(*’▽’)

# mysqldump --defaults-extra-file=/root/.my.cnf --single-transaction --default-character-set=utf8 --complete-insert --no-create-info --where="id >= 1 and id <= 999999" support taiou > support.taiou_p1000000.sql

パーティション削除

mysql> ALTER TABLE taiou DROP PARTITION p1000000;

Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

消したパーティションの範囲のレコードを表示

mysql> SELECT * FROM taiou WHERE id BETWEEN 1 and 999999;
Empty set (0.00 sec)

空ですね。

 

確認

mysql> SELECT * FROM taiou WHERE id BETWEEN 1 and 1000000;
+---------+---------+-------+-----------------------+-----------------------------+---------------------+--------------+
| id      | user_id | name  | title                 | naiyou                      | created_date        | updated_date |
+---------+---------+-------+-----------------------+-----------------------------+---------------------+--------------+
| 1000000 | 2       | ponta | こいつ、うご！        | 謎のロボットに遭遇          | 2016-11-28 03:16:57 | NULL         |
+---------+---------+-------+-----------------------+-----------------------------+---------------------+--------------+
1 row in set (0.00 sec)

レコード総数を見てみましょう。

mysql> select count(*) from taiou;
+----------+
| count(*) |
+----------+
|  9000011 |
+----------+
1 row in set (3.54 sec)

余分なデータまで削除されていないかな？っと次のパーティションの範囲を確認。

mysql> select count(*) from taiou WHERE id BETWEEN 1000000 and 1999999;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (1.10 sec)

次のパーティションのレコードは無事です。

 

レコード総数の確認してみましょう。

mysql> select count(*) from taiou;
+----------+
| count(*) |
+----------+
| 99000011 |
+----------+
1 row in set (34.36 sec)

綺麗に100万レコード消えています。

 

パーティション追加

REORGNIZEを使ってpoverパーティションの間に割り込ませるように再編成することで、パーティションの追加設定が可能です。

 

mysql>
ALTER TABLE taiou REORGANIZE PARTITION pover INTO (
PARTITION p11000000 VALUES LESS THAN (11000000) ENGINE=InnoDB COMMENT = 'range-10000000-10999999',
PARTITION pover VALUES LESS THAN MAXVALUE
ENGINE=InnoDB
);

 

パーティショニングをやめて通常のテーブルに戻す

ALTER TABLE taiou REMOVE PARTITIONING;

 

パーティションを導入すると、パーティション管理が加わり煩雑になります。パーティショニングを超えた量のレコードが発生した場合はDBにエラーが起こり障害になります。

時系列で組む場合は数年分まとめてパーティションを切っておくなど、システム設計をよく考えて導入して下さいね～！

 

関連記事 - More from my site -

Amazonおすすめ

iPad 9世代 2021年最新作

iPad 9世代出たから買い替え。安いぞ！🐱 初めてならiPad。Kindleを外で見るならiPad mini。ほとんどの人には通常のiPadをおすすめします＞＜

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。（スパム対策）