機能追加 #109
不正対策（ASPセッションユニークチェック)
| ステータス: | 終了 | 開始日: | 2015/01/14 | |
|---|---|---|---|---|
| 優先度: | 通常 | 期日: | ||
| 担当者: | - | 進捗 %: | 0% | |
| カテゴリ: | 成果通知 | 作業時間の記録: | - | |
| 対象バージョン: | - |
説明
クリック時にASPのセッションのユニークチェックを行う
ASP別にチェックする／しないを選択
履歴
#3 山本 義治 が約10年前に更新
/www/aff_test_new/ad/www/cl/click_asp.php
[admin@web1 cl]$ diff click_asp.php /www/aff/ad/www/cl/
74c74
< $sql = "select asp_id,session_name,session_unique_check_flg from asp where asp_id = '$asp_id'";
---
> $sql = "select asp_id,session_name from asp where asp_id = '$asp_id'";
81c81
< list($asp_id,$session_name,$session_unique_check_flg) = mysql_fetch_array($res);
---
> list($asp_id,$session_name) = mysql_fetch_array($res);
226,237d225
< //ASPセッションユニークチェック
< $asp_session = "#asp#".$asp_cid."#".$user_id."#".$asp_info."#".$ec;
< $table = "click_log_".date("Ym");
< if($session_unique_check_flg && $user_id){
< $sql = "select count(*) from $table where user_id = '$asp_session'";
< $res = __mysql_query_ex($sql);
< list($asp_session_count) = mysql_fetch_array($res);
< if($asp_session_count > 0){
< print_error("ASPセッションユニークチェックに失敗しました",2);
< exit;
< }
< }
279a268
> $table = "click_log_".date("Ym");
312c301
< '$asp_session',
---
> '#asp#".$asp_cid."#".$user_id."#".$asp_info."#".$ec."',
#5 山本 義治 がほぼ10年前に更新
＜ベンチマーク＞
DBマスター: db3
DBスレーブ: db1
※ユニークチェック時click_logテーブルDBスレーブ参照
※apache benchmark並列10、合計100リクエストを各10回づつテスト
$ ab -c 10 -n 100 "http://web1.i-generation.jp:8000/action/click_asp.php?cid=1051&asid=192&acid=test&ainfo=ainfotest&af=aftest2&guid=ON"
ユニークチェックなし
Requests per second: 50.11 [#/sec] (mean)
Requests per second: 101.11 [#/sec] (mean)
Requests per second: 107.29 [#/sec] (mean)
Requests per second: 110.23 [#/sec] (mean)
Requests per second: 63.93 [#/sec] (mean)
Requests per second: 60.85 [#/sec] (mean)
Requests per second: 106.01 [#/sec] (mean)
Requests per second: 106.00 [#/sec] (mean)
Requests per second: 103.56 [#/sec] (mean)
Requests per second: 105.28 [#/sec] (mean)
ユニークチェックあり
Requests per second: 88.77 [#/sec] (mean)
Requests per second: 86.10 [#/sec] (mean)
Requests per second: 109.82 [#/sec] (mean)
Requests per second: 105.94 [#/sec] (mean)
Requests per second: 107.13 [#/sec] (mean)
Requests per second: 104.63 [#/sec] (mean)
Requests per second: 108.29 [#/sec] (mean)
Requests per second: 102.39 [#/sec] (mean)
Requests per second: 107.64 [#/sec] (mean)
Requests per second: 108.41 [#/sec] (mean)
$ top
db2
top - 19:40:10 up 1162 days, 5:10, 1 user, load average: 0.21, 0.11, 0.09
db3
top - 19:40:25 up 77 days, 20:21, 1 user, load average: 0.12, 0.03, 0.01
＜検証結果＞
ユニークチェックあり、なし共にロードアベレージ0.1〜0.2前後で安定していることを確認
他の要因で多少ロードアベレージの前後はあるが、今回の改修による影響はないと思われます
#6 山本 義治 がほぼ10年前に更新
テーブルロック発生
＜対象サーバ＞
DBスレーブ
＜対象スクリプト＞
/stat_asp.php
＜クエリー＞
SELECT TB1.m_owner_id, TB1.m_id, TB1.asp_id, TB1.listing_id, TB1.t_id FROM (SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201501 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201412 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201411 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201410 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201409 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201408 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201407 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201406 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201405 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201404 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201403 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201402 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201401 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201312 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201311 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201310 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201309 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201308 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201307 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201306 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201305 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201304 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201303 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201302 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201301 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201212 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201211 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201210 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201209 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201208 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201207 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201206 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201205 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201204 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201203 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201202 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201201 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201112 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201111 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201110 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201109 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201108 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201107 WHERE c_id = '112' AND subscriber = 'a3mDQzFjQqUMpPUp' AND status = '0') AS TB1 GROUP BY m_id,asp_id,listing_id,t_id
＜対策＞
ログ系テーブルをmyisam→innodbに変更
click_log_YYYYMM
action_log_YYYYMM
access_action_log_YYYYMM
stat_log_YYYYMM
※MyISAMからInnoDBへ乗り換えるときにハマりやすい挙動の違い
http://gihyo.jp/dev/clip/01/groonga/0006?page=3
◯以下に該当するクエリを利用している
INSERT IGNORE INTO ...
INSERT INTO ... ON DUPLICATE KEY UPDATE ...
LOAD DATA ... IGNORE INTO ...
◯行削除するケースがある