2010-04-26

超適当なページャを表示するTT のテンプレート

pager.tt

[%
    disp_pages = 10;
    disp_pages = disp_pages - 1;
%]
[%
    offset = c.stash.pager.offset;
    limit  = c.stash.pager.limit;
    rows   = c.stash.pager.rows;
%]
[%
    url    = c.stash.pager.url;
    query  = c.stash.pager.query;
%]
[%
    curr_page = ( offset div limit ) + 1;
    max_page  = ( rows + limit - 1 ) div limit;
%]
[%
    disp_min_page = curr_page - disp_pages < 1
                    ? 1
                    : curr_page - disp_pages;
    disp_max_page = disp_min_page + disp_pages > max_page
                    ? max_page
                    : disp_min_page + disp_pages;
%]

[% IF curr_page > 1 %]
    [% prev_offset = offset - limit %]
    <a href="[% url %]?offset=[% prev_offset %]&limit=[% limit %][% query %]">&#60;&#60;</a>
[% ELSE %]
    &nbsp;&nbsp;
[% END %]

[% list = [ disp_min_page .. disp_max_page ] %]
[% FOR i IN list %]
    [% IF i == curr_page %]
        [% i %]
    [% ELSE %]
    <a href="[% url %]?offset=[% ( i - 1 ) * limit %]&limit=[% limit %][% query %]">[% i %]</a>
    [% END %]
[% END %]

[% IF curr_page < max_page %]
    [% next_offset = offset + limit %]
    <a href="[% url %]?offset=[% next_offset %]&limit=[% limit %][% query %]">&#62;&#62;</a>
[% END %]

んで、

<< 1 2 3 4 5 6 7 8 9 10 >>

こんな感じの出る。

呼び出し側は、

なんらかの方法で

- disp_pages 表示するページの数
- offset 何件目から表示しているか
- limit １ページ内の表示件数
- url ページャアクションのURL
- qurey ページャアクションのクエリー

を渡しあげれば良い。

ex.) 
  [%
     INCLUDE pager.tt
       disp_pages = 10
　　　 offset = 50
       limit = 50
       url = ほげほげ
       query = 的な。
  ]%

2010-04-22

クエリープロファイリングコマンド

MySQL

クエリー実行前のプロファイリング

EXPLAIN 文で見てみる

クエリープロファイリング１

set profiling=1;
なにかクエリーを実行
show profile;

クエリープロファイリング２

FLUSH STATUS;
なにかクエリーを実行 (ただし、SQL _NO_CACHE を追加すること)
show session status で確認する
- 特に、
  - show session status like 'Select%';
  - show session status like 'Handler%';
  - show session status like 'Sort%';
  - show session status like 'Created%';
  - なんかは見といた方がいいかな。

2010-04-20

MySQL プロファイリングメモ

MySQL

使用している MySQL プロファイリングツール

* innotop

my.cnf にスローログを仕込む

# slow query log
log-slow-queries=/var/log/mysql-slow.log
long_query_time=1
log-queries-not-using-indexes
スローログを読むツールはなんかあるようだが、なくてもそれほど困らない
程度に読めるもの。
むしろ、Nagios の check_log と連携するとかいいんでないか？

2010-04-16

MYSQL + DBI

MySQL

とあるところから、理解不能の現象を聞いた。
DBIの接続アトリビュートに { AutoCommit => 1, ... }と指定し、
$dbh->commit とすると、rollback するような現象が起こる。
というもの。

あとで詳しく調査する。

2010-04-14

MySQL ファンクションインデックスがないので、日付の比較が気になった

MySQL

MySQL は(Oracleでいうところの)ファンクションインデックスがないので、

where 句における日付比較に関するより良い記述はどうなのかがきになった。

前提条件

設計の時点で datetime でなく、unixtime でやれば整数で比較ができて、一番パフォーマンスがよいのは既に知っている。

datetime 型と日付を表現している文字列との比較を行う。

文字列が表現している日付の精度は'年月日'までとする。

truncate( datetime, n ) など可読性が悪いものは無視。

日付は範囲指定とする。(2010/04/01 ０時〜 2010/04/14 いっぱい)

約２０万件あるテーブルから検索を行う。

比較したもの

――――――――――――――――――――――――――――――
１.
――――――――――――――――――――――――――――――
set profiling=1;
FLUSH STATUS;
select SQL _NO_CACHE * from USER_INFO
where
date_format( LAST_LOGIN, '%Y%m%d' ) >= '20100401'
and date_format( LAST_LOGIN, '%Y%m%d' ) <= '20100414'
;
⇒ 19323 rows in set (0.32 sec)

――――――――――――――――――――――――――――――
２.
――――――――――――――――――――――――――――――
set profiling=1;
FLUSH STATUS;
select SQL _NO_CACHE * from USER_INFO
where
LAST_LOGIN >= str_to_date( '20100401', '%Y%m%d' )
and LAST_LOGIN <= str_to_date( '20100415', '%Y%m%d' )
;

- - - - +

19323 rows in set (0.27 sec)

む、結果セットの構築までの時間に若干の差がある〜。

この時のSQLプロファイルを見てみると・・・

1.Status	1.Duration	2.Status	2.Duration
(initialization)	0.000026	(initialization)	0.000025
Opening tables	0.000006	Opening tables	0.000006
System lock	0.000003	System lock	0.000003
Table lock	0.000003	Table lock	0.000004
init	0.000014	init	0.000016
optimizing	0.000012	optimizing	0.000015
statistics	0.000005	statistics	0.000008
preparing	0.000005	preparing	0.000008
executing	0.000003	executing	0.000003
Sending data	0.328555	Sending data	0.311726
end	0.000017	end	0.00001
query end	0.000005	query end	0.000003
freeing items	0.000011	freeing items	0.000008
closing tables	0.000006	closing tables	0.000004
logging slow query	0.000047	logging slow query	0.000044

さらに、詳しく。
mysql> show session status like 'Select%';

1.Variable_name	1.Value	2.Variable_name	2.Value
Select_full_join	0	Select_full_join	0
Select_full_range_join	0	Select_full_range_join	0
Select_range	0	Select_range	0
Select_range_check	0	Select_range_check	0
Select_scan	2	Select_scan	2

mysql> show session status like 'Handler%';

1.Variable_name	1.Value	2.Variable_name	2.Value
Handler_commit	0	Handler_commit	0
Handler_delete	0	Handler_delete	0
Handler_discover	0	Handler_discover	0
Handler_prepare	0	Handler_prepare	0
Handler_read_first	1	Handler_read_first	1
Handler_read_key	2	Handler_read_key	2
Handler_read_next	0	Handler_read_next	0
Handler_read_prev	0	Handler_read_prev	0
Handler_read_rnd	0	Handler_read_rnd	0
Handler_read_rnd_next	193172	Handler_read_rnd_next	193171
Handler_rollback	0	Handler_rollback	0
Handler_savepoint	0	Handler_savepoint	0
Handler_savepoint_rollback	0	Handler_savepoint_rollback	0
Handler_update	0	Handler_update	0
Handler_write	19	Handler_write	19

mysql> show session status like 'Sort%';

1.Variable_name	1.Value	2.Variable_name	2.Value
Sort_merge_passes	0	Sort_merge_passes	0
Sort_range	0	Sort_range	0
Sort_rows	0	Sort_rows	0
Sort_scan	0	Sort_scan	0

mysql> show session status like 'Created%';

1.Variable_name	1.Value	2.Variable_name	2.Value
Created_tmp_disk_tables	0	Created_tmp_disk_tables	0
Created_tmp_files	0	Created_tmp_files	0
Created_tmp_tables	4	Created_tmp_tables	4

大した差はない。
もっと詳しく！
mysql> show session status where Value != 0;

1.Variable_name	1.Value	2.Variable_name	2.Value
Binlog_cache_use	541	Binlog_cache_use	236
Bytes_received	258	Bytes_received	191
Bytes_sent	1551103	Bytes_sent	1544534
Com_select	1	Com_select	1
Com_show_status	2	Com_show_status	1
Connections	187081429	Connections	187081788
Created_tmp_tables	2	Created_tmp_tables	1
Flush_commands	95	Flush_commands	95
Handler_read_first	1	Handler_read_first	1
Handler_read_key	2	Handler_read_key	2
Handler_read_rnd_next	193416	Handler_read_rnd_next	193167
Handler_write	381	Handler_write	132
Innodb_buffer_pool_pages_data	511	Innodb_buffer_pool_pages_data	509
Innodb_buffer_pool_pages_dirty	4	Innodb_buffer_pool_pages_dirty	44
Innodb_buffer_pool_pages_flushed	1179501423	Innodb_buffer_pool_pages_flushed	1179501833
Innodb_buffer_pool_pages_misc	1	Innodb_buffer_pool_pages_misc	3
Innodb_buffer_pool_pages_total	512	Innodb_buffer_pool_pages_total	512
Innodb_buffer_pool_read_ahead_rnd	20037665	Innodb_buffer_pool_read_ahead_rnd	20037723
Innodb_buffer_pool_read_ahead_seq	96782539	Innodb_buffer_pool_read_ahead_seq	96782539
Innodb_buffer_pool_read_requests	840283026430	Innodb_buffer_pool_read_requests	840283083107
Innodb_buffer_pool_reads	7019723112	Innodb_buffer_pool_reads	7019726231
Innodb_buffer_pool_wait_free	372	Innodb_buffer_pool_wait_free	372
Innodb_buffer_pool_write_requests	15647557929	Innodb_buffer_pool_write_requests	15647563420
Innodb_data_fsyncs	705740458	Innodb_data_fsyncs	705741533
Innodb_data_read	139767250128896	Innodb_data_read	139767310028800
Innodb_data_reads	7273354637	Innodb_data_reads	7273357854
Innodb_data_writes	1721797980	Innodb_data_writes	1721799420
Innodb_data_written	39632893417472	Innodb_data_written	39632907764736
Innodb_dblwr_pages_written	1179501423	Innodb_dblwr_pages_written	1179501833
Innodb_dblwr_writes	13165555	Innodb_dblwr_writes	13165563
Innodb_log_waits	58	Innodb_log_waits	58
Innodb_log_write_requests	1461513220	Innodb_log_write_requests	1461514072
Innodb_log_writes	678742466	Innodb_log_writes	678743521
Innodb_os_log_fsyncs	679450477	Innodb_os_log_fsyncs	679451536
Innodb_os_log_written	982620464640	Innodb_os_log_written	982621374976
Innodb_page_size	16384	Innodb_page_size	16384
Innodb_pages_created	16272816	Innodb_pages_created	16272824
Innodb_pages_read	8530769493	Innodb_pages_read	8530773149
Innodb_pages_written	1179501423	Innodb_pages_written	1179501833
Innodb_row_lock_time	1511801402	Innodb_row_lock_time	1511801402
Innodb_row_lock_time_avg	25599	Innodb_row_lock_time_avg	25599
Innodb_row_lock_time_max	51986	Innodb_row_lock_time_max	51986
Innodb_row_lock_waits	59057	Innodb_row_lock_waits	59057
Innodb_rows_deleted	18676221	Innodb_rows_deleted	18676221
Innodb_rows_inserted	95916416	Innodb_rows_inserted	95916472
Innodb_rows_read	790539293487	Innodb_rows_read	790539491909
Innodb_rows_updated	1308152335	Innodb_rows_updated	1308152984
Key_blocks_unused	6698	Key_blocks_unused	6698
Key_blocks_used	53	Key_blocks_used	53
Last_query_cost	40419.199000	Last_query_cost	44697.199000
Max_used_connections	8	Max_used_connections	7
Open_files	39	Open_files	39
Open_tables	64	Open_tables	64
Questions	3126502867	Questions	3126507217
Select_scan	3	Select_scan	2
Slow_queries	1	Slow_queries	1
Table_locks_immediate	2721	Table_locks_immediate	984
Threads_connected	3	Threads_connected	3
Threads_created	187081428	Threads_created	187081787
Threads_running	3	Threads_running	3
Uptime	7742828	Uptime	7742860
Uptime_since_flush_status	34	Uptime_since_flush_status	11

ああ、めんどくせ。

str_to_date を使うように心がけよう。
ただし、、
日付範囲指定の上限は、20100415 となっているように、
バックオフィスで入力された値は 2010100414 だが、
１日足す処理がいる。

2010-04-14

MYSQL last_insert_id() によるシーケンスのシミュレーションの理解

MySQL

うーん、私には
update sequence set id = last_insert_id(id+1); -> (A)
select last_inset_id() -> (B)
でマルチユーザ対応のシーケンスの代わりになる理由が理解できない。

前提条件

innoDB エンジンを使用する。

last_inset_id() はセッションごとに保持される値である。

update は排他ロックなので、更新中でも他のセッションからの読み取りはできる。(C)
なので、id + 1 の計算が同時刻に複数のセッションで行われた場合は、
id + 1 の計算は各セッションで同じになる。
以下、〜略〜
すなわち、ID のかぶりが起こるような気がするのだが。。。きのせいか。
(C) の解釈が間違っているのか、もっとしらべてみる。
――――――――――――――――――――――――――――――
実践ハイパフォーマンス MySQL(1.2.1 読み取りロック/書き込みロック) より、
「書き込みロックは排他的であり、読み取りロックと他の書き込みロックを両方
ブロックする。」さらに、(1.3.1 分離レベルより、)InnoDB の
トランザクション分離レベルが(デフォルトで)REPEATABLE READ なので、
ダーティーリードがされないことが保証されるということですね〜
――――――――――――――――――――――――――――――
「読み取りロックは共有される、または相互にノンブロッキングである。」

pager.tt

呼び出し側は、

クエリー実行前のプロファイリング

クエリープロファイリング１

クエリープロファイリング２

使用している MySQL プロファイリングツール

my.cnf にスローログを仕込む

MySQL は(Oracleでいうところの)ファンクションインデックスがないので、

前提条件

設計の時点で datetime でなく、unixtime でやれば整数で比較ができて、一番パフォーマンスがよいのは既に知っている。

datetime 型と日付を表現している文字列との比較を行う。

文字列が表現している日付の精度は'年月日'までとする。

truncate( datetime, n ) など可読性が悪いものは無視。

日付は範囲指定とする。(2010/04/01 ０時 〜 2010/04/14 いっぱい)

約２０万件あるテーブルから検索を行う。

比較したもの

この時のSQLプロファイルを見てみると・・・

前提条件

innoDB エンジンを使用する。

last_inset_id() はセッションごとに保持される値である。

日付は範囲指定とする。(2010/04/01 ０時〜 2010/04/14 いっぱい)