サーバーを含むシステムの高速化やビックデータ時代の到来に伴い、
分散処理に注目が集まっている様子。

10年前にHPCCが盛り上がった時にはあまり身近に感じなかったが、
HadoopやMongodbのようにオープンサースで気軽に分散処理出来る
システムが導入出来るようになり、ここ2~3年で再び注目を集めている。
忘れがちだったのだが、ネットワークがボトルネックになる可能性も高いので
システム導入の時点できちんとスケールアウトも含めて設計しておく必要がある。

HPCユーザーが知っておきたいTCP/IPの話
ESnet: http://fasterdata.es.net/
———————————————————-
To make better use of its accumulated knowledge, ESnet has developed this Fasterdata Knowledge Base.
The knowledge base provides provides proven, operationally-sound methods for troubleshooting and
solving performance issues. Our solutions fall into five categories:

Network Architecture, including the Science DMZ model
Host Tuning
Network Tuning
Data Transfer Tools
Network Performance Testing
———————————————————-
上記HPCの資料によるとここら辺もきちんとカスタマイズしておいた方が良さそう。
色々なツールもあるので調査したい場合にインストールして現状把握してみても良いかと思います。
nuttcpなどは再送処理なども見つける事が出来るようです。

■Data Transfer Tools
http://fasterdata.es.net/data-transfer-tools/

■Network Troubleshooting Tools
http://fasterdata.es.net/performance-testing/network-troubleshooting-tools/

■Phil Dykstra’s nuttcp quick start guide
http://wcisd.hpc.mil/nuttcp/Nuttcp-HOWTO.html

例)scamperでMTU含めてネットワークパス確認。
———————————————————————-
http://fasterdata.es.net/performance-testing/network-troubleshooting-tools/scamper/

To install scamper:
wget http://www.wand.net.nz/scamper/scamper-cvs-20110421.tar.gz
tar xvzf scamper-cvs-20110421.tar.gz
./configure; make; make install

[root@ip-xxx-xxx-xxx-xxx1 scamper-cvs-20110421]# ./configure; make; make install
checking for a BSD-compatible install… /usr/bin/install -c
checking whether build environment is sane… yes
checking for a thread-safe mkdir -p… /bin/mkdir -p
checking for gawk… gawk
checking whether make sets $(MAKE)… yes
checking build system type… x86_64-unknown-linux-gnu
checking host system type… x86_64-unknown-linux-gnu
checking how to print strings… printf
checking for style of include used by make… GNU
checking for gcc… gcc
checking whether the C compiler works… yes

[root@ip-xxx-xxx-xxx-xxx1 scamper-cvs-20110421]# dig yahoo.co.jp

; <<>> DiG 9.7.3-P3-RedHat-9.7.3-8.P3.15.amzn1 <<>> yahoo.co.jp
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 24120 ;; flags: qr rd ra; QUERY: 1, ANSWER: 2, AUTHORITY: 0, ADDITIONAL: 0 ;; QUESTION SECTION: ;yahoo.co.jp. IN A ;; ANSWER SECTION: yahoo.co.jp. 287 IN A 203.216.243.240 yahoo.co.jp. 287 IN A 124.83.187.140 ;; Query time: 1 msec ;; SERVER: 172.16.0.23#53(172.16.0.23) ;; WHEN: Sun May 27 08:24:32 2012 ;; MSG SIZE rcvd: 61 [root@ip-xxx-xxx-xxx-xxx1 scamper-cvs-20110421]# [root@ip-xxx-xxx-xxx-xxx1 scamper-cvs-20110421]# /usr/local/bin/scamper -c "trace -M" -i 124.83.187.140 traceroute from 10.157.37.241 to 124.83.187.140 1 10.157.36.2 4.163 ms [mtu: 1500] 2 10.1.22.9 0.378 ms [mtu: 1500] 3 175.41.192.21 0.397 ms [mtu: 1500] 4 27.0.0.165 0.321 ms [mtu: 1500] 5 27.0.0.205 7.595 ms [mtu: 1500] 6 27.0.0.188 10.107 ms [mtu: 1500] 7 61.200.80.201 7.698 ms [mtu: 1500] 8 61.200.80.134 7.857 ms [mtu: 1500] 9 61.200.82.138 7.942 ms [mtu: 1500] 10 124.83.128.26 12.923 ms [mtu: 1500] 11 124.83.128.146 9.725 ms [mtu: 1500] 12 124.83.128.146 9.852 ms !X [mtu: 1500] [root@ip-xxx-xxx-xxx-xxx1 scamper-cvs-20110421]#

その他、サーバー側のNICメモリー設定も環境毎に最適化出来る様子。

[root@colinux ~]# /sbin/sysctl -a | grep mem
net.ipv4.udp_wmem_min = 4096
net.ipv4.udp_rmem_min = 4096
net.ipv4.udp_mem = 2324160 3098880 4648320
net.ipv4.tcp_rmem = 4096 87380 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304
net.ipv4.tcp_mem = 196608 262144 393216
net.ipv4.igmp_max_memberships = 20
net.core.optmem_max = 20480
net.core.rmem_default = 129024
net.core.wmem_default = 129024
net.core.rmem_max = 131071
net.core.wmem_max = 131071
vm.lowmem_reserve_ratio = 256 256 32
vm.overcommit_memory = 0
[root@colinux ~]#

[root@ip-xxx-xxx-xxx-xxx ec2-user]# /sbin/sysctl -a | grep mem
vm.overcommit_memory = 0
vm.lowmem_reserve_ratio = 256 256 32
net.core.wmem_max = 131071
net.core.rmem_max = 131071
net.core.wmem_default = 229376
net.core.rmem_default = 229376
net.core.optmem_max = 20480
net.ipv4.igmp_max_memberships = 20
net.ipv4.tcp_mem = 14679 19574 29358
net.ipv4.tcp_wmem = 4096 16384 626368
net.ipv4.tcp_rmem = 4096 87380 626368
net.ipv4.udp_mem = 14679 19574 29358
net.ipv4.udp_rmem_min = 4096
net.ipv4.udp_wmem_min = 4096
[root@ip-xxx-xxx-xxx-xxx ec2-user]#

補足:
Windowsに関しては、Windows2008からいくつか注意しておくべき事がありそうです。

TCP 受信ウィンドウの自動調整機能が機能しない正しくで Windows Server 2008 R2

All the TCP/IP ports that are in a TIME_WAIT status are not closed after 497 days

Scalable Networking Pack をご存知ですか?


Google Maps API Version 3についての確認

1)API Key不要(V2までは取得が必要だった)
2)iPhone/Android対応
3)JavaScriptの書き方変更

    ヘッダーに以下のGoogle Map API V3の指定箇所を追記。

それぞれ、”script”タグで閉じる。

個人の場合
src=”http://maps.google.com/maps/api/js?v=3&sensor=false type=”text/javascript”

Googleと契約している場合
src=”http://maps.google.com/maps/api/js?v=3&sensor=false&client=gme-指定されたID&channel=チャネル指定用” type=”text/javascript”

function initialize() {
var latlng = new google.maps.LatLng(35.710269,139.811146);
var opts = {
zoom: 15,
center: latlng,
mapTypeId: google.maps.MapTypeId.ROADMAP
};
var map = new google.maps.Map(document.getElementById(“map_v3”), opts);
}

sensor=
位置検知センサー(GPS)があるスマートフォンなどの機器ではsensor=true
一般的なパソコン用の地図なら、sensor=false とするそうです。

    BODYに以下のGoogle Map APIを呼び出す追記

BODY BGCOLOR=”#ffffee” onload=”initialize()”
………………………….
div id=”map_v3″ style=”width: 500px; height: 500px”
………………………….

Google Map Ver3

Google Map Ver3

注意:Google Map API for BusinessでクライアントIDやChannelを指定する場合は、Defaultだと地図上に詳細(ガスステーション、ホテル)
などが表示されない場合があるので以下の設定を必要に応じて調整する必要があります。

Google Maps JavaScript API V3 のマップ タイプ

抜粋
————————————————————————-
visibility(on、off、または simplified)は、地図に要素を表示するかどうかとその表示方法を指定します。
simplified 表示状態は、地図でこれらの要素の表現方法を見やすいように簡略化することを指定します
(たとえば、道路の構造を簡略化すると表示される道路の数が少なくなる可能性があります)。

参考:緯度経度
http://www.geocoding.jp/
http://gmaps-samples-v3.googlecode.com/svn/trunk/styledmaps/wizard/index.html


HDDの速度を測りたい場合は、hdparm コマンドを使用して調査する事が出来ます。

[root@colinux data]# /bin/df -h
Filesystem サイズ 使用 残り 使用% マウント位置
/dev/cobd0 3.8G 2.6G 1009M 73% /
tmpfs 126M 0 126M 0% /dev/shm
/dev/shm 32M 0 32M 0% /usr/local/tmp
[root@colinux data]# /sbin/hdparm -v /dev/cobd0

/dev/cobd0:
readonly = 0 (off)
readahead = 256 (on)
HDIO_GETGEO failed: Inappropriate ioctl for device
[root@colinux data]# /sbin/hdparm -t /dev/cobd0

/dev/cobd0:
Timing buffered disk reads: 74 MB in 3.07 seconds = 24.10 MB/sec

hdparm

-T
ベンチマーク及び比較目的で、キャッシュ読み込みを測定する。
有意な結果を得るためには、少なくとも数メガバイトの空きメモリがあり、
他にアクティブなプロセスがない状態で、この操作を 2, 3 回繰り返すべきである。
これは、ディスクアクセスなしに、Linux のバッファキャッシュから直接読み出す速度を表示する。
これは、テスト環境下でのプロセッサ・キャッシュ・メモリの基本的な処理能力を測定するものである。
-t フラグが同時に指定された場合には、 -T の出力を元にした補正係数が -t 操作の結果に加味される。

-t
ベンチマーク及び比較目的で、デバイス読み込みを測定する。
有意な結果を得るためには、少なくとも数メガバイトの空きメモリがあり、
他にアクティブなプロセスがない状態で、この操作を 2, 3 回繰り返すべきである。
これはデータのキャッシュがない状態から、バッファキャッシュを通してディスクを読み出す
速度を表示する。これは、ファイルシステムのオーバーヘッドなしに、そのドライブが
Linux でどれだけ連続データ読み込み速度を維持できるかを測定するものである。
測定の正確さを上げたいのであれば、 -t の実行の間に BLKFLSBUF ioctl を使って
バッファキャッシュをクリアする。 -T フラグが同時に指定された場合には、 -T の出力
を元にした補正係数が -t 操作の結果に加味される。

hdparm_t

ハードディスクの設定の表示や変更は、hdparmコマンドを使って行うことができる。
ただし変更すれば必ず良い結果がもたらされるというわけではなく、man hdparm
を実行すればhdparmコマンドの全オプションを見ることができ、危険なオプションについては
「DANGEROUS」と表記されている。

※最初に試す最適化は、転送の高速化のためにドライブが直接メモリ
にデータを保存できるようにするDMA(Direct Memory Access)を使用する。
/sbin/hdparm -d1 /dev/hda
※multcountは、一度の操作で読み取ることのできるセクタ数を示すパラメータ。

hdparm_man

最小値と最大値を捨て、残りの値の平均を取り適切な測定結果を得る。
for ((i=0;i<6;i++)) do /sbin/hdparm -t /dev/cobd0; done

hdparm_loop

    参考サイト


Linuxのパフォーマンスを改善する3つのTips


ファイルシステムのアクセスを最適化する


hdparm でハードディスクを高速化する


HDDのアクセスが妙に遅く感じるときは


MYSQL5.1.41がリリースされていたので、アップグレード
C.1.2. Changes in MySQL 5.1.41 (05 November 2009)

■InnoDB Plugin has been upgraded to version 1.0.5. This version is considered of
 Release Candidate (RC) quality.
■The InnoDB buffer pool is divided into two sublists: A new sublist containing blocks
 that are heavily used by queries, and an old sublist containing less-used blocks and from
 which candidates for eviction are taken.
 innodb_old_blocks_pct / innodb_old_blocks_time
■Important Change: Security Fix
Bug#32167, CVE-2008-2079 / Bug#47320, CVE-2009-4028 / Bug#46922
Partitioning: Bug#44059
Replication: Bug#48297 / Bug#48216 / Bug#47678 / Bug#47323 / Bug#47287/ Bug#46640
Bug#42829 / Bug#34582 / Bug#48370 / Bug#48295 / Bug#48293
その他多数……………………….

1) 現在のバージョン確認
original_20091221

http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.41-linux-i686-icc-glibc23.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/

2) 最新版のダウンロード
[root@colinux src]# wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.41-linux-i686-icc-glibc23.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/
–00:06:43– http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.41-linux-i686-icc-glibc23.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/

wget_5141

3) ダウンロードしたtarを解凍
[root@colinux src]# tar zxvf mysql-5.1.41-linux-i686-icc-glibc23.tar.gz
tar_zxvf

4) 展開したtarを自分が希望するパスへ移動 (これはオプション)
mv_mysql5141

5) 新しく展開したフォルダーのパーミッションの変更
[root@colinux local]# chown -R mysql:mysql mysql-5.1.41-linux-i686-icc-glibc23/
[root@colinux local]# chmod -R 755 mysql-5.1.41-linux-i686-icc-glibc23/

6) 現在稼動している、MYSQLサービスを停止します。
mysql_5141_migration

7) シンボリックリンクの削除
[root@colinux local]# pwd
[root@colinux local]# rm mysql

8) アップグレード前のまで利用していた、MYSQLのdataフォルダーを新しいバージョンのMYSQLにコピー

[root@colinux local]# cp -rp mysql-5.1.40-linux-i686-icc-glibc23/data mysql-5.1.41-linux-i686-icc-glibc23/

9) シンボリックリンクの作成とサービスの再開
[root@colinux local]# ln -s mysql-5.1.41-linux-i686-icc-glibc23/ mysql

mysql5141_symbolic

[root@colinux local]# /etc/init.d/mysql.server start
Starting MySQL…. SUCCESS!
[root@colinux local]#

10) システムデータベースのアップグレード(information_schema / mysql )

[root@colinux local]# /usr/local/mysql/bin/mysql_upgrade -u root -p

mysql_5141_sqlupgrade

11) バージョンアップの確認

※ mysql_upgrade_infoファイルの確認
[root@colinux data]# cat /usr/local/mysql/data/mysql_upgrade_info
upgrade_info

※ エラーログの確認
mysql_5141_log_confirmation

※ information_schemaとmysqlスキーマの確認
select TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_TIME,UPDATE_TIME
from information_schema.tables where
TABLE_SCHEMA = 'information_schema' or TABLE_SCHEMA = 'mysql'
and CREATE_TIME is not NULL and UPDATE_TIME is not null
order by CREATE_TIME desc;

system_tables

※ バージョン確認 (mysql> select @@version;)
version_5141


Prepared statementはステートメントを一度定義して、それを何回も違う引数で実行するものです。
これはセキュリティを増しなおかつ効率のよい方法で、アドホックなクエリのストリングに置き換わるものです。

SELECT * FROM Country WHERE code = ?

「?」はいわゆる場所とりです。上のクエリを実行するときはこの場所に値がいります。
「?」は必須ではありません。

Prepared statementはSQL のロジックとデータを分離することでセキュリティを増加します。
ロジックとデータを分離することで、SQLインジェクション攻撃を回避することができます。
通常のクエリを扱っている場合、ユーザから受け取ったデータを処理するには注意が必要です。
これはシングル・クオート、ダブル・クオート、バックスラッシュなどの文字をエスケープする関数を
使用することに関係します。こういったことはpreparedstatementを使用する際には不必要です。
データを分離することでMySQLは自動的にこういった文字を考慮しており特別な関数を使用して
こういう文字をエスケープする必要がありません。

prepared statementでの性能向上はいくつかの異なった機能によります。
まず最初に、クエリを一度しかパースしなくてよいことです。
最初にステートメントの用意をした際、MySQLはステートメントをパースしてシンタクスをチックして、
クエリの実行の用意をします。同じクエリを何回も実行するのであれば、そのオーバヘッドは2度目からありません。
あらかじめ、パースしてあることで例えばなんどもINSERTステートメントを使うような場合、スピードが増加します。

2つ目の性能向上は新しいバイナリーのプロトコルによります。
今までのプロトコルはネットワークを介して転送する前に、全てをストリングに変換していました。
クライアントはデータをストリングに変換し(大抵の場合もとのデータより大きい)、ネットワーク
(か他の方法で)サーバに転送します。サーバはストリングをもとの正しいデータタイプに変換します。
バイナリー・プロトコルであればこのオーバーヘッドがありません。全てのタイプはそのままの形で(バイナリー形式)
で転送されます。そのためCPUの使用も削減され、ネットワークの使用も押さえることができます。

Prepared statementは上の全ての場合に有益です。しかし、アプリケーションの全てに適用すべきでもないし、
できません。最初にこれは DML (INSERT, REPLACE, UPDATE, とDELETE), CREATE TABLE, とSELECT
クエリに限られています。将来の版では他のクエリもサポートされ、prepared statementのクエリのAPIは
もっと一般的なものになります。

時として、prepared statementは普通のクエリよりも遅いときがあります。
理由はサーバーまで2往復するため、一度だけ実行されるだけのクエリであれば実行の速度が落ちます。
そのような場合はprepared statementで得られるセキュリティが性能を落としてまでも必要か考慮する
必要があります。

以上抜粋

mysql> PREPARE s1 FROM 'SELECT 1';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> PREPARE s2 FROM 'SELECT 2';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute s1;
+—+
| 1 |
+—+
| 1 |
+—+
1 row in set (0.00 sec)

mysql> execute s2;
+—+
| 2 |
+—+
| 2 |
+—+
1 row in set (0.00 sec)

※以下のキャプチャーでは、最後のPREPAREステートメントにsyntaxエラーが含まれており
s1として設定したPREPAREステートメントが無効になってしまっている状態。
prepared_statement

mysql> PREPARE s1 FROM 'SELECT 1';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> PREPARE s2 FROM 'SELECT 2';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> PREPARE s1 FROM 'SELECT (1+2)';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute s1;
+——-+
| (1+2) |
+——-+
| 3 |
+——-+
1 row in set (0.00 sec)

mysql> execute s2;
+—+
| 2 |
+—+
| 2 |
+—+
1 row in set (0.00 sec)

mysql>

※以下のキャプチャーでは、最初のPREPAREステートメントが同じ名前で定義されているので
上書きされてs1というステートメントが最後に実行されたステートメントになっている。

prepared_statements

以下のように別名で定義すれば上書きされないので問題無い。
mysql> PREPARE P10 FROM 'SELECT (10+10)';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute P10;
+———+
| (10+10) |
+———+
| 20 |
+———+
1 row in set (0.00 sec)

mysql>

PREPAREDステートメントを利用すれば、ネットワークトラフィックを減らしたり、
同じクエリーを何回も実行するなどの時間を削減する事ができます。

PREPAREDステートメントは、セッション固有のものになります。
他のユーザーのセッションや自分の他のセッションで同じ名前のステートメントを作成しても影響を受けません。
prepared_statement_session

———————————————————————
PREPARED ステートメントを利用した運用 (例:その1)
———————————————————————
ここでは、大陸と人口に変数を入れられるようにしてます。

① PREPARE STATEMENTの作成

PREPARE PS_COUNTRY_POP FROM 'SELECT Name, Population
FROM Country
WHERE Continent = ? AND Population > ?';

② 変数の値をSETしてます。 /* SELECT @C :=’Asia’ ,@P := 1000000000; でもOK */
SET @c = 'Asia', @p = 1000000000;

※SELECTで変数を設定した場合(例)
alternative

③ PREPAREステートメントの実行
EXECUTE PS_COUNTRY_POP USING @c, @p;

prepared_statement_exec

———————————————————————
PREPARED ステートメントを利用した運用 (例:その2)
———————————————————————
ここでは、大陸を変数にして国と人口を表示するためのPREPAREステートメントを利用。

① PREPARE STATEMENTの作成

PREPARE PS_Population FROM 'SELECT Name, Population
FROM Country WHERE Continent = ?';

② 変数の値をSETしてます。 /* SELECT @C :=’South America’ ; でもOK */
SET @c = 'South America';

③ PREPAREステートメントの実行 
EXECUTE PS_Population USING @c;

prepare_statement

※ 追加メモ:セッションを切って再接続してもPREPAREステートメントは利用出来ません。
execute_prepare_statement_session

※明示的にPREPAREステートメントをDEALLOCATEする方法。(セッションを切ればなくなりますが….. リソース開放の為)
① deallocate prepare PS_Population;
② drop prepare PS_Population;
drop_prepare_statement

「おまけ」
SELECT,INSERT,UPDATE,REPLACE,DELETE,SET,DO,
Many SHOW statements,CREATE TABLE statements
など殆どのDDL、DMLが設定可能

mysql> PREPARE s1 FROM 'SHOW FULL PROCESSLIST';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute s1;

show_full_process

—————————————
MYSQL プリペアド・ステートメント
6.9. MySQL クエリキャッシュ

MySQL サーバ には Query Cache 機能があります。 クエリキャッシュの使用時、このキャッシュには、
SELECT クエリのテキストと、クライアントに送られたその結果が格納されます。
後でまったく同じクエリを受け取ると、サーバはそのクエリの解析と実行をもう一度繰り返す代わりに、
クエリキャッシュから結果を取り出します。
注意:クエリキャッシュから古いデータが返されることはありません。データが変更されると、
クエリキャッシュの関連するエントリがすべてフラッシュされます。

    その他利用にあたり参考になりそうなサイト

mysql の server side prepared statement って速いの?
MySQL で prepared statement を使うと query cache が効かないCommentsAdd Star
PHPのPDO::mysql。prepared statementはquery cacheを使っている!


MS SQL, Oracleなどではずいぶんと前からVIEWが利用可能でしたが
MYSQLでも5.xからVIEWが利用可能になってます。


CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]


VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

view_sample

ビュー定義は以下の制限に規定されます。

* SELECTステートメントはFROM節の中にサブ・クエリーを含めることができません。
* SELECTステートメントはシステム変数もしくはユーザー変数を参照することができません。
* SELECTステートメントは準備されたステートメントパラメータを参照することができません。
* ストアド ルーチン内で、定義はルーチン・パラメータもしくはローカル変数を参照することができません。
* 定義で参照したテーブルもしくはビューは存在しなければいけません。
ただし、ビューを生成し終えた後に、定義が参照するテーブルまたはビューを撤去することができます。
この場合、ビューの使用はエラーとなります。この類の問題に対してビュー定義をチェックするには、
CHECK TABLEステートメントを使用。
* 定義はTEMPORARYテーブルを参照できない上、TEMPORARYビューを生成させることができません。
* ビュー定義内で名称を持つテーブルは存在していなければいけません。
* トリガにビューを関連させることはできません。

ORDER BYはビュー定義の中で許容されていますが、それ自身ORDER BYを持つステートメントを使って
ビューから選択すると無視されます。

view_order_by

UNDEFINEDの場合、MySQLは使用すべきアルゴリズムを選択します。それは出来るだけTEMPTABLE
よりMERGEを優先します。これは、MERGEは通常より効率的で、ビューはテンポラリテーブルを使用すると
更新可能ではなくなるためです。

明確にTEMPTABLEを選択する理由は、テンポラリテーブルを選んだ後ステートメントの処理終了に使用する前に、
内在するテーブルのロックを解放することができるからです。その結果、ロックをMERGEアルゴリズムよりも速やかに
解除し、ビューを使う他のクライアントが長時間ブロックされないようにします。

以下に示す3つの理由によって、ビューアルゴリズムをUNDEFINEDにすることができます。

1) CREATE VIEWステートメントの中にALGORITHM節が現れない。
2)CREATE VIEWステートメントにALGORITHM = UNDEFINED節が明確に含まれている。
3) テンポラリテーブルだけを使って処理できるビューに対して、ALGORITHM = MERGEが規定される。
この場合、MySQL は警告を発し、アルゴリズムをUNDEFINEDにセットします。

例)
view_merge

MERGEアルゴリズムは、ビュー中の行と基礎テーブル内の行の間に1対1の関係が要求されます。
この関係が保持されない場合、代わりにテンポラリーテーブルを使用しければなりません。
ビューに多くの生成子が含まれると、一対一の関係に不足が生じます。

* 集約ファンクション (SUM()、 MIN()、 MAX()、COUNT()等)
* DISTINCT
* GROUP BY
* HAVING
* UNION もしくはUNION ALL
* 選択リスト中のサブ・クエリ
* 文字値だけを参照(この場合、基礎となるテーブルは存在しません)

幾つかのビューは更新可能です。すなわち、基礎をなすテーブルの内容を更新するため、UPDATE、DELETEもしくはINSERTのようなステートメントの中でそれらを使うことができます。ビューを更新可能にするため、ビュー中の行と基礎テーブル中の行の間に1対1の関係が存在しなければなりません。ビューを更新不能にするその他の生成子もあります。もっと具体的に言うと、それが以下のいずれかを含んでいるとビューは更新可能となりません。

* 集約ファンクション(SUM()、 MIN()、 MAX()、COUNT()等)
* DISTINCT
* GROUP BY
* HAVING
* UNION もしくはUNION ALL
* 選択リスト中のサブ・クエリ
* 特定結合(このセクション中の後の部分に追加した結合の説明参照)
* FROM節中の更新不能ビュー
* FROM節中のテーブルを参照するWHERE 節中のサブ・クエリ
* 文字値だけを参照(この場合、更新する基礎となるテーブルは存在しません)
* ALGORITHM = TEMPTABLE (テンポラリテーブルの使用は常にビューを更新不能にする)

(INSERTステートメントで更新不能となる)挿入性に関して、それがビューカラムに対するこれらの追加条件
も満たすと、更新不能ビューが挿入可能になります。

* ビューカラム名に重複があってはなりません。
* ビューに、デフォルト値を持っていないベーステーブル内にあるすべてのカラムを含んでいなくてはなりません。
* ビューカラムは派生カラムではなく、単純なカラムリファレンスでなければなりません。
派生カラムは単純なカラムリファレンスでなく、表現から派生したものです。
これらは派生カラムの例です。

3.14159
col1 + 3
UPPER(col2)
col3 / col4
(subquery)

単純なカラムリファレンスと派生カラムを混合して持つビューは挿入できません。
しかし、当該ビューは、派生したものでないこれらのカラムだけをアップデートする場合に限り更新する
ことができます。このビューを想定すると:

CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;

このビューは、col2が表現から派生しているので挿入できません。
しかし、col2を更新しようとしていない場合、それはアップデートすることができます。
このアップデートは許容されます:

UPDATE v SET col1 = 0;

このアップデートは、それが派生カラムをアップデートしようとしているので許容されません。

UPDATE v SET col2 = 0;

MERGEアルゴリズムで処理することができると仮定すると、場合によってマルチ・テーブルビューを
アップデートすることが可能です。これを実現するには、ビューに(外部結合またはUNIONでなく)内部結合
を使用しなければなりません。また、ビュー定義に含まれている1つのテーブルだけがアップデート可能です。
よって、SET節に基づき、ビュー中の1つのテーブルからカラムだけ選択して名前をつけなくてはなりません。
UNION ALLを使用しているビューは、理論的に更新可能かもしれませんが、実装は処理にテンポラリテーブル
を使用するので拒絶されます。

参考サイト
20.2. CREATE VIEW 構文


LEFT JOIN, RIGHT JOIN, INNER JOIN, CROSS JOINで同じ結果を表示する。

LEFT JOIN

SELECT
p.pid AS `Project ID`,
p.name AS `Project Name`,
c.id AS `Client No`,
c.name AS `Client Name`
FROM project AS p
LEFT JOIN client AS c
USING (id) /* or: ON p.id = c.id */
WHERE c.name IS NOT NULL
;

left_join_review

RIGHT JOIN

SELECT
p.pid AS `Project ID`,
p.name AS `Project Name`,
c.id AS `Client No`,
c.name AS `Client Name`
FROM client AS c
RIGHT JOIN project AS p
USING (id) /* or: ON c.id = p.id */
WHERE c.name IS NOT NULL
;

right_join_review1

INNER JOIN

SELECT
p.pid AS `Project ID`,
p.name AS `Project Name`,
c.id AS `Client No`,
c.name AS `Client Name`
FROM project AS p
INNER JOIN client AS c
USING (id) /* or: ON p.id = c.id */
WHERE c.name IS NOT NULL
;

inner_join_review

CROSS JOIN

SELECT
p.pid AS `Project ID`,
p.name AS `Project Name`,
c.id AS `Client No`,
c.name AS `Client Name`
FROM project AS p
CROSS JOIN client AS c
USING (id) /* or: ON p.id = c.id */
WHERE c.name IS NOT NULL
;

cross_join_review


サブクエリーとアウタークエリーの関連性について

Non-correlated subqueryの例
サブクエリーはouter queryを意識せずとも成り立つので、
Correlationはありません。

SELECT DISTINCT Continent
FROM Country
WHERE Code IN (SELECT CountryCode
FROM CountryLanguage
WHERE Language='English'
AND Percentage>50
);


SELECT CountryCode
FROM CountryLanguage
WHERE Language='English'
AND Percentage>50;

subquery_sample

以下のQueryもCorrelationはありません。

SELECT * FROM Country
WHERE Continent = 'South America'
AND Population =
(SELECT MIN(Population) FROM Country
WHERE Continent = 'South America')\G


SELECT MIN(Population) FROM Country
WHERE Continent = 'South America';

subquery_correlation

上記QueryをCorrelationがあるqueryに書き換えてみる。
変更前

SELECT * FROM Country
WHERE Continent = 'South America'
AND Population =
(SELECT MIN(Population) FROM Country
WHERE Continent = 'South America')\G

変更後

SELECT * FROM Country as COUNT01
WHERE Continent = 'South America'
AND Population =
(SELECT MIN(Population) FROM Country COUNT02
WHERE COUNT01.Continent = COUNT02.Continent)\G

——————————————————————————–
上記クエリーは以下のように先頭にAliasを付けてもOK
——————————————————————————–

SELECT COUNT01.* FROM Country as COUNT01
WHERE COUNT01.Continent = 'South America'
AND COUNT01.Population =
(SELECT MIN(COUNT02.Population) FROM Country as COUNT02
WHERE COUNT01.Continent = COUNT02.Continent)\G

——————————————————————–
subquery_change_relation

    サンプルその2


SELECT C.Continent, C.Name, C.SurfaceArea
FROM Country C
WHERE SurfaceArea =(
SELECT MAX(SurfaceArea)
FROM Country C2
WHERE C2.Continent = C.Continent);

select_sub_query

ALLをサブクエリーに利用する
ALL という語は比較演算子の後に指定するもので、“サブクエリが返すレコードの
ALL(すべて)に対して比較が TRUE の場合 TRUE を返す” ことを表します。
次に例を示します。
SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

テーブル t1 に {10} という値を含むレコードがあるとします。 テーブル t2 に含まれている値が {-5,0,+5}
の場合、この式は TRUE になります。なぜなら t2 に含まれている値はすべて 10 より小さいからです。
テーブル t2 に含まれている値が {12,6,NULL,-100} の場合、この式は FALSE になります。
なぜなら t2 に、10 よりも大きい 12 という値が 1 つ存在するからです。 テーブル t2 に含まれている
値が {0,NULL,1} の場合、この式は UNKNOWN になります。

(例)Countryテーブル
all_t1

以下のサブクエリーは全ての結果からPopulationが “>=” の値を算出しているので、
必然的にPopulationの値が一番多い”=”大陸が選択されます。
全部の値の中から ”>” にあたる値は無く “=” のみが最大値に適用される。

SELECT Continent, Name
FROM Country c1
WHERE Population >= ALL (SELECT Population
FROM Country c2
WHERE c1.Continent=c2.Continent
);

all

ANYをサブクエリーに利用する
ANY という語は比較演算子の後に指定するもので、“サブクエリが返すレコードの
ANY(いずれか)に対して比較が TRUE の場合 TRUE を返す” ことを表します。 次に例を示します。

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

テーブル t1 に {10} という値を含むレコードがあるとします。 テーブル t2 に含まれている値が
{21,14,7} の場合、この式は TRUE になります。なぜなら t2 に、10 よりも小さい 7 という値が
存在するからです。 テーブル t2 に含まれている値が {20,10} の場合や、テーブル t2 が空の場合、
この式は FALSE になります。 テーブル t2 に含まれている値が {NULL,NULL,NULL} の場合、
この式は UNKNOWN になります。

IN という語は = ANY のエイリアスです。したがって、次の 2 つのステートメントは同じです。

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);

SOME という語は ANY のエイリアスです。したがって、次の 2 つのステートメントは同じです。

SELECT s1 FROM t1 WHERE s1 <> ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

SOME はめったに使用されませんが、上の例では、この語がどのような場合に役立つかを示しています。
“a is not equal to any b” という英語のフレーズを、ほとんどの人は “a と等しい b はまったく存在しない
” という意味に受け取ります。しかし、SQL 構文では意味が異なります。ANY の代わりに <> SOME
を使用することによって、このクエリの本当の意味を誰もが確実に理解できるようにすることができます。

以下のクエリは、面積がAVGより大きい大陸と名前を選択してます。
このサブクエリーは、アウタークエリーとは関係なく実行可能です。(not correlated subquery)


SELECT Continent, Name
FROM Country
WHERE SurfaceArea > ANY (SELECT AVG(SurfaceArea)
FROM Country
GROUP BY Continent
);

any

上記クエリをALLとANYで比較
all_any

INをサブクエリーに利用する


select CountryCode,Language from CountryLanguage
where CountryCode IN(select Code from Country where GovernmentForm = 'monarchy');

in_select

EXISTS をサブクエリーに利用する


select A.Code,A.name from Country A
where EXISTS (
select * from CountryLanguage B
where A.Code = B.CountryCode
AND B.Language = 'Japanese');

exsists

——————————–
Foot Note
——————————–
Subqueries in the FROM clause of a query cannot be correlated with the outer query.
FROM句のサブクエリーは外部クエリーと依存関係を設定する事が出来ない。

mysql> SELECT Name, Language
-> FROM Country AS c, (SELECT Language
-> FROM CountryLanguage
-> WHERE CountryCode = c.Code
-> ) AS tmp;

ERROR 1054 (42S22): Unknown column ‘c.Code’ in ‘where clause’
mysql>

from_and_subquery

もし上記クエリーを修正すると以下のような感じでしょうかね。


SELECT C.Name, CL.Language FROM Country AS C,
(SELECT CountryCode,Language FROM CountryLanguage) AS CL
WHERE CL.CountryCode = C.Code;

subquery_fix

参考サイト
6.4.2. サブクエリ構文
6.4.2.4. ALL とともに使用したサブクエリ
6.4.2.3. ANY、IN、SOME とともに使用したサブクエリ


関数 perror() は、システムコールやライブラリ関数の呼び出しにおいて、
最後に発生したエラーに関する説明メッセージを生成し、標準エラー出力に出力する。

perror_h

殆どのシステムエラーでは、内部テキストメッセージに加えて、
MySQLは以下のスタイルでシステムエラーコードを表示します。

message … (errno: #)
message … (Errcode: #)

システムのドキュメンテーションを確認するか、perrorユーティリティを使用することで
エラーコードの意味を割り出すことができます。

Can’t find file: ‘./mysql/host.frm’ (errno: 13)
ERROR 1064 (42000)

[root@colinux ~]# perror 13
OS error code 13: Permission denied
[root@colinux ~]#

perror

MYSQLのエラーレベルは、Error , Warning, Noteの3つに分類されていて、
SHOW WARNINGSにて確認することが出来ます。

SHOW WARNINGS は、メッセージを作成した最後のステートメントから生じたエラー、警告、そしてノート
メッセージを表示、または、もしテーブルを利用した最後のステートメントが何のメッセージも作成しなければ、
何も表示しません。関連ステートメントである SHOW ERRORS はエラーだけを表示します。

warnings

エラー警告、そしてノート メッセージの最高格納数は max_error_count システム変数によって
コントロールされています。デフォルトにより、その値は64です。格納するメッセージ数を変更したければ、
max_error_count の値を変更してください。次の例では ALTER TABLE ステートメントは3つの警告
メッセージを発生しますが、max_error_count が1に設定されている為、そのうちの1つしか格納されません。

mysql> DROP TABLE IF EXISTS no_such_table;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+——-+——+——————————-+
| Level | Code | Message |
+——-+——+——————————-+
| Note | 1051 | Unknown table ‘no_such_table’ |
+——-+——+——————————-+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'max_error_count';
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_error_count | 64 |
+—————–+——-+
1 row in set (0.01 sec)

mysql>

警告を無効にするには、max_error_count を0に設定してください。この場合、
warning_count はいくつの警告が起きたか指示しますが、どのメッセージも格納はされません。

SQL_NOTES セッション変数を0に設定して、Note レベルの警告が記録されないようにできます。

MySQLクラスタエラーコードのエラーメッセージを取得するためには、
perrorを–ndbオプションと起動してください。

perror_sql

参考サイト
7.18. perror — エラーコードの説明
4.11.2. エラー ログ
12.5.4.31. SHOW WARNINGS 構文
MySQL Server 5.0 管理監視ガイド


自分が作成した、又は他の人が作成したprocedureの内容を確認したい場合が
業務上あるかと思います。そんな時は以下の方法で確認する事が出来ます。

その1) information_schemaから情報を呼び出す。
該当するテーブルはROUTINESテーブルになります。

information_schema_routines


select ROUTINE_NAME,ROUTINE_DEFINITION
from information_schema.ROUTINES
where ROUTINE_SCHEMA = 'STUDY'
AND ROUTINE_NAME = 'DO_WHILE_TEST'\G

routines

その2) SHOW CREATEステートメントにて確認(information_schemaから情報を得ている)

SHOW CREATE PROCEDURE STUDY.DO_WHILE_TEST\G

show_create_procedure

おまけ) SHOW PROCEDURE STATUSでは内容の確認は出来ないが状態を確認出来る。
SHOW PROCEDURE STATUS LIKE 'DO_WHILE_TEST'\G

show_procedure_status1

参考サイト

21.14. INFORMATION_SCHEMA ROUTINES テーブル