クエリのベンチマーク

Mysql

仕事でクエリの最適化するのに簡単なベンチマークを採る必要があったので作った。探した方が早いかと思ったけど単純に同じクエリを必要回数発行してトータル時間見るだけなので自分が使いやすければそれでおｋ。あんまり変わらないと思うけどAPに合わせてPHPで書いてみた。単体で動いてどこでも使えるようにPEAR使ってないのでコマンドライン用に書くのがめんどくさい。

#!/usr/bin/php
<?php

($argc == 1) ?  Bench::usage() : $params = Bench::parse_args($argv);

$bench = new Bench($params);
$bench->bootstrap();



class Bench
{
    var
        $params = array();
    
    function __construct($params)
    {
        $this->params = $params;
    }
    
    function bootstrap()
    {
        $con = @mysql_connect(
                             $this->params['dbhost'],
                             $this->params['dbuser'],
                             $this->params['dbpass']
                             ) || die(sprintf("[ERROR %d]: %s\n", mysql_errno(), mysql_error()));
        @mysql_select_db($this->params['dbname']) || die(sprintf("[ERROR %d]: %s\n", mysql_errno(), mysql_error()));

        $this->bench();
        
        mysql_close();
    }
    
    function output($file, $queries, $begin, $end)
    {
       echo sprintf('[%s]: %d queries %d times total %s sec (avg. %s sec).', $file, count($queries), $this->params['count'], 
                    round($end - $begin, 5), round(($end - $begin)/$this->params['count'], 5)) , "\n"; 
    }
    
    function execute($file)
    {
        $_queries = explode(';', file_get_contents($file));
        foreach($_queries as $query)
        {
            if(trim($query) == '') continue;
            $query = ($this->params['cache'])
                      ? preg_replace('/SELECT[\s\t\n\r]+(SQL_NO_CACHE)?/i', 'SELECT ', $query)
                      : preg_replace('/SELECT[\s\t\n\r]+(SQL_NO_CACHE)?/i', 'SELECT SQL_NO_CACHE ', $query);
            mysql_query($query) || die(sprintf("[ERROR %d]: %s on '%s'\n", mysql_errno(), mysql_error(), $file));
            $queries[] = $query;
        }
        $begin = Bench::get_microtime();
        for($i=0; $i<$this->params['count']; $i++)
        {
            foreach($queries as $query) mysql_query($query);
        }
        $end = Bench::get_microtime();
        $this->output($file, $queries, $begin, $end);
    }

    function bench()
    {
        foreach ($this->params['files'] as $file)
        {
            if (is_dir($file))
            {
                $_files = glob(rtrim($file, DIRECTORY_SEPARATOR) . DIRECTORY_SEPARATOR . '*.sql');
                if (!empty($_files))
                {
                    foreach($_files as $_file)
                    {
                        $this->execute($_file);
                    }
                }
            }
            else
            {
                $this->execute($file); 
            }
        }
    }

    function usage()
    {
    ?>
    Usage: bench [options] file ... 

    The options are as follows:
        -h   Database Host     (default: localhost)
        -u   Database User     (default: root)
        -p   Database Password (default: '')
        -d   Database Name     (default: mysql)
        -n   Repeat Count      (default: 1000)
        -c   Using Query Cache (default: false)      

    <?php
        exit();
    }

    function parse_args($args)
    {
        $script = array_shift($args);
        $params = array(
                        'dbhost' => 'localhost',
                        'dbuser' => 'root',
                        'dbpass' => '',
                        'dbname' => 'mysql',
                        'count'  => 1000,
                        'cache'  => false,
                        'files' => array()
                    );
        $options = array(
                        '-h' => 'dbhost',
                        '-u' => 'dbuser',
                        '-p' => 'dbpass',
                        '-d' => 'dbname',
                        '-n' => 'count',
                        '-c' => 'cache',
                    );
        $key = '';
        foreach ($args as $arg)
        {
            if($key)
            {
                switch($key)
                {
                    case 'count':
                        $params[$key] = intval($arg);
                        break;
                    case 'cache':
                        $params[$key] = in_array($arg, array('0','false')) ? false : true;
                        break;
                    default:
                        $params[$key] = $arg;
                }
                $key = '';
            }
            else if(isset($options[$arg]))
            {
                $key = $options[$arg];
            }
            else
            {
                $params['files'][] = $arg;
            }
        }
        if(empty($params['files'])) Bench::usage();
        foreach ($params['files'] as $file)
        {
            if(!is_dir($file) && !is_file($file)) Bench::usage();
        }
        
        return $params;
    }

    function get_microtime()
    {
        list($usec, $sec) = explode(" ", microtime());
        return ((float)$usec + (float)$sec);
    }
}
?>

適当なディレクトリに『bench.php』とか適当な名前で保存して実行権限つけて使います。
スイッチも単純なものしかないけど、

$ ./bench.php

    Usage: bench [options] file ... 

    The options are as follows:
        -h   Database Host     (default: localhost)
        -u   Database User     (default: root)
        -p   Database Password (default: '')
        -d   Database Name     (default: mysql)
        -n   Repeat Count      (default: 1000)
        -c   Using Query Cache (default: false)

みたいな感じで。あとは測定したいクエリを『test.sql』とかに書いて、

$ cat test.sql
SELECT * FROM users;
SELECT * FROM entris;


$ ./bench.php -d DB名 test.sql
[test.sql]: 2 queries 1000 times total 0.46662 sec (avg. 0.00047 sec).

な感じで使えます。

  • デフォルトはクエリキャッシュを無効にする(SQL_NO_CACHEを自動付加）ようになってるのでキャッシュ有効にする場合は -c true オプションつけて実行してください。（別途my.iniとかでクエリキャッシュを有効にしておく必要有り）
  • 複数ファイルをテストしたいときはそのままファイル名をスペースで繋げて実行してください。
  • ファイル名の代わりにディレクトリ名を指定すればディレクトリ内の *.sql をすべてテストできます。