アシアルブログ

アシアルの中の人が技術と想いのたけをつづるブログです

MySQLのストアドプロシージャと生PHPによるパフォーマンス比較

皆さん、こんばんは。笹亀です。

7月もあっという間に10日間が過ぎて、夏真っ盛りになってきました。
自分も夏対策でアイス眠というマットレスを購入して夏を乗り越えようと思っております。

さて、本日はストアドプロシージャについて検証をしてみたいと思います。
ストアドプロシージャとは、一連のSQL文や処理に名前をつけて保存したものです。
PHPの関数と同じでSQLを関数みたいにしたものがストアドプロシージャになります。
MySQLではストアドプロシージャはMySQLでは5.0から利用が可能です。
今回はMySQLのストアドプロシージャの使い方だけではなく、
PHPで記載したときとのパフォーマンスを比較してみたいと思います。

MySQLのストアドプロシージャを呼び出して処理をするPHPプログラムとストアドプロシージャで作成したものと同じSQLを実行するPHPプログラムを作成します。
上記2つのプログラムを前回のブログで紹介したJMeterで負荷をかけて、パフォーマンス値を検証してみたいと思います。

まずはテスト用のデータ保存用のテーブルを作成します。


CREATE TABLE `t_test_procedure` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `body` text,
  `created_at` datetime,
  `updated_at` datetime,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


次にMySQL用の簡単なストアドプロシージャを作成します。
 ※IDをSELECTしてそのIDのデータがあればUPDATE、なければINSERTする


delimiter $$
DROP PROCEDURE IF EXISTS TEST_PROCEDURE$$

-- usage : call TEST_PROCEDURE('1', 'てきすとデータほげほげ', @code);
-- usage : call TEST_PROCEDURE('2', 'てきすとデータほげほげ', @code);
  /*
   * テスト用のプロシジャープログラム
    IDでSELECTして同じものがあれば更新するプロシジャー
   */
  CREATE PROCEDURE TEST_PROCEDURE (
    IN  v_id       BIGINT,    -- ID
    IN  v_text     TEXT,     -- テストテキスト
    OUT code        TEXT     -- 結果コード  
  ) 
ESC:BEGIN
    DECLARE  cnt       BIGINT;

    DECLARE EXIT HANDLER FOR SQLWARNING, SQLEXCEPTION, NOT FOUND
      BEGIN
        SET code    = '-99';
      END;   
  
  /* データ既に登録されているか確認 */
  SELECT COUNT(id) INTO cnt FROM t_test_procedure
      WHERE id = v_id;

  /* 登録されていない場合はINSERTそれ以外はUPDATE */
  IF cnt = 0 THEN
    INSERT INTO t_test_procedure VALUES (v_id, v_text, now(), now());
  ELSE
    UPDATE t_test_procedure SET body = v_text, updated_at = now() WHERE id = v_id;
  END IF;
  
  SET code = '0'; -- 正常コードを返す
END;
$$
delimiter ;


次にプロシージャを実行するPHPプログラムとプロシージャを展開したPHPプログラムを準備します。
■プロシージャ版


<?php
$dsn = 'mysql:dbname=test;host=localhost;unix_socket=/tmp/mysql.sock';
$user = 'root';
$password = 'pass';

try{
    $dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
    print('Error:'.$e->getMessage());
    die();
}

$stamp = microtime();
list($msec, $sec) = explode(" ", $stamp);

$text ='TESTTEST String BODY DATE = ';
$text .= date('Y/m/d H:i:s.') . (float)$msec;

$dbh->query("START TRANSACTION");
try {
  $sql = 'call TEST_PROCEDURE(?,?,@res)';
  $sth = $dbh->prepare($sql);
  $sth->execute(array($_GET['id'], $text));
} catch(Exception $e) {
  $dbh->query("ROLLBACK");
  exit;
}

$dbh->query("COMMIT");

//$sth = $dbh->prepare("SELECT @res");
//$sth->execute();
//$result = $sth->fetchAll();

var_dump($result[0][0]);

$dbh = null;


■展開版


<?php
$dsn = 'mysql:dbname=test;host=localhost;unix_socket=/tmp/mysql.sock';
$user = 'root';
$password = 'pass';

try{
    $dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
    print('Error:'.$e->getMessage());
    die();
}

$stamp = microtime();
list($msec, $sec) = explode(" ", $stamp);

$text ='TESTTEST String BODY DATE = ';
$text .= date('Y/m/d H:i:s.') . (float)$msec;

$dbh->query("START TRANSACTION");
try {
  $sth = $dbh->prepare("SELECT count(id) FROM t_test_procedure WHERE id = ?");
  $sth->execute(array($_GET['id']));
  $result = $sth->fetchAll();

  if ($result[0][0] == 0) {
    $sql = 'INSERT INTO t_test_procedure VALUES(?, ?, now(), now())';
    $sth = $dbh->prepare($sql);
    $sth->execute(array($_GET['id'], $text));
  } else {
    $sql = 'UPDATE t_test_procedure SET body=?, updated_at=now() WHERE id=?';
    $sth = $dbh->prepare($sql);
    $sth->execute(array($text, $_GET['id']));
  }
} catch(Exception $e) {
  $dbh->query("ROLLBACK");
  exit;
}

$dbh->query("COMMIT");

$dbh = null;


上記2つのプログラムを実行するそれぞれのテストケースをJMeterで作成します。JMeterについては前回ブログに使い方などを記載しておりますので、参考にしてください。
 ※1〜20,000までのIDを発行して登録して1〜20,000までを発行しおわった後に更新するテストケースを作成します(。

同時アクセス100で200回処理(計:20,000アクセス)をさせました結果を表示します。
注目する箇所は「Throughput」部分です。

プロシージャ版の負荷テスト結果

Throughput: 248.5/sec(合計値

展開版の負荷テスト結果

Throughput: 231.0/sec(合計値

予想ではプロシージャの方が処理が遅いと思っていましたが、展開版の方が時間が処理能力がよくありませんでした。
以前のプロジェクトで複雑な処理をしたプロシージャがあり、負荷テストをした際にそのプロシージャの実行に時間がかかっておりました。対応として今回の検証のようにPHPで展開したものを新たに用意して両方の負荷テストしました。その際にはPHPで展開して実装した方が2倍くらいの処理能力が向上しました。その経緯があったので展開版の方が処理能力がいいと思っておりました。

今回の検証でシンプルな処理をまとめてするのであればプロシージャを利用してもさほど処理能力は変わらないとがわかりました!ただ、プロシージャに頼り過ぎて複雑にSQL文と処理を組み込んでしまうとパフォーマンス低下につながりますので、うまく使い分けをして利用することをお薦め致します^^

JMeterを有効活用して精度の高い負荷テストをしよう

みなさん、こんばんは。笹亀です。

アシアルのコーポレートサイトが先日、5年ぶりくらいにリニューアル致しました!
サイトもHTML5で組み直し、全面リニューアルしております。
とてもインパクトがあるサイトになっておりますので、是非、覗いて行ってください。
 http://www.asial.co.jp

本日は、JMeterについてご紹介しようと思います。
JMeterは負荷テストを行うための有名なツールですが、ヘッダー情報などの設定を自由にできることが便利です。自分がよく負荷テストをかけるときには、よくユーザ固有な情報を使った負荷テスト(例:携帯のUIDを使った操作)を考えて実施することが多いです。その手法についても一緒にご紹介させていただきます。

まずはJMeterをインストールしましょう。下記の箇所を参考にするとインストールできると思います。JMeterインストーラーはないので、コマンドで実行することになります。
http://ogsheltie.blogspot.jp/2011/09/jmeter.html

実行コマンド例:/Applications/jakarta-jmeter-2.3.4/bin/jmeter



JMeterを起動したら、負荷テストをするために下記の情報について準備をしておきます。
1.負荷テスト用のデータ登録用テーブル作成


CREATE TABLE `T_TEST` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `UID` varchar(255) DEFAULT NULL,
  `HEADER` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


2.負荷テスト用にデータ登録をするPHPを作成(jmeter_test.php


<?php

$pdo = new PDO('mysql:dbname=test;host=127.0.0.1;', 'root', 'pass');

//ヘッダー情報取得(AuのUID取得
$uid = $_SERVER['HTTP_X_UP_SUBNO'];
 
$header_text = print_r($_SERVER, true);

//DBに登録
$pdo->query("INSERT INTO T_TEST VALUES('', '{$uid}', '{$header_text}')");

echo 'Insert OK';



上記で、負荷テストの準備完了です。次にJMeterのテストケースを作成していきます。
1.テスト計画を右クリックし、「追加 → スレッドグループ」を追加して、スレッド数、Ramp-Up期間、ループ回数を入力します。
 ※スレッドとしてまとまったテストケースの設定がおこなえるものです。


2.スレッドグループを右クリックし、「追加 → 設定エレメント → HTTPリクエスト初期値設定」を追加して、サーバ名ポート番号を入力します。
 ※サーバ名やポート番号などの初期値の設定ができるものです。


3.スレッドグループを右クリックし、「追加 → 設定エレメント → ユーザー定義変数」を追加して、変数を登録します。
 ※Jmeterで使用できる変数を作成することができます。

 名前:datetime 値:${__time(yyyyMMddss)}

4.スレッドグループを右クリックし、「追加 → 設定エレメント → HTTP認証マネージャ」を追加して、認証情報を登録します。
※ベーシック認証が掛かっている領域で認証してくれる仕組みです

 ※必ず、自分のテストをしたい環境に合わせて設定をしてください。

5.スレッドグループを右クリックし、「追加 → 設定エレメント → カウンタ」を追加して、カウンタ情報を登録します。
※テストを実行する度にカウントしてくれるカウンターの設定ができます。(参照名でテストで使用可能。


6.スレッドグループを右クリックし、「追加 → サンプラー → HTTPリクエスト」を追加して、テストするリクエスト情報を登録します。
※実行するスクリプトのテストケースを作成します。


7.HTTPリクエストを右クリックし、「追加 → サンプラー → HTTPヘッダマネージャ」を追加して、テストするリクエストのヘッダー情報を登録します。
※実行するスクリプトのテストケースを作成します。

ヘッダー名:x-up-subno(AuのUIDヘッダー名
ヘッダー値:stg_${datetime}${usercounter}_hb.ezweb.ne.jp
 ※3,5で登録した変数名を使用して動的に値が変更するヘッダー情報を作ります。

8.スレッドグループを右クリックし、「追加 → リスナー → 統計レポート」を追加して、テスト結果を表示するレポートを登録します。
※実行したテスト結果をレポート化するものです。


9.統計レポートをクリックした状態で「実行 → 開始」テストを実行します。




上の図は、100メソッドで5000ループするので合計50000回、実行されたことになります。スクリプト実行のエラー率や1秒あたりの処理数(スループット)などが表形式で表示されます。今回の実施結果では、エラー率は0%で1秒間に307リクエストをさばけるという結果が出ております。

レポートには統計レポートの他にもグラフなどで表示するリスナーもあります。
 ※追加 → リスナー → グラフで表示

今回のテストで一番お伝えしたかった箇所は7で設定したテストごとにユニークな情報(AuのUID)を作成する箇所です。このようにテスト毎でユニークな情報を作れれば負荷のテストケースとしても精度が高い負荷テストが行えます。負荷テストをするときには単にテストするのではなく、なるべくアクセス想定をした形で実施する方がよりよいテストと言えると思います。