こんにちは、yoku0825です。

> 一時テーブルのディレクトリの場所と容量、設定について

> <snip>

> ディスクフルになるかもしれないことを想定した場合、一時テーブルはOS領域側とMySQLのデータ領域のどちらにしておくのがベターでしょうか。


ディスクフルだけを考えるのであれば、↓のようになります。

- OS領域側に作った場合…フルになった時にデータベースの破損を心配する必要はない、ただし非特権ユーザーで動いているその他のデーモンは死ぬかもしれない
- MySQL領域側に作った場合…フルになった時にまず間違いなくbinlogは壊れる、mysqld以外のプロセスは影響を受けない

OS領域 < MySQL領域 でストレージを切ることが多いこと（ストレージサイズが大きければ大きいほどディスクフルは遠のく）と
変にデーモンがが死ぬよりMySQLを全部まるっとリストアする方が簡単なこと（これは本当に個人的ですが）から
わたしならMySQL領域側にtmpdirを設定します。

2パーティションにこだわらないなら、tmpdir専用にパーティションを切るのが一番安全側に倒した感じでしょうか。


> また、一時テーブルがなるべく作成されないように、tmp_table_sizeとmax_heap_table_sizeはどの程度の容量を指定すべきでしょうか。


ディスク領域を食いつぶすようなテンポラリーテーブル（十GB単位？）に対しては、
tmp_table_size と max_heap_table_size
で制御するのは難しいと思います（100GBに設定しておいたところで、100GB + αになった時点で100GB吐かれるということなので）
調達面でも、（たとえば）100GBのメモリーを確保するよりは100GBストレージを足してtmpdir専用に領域を切った方が簡単な気がします。。


> InnoDBの場合はどうなのかという記載は特に見つからないのですが、ストレージエンジンに関わらず、必ず書き込みロックがかかると考えてよいのでしょうか。


1. ストレージエンジンより上のレイヤーでロックを取ります（メタデータロック）。この間は読み書きどちらもできません。
2. InnoDBのレイヤーでロックを取りそうですが、ADD PARTITIONの場合はロックを取らなさそうにも見えます。
    https://github.com/mysql/mysql-server/blob/mysql-5.7.17/storage/innobase/handler/ha_innopart.cc#L4271-L4293
    取ったとしてibdファイルに対して外部ロックしているようにも思える。。（未検証）

ALTER TABLE t1 ADD PARTITION (..), ALGORITHM= INPLACE, LOCK=NONE;
が構文エラーとして蹴られたので、
そもそもオンラインALTER TABLEとしてパースされていないと思います（ので、いつも通りの書き込みブロックだと思います）

ただ、ADD PARTITIONだけなら軽そうな処理に見えるのですがどうでしょう…？ （つまり、がまんする）


yoku0825,


2017年4月6日 10:14 Yuji Fujihara <yflab73@xxxxxxxxxx>:
> お世話になっております。藤原と申します。

>

> 以下、2つについて質問させてください。

> なお、後日実機でも検証する予定です。

>

> 一時テーブルのディレクトリの場所と容量、設定について

> 初期値では、/tmpに一時テーブルが作成されますが、この一時テーブルの領域は、どのように設計することが多いのかご存知であれば設計例を教えていただけますでしょうか。

>

> OS領域とMySQLのデータ領域のディスクパーティションは分けています。

>

> 気になるのは大量のSQLが実行され、一時テーブルが作成され、ディスクフルになってしまった時の挙動です。

>

> ディスクフルになるかもしれないことを想定した場合、一時テーブルはOS領域側とMySQLのデータ領域のどちらにしておくのがベターでしょうか。

>

> また、一時テーブルがなるべく作成されないように、tmp_table_sizeとmax_heap_table_sizeはどの程度の容量を指定すべきでしょうか。

>

> 以下のページを参考にしています。

> https://dev.mysql.com/doc/refman/5.6/ja/internal-temporary-tables.html

> https://dev.mysql.com/doc/refman/5.6/ja/temporary-files.html

> http://k-1-ne-jp.blogspot.jp/2012/10/mysqltmpdir.html?m=1

>

>

>

> パーティション追加時のInnoDBの書き込みロックについて

> MySQLの公式ページでは、パーティション操作時は、テーブルの書き込みロックがかかると記載があります。

>

> InnoDBの場合はどうなのかという記載は特に見つからないのですが、ストレージエンジンに関わらず、必ず書き込みロックがかかると考えてよいのでしょうか。

>

> 想定しているは、1テーブルに対して、日毎にパーティションを作成しており、一日一回未来日のパーティションを追加しています。この時、InnoDBにおいて、書き込みロックがかかってしまうのかどうか、書き込みロックの回避方法があるのかどうかです。

>

> ご存知の方がおりましたらご教授いただけますと幸いです。

>

> 書き込みロックが回避できない場合は、事前に未来日のパーティションを作成しておくことも考えています。1テーブルに対するパーティション作成の上限値は8192ということも理解しています。

>

> 以下のページを参考にしています。

> https://dev.mysql.com/doc/refman/5.6/ja/partitioning-limitations.html

> https://dev.mysql.com/doc/refman/5.6/ja/online-ddl-partitioning.html

> https://dev.mysql.com/doc/refman/5.6/ja/partitioning-limitations-locking.html

>

> 藤原