ボトルネック見つけ用メモ

なんか色々と資料散らかってるわ忘れてるわで、散々と面倒だったので。
後で付け足す前提で、手元にある「散らかった資料」を整理してまとめておきます的なmemo。

…あぁ先に。set globalとかは「すげぇ激しいパーミッション」が要求されるのでご注意のほどを。
具体的には「SUPER」っちゅぅのが必要です。GRANTで適当に設定しといてちょ。

まず、ログ系の状況の確認。

show variables like 'log%';

特に「重いクエリ」周りの確認。

show global variables like 'slow_query_log'; -- slow_query_log出す？ 出さない？
show global variables like 'long_query_time'; -- 何秒以上を「遅い」とする？
show global variables like 'min_examined_row_limit'; -- 「すんげい大量のレコードを求めるクエリ」の大量っておいくつ？

で、コンソールから動的に設定。

set global slow_query_log_file = 'slow_query_logの吐き出し先を指定';
set global long_query_time = 2; -- とりあえず２秒
set global slow_query_log = 1; -- slow log開始！

ちなみに秒数は、小数点とか使えるようになったぽ by 5.1以降。
とりあえずこれ仕込んで「重いクエリ」を探し出しませう。

ただ、もう一つ「なんか細かいけど鬼のようにクエリ投げてる的な？」っていうボトルネックがあるので。
こんな手順で発見。

まずは「全クエリ」を引っ掛けるために、こんな設定。

set global long_query_time = 0;

次に「重いと思われるPage」にアクセス、クエリログを溜め込む。
てけとうに溜め込んだら

set global long_query_time = 2;

とかで一端元に戻す。

そのあと「mysqldumpslow」を使う。これで「似たようなSQL」をカウントしてくれる！！
実際にはこんな感じ。path切れてなかったらてけとうに探すこと。

mysqldumpslow -s t slowログファイル名
mysqldumpslow -s at slowログファイル名

optionがtなのかatなのかは、なんかよくわからなかった。ドッチでも、とりあえず目的は達成できたので、あんまり気にしてないｗ
これで「ほんのわずかなPageアクセス」で「驚くほど大量のクエリ」があったら、色々と絞殺ぢゃなくて考察をしてみませう。
memcachedとか、こゆときいいよねぇ。MagicWeaponはmemcachedとの相性が「ある程度」いいと思ってるんだけど、他のFrameWorkってどうなんだべさ？

あとは「index使ってない悪い子」を探すあたり。

show global variables like '%indexes';
set global log_queries_not_using_indexes = on;

ついでに、クエリの「受け入れ長さ」の確認と設定変更。

set max_allowed_packet = 1024*1024*32(計算して変換してね);
show variables like 'max_allowed_packet';

思い出したり掘り出したり発掘したりしたら、追記予定。

追記 2012-02-14

set global long_query_time = 2;

やると、いったん「show global variables like 'slow_query_log';」が、OFFになるらしい。注意。
追記の追記。これ、誤認かもしれない。確認して、確認取れた人おしえてたも〜。

あと「現在滞留中」のクエリ状況とかを見るなら

SHOW PROCESSLIST; -- 長いクエリは省略表示
SHOW FULL PROCESSLIST; -- 長いクエリも完全に表示

とか。
ちょっとひねると

SELECT * FROM information_schema.PROCESSLIST WHERE time > 10 AND info LIKE 'SELECT%';

とか(10秒以上のんびりこいてるSELECTを探せ)。
これは、元々

DESC information_schema.PROCESSLIST;

で見て取れるテーブルがあるから。

追記 2012-02-15
しゃれにならないプロセスがあったら、まずはshow processlistでIDを補足。多分一番左にあると思われる、数字。
で、その数字に対して

mysqladmin kill {ID}

で、その対象プロセスをkill。
ド最悪、これで「厄介なSQL プロセス」を殺せます。

MySQLのパーティションでセッションテーブル管理：失敗編

とりあえずアバウトにmemo。

やりたいこと。

drop table session_test;
create table session_test (
  session_id varbinary(256) not null,
  use_id     varbinary(64) not null,
  data       blob,
  insert_date datetime,
  update_date datetime,
  PRIMARY KEY (session_id)
) ENGINE=InnoDB
  PARTITION BY RANGE ( hour(update_date) ) (
    PARTITION p00 VALUES less than(0),
    PARTITION p01 VALUES less than(1),
    PARTITION p02 VALUES less than(2),
    PARTITION p03 VALUES less than(3),
    .
    .
    .
    PARTITION p23 VALUES less than(23),
    PARTITION pmax VALUES LESS THAN MAXVALUE  
  );

んで、バッチで

DROP PARTITION p(二時間前);

ってやりたいげ。

で…現実。
まずそもそもとして
「A PRIMARY KEY must include all columns in the table's partitioning function」
って怒られる orz
だって「レンジの値はど〜したってPKぢゃねぇもん；；」
＆
update_dateが15:59:00から16:00:00にupdateされた時って、ちゃんと動くのかしらん？
いやまぁ「PKだからうごかねぇよ！」って前提条件は置いておくとして。
# 一説によると*1これはちゃんと対応してるぽ…それだけになおのこと「PK縛り」が残念にすぎる orz

ちなみに、本気でやると、パーティションの設定部分が多分

    PARTITION p00 VALUES IN (0,3,6, 9,12,15,18,21),
    PARTITION p01 VALUES IN (1,4,7,10,13,16,19,22),
    PARTITION p02 VALUES IN (2,5,8,11,14,17,20,23),
    PARTITION pmax VALUES LESS THAN MAXVALUE  

ってなる。「今必要なパーティション」「念のために寝かせておくパーティション」「dropするパーティション」があれば十分なはずだしね。

さて…こんな風に使える日はくるのか！？

*1: http://www.s-quad.com/wordpress/?p=742

でっかいテーブルをまりっと更新する方法のひとつ の応用編と困ったこと

んで。
これの亜種として「部分的に入れ替える」事を、やることがあります。
んと…住所だと「東京都だけ入れ替える」とか。

trancate 郵便番号テーブル_tmp;
loop insert into 郵便番号テーブル_tmp(...) values(...);
begin;
delete from 郵便番号テーブル where 都道府県='東京都';
insert into 郵便番号テーブル(...) select ... from 郵便番号テーブル_tmp;
commit;

で…まぁ「結構でかい」のをやったら、とっても嫌がられました orz

The total number of locks exceeds the lock table size

えと…「でけぇよ！！」って感じ？
基本的には
my.cnfの中にある「innodb_buffer_pool_size」をでっかくしてあげると良いみたい。

…さて。いくつにしたらよかんべ orz

でっかいテーブルをまりっと更新する方法のひとつ

んと…例えば「郵便番号をkeyにした住所テーブル」なんてのが、割とわかりやすいところであるのですが。
この子を更新する場合、普通に考えると

begin;
trancate 郵便番号テーブル;
loop insert into 郵便番号テーブル(...) values(...);
commit;

ってな処理かと思うのですが(エラー時の処理は省略してるので気をつけてね)。
この場合、トラン中が結構長いのと、そのために、結構なDB負荷がかかります(いやまぁ真面目に計測はしてないんですが)。
そこで。若干荒っぽく、こんな処理の仕方があります。

trancate 郵便番号テーブル_tmp;
loop insert into 郵便番号テーブル_tmp(...) values(...);
begin;
trancate 郵便番号テーブル;
rename table 郵便番号テーブル_tmp to 郵便番号テーブル;
commit;

いやまぁたいした手段でもないのですが「こーゆーやりかたもあるでよ」ってことで。
これだと、極論「insert１文ごとにマイクロスリープ」とかぶち込んだり「DBハンドルの接続数を確認して、一定数以上ならしばらくwait」とか、まぁ細かい小手先技が、色々と盛り込めるもんでｗ

INSERT ... ON DUPLICATE KEY UPDATE 構文 と REPLACE 構文

REPLACEは「なきゃinsert、あったらdelete＆insert」なので、平たく「上書き」。SNSの足跡なんかにめっさ便利。
ON DUPLICATE KEYは「なきゃinsert、あったらupdate」なので、カウンタ系とかの実装にめっさ便利。

…恐ろしいくらいに綺麗に記憶から抜けていたので(苦笑
これ以上忘れ倒す前に、メモ。

あ、一応念のため。MySQL以外で使えるかはわからんです。なので、本来的には、おいちゃん好みから外れる。
…他のDBMSで、同じようなのあるですかねぇ？ あるんなら、最悪「一枚ラッピング」して、大手を振って使うのですがｗ

文字コード関連

とりあえずアバウトにmemo。
あとで整理…予定(苦笑

確認関連
show variables like 'character_set%';
show variables like "char%";
status

show create database データベース名 \G
show create table テーブル名 \G

設定関連
[client]
default-character-set=utf8
[mysqld]
default-character-set = utf8
skip-character-set-client-handshake
character-set-server = utf8
collation-server = utf8_general_ci
init-connect = SET NAMES utf8

or

set names utf8;

あんど
create database wordpress default character set utf8;

以下余談
…おいちゃん的にはlatin1でいいというかむしろ「DBには文字コードなんぞ意識して欲しくない」と思うんですよねぇ正直。
sjisはまぁ論外として(5c問題とかね)、EUCには、１文字検索とかで「なんでこれひっかかるの？」があったのですが。原理わかってればある程度わかるし。で…utf-8にはンな問題ないし。
だとしたら。素直に全部「バイナリデータとして無処理で in/out 」したいんですけどねぇ。
…こういう感覚って稀なのかしらん？

新しいサービス構築用memo

create database でぇたべぇす名;
grant ALTER, CREATE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, UPDATE on でぇたべぇす名.* to ユーザ名@ホスト名 identified by 'ぱすわぁど';
flush privileges ;

ちなみに「ぱすわぁど作成」にはこちらが便利…とかって宣伝ｗ
http://www.gjmj.net/makepass.php