« June 2009 | Main | August 2009 »

July 2009

July 27, 2009

PostgreSQL のボトルネックを統計的に監視・解析する方法

 先日書いた「MySQL のボトルネックを統計的に監視・解析する方法」について、PostgreSQL でも pg_stat_activity テーブルを使って実行中のクエリ一覧を取得できると higepon さんに教えてもらったので、やってみました。

% ppdump > ppdump.txt

のようにクエリをサンプリング (デフォルトで100秒間程度) して、

% ppfilter < ppdump.txt | ppreport

のようにすると、平均負荷の高いクエリから順にソートされて表示されます。詳しい使い方や考え方については、mprofile のエントリをご参照ください。

 pprofile のソースコードは、/platform/postgresql/pprofile – CodeRepos::Share – Tracに置いてあります。負荷が高い PostgreSQL 環境が手元にないので、ちゃんと統計が取れているかいまいち不安なのですが、興味のある方は、お試しいただければ幸いです。

July 22, 2009

Using a statistical approach to analyze / monitor MySQL bottleneck queries

What comes up to your mind when you hear the phrase "MySQL performance tuning?"  Most of the discussions covering the topic are things like server parameter tuning and blog entries describing the use of the "EXPLAIN" command.  However, it is too time-consuming to check every single SQL query.  A better approach is at first to determine the SQL queries that are actually consuming the server resources, and then to optimize those queries.

So the question is how to find out the bottleneck queries.  At MySQL Conference & Expo 2009, Mark Callaghan explained in his keynote that Google was taking a statistical approach using the "SHOW PROCESSLIST" as a solution.  The same command is mentioned as a rival approach in the webpages of MySQL Query Analyzer as well.  Today, having a need for something alike, I wrote a set of tiny script files under the name "mprofile" that does the same thing.  It can be used as follows.

Sampling SQL queries

Use mpdump to sample running queries from mysqld periodically.  The default setting is to fetch 1,000 samples in 0.1 second interval  (in other words, it would take a little more than 100 seconds to execute).  Please use the --help option to find out the way to specify the number of samples, the interval, and connection parameters to mysqld can be specified as command line parameters.

% mpdump > mpdump.txt

Merging the queries and generating the report

Use mpfilter command to rewrite the variables in the sampled queries, then feed the result into mpreport command to sort the queries from the heaviest one to the lighter.  Each line of the report is in the format "probability:SQL query," where probability is the average percentage of if the SQL query is executed.  The example below is taken from the mysqld of Pathtraq.  It is obvious that which queries are consuming the resources (and that the heaviest query is running at the probability of 138.3%: it is run by 1.383 threads by average).

% mpfilter < mpdump.txt | mpreport | head -100
138.300:select pt_sum(hits,?) as cnt from url inner join counter on url.id=counter.url where (pt_url_compress(?)<=rurlc and rurlc<pt_url_compress(?,?))
99.100:insert into tt select t1.url,cnt_mixed&? as cnt,********** where url.status_code=?
94.200:select id,pt_url_decompress(rurlc) as rurl from url where crawl_cnt<? and crawl_at>=? and crawl_at<?-?*pow(?,crawl_cnt) limit ?
22.000:select id,status_code from url where rurlc=pt_url_compress(?)
14.300:select id,pt_url_decompress(rurlc) as rurl,cnt from (select url,cnt from counter_by_day **********
9.300:insert into counter (url,referer,hits) values (?,?,pt_cnt_add(null,?,?)) on duplicate key update hits=pt_cnt_add(hits,?,?)
7.400:insert ignore into **********
6.100:select pt_sum(hits,?) as cnt from counter where url=?
5.900:select (crawl_at<? and -crawl_at<? and pt_sum(hits,floor(-crawl_at/?))>?) as recrawl from url inner join counter on url.id=counter.url where id=?
5.700:insert into **********
5.700:select t1.id,pt_url_decompress(rurlc) as rurl,cnt,score from **********
4.600:select id,name,dispname from category_ja order by disporder,id
4.300:select addr from **********
3.200:update url set crawl_at=?,lang=?,status_code=if(status_code=?,?,?) where i
d=?
(snip)

By using the report, we can decide from which query to start optimizing, by using explain, or by letting their responses be cached by memcached, etc.  It is also possible to replace mpfilter with a house-made script to customize the report.

Conclusion

There are other ways to find out bottlneck queries, like using the slow query log or MySQL Enterprise Monitor.  Comparing with them, the merits of using the described solution using "SHOW PROCESSLIST" are

  • no need to change current environment
  • practically no overhead
  • potential to build a customized performance tuning / monitoring tool
As an example of using "SHOW PROCESSLIST" for other purposes than performance tuning, it would be possible to keep a daily sample log of SQL queries using mprofile, and use them to find out the long-term changes of the bottlenecks to build server reinforcement plans or for refactoring the design of the application.

Finally, the source code of mprofile is available from /platform/mysql/mprofile – CodeRepos::Share – Trac.  Have fun!

MySQL のボトルネックを統計的に監視・解析する方法

 MySQL のチューニング、と言った場合には、サーバーパラメータの調整や EXPLAIN コマンドを利用したクエリ実行計画の最適化が話題に上ることが多いです。しかし、発行する全ての SQL について、いちいち EXPLAIN コマンドを使って確認していては、いくら時間があってもたりません。チューニングを効率的に進めるには、まず、ボトルネックとなっている SQL クエリを特定し、次にその最適化を行うべきです。

 ではどのようにして、ボトルネックを特定するのか。MySQL Conference & Expo 2009 のキーノートにおいて Mark Callaghan 氏は、Google では SHOW PROCESSLIST コマンドを使った統計的アプローチを使っていると述べていらっしゃいます (参照: MySQLConf 09: Mark Callaghan, "This is Not a Web App: The Evolution of a MySQL Deployment at Google")。また、SHOW PROCESSLIST を使った統計的アプローチは商用製品である MySQL Query Analizer のウェブページでも競合手法として比較対象になっています。今回、ちょっと必要になったので、同様のことを行うスクリプト「mprofile」を書いてみました。こんな感じで使います。

SQL クエリをダンプ

 mpdump コマンドを利用して、定期的に実行中の SQL クエリ一覧を MySQL から取得します。デフォルトでは 0.1 秒間隔で 1,000 回サンプリングを行います (つまり、実行に100秒強かかります)。実行間隔やサンプリング回数、MySQL サーバ接続時のパラメータは引数で指定できるとで、詳しくは --help を参照ください。

% mpdump > mpdump.txt

SQL クエリをマージして統計表示

 mpfilter コマンドを使ってクエリの変数を「?」に書き換え、mpreport コマンドでクエリを負荷順にソートして表示します。mpreport は、「実行確率:SQLクエリ」の一覧を実行確率の降順で表示します。「クエリの実行確率」とは、そのクエリが実行されていた確率をパーセントで表示したものです。同一クエリが複数スレッドから実行されていれば、値は 100% を超えることもあります。以下の例は、パストラックの MySQL サーバの負荷を解析してみた結果です。どのようなクエリが一番負荷を与えているか (また、一番重たいクエリが、138.3% の確率=平均 1.383 スレッドによって実行されていること) が一目瞭然です。あとは、この一覧の順に、最適化が可能か、あるいは memcached でキャッシュすべきか、といったことを検討していくことになります。

% mpfilter < mpdump.txt | mpreport | head -100
138.300:select pt_sum(hits,?) as cnt from url inner join counter on url.id=counter.url where (pt_url_compress(?)<=rurlc and rurlc<pt_url_compress(?,?))
99.100:insert into tt select t1.url,cnt_mixed&? as cnt,********** where url.status_code=?
94.200:select id,pt_url_decompress(rurlc) as rurl from url where crawl_cnt<? and crawl_at>=? and crawl_at<?-?*pow(?,crawl_cnt) limit ?
22.000:select id,status_code from url where rurlc=pt_url_compress(?)
14.300:select id,pt_url_decompress(rurlc) as rurl,cnt from (select url,cnt from counter_by_day **********
9.300:insert into counter (url,referer,hits) values (?,?,pt_cnt_add(null,?,?)) on duplicate key update hits=pt_cnt_add(hits,?,?)
7.400:insert ignore into **********
6.100:select pt_sum(hits,?) as cnt from counter where url=?
5.900:select (crawl_at<? and -crawl_at<? and pt_sum(hits,floor(-crawl_at/?))>?) as recrawl from url inner join counter on url.id=counter.url where id=?
5.700:insert into **********
5.700:select t1.id,pt_url_decompress(rurlc) as rurl,cnt,score from **********
4.600:select id,name,dispname from category_ja order by disporder,id
4.300:select addr from **********
3.200:update url set crawl_at=?,lang=?,status_code=if(status_code=?,?,?) where i
d=?
(以下略)

 また、mpfilter を独自のフィルタに差し替えることで、特定のパラメータをもつ SQL クエリを別のグループとして統計に反映する、といったことも簡単に行えるようになっています。

まとめ

 ボトルネックとなっている SQL を解析する手段としては、他にスロークエリログを使う手法 (参考: MySQL 5.1のmysqldumpslowで快速チューニング - SH2の日記) や、MySQL Enterprise Monitor を使うという手もあります。これらと比較して、今回紹介した SHOW PROCESSLIST を使うメリットは、

  • 既存環境の変更が不要
  • サンプリングによるので負荷が軽い
  • スクリプトを組み合わせて、柔軟なチューニングや監視の仕組みを作ることができる

といった点になるかと思います。ボトルネックを解析する以外にも、たとえば apache に付属する rotatelogs と組み合わせて、mpdump 結果が日ごとに異なるファイルに保存されるようにしておけば、SQL クエリの負荷が長期的にどのように変化してきているかを把握して、サーバの増強計画に反映させたりすることもできるでしょう。

 最後になりましたが、今回書いた mprofile のソースコードは /platform/mysql/mprofile – CodeRepos::Share – Trac に置いてあります。それでは、have fun!

July 14, 2009

Intruducing Incline - a synchronization tool for RDB shards

For the last weeks, I have been writing a tool called "Incline," a program that automatically maintains consistency between sharded MySQL databases.  The aim of the software is to free application developers from hand-writing code for keeping consistency between RDB nodes, so that they can concentrate on writing the application logic.

Background

Denormalization is unavoidable in a sharded RDB environment.  For example, when a message is sent from a user to another user, the information should be stored on a database node where the sender of the message belongs to, and on another node where the receiver does.  In most cases, denormalization logic is hand-written by web application developers, and since it has been a burden for creating large-scale web services.  Incline takes off the load from developers.  By reading the definition files, Incline keeps the tables on a sharded MySQL environment in sync, by providing a trigger-generator and a replication program that, synchronously or asynchronously reflects the changes of source tables into materialized-view-like tables.

Installing Incline

Incline is written in C++ and uses autotools for building the tools.  However, since I have not yet added automatic lookup for libmysqlclient, you need to specify their location manually to build Incline.  My build procedure is as follows.

% svn co http://kazuho.31tools.com/svn/incline/trunk incline
% cd incline
% autoreconf -i
% ./configure CFLAGS=-I/usr/local/mysql/include/mysql CXXFLAGS=-I/usr/local/mysql/include/mysql LDFLAGS='-L/usr/local/mysql/lib/mysql -lmysqlclient'
% make
% make install

Defining Replication Rules

Replication rules of Incline are written using JSON files.  Consider creating a twitter-like microblog on a shared environment.  It would be consisted of four tables, each of them distributed through RDB shards by the user_id column.  The "tweet" table and "following" table are updated by user actions, while "followed_by" table and "timeline" table are denormalized tables that need to be kept synchronized to the former two.

CREATE TABLE tweet (
  tweet_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  body VARCHAR(255) NOT NULL,
  ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (tweet_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE following (
  user_id INT UNSIGNED NOT NULL,
  following_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id,following_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE followed_by (
  user_id INT UNSIGNED NOT NULL,
  follower_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id,follower_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE timeline (
  user_id INT UNSIGNED NOT NULL,
  tweet_user_id INT UNSIGNED NOT NULL,
  tweet_id INT UNSIGNED NOT NULL,
  ctime TIMESTAMP NOT NULL,
  PRIMARY KEY (user_id,tweet_user_id,tweet_id),
  KEY user_ctime_tweet_user_tweet (user_id,ctime,tweet_user_id,tweet_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Incline uses two JSON files to define a replication rule.  The first file, microblog.json defines the mapping of columns using directives "pk_columns" (primary key columns) and "npk_columns" (non-pk_columns) between the "source" table(s) and "destination" of the replication.  When merging more than two tables to a single destination, "merge_cond" attribute is used to define the inner join condition(s).

microblog.json
[
  {
    "source"      : [ "tweet", "followed_by" ],
    "destination" : "timeline",
    "pk_columns"  : {
      "followed_by.follower_id" : "user_id",
      "tweet.user_id"           : "tweet_user_id",
      "tweet.tweet_id"          : "tweet_id"
    },
    "npk_columns" : {
      "tweet.ctime" : "ctime"
    },
    "merge"       : {
      "tweet.user_id" : "followed_by.user_id"
    },
    "shard-key"   : "user_id"
  },
  {
    "source"      : "following",
    "destination" : "followed_by",
    "pk_columns"  : {
       "following.following_id" : "user_id",
       "following.user_id"      : "follower_id"
    },
    "shard-key"   : "user_id"
  }
]

The second file shard.json defines the mapping between user_id and RDB nodes.  Range-based sharding (on an integer column) is specified in the example.  Other algorithm currently supported are: range-str-case-sensitive and hash-int.

shard.json
{
  "algorithm" : "range-int",
  "map"       : {
    "0"    : "10.0.1.1:3306",
    "1000" : "10.0.1.2:3306",
    "2000" : "10.0.1.3:3306",
    "3000" : "10.0.1.4:3306"
  }
}

With the definition, tables will be synchorized in the direction described in the figure below (only illustrates two nodes).

Running Incline

To run Incline, queue tables should be created and database triggers need to be installed on each RDB node.  This can be done by calling "incline create-queue" and "incline create-trigger" commands for each node, and the setup is complete.

% incline --mode=shard --source=microblog.json --shard-source=shard.json --database=microblog --mysql-host=10.0.1.1 create-queue
% incline --mode=shard --source=microblog.json --shard-source=shard.json --database=microblog --mysql-host=10.0.1.1 create-trigger
% incline --mode=shard --source=microblog.json --shard-source=shard.json --database=microblog --mysql-host=10.0.1.2 create-queue
% incline --mode=shard --source=microblog.json --shard-source=shard.json --database=microblog --mysql-host=10.0.1.2 create-trigger
...

The installed triggers take care of synchronizing the denormalized tables within each node.  The next step is to run the forwarder (replicator between RDB nodes) for each node, so that the views should be kept in sync.  This can be done by calling "incline forward."The forwarding logic is defined in a way that the data stored in RDB nodes would become eventually synchronized even if either of the RDB nodes or the forwarder unexpectedly dies.  Use of daemontools or alike to automatically (re)start the forwarder is desirable.

% incline --mode=shard --source=microblog.json --shard-source=shard.json --database=microblog --mysql-host=10.0.1.1 forward &
% incline --mode=shard --source=microblog.json --shard-source=shard.json --database=microblog --mysql-host=10.0.1.2 forward &
...

And this is it.  The two tables: "followed_by" and "timeline" are updated automatically when the "tweet" or "following" table is modified.  Application developers do not need to take care of shard consistency any more, all that needs to be remembered is that changes should be written into the latter two (modification to the former two can be disabled by using different access privileges between the web application and the Incline forwarder).

Next Steps

Having started writing code late last month, Incline is still immature.  Current status is somewhere around early beta.  In the next months, I plan to polish up and optimize the code, add PostgreSQL support (currently Incline works only on MySQL), as well as extending it so that it could used together with Pacific, a framework I have been working, that provides dynamic node addition / removal of nodes on a sharded RDB environment.  Thank you for reading, please stay tuned.

July 13, 2009

パストラック (Pathtraq) のおしらせ:総選挙特集ページ開設と IE 版プラグインのバージョンアップについて

パストラック (Pathtraq) のお知らせです。

・総選挙特集ページの開設について

 明日2009年7月14日より、パストラック (Pathtraq) 上に、次期衆議院選挙の特設ページ、「総選挙特集 2009」を開設します

 総選挙特集ページは、従来からあるパストラックのリアルタイム検索機能をカスタマイズし、衆議院各会派名と「選挙」をキーワードした検索結果を一覧表示することで、総選挙に関連して、注目を集めている (アクセスが伸びている) ウェブページを、ほぼリアルタイムに可視化するサービスです。現在は、ポータルやメディア各社が開設したばかりの特集ページが上位に来ていますが、今後は、各社の新着記事や新しいブログエントリ等、新たに注目を集めたページが上位に食い込んでくると思われます。パストラックの統計に偏りがあるのは確かですが、こうした可視化によって、選挙がより身近な問題として感じられるようになればいいな、と思っています。総選挙特集について、詳しくは「サイボウズ・ラボ : パストラック (Pathtraq)、次期衆議院議員選挙に関する特設ページを公開」をご覧ください。

画面写真

・IE 版ツールバーのバージョンアップについて

 また、総選挙特集ページの開設と同時に、Internet Explorer 用ツールバーをバージョン 0.8 にバージョンアップしました。今回のバージョンアップにより、Internet Explorer 8 に対応 (User-Agent がおかしくなる問題を修正等) したほか、パストラックのログ送信機能のオンオフを全てのウィンドウで共有するように仕様を変更いたしました。Internet Explorer でパストラックのツールバーをご使用の方々には、バージョンアップをしていただければと思います。

July 06, 2009

PicoJSON, a header-file-only JSON parser in C++

Last week I had been looking for a tiny JSON parser written in C++, but was not able find one that met my needs (jsonxx was the most interesting, I took its is<type> and get<type> interfaces).  So instead I wrote my own, and that is PicoJSON.  The advantages of PicoJSON over other C++ JSON parsers are as follows.

  • C++ header-only implementation (just include the header file from your source code and that's it)
  • standalone (does not depend on other libraries like boost)
  • integration with STL (JSON array represented as std::vector, JSON object as std::map)
  • operator<<, operator>> and iterator based parser / serializer interface
  • provided under the New BSD License

The header file is available from coderepos (link).  Please read the examples included to find out how to use the library.

Related Articles:

July 02, 2009

今更 C++ で JSON パーサ「picojson」を書いたわけ

 既に mattn さんが、「Big Sky :: ヘッダファイルだけでC++から使えるJSONパーサ「picojson」が凄い!」で紹介してくださっています (mattn さん、アドバイス&バグ情報ありがとうございます!) が、いまさら C++ で JSON パーサを作りました。それは、以下の3点を満たすものがなかったから。

  • ヘッダファイル only
  • boost 等、他の重たいライブラリに依存しない
  • array や object が STL にマッピングされる

 コードは、coderepos に置いてありますので、よろしければお使いください (picojson.h)。

 なお、現時点での制限事項として、

  • \n や \r, \uXXXX といったエスケープの処理が未実装rev. 34232 で対応しました (含サロゲートペア)
  • 空白文字の判断基準が RFC と異なるrev. 34277 で空白と文字列定数内で許容する文字を RFC4627 に一致させました

といった点がありますので、解釈のゆれによってセキュリティ上問題が発生しうるケースでのご使用は注意していただければと思います。

 あとは、operator<< と >> を定義して、iostream から読み書きできるようにするくらいかなrev. 32477 の時点で operator<< と >> に対応済です。それでは have fun!