www.hbirds.net

MySQL のチューニングをしてみました。

$ sudo /usr/bin/mysqltuner

>> MySQLTuner 1.0.1 – Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with ‘–help’ for additional options and output filtering
Please enter your MySQL administrative login: *****
Please enter your MySQL administrative password: ********

——– General Statistics ————————————————–
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.41-3ubuntu12.10-log
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 76M (Tables: 170)
[!!] Total fragmented tables: 9

——– Performance Metrics ————————————————-
[--] Up for: 8d 3h 25m 53s (1M q [2.389 qps], 57K conn, TX: 23B, RX: 665M)
[--] Reads / Writes: 67% / 33%
[--] Total buffers: 560.0M global + 160.4M per thread (151 max threads)
[!!] Maximum possible memory usage: 24.2G (498% of installed RAM)
[OK] Slow queries: 0% (19/1M)
[OK] Highest usage of available connections: 8% (13/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/24.0M
[OK] Key buffer hit rate: 100.0% (127M cached / 30K reads)
[OK] Query cache efficiency: 71.9% (943K cached / 1M selects)
[!!] Query cache prunes per day: 17560
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 176K sorts)
[!!] Temporary tables created on disk: 33% (105K on disk / 311K total)
[OK] Thread cache hit rate: 99% (20 created / 57K connections)
[!!] Table cache hit rate: 11% (256 open / 2K opened)
[OK] Open file limit used: 47% (486/1K)
[OK] Table locks acquired immediately: 99% (743K immediate / 744K locks)

——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Temporary table size is already large – reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 32M)
table_cache (> 256)

「—- Recommendations —–」以降におすすめの 設定値が出力されます。(文中の[!!]箇所は改善が必要な箇所です)
query_cache_size (> 32M)とtable_cache (> 256)を大きくしたほうがいいよということなので、

$ sudo nano my.cnf で　
query_cache_size 64M
table_cache 512
に修正しました。

mysql のリスタート
$ sudo /etc/init.d/mysql restart

別途、WordPressのプラグインでデータベースのOptimizeもしてから、再度 mysqltuner を実行
——– General Statistics ————————————————–
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.41-3ubuntu12.10-log
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 76M (Tables: 170)
[!!] Total fragmented tables: 5

——– Performance Metrics ————————————————-
[--] Up for: 24m 13s (5K q [3.621 qps], 490 conn, TX: 62M, RX: 1M)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 592.0M global + 160.4M per thread (151 max threads)
[!!] Maximum possible memory usage: 24.2G (499% of installed RAM)
[OK] Slow queries: 0% (0/5K)
[OK] Highest usage of available connections: 2% (4/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/23.8M
[OK] Key buffer hit rate: 95.8% (407K cached / 17K reads)
[OK] Query cache efficiency: 43.7% (1K cached / 3K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 608 sorts)
[!!] Temporary tables created on disk: 35% (415 on disk / 1K total)
[OK] Thread cache hit rate: 99% (4 created / 490 connections)
[OK] Table cache hit rate: 22% (304 open / 1K opened)
[OK] Open file limit used: 51% (609/1K)
[OK] Table locks acquired immediately: 100% (3K immediate / 3K locks)

——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours – recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Temporary table size is already large – reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses

ちゃんとチューニングできたようです。

翌日、再度チェックしてみると、
——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours – recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Temporary table size is already large – reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 64M)

64Mでも足りないと言っているので
64M→128Mにしてみました。
どこまで行くのか・・・・

参考サイト
・MySQLを診断してチューニングポイントを教えてくれるツール MySQL Tuner の紹介

Webサイトのリアルタイムアクセス解析を行なってくれるPiwikをUbuntu10.04サーバーにインストールしてみました。
本家のサイトは、piwikになります。
インストール方法は、本家サイトに詳しく書いてあります。

Piwik is a downloadable, open source (GPL licensed) real time web analytics software program. It provides you with detailed reports on your website visitors: the search engines and keywords they used, the language they speak, your popular pages… and so much more.

Google Analytics もいいですが、piwikもなかなか良いと思います。
こちらは、Google Analytics の画面です。

こちらが、Piwikのダッシュボード画面です。
Google Analytics に取って代わることを目指しているだけあって、かなり自由度が高いと思います。

ブログパーツのようにWidgetに張り付けることもできます。

piwik は、分析対象のWebサイトを設置しているサーバーに分析プログラムをアップロードしたり、分析用のデータベースを設置したり、パーミッションを変更したり等々と多少手間がかかります。
phpやmysqlの所定のバージョン以上のものが、サーバーにインストールされていることが条件になります。
本家サイトでは５分で設置できると書いてありますが、はじめての場合は５分では無理でしょうね。自分の場合は、１５分くらいはかかったかな。

今回は、ワードプレスで作成してるこのサイト(www.hbirds.net)にpiwikをインストールしてみたいと思います。
以下は、導入手順です。

1.本家サイトから分析プログラムをダウンロードし、解凍します。
latest.zip というファイルがダウンロードされます。
解凍すると、piwik というフォルダができます。

2.piwikフォルダをwww.hbirds.net直下に適当なFTPアップローダーを使ってアップロードします。
　自分の場合は、/var/www/www.hbirds.net ディレクトリにアップロードします。
　$ cd /var/www/www.hbirds.net
　$ sudo unzip latest.zip
　$ sudo wget http://piwik.org/latest.zip
　　
3.適当なWEBブラウザで http://www.hbirds.net/piwik とアクセスします。
　あとは、表示されるインストラクションに従って、基本的に進めていきます。
自分の場合は、右のようなメッセージが表示されました。
パーミッションを修正する必要があるようです。


指示どおりに以下のとおり処理します。
$ sudo chmod 0777 /var/www/www.hbirds.net/piwik/tmp
$ sudo chmod 0777 /var/www/www.hbirds.net/piwik/tmp/templates_c/

画面の下２行のコマンドは、実行するとディレクトリがないのでエラーになりますが、システムが作ってくれるので、気にせずに上の画面のなかの refresh the page リンクをクリックします。
すると、右の画面が表示されます。
Next で次のステップに進みます。
全部で９ステップありますが、3ステップ目でデータベースのセットアップが必要になるので、piwik 用のデータベースを予め作成しておきます。

4.piwik 用データベースの作成
$ sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 51905
Server version: 5.1.41-3ubuntu12.9-log (Ubuntu)

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.

mysql> CREATE DATABASE piwik; ←piwik という名前のデータベースを作成
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL on piwik.* to piwikuser@localhost IDENTIFIED BY ‘abcdefg’; ←piwikuser というユーザーをパスワードabcdefgで作成。もちろんユーザー名はパスワードは任意にセットしてください。
Query OK, 0 rows affected (0.04 sec)

mysql> FLUSH PRIVILEGES;←変更を反映
Query OK, 0 rows affected (0.00 sec)

以上で、piwik用のデータベースが作成できました。

5.データベース名やパスワードを入力し、データベースのセットアップを行ないます。
　セットアップ画面は、省略です。
　次のステップに進むとデータベース内にpiwikのテーブルが作られます。

6.分析対象のサイトを登録します。
右の画面のように分析対象を登録します。

7.トラッキングコードをサイトに張り付けます。
次のステップに進むと、右の画面が表示されます。
Here is the JavaScript Tracking tag to include on all your pages, just before the tag.
ということで、JavaScript Tracking tagをfooter.phpなどにコピペして張り付ける必要がありますが、WordPressのプラグインにこのコードを自動で張り付けてくれるもの(WP-piwik plugins)がありますので、そちらを使ってもOKです。

8.Congratulations
以上でインストールが完了しました。

9.ログイン
ユーザー名とパスワードでログインします。

10.WP-Piwik Plugins のセッティング

番外です。
WP-Piwik プラグインは、Piwik と連携したWordPressプラグインです。
右はセッティング画面の一部ですが、ADD Script にチェックするとトラッキングコードを自動的に張り付けてくれます。



他にも、WordPressのダッシュボードに右のようなグラフ等を表示したりしてくれます。

WordPress の Widget にPiwikの分析パーツを張り付ける方法
Piwikのダッシュボード画面のWidgetをクリックして、右の画面のように張り付けたい分析パーツを選択し、Embed Iframe のコードをコピーし適宜修正します。

後は、WordPress のWidget（Text) にペーストするだけです。

投稿記事の表示順序を自由自在に変えるテクニックです。
投稿記事はデフォルトでは、日付順に新しいものから古いものへ順に並ぶようになっていますが、例えばある特定カテゴリーの投稿記事を表示する場合は、逆に古いものから順に並ばせたいといった場合があります。
このサイトでも使っているWP-PostvVewsプラグインなども、記事の閲覧者数をカスタムフィールドに登録して大きい順に表示したりしています。
こういったテクニックについて、簡単に紹介します。

最も簡単なのは、それ用のプラグイン Custom Query String Reloaded を使うことです。これは、カスタムフィールドには対応していませんが、カテゴリー別に記事のソート順序などをかえることができるようになっています。

今回紹介する方法はより汎用性がある方法で、WordPress から　SQL を発行して必要な記事を表示する方法です。MySQLの知識が必要になりますが、それを習得すれば、投稿記事をいかようにも抽出し、かつ並べ変えも可能になるでしょう。

スクリプトのコアな部分は以下のとおりです。
このケースの場合は、見て分かるように、投稿日が現時点より前、投稿タイプが’post’、公開されている、かつパスワード保護されていない投稿記事を１０件、更新日の新しい順に並べ替えて配列$pageposts に取得しています。
それをsetup_postdata()で整形化しています。

<?php
 $querystr = "
    SELECT $wpdb->posts.*
    FROM $wpdb->posts
    WHERE post_date < '".current_time('mysql')."'
    AND post_type = 'post'
    AND post_status = 'publish'
    AND post_password = ''
    ORDER BY post_modified DESC LIMIT 10
 ";
 $pageposts = $wpdb->get_results($querystr, OBJECT);
?>
 <?php if ($pageposts): ?>
  <?php foreach ($pageposts as $post): ?>
    <?php setup_postdata($post); ?>
   <div>この間にコンテント表示のスクリプトを書く</div>
  <?php endforeach; ?>
  <?php else : ?>
 <?php endif; ?>

WordPressのデータベース構造とMySQLのSELECT文の文法について理解できれば、
$querystr を必要な条件で書き換えればOKです。

次の例は、WP-PostsViewsプラグインによるカスタムフィールドの値（閲覧数）で大きい順にソートして表示するケースです。前の例は、実はこのテクニックを使わなくてもWPの関数を使えばできるのですが、カスタムフィールドを使って投稿記事をソートするのは、この方法でしかできないと思います。
$querystr 以外のスクリプトは上の例とまったく同じです。

 $querystr = "
    SELECT DISTINCT $wpdb->posts.*, (meta_value+0) AS views
    FROM $wpdb->posts LEFT JOIN $wpdb->postmeta ON $wpdb->postmeta.post_id = $wpdb->posts.ID
    WHERE post_date < '".current_time('mysql')."'
    AND post_type = 'post'
    AND post_status = 'publish'
    AND meta_key = 'views'
    AND post_password = ''
    ORDER BY views DESC LIMIT 10
 ";

このケースを応用すれば、カテゴリーごとに閲覧数が多い順に投稿記事を表示したりすることもできますね。

参考文献はこれです。

XamppのVista パソコンへのローカルインストールの方法を忘れないようにメモしておきます。
過去に何度かインストール・アンインストールをしたことがあるパソコンを対象にしています。

１．Windows用の最新版のXampp1.7.1をダウンロード
　　インストーラ版をダウンロード
　　※アンインストールする場合は、uninstall.exe を実行させること。
　　　完全にファイルが削除できなかった場合は、再起動してからxamppフォルダを右クリックして削除
　　　すれば良い。

２．サービスに残っているMySQLを削除
　　コマンドプロンプトを右クリックで管理者権限により起動し、>sc.exe delete mysql を実行します。

３．Xamppインストーラを起動
　　xamppフォルダはProgramFilesには作らないようにし、デフォルトのCドライブのルートとします。
　　サービスとしてのインストールのチェックは、Apache,MySQLとも入れないようにします。

４．Xamppコントロールパネルを起動
　　ここでApache,MySQLのSVCにチェックを入れて、Apache,MySQLを起動させます。

５．日本語設定ファイルの更新
　　Xampp/mysql/bin/のmy.iniファイルの
　　・[client]に以下を追記
　　

default-character-set=utf8

　　・[mysqld]に以下を追記
　　

default-character-set=utf8
skip-character-set-client-handshake

６．mysqlを起動して確認
コマンドプロンプトからMySQLを起動し、status コマンドによりキャラクターセットがutf8になっていることが確認できれば成功

MySQLのお勉強のためにXamppをVistaにインストールしてみた。
Xamppコントロールパネルで、svc（サービス）にチェックが入っていると、MySQLが「MySQLの起動中にエラーが発生しました[-1]」と出て起動できません。
調べてみると、以前にインストールしたMySQLがサービスとして残っていることに原因がありそう。

対処方法は、「スタート」からコマンドプロンプトを右クリックして管理者権限で起動し、sc.exe delete MySQLと入力すればよい。右のようになれば削除成功！


すかさず、Xamppのコンパネに戻り、MySQLを起動すると右の画面のように無事に起動させることができました。

MySql を簡単に操作できるMySQL Quick Admin を入れてみました。
WordPress の管理画面からphpmyadmin でも操作できますが、どっちが使い勝手がいいのかな。

１．MySQL Quick Admin をダウンロード・解凍します
　　MySQL Quick Admin

２．解凍したファイルのなかにあるconfig.phpを開いて、データベース関係の設定を変更
　　username とpassword　だけ書いてあげればOKです。

３．サーバーにアップロードします
　　www.hbirds.net の直下に置きました。

４．MySQL Quick Admin を開きます
　　www.hbirds.net/mqa_v.1.5.5/login.php にブラウザでアクセス

SSH（Poderosa)でMySql にアクセスする方法

１．Xrea のコントロールパネルにログインし、SSH を使えるようにする
　　しばらく使わないと自動的に閉じられてしまうようなので、その場合はXreaの設定（ホスト情報登録）からしなければなりません。

２．Poderosaにパスワードを打ち込んでSSH接続する
　　hbirds@s283:~> 　と出れば接続できてます。

３．MySql に接続する
　　hbirds@s283:~>mysql -u hbirds_57 -p
Enter password:
ここで、パスワードを右クリックから貼り付けてエンターキーを押して
　　※パスワードは画面に表示されず、カーソルも動かないが気にせず、エンターキーを押すこと
　　mysql>
　　となったら、アクセスできてます。

４．データベースに接続する
　　mysql>connect hbirds_57

１．Poderosa を起動し、サーバーにアクセス
　　コンピュータの絵のところをクリックし、XrreのパスフレーズXXXXXXXXXXを入力

２．以下のように入力する
　hbirds@s283:~>mysql -u hbirds -p
　password:XXXXXXXXXX
　※pasword は右クリックの貼り付けで入力し、画面に表示されなくても気にせずエンター

３．クエリーキャッシュのサイズを確認してみると134Mもある。
　　データベースのアクセスの高速化にはキャッシュを増やせば良いようですが、１３４Mもあれ
　　ばもうクエリキャッシュをこれ以上増やす必要はないですね。

mysql> SHOW VARIABLES LIKE ‘query_cache_size’;
+—————————+—————–+
|　Variable_name　　|　　Value　　　|
+—————————+—————–+
|　query_cache_size |134217728　|
+—————————+—————–+
1 row in set (0.00 sec)

ワードプレスにギャラリーを設置するプラグインとして、WPG2というものがあることを発見して、早速インストールしてみた。
インストールは、通常のプラグインとまったく同様で、ダウンロード、解凍、アップロード、有効化で問題ありませんが、管理画面で各種設定をしようと試みたところ？？？？。
よくよく調べてみると、そもそもこのプラグインは単体で動くものではなく、Gallery2というサーバー上のデータベースを使って写真等を保管・開示したりするソフトと連動して、ワードプレス上でGallery2を展開するプラグインでした。

ということで、Gallery2 をまずインストールしてみました。

１．Gallery2（圧縮ファイル）をダウンロード
　　サイトからフルバージョンのtar.gzファイルを選択し、ダウンロードします。

２．tar.gzファイルをアップロード
　　/www.hbirds.net/gallery2/にアップロードします。

３．SSHでtar.gzファイルを展開（解凍）
　　詳細な手順は、ここを参照してください。

４．http://www.hbirds.net/gallery2/gallery2にアクセス
　　インストール画面が開きます。
　　多言語化されており、日本語を選択すると以後、日本語でインストール手順が表示されます。
　　特に注意を要する点は、以下の２点です。
　　・サーバー上にGallery2用にmysqlデータベースを予め作成しておく
　　・セーフモードのサーバー(xrea等)の場合は、セーフモードを回避する.htaccessファイルを
　　　予め作成し、/www.hbirds.net/gallery2/　に入れておく。

忘れないように簡単にインストール手順を書いておきます。

１．WordPressのダウンロードと解凍
　　Googleなどで検索すれば、ダウンロードサイトはすぐにわかります。
　　解凍したら、フォルダー内のReadmeファイルをよく読みます。

２．データベース（mysql）の作成
　　サーバーXREAのコントロールパネルにアクセスし、WordPress用のデータベースを
　　作成します。
???? XREAの場合、データベース名とユーザー名は同じになり、hbirds_○○となります。
　　パスワードを入力します。文字コードは、UNICODEにします。
　　以上を入力して、「作成」ボタンを押します。

３．wp-config-sample.phpの修正
???? ２．のデータベース名、ユーザー名、パスワードを書き換えて、wp-config.php
　　として保存します。

４．WordPressフォルダをサーバーにアップロード
　　www.hbirds.net　の直下にアップロードします。

５．www.hbirds.net/wp-admin/install.php　にアクセス
　　あとは指示通りにすれば良いです。
　　デフォルトのユーザー名が「admin」になっているところが分かりづらかったのですが、
　　あとは問題なくインストールできました。

忘れないように簡単にインストール手順を書いておきます。
１．WordPressのダウンロードと解凍
　　Googleなどで検索すれば、ダウンロードサイトはすぐにわかります。
　　解凍したら、フォルダー内のReadmeファイルをよく読みます。

２．データベース（mysql）の作成
　　サーバーXREAのコントロールパネルにアクセスし、WordPress用のデータベースを作成します。
　　XREAの場合、データベース名とユーザー名は同じになり、hbirds_○○となります。
　　パスワードを入力します。文字コードは、UNICODEにします。
　　以上を入力して、「作成」ボタンを押します。

３．wp-config-sample.phpの修正
　　２．のデータベース名、ユーザー名、パスワードを書き換えて、wp-config.php として保存します。

４．WordPressフォルダをサーバーにアップロード
　　www.hbirds.net　の直下にアップロードします。

５．www.hbirds.net/wp-admin/install.php　にアクセス

　あとは指示通りにすれば良いです。デフォルトのユーザー名が「admin」になっているところが分かりづらかったが、あとは問題なくインストールできました。