カテゴリー別アーカイブ: database

mac osx におけるファイルディスクリプタの上限

概要

mac osxでmysql5.7.11を用いてアプリケーション開発を行ったところ、特定の動作をした時点でアプリケーションが動作しなくなる現象に直面した。

mysqlのエラーログを確認してみると下記のようなエラーが出ていることが確認できた。

[Warning] File Descriptor 2032 exceedeed FD_SETSIZE=1024

開発にともなって行った「特定の動作」とはmysqlにおいて、あるテーブルにpartitioningを適用したことである。それも結構な数の。
なるほどmysqlエラーログと合わせてみてみるとその状況は察することができる。
partitioningを切ったことで、物理的に異なるファイルとしてその実態データファイル(InnoDBでいうところのibdファイル)が分離された。
mysql上でそれら実体ファイルをオープンしているfile descriptorがosの上限を上回ったのであろう。

この点について原因の調査と、対応方法をまとめる。

検証

さて、ここでアプリケーション上で起こった問題を再現して、再度その問題が憶測と合っているか検証してみよう。

ここで、sqlを作成する簡単なphpプログラムを用意する。
余談だがやっぱ私の年代となるとスクリプトももっぱらphpだな。

テーブルを作成するsqlを生成するphpコード

下記のコードを実行することで、3000個のパーティショニングを日付で切ったテーブルを作成する
ちなみに、paritioningの最後のところでカンマが一つ多くなるので注意されたし。
そのまま実行する場合は構文エラーになるので手動で削除してください。

<?php
date_default_timezone_set('Asia/Tokyo');

$pivot = strtotime('2010-01-01 00:00:00');
create_partition($pivot);

// create partition ddl
function create_partition($date){
	echo "CREATE TABLE t (id BIGINT auto_increment, name VARCHAR(50), purchased DATE, primary key(id, purchased)) ENGINE=InnoDB DEFAULT charset=utf8 PARTITION BY RANGE COLUMNS(purchased) (";
	for($i=0; $i<3000; $i++) {
		echo "PARTITION p{$i} VALUES LESS THAN ('" . date('Y-m-d', $date) . "')," . PHP_EOL;
		$date = strtotime(date('Y-m-d H:i:s', $date) . " +1 days");
	}
	echo ");";
}

データを挿入するsqlを生成するphpコード

続いて各パーティションにデータを挿入してみる。
下記のコードを実行する。

<?php
date_default_timezone_set('Asia/Tokyo');

$pivot = strtotime('2010-01-01 00:00:00');
create_data($pivot);

// insert data
function create_data($date){
	for($i=0; $i<2999; $i++) {
		echo "INSERT INTO t(name, purchased) VALUES('name{$i}', '" . date('Y-m-d H:i:s', $date) . "');" . PHP_EOL;
		$date = strtotime(date('Y-m-d H:i:s', $date) . " +1 days");
	}
}

そしてその状態で再度mysqlへ接続し、use文を用いてデータベースを選択すると

[Warning] File Descriptor 2032 exceedeed FD_SETSIZE=1024

再現した。

調査

原因は概ね特定できたので、この件について対応方法がないかweb上で検索する。

するとピタリ。こちらのmysqlのバグレポートに全く同じ事象が報告されている。

詳しくは上記のリンクに目を通していただけるとよい。
要約すると osx 固有のバグであり、予想していたように FD_SETSIZE が os の規定値を上回っているためである。
また、するとこの os が持っているファイルディスクリプタの上限値を変更できないのか?
ということを想像するが、残念ながらこれは mac osx で動作している現在の mysql5.7.11 では不可能である。

というのもこちらの github コードを参照してもらうとわかるように osx では FD_SETSIZE 決め打ちで上限を規定している。
これを回避するためには自分でソースコードを改修し、リビルドするしかない。

対応

では、この問題に対応できないのか?というと、そういうわけでもない。
別のアプローチで対処する事が可能である。

問題は mysql がテーブルを参照する際にオープンしている file descriptor の数が多すぎることである。
mysql アプリケーションのパラメータを変更することである程度コントロールすることができる。
具体的には一番大きな影響を与えている、テーブルのキャッシュを数を減らすことである

テーブルエントリのキャッシュ数を変更するには下記の項目を my.cnf に付け加える
数値は上限(osxでは1024)に達しないように調整する。

table_open_cache=400

すると一度オープンしたテーブルの file descriptor を保持しないようになるため、問題を解決できる。
なお、この対応方法はパフォーマンスに大きく影響をあたえる場合もあるので十分注意するべきである。
通常の linux ベースのサーバなどでは file descriptor の上限を上げるなどの対応を合わせて検討するべきである。


mysqlにおけるvarcharのindex使用時の注意

概要

みなさんmysqlを使用していてvarcharのカラムにindexをつけることってないだろうか?
selectする際にindexが付いているカラムを対象にwhereをつけているのにindexが有効になっていない機会があり得る。
今回はそんな時に認識しておかないと、はまるかもしれない挙動について取り上げる。
(そもそもvarcharにindexつけることについてはまた別の機会にでも)

準備

  • テーブル
  • varcharのカラムにindex付いているテーブルを用意

  • 確認方法
  • 確認方法としてはmysqldの設定としてINDEXの効いていないクエリがslowlogに出力されるように設定し、slowlogの出力を確認しながら進める。

    事前確認

    まずは普通にqueryを発行してみる

  • 数値として検索した場合
  • mysql> select count(*) from t1 where column1 = 7395584;
    +----------+
    | count(*) |
    +----------+
    |       37 |
    +----------+
    1 row in set (0.63 sec)
    
    # Time: 150129  5:47:34
    # User@Host: mysql[mysql] @ localhost []
    # Query_time: 0.633334  Lock_time: 0.000088 Rows_sent: 1  Rows_examined: 1264725
    SET timestamp=1422510454;
    select count(*) from t1 where column1 = 7395584;
    
  • 文字列として検索した場合
  • mysql> select count(*) from t1 where column1 = '7395584';
    +----------+
    | count(*) |
    +----------+
    |       37 |
    +----------+
    1 row in set (0.01 sec)
    

    早い。slowlogの出力はなし。

  • explain
  • explainすると数値として検索した場合はいわゆるフルスキャンとなっていることがわかる

    mysql> explain select count(*) from t1 where column1 = 7395584;
    +----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
    | id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows    | Extra                    |
    +----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
    |  1 | SIMPLE      | t1     | index | idx1          | idx1 | 396     | NULL | 1265625 | Using where; Using index |
    +----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
    1 row in set (0.00 sec)
    
    mysql> explain select count(*) from t1 where column1 = '7395584';
    +----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+
    | id | select_type | table  | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
    +----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+
    |  1 | SIMPLE      | t1     | ref  | idx1          | idx1 | 386     | const |   37 | Using where; Using index |
    +----+-------------+--------+------+---------------+------+---------+-------+------+--------------------------+
    1 row in set (0.00 sec)
    

    と、生のmysqlクライアントからの検索は分かった。
    アプリケーションからの実行はどうなるんだろうか。
    使用する機会の多いpdoについて検証してみる。

    アプリケーションレイヤからの検証

  • 生のquery
  • 簡単なphpアプリケーションを作成して、slowlogの出力を見る。

    <?php
    $dns = "mysql:host=localhost;dbname=testdb;charset=utf8";
    $user = "mysql";
    $pass = "pass";
    $pdo = new PDO($dns, $user, $pass);
    
    $stmt = $pdo->query("SELECT count(*) AS cnt FROM t1 WHERE column1 = 7395584");
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    var_dump($row);
    

    出力は下記のようになった。きちんと動作はしている。

    [vagrant@local tmp]$ php select.php 
    array(1) {
      ["cnt"]=>
      string(2) "37"
    }
    

    さてslowlog出力は?

    # Time: 150129  5:55:12
    # User@Host: mysql[mysql] @ localhost []
    # Query_time: 0.242804  Lock_time: 0.000055 Rows_sent: 1  Rows_examined: 1264725
    SET timestamp=1422510912;
    SELECT count(*) AS cnt FROM t1 WHERE column1 = 7395584;
    

    でた!
    これに関してはpdoは特に何もしない。mysqlクライアントから直接クエリを入力した時と同じような結果となった。
    結果は割愛するが、シングルクォートで文字列を囲った時にはslowlogの出力はされなくなった。したがってindexによる検索が有効になっているようである。

    ここで勘の良い人は気になっているかもしれないが、では逆に数値のカラムにINDEXがはられている場合、where句に記述したデータをシングルクォートでくくり文字列として検索した場合どうなるのか。
    ということで気になっているであろうが、これに関して検証した結果どちらでも適切にindexが効くことが確認できた。

  • prepared statement
  • さてprepared statementを利用した場合はどのようになるのか、検証したいと思う。
    pdoにはbindを行うために二種類のインタフェースが存在するが、今回はbindValueを取り上げる。
    マニュアルを確認すると第三引数にバインドするパラメータの方が指定できる。なんとなく今検証していることをの結果を匂わせるようなインタフェースをしている。

    まずは第三引数のことはおいておき、純粋にPHPの型推論による挙動の変化を観察しようではないか。

    初めに数値としてbindValueを実行してアプリケーションを叩いてみる。
    アプリケーションは下記のようになる。

    <?php
    $dns = "mysql:host=localhost;dbname=testdb;charset=utf8";
    $user = "mysql";
    $pass = "pass";
    $pdo = new PDO($dns, $user, $pass);
    
    $sql = "SELECT count(*) AS cnt FROM t1 WHERE column1 = :column1";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array('column1' => 7395584));
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    var_dump($row);
    

    動作を確認し、結果も正常に取得できている。

    [vagrant@local tmp]$ php select2.php 
    array(1) {
      ["cnt"]=>
      string(2) "37"
    }
    

    さて、肝心なslowlogはどうなっているかというと。
    出ていない!
    prepared statementとbindを利用すると、数値文字列などのギャップはpdo側で吸収してくれるようだ。

    また当然bindの引数の型を文字列に加工したとしてもslowlogは出力されずに、indexが有効になっていることが確認できた。

    これについて思うことは、phpの場合に限って言うとなるべくprepared statementを使用したほうがお馬鹿なミスが減るだろうということだ。
    生のsqlは思ったよりもずっとデリケートなのかもしれない。

    他のアプリケーションに関してもアプリケーションが提供してくれる機能を用いるほうが良いだろう。


    count(*)からinnodbにおけるindex構成を確認する

    * 概要

    今回はinnodbにおけるcountの高速化について検証する。

    きっかけは下記のブログですが。いつもお世話になっております。

    http://nippondanji.blogspot.jp/2010/03/innodbcount.html

    要約すると下記のようなスキーム雨がある時

    CREATE TABLE t1 (  
      a bigint(20) unsigned NOT NULL AUTO_INCREMENT,  
      b int(11) DEFAULT NULL,  
      c tinyint(4) DEFAULT NULL,  
      d date DEFAULT NULL,  
      e varchar(200) DEFAULT NULL,  
      f varchar(200) DEFAULT NULL,  
      g varchar(200) DEFAULT NULL,  
      h varchar(200) DEFAULT NULL,  
      i varchar(200) DEFAULT NULL,  
      PRIMARY KEY (a)  
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
    

    下記のようなsqlを想定する

    SELECT count(*) FROM t1;
    

    このとき例えばtinyintなどにindexを貼ることで、count(*)の高速化が見込める。

    innodbのcount(*)において全レコードへのアクセスが必要になることは変わりないが
    これは主キー(bigint)を全走査することよりも、小さいindexを全走査するほうが効率が良いということである。

    まあ頭のなかでは理解できて、予想はできているんだけどちゃんと自分の手でピコピコやりたいなというところで下記を確認する。
    1. 検索速度がa,b,cで変わることを確認(参照テーブルも)
    2. e,f,g,h,iがあるときとないときで検索速度がそこまで変わらないことの確認(クラスタインデックスのノードが影響を与えないこと)

    * 検証

    検証2のリーフノードの大きさの検証のため下記データベースを用意する。

    CREATE TABLE t1 (  
      a bigint(20) unsigned NOT NULL AUTO_INCREMENT,  
      b int(11) DEFAULT NULL,  
      c tinyint(4) DEFAULT NULL,  
      d date DEFAULT NULL,  
      e varchar(200) DEFAULT NULL,  
      f varchar(200) DEFAULT NULL,  
      g varchar(200) DEFAULT NULL,  
      h varchar(200) DEFAULT NULL,  
      i varchar(200) DEFAULT NULL,  
      PRIMARY KEY (a)  
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
    
    CREATE TABLE t2 (
      a bigint(20) unsigned NOT NULL AUTO_INCREMENT,  
      b int(11) DEFAULT NULL,  
      c tinyint(4) DEFAULT NULL,  
      PRIMARY KEY (a)  
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
    

    下記みたいな感じでデータを作成する

    <?php
    
    $pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8','root','');
    $stmt = $pdo->prepare("INSERT INTO t1 (b,c,d,e,f,g,h,i) VALUES (:b,:c,:d,:e,:f,:g,:h,:i)");
    
    $bind = array();
    $bind['b'] = $i;
    $bind['c'] = 1;
    $bind['d'] = '2014-12-25';
    $bind['e'] = '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
    $bind['f'] = '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
    $bind['g'] = '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
    $bind['h'] = '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
    $bind['i'] = '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
    
    foreach($bind as $key => $value){
     $stmt->bindValue($key, $value);
    }
    
    for($i=1; $i<=10000000; $i++) {
     $stmt->execute();
    }
    
    // 値同じだけ今回はcountなので影響なし。
    

    * sqlのクエリキャッシュを無効にする

    mysql> show variables like 'query_%';
    +------------------------------+---------+
    | Variable_name                | Value   |
    +------------------------------+---------+
    | query_alloc_block_size       | 8192    |
    | query_cache_limit            | 1048576 |
    | query_cache_min_res_unit     | 4096    |
    | query_cache_size             | 0       |
    | query_cache_type             | OFF     |
    | query_cache_wlock_invalidate | OFF     |
    | query_prealloc_size          | 8192    |
    +------------------------------+---------+
    7 rows in set (0.00 sec)
    

    * 検索する

    まずは2.について。リーフノードのデカさは検索性能に影響するのか。
    予想ではしないと思う。B木のキーノードだけ走査するわけだから。

    mysql> explain select count(*) from t1;
    +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
    | id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
    +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
    |  1 | SIMPLE      | t1    | index | NULL          | PRIMARY | 8       | NULL | 9140175 | Using index |
    +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
    1 row in set (0.00 sec)
    
    mysql> desc t1;
    +-------+---------------------+------+-----+---------+----------------+
    | Field | Type                | Null | Key | Default | Extra          |
    +-------+---------------------+------+-----+---------+----------------+
    | a     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
    | b     | int(11)             | YES  |     | NULL    |                |
    | c     | tinyint(4)          | YES  |     | NULL    |                |
    | d     | date                | YES  |     | NULL    |                |
    | e     | varchar(200)        | YES  |     | NULL    |                |
    | f     | varchar(200)        | YES  |     | NULL    |                |
    | g     | varchar(200)        | YES  |     | NULL    |                |
    | h     | varchar(200)        | YES  |     | NULL    |                |
    | i     | varchar(200)        | YES  |     | NULL    |                |
    +-------+---------------------+------+-----+---------+----------------+
    9 rows in set (0.00 sec)
    
    mysql> select count(*) from t1;
    +----------+
    | count(*) |
    +----------+
    | 10000000 |
    +----------+
    1 row in set (15.87 sec)
    
    mysql> explain select count(*) from t2;
    +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
    | id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
    +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
    |  1 | SIMPLE      | t2    | index | NULL          | PRIMARY | 8       | NULL | 9223787 | Using index |
    +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
    1 row in set (0.00 sec)
    
    mysql> desc t2;
    +-------+---------------------+------+-----+---------+----------------+
    | Field | Type                | Null | Key | Default | Extra          |
    +-------+---------------------+------+-----+---------+----------------+
    | a     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
    | b     | int(11)             | YES  |     | NULL    |                |
    | c     | tinyint(4)          | YES  |     | NULL    |                |
    +-------+---------------------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)
    
    mysql> select count(*) from t2;
    +----------+
    | count(*) |
    +----------+
    | 10000000 |
    +----------+
    1 row in set (1.98 sec)
    

    !驚愕である。リーフノードの大きさが検索性能に大きく依存しているではないか。
    これについてはもっと深追いして理解する必要がありそうだ。

    * b (int)にindexを追加

    mysql> ALTER TABLE t1 ADD INDEX idx_int(b);
    Query OK, 0 rows affected (33.17 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> explain select count(*) from t1;
    +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
    | id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
    +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
    |  1 | SIMPLE      | t1    | index | NULL          | idx_int | 5       | NULL | 9140175 | Using index |
    +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
    1 row in set (0.00 sec)
    
    mysql> select count(*) from t1;
    +----------+
    | count(*) |
    +----------+
    | 10000000 |
    +----------+
    1 row in set (1.92 sec)
    

    疾いっ!

    * つづいてc (tinyint)にindexを追加

    mysql> ALTER TABLE t1 ADD INDEX idx_tinyint(c);
    
    Query OK, 0 rows affected (34.54 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> explain select count(*) from t1;
    +----+-------------+-------+-------+---------------+-------------+---------+------+---------+-------------+
    | id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows    | Extra       |
    +----+-------------+-------+-------+---------------+-------------+---------+------+---------+-------------+
    |  1 | SIMPLE      | t1    | index | NULL          | idx_tinyint | 2       | NULL | 9140175 | Using index |
    +----+-------------+-------+-------+---------------+-------------+---------+------+---------+-------------+
    1 row in set (0.00 sec)
    
    mysql> select count(*) from t1;
    +----------+
    | count(*) |
    +----------+
    | 10000000 |
    +----------+
    1 row in set (1.78 sec)
    

    あんまりintと変わらない。これも参考元にある通り。
    だが内部的には読み込みページとかの量が半減しているはずである。

    さて予想外の挙動を見せたクラスタインデックスの違いはなんだろうか。

    ここでt1のvarcharで表されるカラムに対してもゴミを投入していたことに着目する具体的には下記のような感じで全レコードに対して同じようなデータを投入している
    検証のために下記のようなテーブルt6を作成した

    CREATE TABLE <code>t6</code> (
      <code>a</code> bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      <code>b</code> int(11) DEFAULT NULL,
      <code>c</code> tinyint(4) DEFAULT NULL,
      <code>d</code> date DEFAULT NULL,
      <code>e</code> varchar(200) DEFAULT NULL,
      <code>f</code> varchar(200) DEFAULT NULL,
      <code>g</code> varchar(200) DEFAULT NULL,
      PRIMARY KEY (<code>a</code>)
    ) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8;
    

    これに対してデータを1000万件投入する。その時のデータと速度は下記のようになっている

    mysql> select * from t6 limit 1;
    +----------+-----------+------+------------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------+
    | a        | b         | c    | d          | e                                                                                                    | f                                                                                                    | g    |
    +----------+-----------+------+------------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------+
    | 10000001 | 100000000 |    1 | 2014-12-25 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |      |
    +----------+-----------+------+------------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------+
    1 row in set (0.00 sec)
    
    mysql> select count(*) from t6;
    +----------+
    | count(*) |
    +----------+
    | 10000000 |
    +----------+
    1 row in set (10.42 sec)
    

    リーフノードのデータ量が検索速度に影響を及ぼす可能性を検証するためにカラムgを空文字にupdateして検証する。

    mysql> update t6 set g = '';
    Query OK, 10000000 rows affected (1 min 55.63 sec)
    Rows matched: 10000000  Changed: 10000000  Warnings: 0
    
    mysql> alter table t6 engine innodb;
    Query OK, 10000000 rows affected (59.86 sec)
    Records: 10000000  Duplicates: 0  Warnings: 0
    
    mysql> select count(*) from t6;
    +----------+
    | count(*) |
    +----------+
    | 10000000 |
    +----------+
    1 row in set (6.58 sec)
    

    なんと!高速化されたではないか。
    正直今までcount(*)するときにindexのキーだけなめて検索しているのかと思っていた。(これはcount(id)でも計測時間が変わらなかったことからの推測でもある)
    しかしこの結果から導かれるのは、count(*)したときにリーフノードのデータを読み取っているということにほかならないのではないかと。

    B木インデックスってのはB木のキーと値を全部読み取るような振る舞いをしている。と仮定できる。