質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Q&A

解決済

3回答

2754閲覧

事前に抽出条件を想定してクエリキャッシュをメモリに載せておく方法

yoshi123

総合スコア28

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

0グッド

1クリップ

投稿2019/02/11 01:28

10億レコードのテーブルからの単純な抽出を検討しています。

<tagmapテーブル>
id int
tag_id int

idが振られている記事(1000万レコード)に対して1記事あたり100個のタグ付けを行っている想定のテーブルです。
タグ数(tag_id)は100万程度です。

SELECT id FROM tagmap WHERE tag_id=xxxxx;

の検索結果が、

初回
10000 rows in set (1.85 sec)

2回目
10000 rows in set (0.00 sec)

という具合で、キャッシュを使っていると思われる2回目が劇的に改善します。

WHERE句を変えると、やはり初回は1~2sec、2回目は0.00~0.01secで返ってきます。

初回のパフォーマンスのこれ以上のチューニングが難しく(パーティションなどが使いづらい)、何とか事前計算などでユーザーには最初から2回目のパフォーマンスを提供したいのですが、再起動時などに全ての抽出クエリのキャッシュをメモリに載せて、それを維持しておくことは可能でしょうか。
手順や必要なメモリ量の見積もりがわかる方がいれば教示いただけますでしょうか。

抽出パターンは単純なtag_idとして100万通りです。
(AND検索などはプログラムで絞り込む、または1~2secかけて抽出で構いません)

tagmapテーブルはファイルを見たところ約1GBのディスク量です。

サーバーのメモリは128GBなどは用意できるので、再起動時などに全てメモリに載せられれば、と考えています。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

maisumakun

2019/02/11 02:12

テーブルのインデックスはどのようになっていますか?(正しくかければ、1回目でも1秒もかからないかと思います)
yoshi123

2019/02/11 03:20

id,tag_idの複合主キー、別途、tag_id→idでindexをつけています。 show create table tagmap; +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tagmap | CREATE TABLE `tagmap` ( `id` bigint(20) NOT NULL, `tag_id` bigint(20) NOT NULL, PRIMARY KEY (`id`,`tag_id`), KEY `index_tag` (`tag_id`,`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ explain select * from tagmap where tag_id=10000; +----+-------------+--------+------+---------------+-----------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+-----------+---------+-------+-------+-------------+ | 1 | SIMPLE | tagmap | ref | index_tag | index_tag | 8 | const | 10218 | Using index | +----+-------------+--------+------+---------------+-----------+---------+-------+-------+-------------+ のような状況です。
guest

回答3

0

8.9.3 MySQL クエリーキャッシュ
上記は確認されていますか?

クエリーキャッシュは、あまり頻繁に変更されないテーブルがあり、それに対してサーバーが多くの同一のクエリーを受け取る環境で役立つことがあります。

上記のこの要件を満たしたものですか?10億件を想定したテーブルがそうであるようには思えません。
また、仮に10億件のデータを全てキャッシュに格納すれば、2回目の性能が出せるとは言い切れません。
逆に遅くなる場合もあります。

パフォーマンスのこれ以上のチューニングが難しく(パーティションなどが使いづらい)

出来ない理由は何でしょう?

バッチ処理でもないのに10億件中から1万件取り出す想定は何なのでしょう?

キャッシュ以前に、10億件の持ち方と取り出す単位など設計の見直しだと感じます。
キャッシュすることに意味が無い理由については、以下です。
8.9.3.1 クエリーキャッシュの動作

テーブルが変更された場合、そのテーブルを使用するキャッシュされたすべてのクエリーが無効になり、キャッシュから削除されます。これには、変更されたテーブルにマップされた MERGE テーブルを使用するクエリーも含まれます。テーブルは、INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE、または DROP DATABASE などの多くの種類のステートメントによって変更できます。

頻繁に更新されるテーブルのキャッシュは意味がありません。

追記

tagmapの指すテーブルにtagmapの代わりにJson型でタグを持たせる方法だと、検索対象の件数自体を減らせるので効果があるかもしれません。
json型でもindexが貼りたい!
但し、扱えるのは5.7以降です。

それから、全文検索で検索対象として1万件想定していないなら、一定件数でページングするなどの仕組みでのSQLで検証すべきです。

投稿2019/02/11 02:18

編集2019/02/11 04:08
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yoshi123

2019/02/11 03:15

なるほど、クエリーキャッシュがバイトごとの正確なマッチングでクエリーを同一判定してキャッシュを使うということだと、100万クエリーのテキストマッチングになるということですね。 これではメモリがあってもそちらのオーバーヘッドの方が大きくなりそうですね、、、 すみません、クエリーキャッシュのところはマニュアルを見ていませんでした。 想定というのは1000万記事に対する全文検索です。 一方で、各記事に対しては、付与されているタグを表示したいというものです。 他の抽出条件があったり、分析に使っていることもあって、できればテキスト部分のデータもMySQLで一元的に扱いたい、という要件があります。 MATCH~AGAINSTでテキスト部分の全文検索だと10~20secかかり現実的に実用に使うのは難しいため、タグ化しているものはタグでヒットさせることで、1~2秒にはできそう、というところが現状で、でももう1桁パフォーマンスを上げたい、と考えています。 (タグ化されていないワードは仕方なくMATCH~AGAINSTで) 検索ワードはAND検索になりますので、下記のようなSQLを想定しています(例えば3語で検索の場合)。 SELECT tm.id FROM tagmap tm, tag t WHERE tm.tag_id = t.tag_id AND (t.tag IN ('タグ1', 'タグ2', 'タグ3')) GROUP BY tm.id HAVING COUNT( tm.id )=3; なので1万件を取り出すというよりは、基本的には複合ワードで10件だったり数百件に絞り込まれる想定です。 それ以上はLIMITをかけるので、「10000件ヒットして全て取り出す」ということではありません。 テーブルの更新は数も多いので日次バッチで考えています。 タグをカテゴリーや付与件数などで付番してパーティションをtag_idで分けて10億レコードを分割する案も含めて検討していますが、、idから「記事に付与されているタグ」を抽出する時に全パーティションから抽出することになるので、逆にパフォーマンス悪化が懸念されます。 ただ、パーティションでマッピングの抽出が改善できることは想定できるので、データは重複で持つことになるのでメンテナンス性との検討になりますが、記事テーブルに「付与タグ」カラムを設けて、"/"区切りなどでtag_id(s)を持たせる、というのが現実解なのかなと考えています。 もう少しいくつか方法を試していきたいと思います。
sazi

2019/02/11 04:10 編集

json型などについて追記しました。 あり得ないことを検証しても、単にハードルを上げているだけですから。
yoshi123

2019/02/11 04:41

json型は初めて知りました。 これはタグ以外にも適用できそうです。 全文検索もver5.7以降の方が早いようなのでバージョンアップ前提で試してみたいと思います。 ありがとうございます。
guest

0

件数が増えてくるとカスタムでチューニングしない限り
実用に耐えないと思います。
それでもいくつか効果があるものとして以下試す価値があるかもしれません

  • パーティショニング
  • triggerで抽出条件単位で二重登録

ちなみにsetとfind_in_setの組み合わせで試してみましたがあまり
効率的にインデックスは使用されていなかったです

投稿2019/02/12 00:44

yambejp

総合スコア114572

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yoshi123

2019/02/14 13:54

ありがとうございます。 皆様からアドバイスいただき、今回は現実的な対応としてクラスター構成とパーティションを検討していくことになりました。
guest

0

ベストアンサー

キャッシュだけってのは多分簡単にはできません。そこで、キャッシュを持ち越す方法としては、
1.OSごと仮想化して仮想化環境の機能を使って再起動前の状態で続行する。

これだとMySQLの再起動時には意味がないので、
2.MySQL再起動時に想定したクエリを事前に発行する。

単に早ければいいなら、
3.もっと速い環境に変更する。(MEMORY ストレージエンジン,MySQL Clusterなど)

全て併用可能です。

投稿2019/02/11 02:43

hihijiji

総合スコア4150

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yoshi123

2019/02/11 03:25

用途からすると(基本的に永続データのため)、ストレージエンジンがメモリというのはナンセンスですが、マスターを持っておいて、メモリにレプリカのようなものを展開できると早いかもしれませんね。 Clusterと合わせて検討してみます。 ありがとうございます。
yoshi123

2019/02/14 14:00

皆様からアドバイスいただき、今回は現実的な対応としてクラスター構成とパーティションを検討していくことになりました。 HA構成はハードルが高いかと思っていたのですが、調べてみるとDBを4,5台持って交代でメンテナンスをするならクラスターで組んでしまっても運用できそうなので、検証していこうと思います。 ありがとうございました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問