2010年03月03日

MySQLインデックス・簡単調査・最適化

今回はMySQLネタです。

MySQLに対してアクセスが増え、データが溜まってくると、サーバが遅くなったという連絡がはいることがあります。

体感でですが、データが10万件を超えたぐらいから、そういう話が増えて来るようにも思います。

原因としては、INDEXの張り方がうまくない(そもそもはっていない)ということが多いと思うのですが、どこにどうやってはれば良いのか、すぐに分かります?

今回、簡単に見つける方法があることに気がついたので、メモしておきます。phpMyAdminを使います。

phpMyAdminには、「MySQLプロセスの表示」という部分がありますので、「今、サーバが重くなっている」という時点でこちらを開きます。
screen.png

すると、現在実行しているSQLの情報が表示されます。
「プロセス」というタブを数回押してみると、その状態で「いつも同じようなSQL文が何度も表示されている」かもしれません。

それが、INDEXをはるべき場所です。

今回のケースでは、
---------------------------
SELECT *
FROM sales_items
WHERE sale_id = "111111"
---------------------------
というようなSQLが大量に存在していました。
ここで注目すべきは、「WHERE句の後」です。

この場合は、「sale_id が 111111のところ」を検索しにいって時間がかかっているようです。調べてみると、やはり sale_id にはインデックスがはられていませんでした。

phpMyAdminから早速、INDEXをはってみると、このプロセスには表示されないよう(=時間がほとんどかからない作業)になりました。topで見ても、これだけでしゅるしゅると負荷率が下がってゆきます。

なお、
---------------------------
SELECT *
FROM sales_items
WHERE sale_id = "111111" AND nanika_id = 123
---------------------------
のように、WHERE句の後に複数の条件があってプロセスがたくさん存在しているような場合には、複合INDEXを張ったほうが効率が良いようです。

これが一番手軽だとは思いますが、もう少しプロセスを詳しく知りたい、とか、SQL文が長すぎてすべて表示されないんだけど、というような場合には、もう少し工夫できます。

こちらに非常に便利な記事がありました。

Open Tools for MySQL Administrators
http://www.oreillynet.com/pub/a/mysql/2006/10/19/mysql-tools.html

このあたりから便利なツールを見つけてきてチェックするというのも良いと思います。個人的には、「おっ、こんなので十分だ」と思ったんですがいかがでしょうか。

Monitoring MySQL Queries Using Bash Script
http://blog.taragana.com/index.php/archive/monitoring-mysql-queries-using-bash-script/
-----------------------------------
#!/bin/bash
while [ 1 ]
do
mysql -N -u root -pパスワード -e 'show full processlist' |grep -v 'show full processlist'
sleep 2
done
-----------------------------------

たったのこれだけで、えんえんと状態を表示してくれますので、MySQLの状態把握ができます。知らなかった人にはちょっとお役に立てばと思います。


posted by SDozono at 11:47| 東京 🌁| Comment(0) | TrackBack(0) | MYSQL | このブログの読者になる | 更新情報をチェックする

2007年11月27日

MySQLバルクインサート

//UTF-8のファイルをEUC-JPに変換しながらINSERT INTO してゆく例。
 
 $fp = fopen("ファイル名.sql","r"); //sql file
 $con = mysql_connect("localhost", "ユーザ名", "パスワード"); //host, username, passowrd
 mysql_select_db("テーブル名"); //database name
 if (!$con){
  die("couldn't connect".mysql_error());
 }
 mysql_query("SET NAMES UJIS");
 while ( !feof($fp) ) {
  $one = fgets($fp, 9182);
  if(strpos($one, "insert  into") !== false){
   $query = mb_convert_encoding($one,"EUC-JP","UTF-8");
   mysql_query($query);
  }
 }
 echo "ok.";
 fclose($fp);
posted by SDozono at 00:19| 東京 ☀| Comment(0) | TrackBack(0) | MYSQL | このブログの読者になる | 更新情報をチェックする

2005年10月15日

mysynxのゆくえ

マスターMYSQLにつなげない場合には、スレーブMYSQLからデータを受け取るようにするのはどうだろう?
ミッションクリティカルなものにも使えるかな?
さらに擬似RAIDも実現できたら最高だ。

Javaではそういうものがある
http://c-jdbc.objectweb.org/

から、PHPでやってできないことはないかも。
posted by SDozono at 11:19| 東京 ☁| Comment(0) | TrackBack(0) | MYSQL | このブログの読者になる | 更新情報をチェックする

2005年10月14日

mysynx - MYSQL easy synchronization technique!

PHPアプリケーションの中で、MYSQLのレプリケーション(もどき)をしてしまおう、というツールを作ってみました。

mysql_query(うにゃうにゃ)
とあったら、
$mySynx->mysql_query(うにゃうにゃ)
と置き換えるだけで、設定ファイルに基づいて複製を作るはず、です。

まだ、テストが必要かも。
データも暗号化したほうが安全。
途中で回線が途絶えた時のことも考えると、ログでシンクしたほうがいいよね……。
でも、SQLyogと併用で使うなら便利だと思います。
スピードもさらに最適化できるかな。

HTTP Tunnelingを使えば、共有サーバのMYSQLもスレーブとして使用できます。
LGPLで配布しようと思っていますので、まずはお披露目まで。

※重要
バージョン0.6は、トンネリングがつながらないバグがありました。0.6をダウンロードしてくださった方、本当にすみません。0.7をお試しください。こちらはチェックしました!

mysynx07.zip
posted by SDozono at 14:48| 東京 ☀| Comment(0) | TrackBack(0) | MYSQL | このブログの読者になる | 更新情報をチェックする

SQLyogの“シンクロナイズ”機能は、日本語にもネイティブ対応!

先日、送ったパッチを当ててくれたので、SQLyog 4.2のテスト版からは、日本語のシンクロナイズに対応していると思います。

あとは、表示のほうも対応してほしい……。
posted by SDozono at 13:28| 東京 ☀| Comment(0) | TrackBack(0) | MYSQL | このブログの読者になる | 更新情報をチェックする

2005年08月13日

SQLyogの効用

現在、サーバ間のMYSQLのデータコピーやシンクロナイズは、SQLyogを使用して作業している。エクスプローラー感覚でできるのはたいへん便利だ。
SQLyogはバージョンアップするたびに機能が追加・強化されており、その様子を眺めているのは楽しい。昔、1980年代にSONYのウォークマンのカタログを眺めていた時のような感覚を味わう。

ただ、こと日本語に関していうと、
・SQLyog本体は、SJISデータの閲覧にしか対応していない。
 (データの閲覧には phpMyAdmin を利用しているからいいけれど……。)
・MYSQL 4.1がからんでくると、シンクロナイズがうまくいかない時がある。
 (内部でUTF8変換がかかっているらしい。サーバの設定?MYSQLの設定?)

SQLyogは、PHPファイルで書いてあるSQLyogtunnel.phpでMYSQLからデータを吸い出し・書き込むことができる。
だから、このPHPファイルに少し手を加えれば何とかなるかな、とフィルタ関数を追加してみた。

具体的には、insert と update を発行する瞬間をつかまえて、文字列の中に0xC3と0xC2があったら削除。0xC3の場合は、64(0x40)を次の文字コードに足して、元の文字コードをリストアする、ってなことをやってみた。

それから、"SET NAMES UJIS"も発行。

とりあえず、これでうまくいっている。
posted by SDozono at 07:12| Comment(0) | TrackBack(0) | MYSQL | このブログの読者になる | 更新情報をチェックする

2005年08月08日

PHPで楽にMYSQLを使うには

PHPからMYSQLにアクセスし、情報を処理するには、phpMyAdminを使うのがいい。
洗練されたインターフェイスで、遠隔地から操作できる。

http://www.phpMyAdmin.net

ところが、これをそのまま自分のアプリケーションに流用するというわけにはいかない。
現在使用しているMYSQLデータベースの内容を編集するテーブルエディタをてっとりばやく作りたい場合には、とりあえず現在のところは、

http://www.phpmyedit.org/

を利用している。日本語を通るように、さらにサーバとクライアントで文字コードを変えられる仕掛けを作った。とても便利だ。

ちょっと調べてみて、さらに今後、使えるようになりそうだな、という、データベース用コードジェネレーターツールをいくつか。

http://www.phpbuns.org/
http://phplens.com/lens/

追加。
http://dbperceptor.sourceforge.net/
http://www.phpguru.org/
posted by SDozono at 11:35| Comment(0) | TrackBack(2) | MYSQL | このブログの読者になる | 更新情報をチェックする
WebとCTI、VoIP技術の高みを目指して - 技術者の24時間
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。