少し叙情的な余談。
10月5日、 Highload ++という会社がPeter Zaitsevをカンファレンスに招待し、 MySQL開発者向けの集中的な1日トレーニングを実施しました 。 人はしっかりしているようで、説明が明確に示されていた
提案されたトレーニングは上級コースです
...
提供されるコースは、集中的な1日トレーニングです。 学生にはLinuxパーソナルコンピューターが提供されます。 タスクを完了する時間は制限されます。
ああすごい、何か新しいこと、ある種のタスク、最も効率的なアーキテクチャの検討...と思います。 最後に、心は心に教えられると思います。 午後だけの会議でMySQLについて話し始め、タスクがなく、Linuxについて明確なケースを提示しなかったとき、私は驚きました。 ええ、私は座って考えることを誇りに思っていませんが、耳を傾けますが、昼食後、クエリ実行計画を勉強することでMySQLの最適化を達成できるようになり、部屋で議論が始まりました-クエリ計画を正しく読む方法を我慢できませんでした。 次のコーヒーブレイクで、彼はピーターのところに行き、会議でデータベース開発者向けに本当に高度な情報があるかどうかを尋ねました（過去2時間）。 のようなもの
一般に、高度なMySQL最適化コースは自然には存在しません。 この誤解の理由は、コースの大半が州向けであり、「インド人」が開発に従事しており、このための深い知識を必要としないためです。
それからどういう意味か。
また、MySQLは非常に曲がりくねって書かれているという理由で、おそらく最良の理由で、最適に動作しない大量のたわごとであるというPeterの考えが本当に好きでしたが、何が起こったかが判明しました。 また、データベースサーバーを最適化する方法に関係なく、ソースコードがバグやアーキテクチャの誤算に満ちているため、遅かれ早かれパフォーマンスの低下を経験することになります。 これらの問題を回避する唯一の方法は、大量のRAM（512 Gb +）とSSDディスクを使用することです（会議で示されているように、10,000書き換えサイクルの2 Tbがこれに最も適しています。 ）
もちろん、私はより効率的なアーキテクチャを使用する代わりに、鉄を投げて問題を解決するサポーターではありませんが、パーティションの問題は彼の言葉の完全な真実を示しました。
開始する
そこで、プロジェクトでパーティション分割を使用することにしました。
まず、パーティションを開くコードを見て、それについて考えますか？
is_part = strstr(norm_name, "#P#"); retry: /* Get pointer to a table object in InnoDB dictionary cache */ ib_table = dict_table_get(norm_name, TRUE); if (NULL == ib_table) { if (is_part && retries < 10) { ++retries; os_thread_sleep(100000); goto retry; }
あなたの母！
さて、10日には、100％を開こうとします！
解決したい主な概念は次のとおりです。
- アーカイブ（たとえば、先月のデータだけが必要な場合の99％では、毎月のパーティション分割を使用して、毎月古いデータをアーカイブにドロップするのが理にかなっています）
- データウェアハウジング（MySQLベースのOLAPシステムを作成することにし、そこにないOLAPキューブの代わりに、時間、日、月、年単位のパーティションで事前に集計されたテーブルの束を使用します）
- その他のケース（よりエキゾチックで、最も重要な分類に従って、バージョン5.6までのMySQLのパーティションを忘れるので、これらのケースは最初の2つのポイントに該当するため、考慮しません）
パーティションの数という1つの基準のみでこのパーティションを選択しました。 前者の場合は100個を超えることはほとんどなく、後者の場合は10,000以上です。
ご存知のように、MySQLは非常に多くのテーブルで動作します-100,000は、アーキテクチャの変更を検討する価値があるおおよその道徳的なしきい値です。 そのため、すべてのロジックで、彼にとって数万のパーティションは問題になりません。 残念ながら、そうではありません。
以前は、すべてのメタデータをRAMに保持するDBMSを使用していましたが、ディクショナリクエリは問題を引き起こしません。このため、パーティション化ユーティリティのサポートを実装するとき、 INFORMATION_SCHEMA.PARTITIONSからクエリを作成することを恐れませんでした。 これは致命的な間違いでした...どのパーティションがこのテーブルにあるかを正確に調べるにはどうすればいいですか：
-このリストを自分で保持する
-いくつかの要求を実行します（たとえば、毎日のパーティション）
select count(1) into v_av_partitions_count from information_schema.partitions where table_name = i_table_name and substring(partition_name, length(i_table_name) + 2) = cast(date_format(i_data_date, '%Y%m%d') as signed) and table_schema = database(); select min(partition_name), min(partition_description) into v_future_partition, v_future_partition_desc from information_schema.partitions where table_name = i_table_name and substring(partition_name, length(i_table_name) + 2) > cast(date_format(i_data_date, '%Y%m%d') as signed) and table_schema = database();
データディクショナリから必要な結果を取得したり、新しいパーティションを作成したり、再編成を既存にしたりします。
最初の解決策のマイナス面を説明する必要はないと思います。なぜ2番目の解決策を使用できないのかを説明した方がよいでしょう。
ヒューストンに問題がある
私の問題は、データベース構造（約7000個の空のパーティション）に入力し、30分ごとにevent_schedulerのパーティションチェックを設定したときに始まりました。 時間が来たら（ INFORMATION_SCHEMA.PARTITIONSからの2つのリクエストを開始して）、私のラップトップは離陸し、3〜5分間の最大ディスク負荷を与えました。 私は奇妙に考えました...私は空のデータベースを持っています。 不安になった私は、この不幸なテーブルからのクエリの呼び出しがどのように機能するかを詳しく調べる必要があることに気付きました。 PERFORMANCE_SCHEMAは、7000個のファイルを読み取ることを保証しました。データベースはかなりトリミングされているため、これらの読み取りはディスクから行います。 ソースを開くと、実際の問題が何であるかがわかりました。 データ辞書-RAMには保存されません。 パーティションを記述するテーブルからリクエストを行うと、次の操作が発生します。
- 正確に何を要求したかは関係ありません-すべてのテーブルとすべてのパーティションの説明がディスクから読み取られます
- これらがINNODBテーブルである場合、それらの統計はありません（このビューを表示するには、各パーティションのレコード数のおおよその統計とカーディナリティなどのインデックスのいくつかの特性が必要です）、パーティションの説明だけでなく、インデックスの動的サンプリングも行います
- しかし、それだけではありません。ほとんどの場合、より高速に使用するためにインデックスの一部をハッシュマップの形式で保存する適応キャッシュがあるためです。
したがって、データディクショナリからの1つのクエリで、
- テーブルキャッシュを洗浄します-すべてのスレッドで共有されます
- ウォッシュバッファキャッシュ-すべてのセッションで使用
- 適応キャッシュを洗い流します-書き込みと読み取りは、一貫性を確保するために1つのロックだけで制御されます
以下は、 TABLE_ONEによるリクエスト中にgdbが取得したトレースの例です。
Thread 6 (Thread 0x7fccff1be700 (LWP 2561)): #0 0x00007fcd3af4c493 in pread64 () from /lib/libpthread.so.0 ... #5 fil_io (type=10, sync=<value optimized out>, space_id=<value optimized out>, zip_size=<value optimized out>, block_offset=<value optimized out>, byte_offset=<value optimized out>, len=16384, buf=0x7fcd1a0b4000, message=0x7fcd08a6e800) at /home/mcshadow/svn/mysql-5.5.15/storage/innobase/fil/fil0fil.c:4481 #6 0x00000000007e527a in buf_read_page_low (err=0x7fccff1b85c8, sync=1, mode=<value optimized out>, space=52456, zip_size=0, unzip=<value optimized out>, tablespace_version=312, offset=4) at /home/mcshadow/svn/mysql-5.5.15/storage/innobase/buf/buf0rea.c:148 ... #9 0x00000000007bb6f1 in btr_block_get_func (index=0x7fccf4adfb78, mtr=0x4000) at /home/mcshadow/svn/mysql-5.5.15/storage/innobase/include/btr0btr.ic:55 ... #14 0x00000000007f1edf in dict_table_get (table_name=<value optimized out>, inc_mysql_count=1) at /home/mcshadow/svn/mysql-5.5.15/storage/innobase/dict/dict0dict.c:753 #15 0x0000000000776533 in ha_innobase::open (this=0x7fccf4128130, name=0x7fccff1b9230 "./DATABASE/TABLE_TWO#P#TABLE_TWO_20100823", mode=<value optimized out>, test_if_locked=<value optimized out>) at /home/mcshadow/svn/mysql-5.5.15/storage/innobase/handler/ha_innodb.cc:3737 ... #18 0x000000000064716d in handler::ha_open (this=0x28, table_arg=<value optimized out>, name=0x7fccf4076d08 "./DATABASE/TABLE_TWO", mode=2, test_if_locked=26128432) at /home/mcshadow/svn/mysql-5.5.15/sql/handler.cc:2150 ... #23 0x000000000052d6a4 in open_normal_and_derived_tables (thd=0x28, tables=0x7fccf4027968, flags=4294967295) at /home/mcshadow/svn/mysql-5.5.15/sql/sql_base.cc:5489 ... #25 0x00000000005aa51f in get_all_tables (thd=<value optimized out>, tables=<value optimized out>, cond=<value optimized out>) at /home/mcshadow/svn/mysql-5.5.15/sql/sql_show.cc:3781 ...
もちろん、このような嘆かわしい結果を見て、私は非常に怒っていました。ソースコードをざっと調べただけで、単純に修正することは不可能だとわかりました。 しかし、それはすべてとは程遠いものでした。
森に行くほどfireが増えます
すべてのマニュアルは、MySQLにはパーティションテーブルでの作業を最適化するための2つのスキームがあることを教えてくれます。
-リクエストを解析する段階でのパーティションの静的なリンクのパーティションプルーニング ;分析のために、予測分析はパーティション表の制限に基づいてのみ使用され、それ以上、以下、または以下で機能します
-実行時のパーティション選択の動的リンク。条件に等しい条件では述語のみが使用されます
これについては詳しく説明しません-これは別の記事のトピックです。 つまり、最初のケースはクエリ自体でパーティションが明示的に指定されている場合であり、2番目のケースは別のテーブルのデータに基づいてクエリ実行の段階でパーティションを計算できる場合です。 あなたが理解しているように、2番目はそれを使用しないほうが良いです-プランを変更すると、すべてのパーティションを飛ぶので、この理由でどこでも可能な限り最初の方法を使用してパーティションを最速で最も信頼性が高いと判断します-リクエストに示されている... 素朴 。
では、待ち伏せはどこにあるのでしょうか？ 待ち伏せ自体は、クエリに関係するすべてのテーブルを開いた後にパーティションプルーニングが実行されることです。 また、SELECT、INSERT、UPDATEの実行は重要ではありません。MySQLは次の操作を実行します。
- クエリに参加しているすべてのテーブルのすべてのパーティションを開きます
- 使用されるエンジンのルールに従って、各パーティションの読み取りロックをハングアップします
- このINNODBがXロックを切ることを忘れずにテーブルとそのインデックスの統計を計算する場合-よく、それなしでどのようになりますか
- そして、これがすべて行われた後にのみパーティションの整理が行われます
信じられない？ コードを貼り付け
char* is_part = NULL; is_part = strstr(table->name, "#P#"); if (is_part) { ut_print_timestamp(stderr); fprintf(stderr, " TRACE dict_update_statistics %s %s\n", table->name, only_calc_if_missing_stats?"true":"false"); }
統計を計算するdict_update_statisticsプロシージャの開始時に、結果が驚くでしょう。 デフォルトのDBMS設定では、統計の再計算はキャッシュされませんが、メタデータを取得しようとするたびに行われます。
上記に照らして、バグ＃19588のクローズは喜ばれます
タールの樽に蜂蜜のスプーン
MySQL 5.6はまもなくリリースされます;彼らは次のような便利なものをたくさん約束します
-INFORMATION_SCHEMAの静的辞書（パーティションがあるかどうかはわかりませんが、そうすることを本当に望みます）
-テーブルを開いてロックを設定する前に、どのパーティションのプルーニングが機能するかを使用する場合
select * from table partition (p1)
構築
select * from table partition (p1)
結論またはそれと一緒に暮らす方法
-アーキテクチャに1000以上のパーティションが多数ある場合は、アーキテクチャを熟考して修正しますが、MySQLのパーティションエンジンはまだサンドボックスを残していません
-パラメータ
innodb_stats_on_metadata = OFF
設定します。これにより、リクエストごとにパーティションの統計を再読み込みできなくなりますが、これはまだ発生しますが、それほど頻繁ではありません
ulimit -n
をシステム内の可能な最大パーティション数に増やし、2を掛けます
table_open_cache
パラメーターをシステムのパーティションの最大可能数まで増やします。これには2を掛けることにより、テーブルキャッシュが
table_open_cache
されるのを防ぐことができます。
-INNODBテーブルで作業する場合、
myisam_use_mmap
パラメーターを含めるようにして
myisam_use_mmap
はインデックスのみをキャッシュし、データファイルをオペレーティングシステムにキャッシュします。したがって、負荷が増加すると、辞書はディスクに移動しますが、これは望ましくありません。
INFORMATION_SCHEMA.PARTITIONS
テーブルに対してクエリを実行しないでください。ご存じのように、使用するテーブルのすべてのパーティションのDML操作でブレーキやロックが発生することはありません。