VOYAGE GROUP エンジニアブログ

voyagegroup_techのブログ
VOYAGE GROUPエンジニアブログです。

DB

Redshiftの操作権限の設定とその方法

こんにちは。システム本部の@yuu_itoです。
主に弊社のメディアサービスのデータ分析の業務を行っています。

昨今、世間ではセキュリティに関する問題が増加しています!

情報セキュリティにおいて最小権限の原則という用語もあったりしますが、
システムの利用者に本当に必要な権限のみを付与することで
誤った操作を予防したり、外部からの侵入が起きてしまった場合にも
損害を最小限にできるように設計することが大事です。

前回の記事ではBigQueryについて書かれていましたが、
同様のDWH(データウェアハウス)機能を提供するサービスとしてAmazon Redshiftがあります。
今回はRedshiftのデータベースアクセスコントロールの設定方法について調べてみました。

■ データベース
データベースに対する権限設定コマンドは
PostgreSQLや、MySQL同様GRANTREVOKEコマンドが使えます。 ALLは全権限、CREATEはスキーマ作成、TEMPORARYは一時表を作成する権限です。
テーブルへの SELECT や UPDATE の権限についてはスキーマ単位、テーブル単位で設定する必要があります。

■ スキーマ
MySQLではスキーマとデータベースは同義である(*1)と思いますが、Redshift、PostgreSQLではデータベースの中にスキーマがあり、スキーマの中にテーブルを配置するイメージです。 Redshiftのオブジェクト構成イメージ

スキーマの操作もGRANTコマンドを使います。 PUBLICスキーマについて
Redshiftは各データベースにデフォルトでPUBLICいう名のスキーマが存在し、全てのユーザ(全ユーザはPUBLICグループに所属している)にCREATE権限がついています。書込み権限が不要なユーザも存在すると思うので、REVOKEでCREATE権限を除いておくと良さそうです。
http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_Schemas_and_tables.html

■ テーブル、カラム
テーブルについてもGRANTで個別に設定できます。しかしDBやスキーマのレベルでのみ管理するルールにしておけば、テーブル単位での管理をする必要はなくなるのでDB管理者としては気にすることが減って良さそうです。

カラム単位での操作制限はサポートされていなかったです。
一部のカラム隠蔽したいのであれば、ビュー(View)を作成したり、COPYコマンドでカラムを絞ってロードすることもできます。
また、CREATE TABLE AS(通称CTAS:しーたす)だったりINSERT INTO SELECTを使ってテーブルコピーを作る方法があります。

ビューの場合、テーブルコピーよりもコストが低い反面データベースをまたいだ作成ができないためテーブル、ビュー単位での管理が必要になります。 逆にCTASであればデータベースをまたいで作成することができるので、DBやスキーマのみの管理で済みます。

例えば、個人情報を除いたテーブルを権限を分けた別のデータベースやスキーマに作成すると良さそうです。 スキーマ、テーブルの構成例 CTASのコマンド例

■ ユーザの権限確認方法
データベース、スキーマ、テーブル単位で権限をチェックする関数がありました。
コマンド一発!一覧で確認するようなコマンドがあると期待していたのですが見つからず...
http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_System_information_functions.html
HAS_XX_PRIVILEGEを使って確認できました。
試しに書いてみたクエリをgistに貼っておきます。
https://gist.github.com/yuu-ito/30733a9223e59e32bdb5#file-acl_check-md

■ まとめ
簡単でしたが、Redshiftの操作権限の設定とその方法について調べてみました。今回はDBユーザのセキュリティのみに焦点をあてましたが、実際にはネットワーク構成やデータの元となる運用サービスのデータベースサーバなどの関連システムについても一緒に整理する必要があります。AWSサービスでのシステム構成設計についてはいろんな記事でまとめられているのでそちらを探してみてください。

■ 参考
データベースセキュリティの管理 - Amazon Redshift
http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_Database_objects.html
*1:
http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_schema

■ お約束
VOYAGE GROUPでは、一緒に働いてくださる仲間を募集しております。
株式会社VOYAGE GROUP アドテクユニット キャリア採用サイト
株式会社VOYAGE GROUP - 採用
どんな会社か気になる方は、是非下記のサイトもご覧ください。
VOYAGE CULTURE

SQLアンチパターンのススメ

こんにちはシステム本部 三浦@hironomiuです。

VOYAGE GROUPでは様々な社内勉強会を開催しているのですが今年の1月からoreillyから出版されているSQLアンチパターン勉強会を三浦@hironomiuが開催していました。

本の内容は名前の通りSQL(RDBMSまで広げた)を扱う際の「べからず」集です。なかなかの良書なのでまだお読みでない方は一読をおススメします!

勉強会の進め方ですが
  • 週1時間
  • 1回あたり2章~3章のペースで進める
  • ベテランと若手を同じぐらいの人数(合計で最大8人)にて進める
  • 輪読ではなく三浦@hironomiuがメンターとし進め、適時参加者が経験談を語る形で進める
のような形で進めました。本来ですと1章1週でも良い内容なのですが全25章、25週はモチベーションを維持することに不安があったのでこのように進めました。

全25章となかなかのボリュームでしたが勉強会は第2期まで開催し、どちらも無事最終章まで11週間で終えることができました。(77日!!改めて見ると長いですね!)

開催中、章によってはベテランから「あるある!」と自身の経験などの話なども起こり若手にも有意義な知見が伝わったと思っています。
章ごとに様々な知見が得られるだけでなく例えば1章ではジェイウォークの命名について解決策である連関エンティティを交差テーブル(交差点)に見立てて名付けているあたりに深みを感じられたりします。

11週続ける中では開催中、監訳者のお一人、和田卓人@t-wadaさんにもお越し頂きアドバイスを頂ける週もあり全体を通して大変有意義な勉強会になったと思っています。

全11週にて各週で勉強会の際に用意した資料は下記となります。


今後も第3期、第4期と開催し内容、密度、資料もブラッシュアップしていきたいと思います!!

MongoDBのGUIツールを比較してみた

みなさんこんにちは。
(株)adingoでprivate DMPと呼ばれるcosmi Relationship Suiteの開発をしています。
@tana_raことあらたです。

世の中にはアルファベット3文字が溢れていて混乱しますよね。AWSと広告業界が混ざるとカオスです。
AWSとかEMRとかSSPとか。DMPもその一つですね。
ちなみにDMPはData Management Platformの略です。

ところでcosmiでは、データの管理にMySQLとMongoDBを利用しています。
私は開発時にはMySQLのGUIツールをよく利用するのですが、
MongoDBのGUIツールってあまり聞かないなと思ったので探してみました。
今日は触ってみたMongoDBのGUIツールを紹介してみたいと思います。
基本的にWEBベースの物を利用しています。

触ってみたGUIツールたち
  • phpMoAdmin 1.1.2
  • RockMongo 1.1.7
  • mongo-express 0.18.0 (※) 
  • Genghis 2.3.11
※mongo-expressは最新版では、依存ライブラリのバージョンによって動かなかったため、
現在masterブランチに上がっているものを利用しています。

phpMoAdmin

まずはじめにphpMoAdminです。
名前から分かる通りPHP製です。
phpMyAdminとかphpMyFaqとか似た名前のものがたくさんありますね。
1ファイルで出来ているので、apacheのDocument Rootに配置するか、
PHPのビルトインサーバで利用することが出来ますね。

インストール
$ wget http://www.phpmoadmin.com/file/phpmoadmin.zip
$ unzip phpmoadmin.zip
$ mv moadmin.php /var/www/html/moadmin.php
でインストール完了です。
以下がその画面です。

phpMoAdmin1


何にもせず接続できました。
デフォルト設定で接続ができますが、Configファイルなどはないので
portを変えたり、認証をつけたりすると使えなくなってしまいます。

(無理やりコードを変更することで色々できますが・・・w)
基本的なデータの確認は可能ですが、ユーザ認証とかは無いみたいですね。

RockMongo

次にRockMongoです。これもPHP製。
したがって、phpMoAdminと同様の方法で利用することが出来ます。


インストール
$ wget "http://rockmongo.com/downloads/go?id=14” -O rockmongo.zip
$ unzip rockmongo.zip
$ mv rockmongo-1.1.7 /var/www/html/rockmongo
これで完了です。
アクセスすると以下のような画面が現れます。
RockMongo
ログイン認証もできますが、以下のように
/var/www/html/rockmongo/config.php
の31行目を編集すれば認証を無くすことも可能です。
31 //$MONGO["servers"][$i]["control_auth"] = true;//enable control users, works only if mongo_auth=false
32 $MONGO["servers"][$i]["control_auth"] = false;
するとこんな画面が現れます。

RockMongo2

RockMongoは結構機能が多そうですね。
データの確認も見やすくていいです。

mongo-express
mongo-expressはJavaScript(node.js)製のGUIツールです。
CSSはTwitter Bootstrapを利用しているようで綺麗です。

インストール
$ npm install https://github.com/andzdroid/mongo-express/tarball/master
・・・
$ cd node_modules/mongo-express
$ cp config.default.js config.js
$ vim config.js
デフォルトだとconfig.jsが無いため怒られます。
なのでconfig.default.jsをコピーして使いましょう。
今MongoDBで認証は行っていないのでconfig.jsで認証が必要ないように変更します。
変更しないとConnectionErrorとコンソールに出てきます。

 35 //    adminUsername: 'admin',
 36 //    adminPassword: 'pass',
 37     adminUsername: '',
 38     adminPassword: '',

$ node app
Mongo Express server listening on port 8081
Database connected!
Admin Database connected
以下がアクセスした時の画面です。
mongo-express-find
右のバツボタンを押したらこのレコード消してくれそうだなーと思って押したら
確認なしで消してくれましたw
とてもカジュアルですね・・・(´・ω・`)w

Genghis

最後にGenghisです。
Genghisの読みがわからなくてぐぐったらチンギスハンのチンギスらしいです。
gem installでもできるらしいのですが、
PHPerな私はPHPのビルトインサーバを利用しました。
(色々躓いたわけでは決してないです。)
こちらもTwitter Bootstrapを利用しているのでどことなくモダンな感じです。

インストール

$ wget https://github.com/bobthecow/genghis/archive/v2.3.11.zip -O genghis.zip
$ unzip genghis.zip
$ mv genghis-2.3.11 genghis
$ cd genghis
$ php -S localhost:8080 genghis.php
として、アクセスしたら下のような画面にたどり着きました。

genghis-top


localhostを選択すればlocalhostのサーバにつながります。
その他にもAdd serverとやれば、他サーバのMongoDBにもつなげることが出来そうです。
localhostにつなげてみた画面が以下のようになります。

genghis

Twitter Bootstrapのおかげかとても見やすいですが、
findとinsertくらいの機能しかありませんでした。

まあ見やすければ・・・いいですかねw

全体的な比較
phpMoAdmin RockMongo mongo-express Genghis
Version 1.1.2 1.1.7 0.18.0 2.3.11
言語 PHP PHP JavaScript(node.js) PHP or Ruby
License GNU GPL BSD MIT MIT
Github phpMoAdmin RockMongo mongo-express Genghis
出来ること
データ
コレクション
データベース管理
認証 × △(configファイル利用) ×
他サーバアクセス × × △(configファイル利用)


という感じでしょうか。
データの確認レベルであればphpMoAdmin以外はどれも良さそうだなーと思いました。
MongoDBにはAggregationMap-Reduce機能もありますが、
それら用のなにか特別な機能はなさそうでした。
考えてみたら、結局難しい処理はシェルで打ち込んでしまうと思ってしまい、
個人的にはGUIツールに要求する機能はデータの確認くらいという結論に至りました。

そうすると、データの確認がカジュアルにできる、
mongo-expressはかなりいいなと思います。
(開発環境とはいえ誤って複雑なレコードを消したら泣きそうですがw)

GUIツールは個人に依る部分があると思うので皆さんも試してみるといいかもしれません。
以下に検証に利用したレポジトリを用意したので、
是非みなさん、MongoDBのGUIツールと戯れてみてください。
VagrantとVirtualboxを利用してMongoDBのGUIツールを検証出来るレポジトリです。

https://github.com/tarata/mongo-gui-try


やっぱりioDriveは優秀だった! MySQL編

こんにちは、DBAの三浦(@hironomiu)です。

ひょんなことからサービス利用前のioDrive(80G)で検証する機会を取れました。
(ioDrive自体は販売初期の頃からサービスで使っていたりします。)

fusion-powered-io
スペック情報等は下記のfusionioのサイトを参照頂ければと思います。
http://www.fusionio.com/platforms/iodrive/
 
今回は前々からMySQLのDiskとしてどのくらいのパフォーマンスが出るのか興味があったので軽く動かしてみました。
サービス利用前とあるように時間が相当限られていたのでioDrive
(80G)に最適なOS、FileSystemの選定
カーネル、my.cnf等の細かいチューニングは行っておらず、情報の採取についても代表的なものしか行っていません。

比較の対象としてioDrive
(80G)と同モデルのIAサーバでDiskをHDD(4玉 RAID1+0)のものと三浦の検証用のPCに挿したSSD(X25-E Extreme 64G)を用いてみました。
こちらも上記の通り、同一条件ではありませんので、改めて厳密な性能検証では無くioDriveスゲー的な記事である点をご了承下さい。

システムの主な設定
 ・OSは全てCentOS5.5(64bit)
 ・FileSystemはioDrive
(80G)SSD(X25-E Extreme 64G)はext3、HDD(4玉RAID1+0)はxfs
 ・ext3にはfstabで
noatime,data=writebackを設定
 ・I/Oスケジューラ(/sys/block/~/queue/schedulerはnoopで統一
 ・I/Oキュー(/sys/block/~/queue/nr_requests)は1000で統一

MySQLの主な設定(全て統一)
 ・バージョンは5.5.8
 ・レプリケーションは行わない(スタンドアロン)
 ・バイナリログの取得は行わない
 ・
innodb_buffer_pool_size=128M
 ・
skip_innodb_doublewrite
 ・
query_cache_size=0
 ・innodb_read_io_threads= 4
 ・innodb_write_io_threads= 4

読込み性能
まずは読込み性能 hdparmを各10回取得しました。
ioDrive(80G)
[1] Timing buffered disk reads: 2034 MB in 3.00 seconds = 677.49 MB/sec
[2] Timing buffered disk reads: 2034 MB in 3.00 seconds = 677.47 MB/sec
[3] Timing buffered disk reads: 2034 MB in 3.00 seconds = 677.53 MB/sec
[4] Timing buffered disk reads: 2034 MB in 3.00 seconds = 677.85 MB/sec
[5] Timing buffered disk reads: 2034 MB in 3.00 seconds = 677.90 MB/sec
[6] Timing buffered disk reads: 2034 MB in 3.00 seconds = 677.67 MB/sec
[7] Timing buffered disk reads: 1946 MB in 3.00 seconds = 648.60 MB/sec
[8] Timing buffered disk reads: 2036 MB in 3.00 seconds = 678.16 MB/sec
[9] Timing buffered disk reads: 2034 MB in 3.00 seconds = 677.72 MB/sec
[10] Timing buffered disk reads: 2034 MB in 3.00 seconds = 677.53 MB/sec
SSD(X25-E Extreme 64G
[1] Timing buffered disk reads: 100 MB in 0.51 seconds = 197.56 MB/sec
[2] Timing buffered disk reads: 100 MB in 0.50 seconds = 201.26 MB/sec
[3] Timing buffered disk reads: 100 MB in 0.50 seconds = 199.96 MB/sec
[4] Timing buffered disk reads: 100 MB in 0.50 seconds = 201.40 MB/sec
[5] Timing buffered disk reads: 100 MB in 0.50 seconds = 201.32 MB/sec
[6] Timing buffered disk reads: 100 MB in 0.50 seconds = 199.57 MB/sec
[7] Timing buffered disk reads: 100 MB in 0.50 seconds = 201.51 MB/sec
[8] Timing buffered disk reads: 100 MB in 0.50 seconds = 199.67 MB/sec
[9] Timing buffered disk reads: 100 MB in 0.50 seconds = 201.43 MB/sec
[10]Timing buffered disk reads: 100 MB in 0.50 seconds = 200.47 MB/sec
HDD(4玉RAID1+0)
[1] Timing buffered disk reads: 926 MB in 3.00 seconds = 308.63 MB/sec
[2] Timing buffered disk reads: 890 MB in 3.00 seconds = 296.49 MB/sec
[3] Timing buffered disk reads: 908 MB in 3.01 seconds = 301.95 MB/sec
[4] Timing buffered disk reads: 906 MB in 3.00 seconds = 301.70 MB/sec
[5] Timing buffered disk reads: 918 MB in 3.01 seconds = 305.21 MB/sec
[6] Timing buffered disk reads: 896 MB in 3.00 seconds = 298.20 MB/sec
[7] Timing buffered disk reads: 910 MB in 3.00 seconds = 302.98 MB/sec
[8] Timing buffered disk reads: 904 MB in 3.00 seconds = 300.97 MB/sec
[9] Timing buffered disk reads: 924 MB in 3.01 seconds = 307.06 MB/sec
[10] Timing buffered disk reads: 904 MB in 3.01 seconds = 300.75 MB/sec
平均(MB/sec)
ioDrive(80G)SSD(X25-E Extreme 64GHDD(4玉RAID1+0)
674.792 200.415 302.394

もうちょっとioDriveが行くかなと思いましたが、それでも十分早いですね。

書込み性能
次に書込み性能 ddを1Gbyteで各10回取得しました。
ioDrive(80G)
[1] 1073741824 bytes (1.1 GB) copied, 1.16875 seconds, 919 MB/s
[2] 1073741824 bytes (1.1 GB) copied, 1.19023 seconds, 902 MB/s
[3] 1073741824 bytes (1.1 GB) copied, 1.19167 seconds, 901 MB/s
[4] 1073741824 bytes (1.1 GB) copied, 1.49715 seconds, 717 MB/s
[5] 1073741824 bytes (1.1 GB) copied, 1.45808 seconds, 736 MB/s
[6] 1073741824 bytes (1.1 GB) copied, 1.4612 seconds, 735 MB/s
[7] 1073741824 bytes (1.1 GB) copied, 1.42681 seconds, 753 MB/s
[8] 1073741824 bytes (1.1 GB) copied, 1.46143 seconds, 735 MB/s
[9] 1073741824 bytes (1.1 GB) copied, 1.46766 seconds, 732 MB/s
[10] 1073741824 bytes (1.1 GB) copied, 1.46032 seconds, 735 MB/s
SSD(X25-E Extreme 64G
[1] 1073741824 bytes (1.1 GB) copied, 0.89152 seconds, 1.2 GB/s
[2] 1073741824 bytes (1.1 GB) copied, 1.64124 seconds, 654 MB/s
[3] 1073741824 bytes (1.1 GB) copied, 1.31282 seconds, 818 MB/s
[4] 1073741824 bytes (1.1 GB) copied, 1.39209 seconds, 771 MB/s
[5] 1073741824 bytes (1.1 GB) copied, 1.483 seconds, 724 MB/s
[6] 1073741824 bytes (1.1 GB) copied, 1.74863 seconds, 614 MB/s
[7] 1073741824 bytes (1.1 GB) copied, 2.06978 seconds, 519 MB/s
[8] 1073741824 bytes (1.1 GB) copied, 1.90353 seconds, 564 MB/s
[9] 1073741824 bytes (1.1 GB) copied, 1.41756 seconds, 757 MB/s
[10] 1073741824 bytes (1.1 GB) copied, 2.16436 seconds, 496 MB/s
HDD(4玉RAID1+0)
[1] 1073741824 bytes (1.1 GB) copied, 1.29015 seconds, 832 MB/s
[2] 1073741824 bytes (1.1 GB) copied, 1.31637 seconds, 816 MB/s
[3] 1073741824 bytes (1.1 GB) copied, 1.27612 seconds, 841 MB/s
[4] 1073741824 bytes (1.1 GB) copied, 1.42499 seconds, 754 MB/s
[5] 1073741824 bytes (1.1 GB) copied, 1.84404 seconds, 582 MB/s
[6] 1073741824 bytes (1.1 GB) copied, 1.92831 seconds, 557 MB/s
[7] 1073741824 bytes (1.1 GB) copied, 1.36545 seconds, 786 MB/s
[8] 1073741824 bytes (1.1 GB) copied, 1.28768 seconds, 834 MB/s
[9] 1073741824 bytes (1.1 GB) copied, 1.33781 seconds, 803 MB/s
[10] 1073741824 bytes (1.1 GB) copied, 1.29652 seconds, 828 MB/s
平均(MB/s)
ioDrive(80G)SSD(X25-E Extreme 64GHDD(4玉RAID1+0)
786.5 711.7 763.3

シーケンシャルな書込みでは差が見受けられませんでした。

ランダムI/0(bonnie++)についてはパスしました。

次にmysqlをインストールし、「JdbcRunner - 汎用データベース負荷テストツール」を使用させて頂いて性能を取得しました。
結果の見方については下記の製作者:Sadao Hiratsukaさんのサイトを参照頂ければと思います。
http://hp.vector.co.jp/authors/VA052413/jdbcrunner/

テストシナリオはtpccで下記設定で各4回の結果を採用しました。
Warmup time          : 10 sec
Measurement time : 60 sec
Number of tx types : 5
Number of agents : 200
Connection pool size : 200
Statement cache size : 40
Auto commit : false
Sleep time : 0,0,0,0,0 msec

今回結果はTotal tx countを抜粋しました。
ioDrive(80G)
[Total tx count] 3495,3614,358,331,328 tx
[Total tx count] 3594,3577,375,368,354 tx
[Total tx count] 3717,3665,401,393,358 tx
[Total tx count] 3509,3459,361,369,355 tx
SSD(X25-E Extreme 64G
[Total tx count] 1524,1472,137,146,152 tx
[Total tx count] 1591,1558,182,163,144 tx
[Total tx count] 1619,1461,147,175,133 tx
[Total tx count] 1532,1357,122,172,158 tx
HDD(4玉RAID1+0)
[Total tx count] 2889,2772,276,282,282 tx
[Total tx count] 2785,2822,283,284,282 tx
[Total tx count] 2866,2818,266,274,280 tx
[Total tx count] 2735,2700,283,300,249 tx

こう見るとHDD(4玉RAID1+0)も結構がんばってるな~と見えるんですが、下記はjdbcrunnerで検証中のmpstatの抜粋です。(文字数の都合でSSD(X25-E Extreme 64Gは省略しました。)

ioDrive(80G)
18時34分42秒 CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
18時34分43秒 all 13.23 0.00 2.66 3.99 0.25 1.00 0.00 78.87 19248.51
18時34分43秒 0 7.92 0.00 0.99 2.97 0.00 0.00 0.00 88.12 992.08
18時34分43秒 1 19.80 0.00 2.97 4.95 0.00 0.99 0.00 71.29 740.59
18時34分43秒 2 14.00 0.00 2.00 5.00 0.00 1.00 0.00 78.00 704.95
18時34分43秒 3 17.82 0.00 1.98 5.94 0.00 1.98 0.00 72.28 786.14
18時34分43秒 4 17.00 0.00 7.00 3.00 2.00 3.00 0.00 68.00 11339.60
18時34分43秒 5 4.90 0.00 2.94 1.96 0.98 0.98 0.00 88.24 2635.64
18時34分43秒 6 4.00 0.00 0.00 1.00 0.00 0.00 0.00 95.00 0.00
18時34分43秒 7 16.16 0.00 2.02 4.04 0.00 1.01 0.00 76.77 626.73
18時34分43秒 8 10.89 0.00 2.97 3.96 0.00 0.99 0.00 81.19 350.50
18時34分43秒 9 15.15 0.00 1.01 4.04 0.00 0.00 0.00 79.80 0.99
18時34分43秒 10 28.71 0.00 4.95 8.91 0.00 1.98 0.00 55.45 1074.26
18時34分43秒 11 3.96 0.00 3.96 2.97 0.00 0.00 0.00 89.11 0.00

18時34分43秒 CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
18時34分44秒 all 12.24 0.00 3.08 4.08 0.25 0.83 0.00 79.52 19355.00
18時34分44秒 0 4.00 0.00 1.00 2.00 0.00 0.00 0.00 93.00 1001.00
18時34分44秒 1 18.81 0.00 2.97 5.94 0.00 0.00 0.00 72.28 675.00
18時34分44秒 2 12.87 0.00 2.97 4.95 0.00 0.99 0.00 78.22 578.00
18時34分44秒 3 14.14 0.00 2.02 4.04 0.00 0.00 0.00 79.80 610.00
18時34分44秒 4 13.86 0.00 7.92 2.97 1.98 3.96 0.00 69.31 11852.00
18時34分44秒 5 4.00 0.00 5.00 3.00 0.00 0.00 0.00 88.00 2403.00
18時34分44秒 6 0.99 0.00 0.99 1.98 0.00 0.00 0.00 96.04 0.00
18時34分44秒 7 17.82 0.00 1.98 5.94 0.99 0.99 0.00 72.28 537.00
18時34分44秒 8 17.00 0.00 2.00 6.00 0.00 1.00 0.00 74.00 580.00
18時34分44秒 9 14.71 0.00 2.94 2.94 0.00 0.98 0.00 78.43 409.00
18時34分44秒 10 25.25 0.00 5.05 10.10 0.00 1.01 0.00 58.59 706.00
18時34分44秒 11 3.03 0.00 2.02 2.02 0.00 0.00 0.00 92.93 0.00

18時34分44秒 CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
18時34分45秒 all 13.41 0.00 3.25 4.83 0.25 0.67 0.00 77.60 21473.00
18時34分45秒 0 2.00 0.00 0.00 4.00 0.00 0.00 0.00 94.00 1001.00
18時34分45秒 1 16.00 0.00 2.00 7.00 0.00 1.00 0.00 74.00 514.00
18時34分45秒 2 19.19 0.00 4.04 7.07 0.00 1.01 0.00 68.69 764.00
18時34分45秒 3 18.81 0.00 1.98 3.96 0.00 0.99 0.00 74.26 543.00
18時34分45秒 4 10.00 0.00 9.00 3.00 3.00 3.00 0.00 72.00 13967.00
18時34分45秒 5 3.00 0.00 5.00 1.00 0.00 0.00 0.00 91.00 2407.00
18時34分45秒 6 4.04 0.00 0.00 3.03 0.00 0.00 0.00 92.93 0.00
18時34分45秒 7 19.19 0.00 2.02 5.05 0.00 0.00 0.00 73.74 539.00
18時34分45秒 8 15.00 0.00 2.00 6.00 0.00 1.00 0.00 76.00 493.00
18時34分45秒 9 19.19 0.00 2.02 5.05 0.00 1.01 0.00 72.73 623.00
18時34分45秒 10 31.31 0.00 4.04 7.07 0.00 1.01 0.00 56.57 629.00
18時34分45秒 11 4.00 0.00 5.00 4.00 0.00 0.00 0.00 87.00 0.00

18時34分45秒 CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
18時34分46秒 all 12.90 0.00 3.00 4.49 0.25 0.75 0.00 78.62 20980.81
18時34分46秒 0 11.11 0.00 0.00 0.00 0.00 0.00 0.00 88.89 1011.11
18時34分46秒 1 16.00 0.00 3.00 7.00 0.00 0.00 0.00 74.00 776.77
18時34分46秒 2 15.15 0.00 2.02 7.07 0.00 1.01 0.00 74.75 632.32
18時34分46秒 3 15.15 0.00 2.02 6.06 0.00 1.01 0.00 75.76 741.41
18時34分46秒 4 10.00 0.00 8.00 2.00 2.00 3.00 0.00 75.00 12580.81
18時34分46秒 5 4.00 0.00 2.00 1.00 0.00 1.00 0.00 92.00 2574.75
18時34分46秒 6 6.00 0.00 1.00 2.00 0.00 0.00 0.00 91.00 0.00
18時34分46秒 7 16.67 0.00 3.92 6.86 0.00 0.98 0.00 71.57 572.73
18時34分46秒 8 20.00 0.00 3.00 5.00 0.00 1.00 0.00 71.00 754.55
18時34分46秒 9 16.00 0.00 3.00 6.00 0.00 0.00 0.00 75.00 788.89
18時34分46秒 10 21.57 0.00 4.90 9.80 0.00 0.98 0.00 62.75 540.40
18時34分46秒 11 2.97 0.00 3.96 0.99 0.00 0.00 0.00 92.08 0.00
HDD(4玉RAID1+0)
16時34分27秒 CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
16時34分28秒 all 4.58 0.00 1.00 30.62 0.08 1.16 0.00 62.56 7223.76
16時34分28秒 0 0.99 0.00 0.00 2.97 0.00 0.00 0.00 96.04 991.09
16時34分28秒 1 5.94 0.00 0.99 41.58 0.00 1.98 0.00 49.50 449.50
16時34分28秒 2 0.00 0.00 0.99 0.99 0.00 0.00 0.00 98.02 0.00
16時34分28秒 3 6.00 0.00 1.00 54.00 1.00 1.00 0.00 37.00 455.45
16時34分28秒 4 6.93 0.00 1.98 51.49 0.00 0.99 0.00 38.61 434.65
16時34分28秒 5 7.00 0.00 1.00 40.00 0.00 2.00 0.00 50.00 455.45
16時34分28秒 6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00
16時34分28秒 7 3.00 0.00 1.00 26.00 0.00 1.00 0.00 69.00 230.69
16時34分28秒 8 1.00 0.00 0.00 4.00 0.00 0.00 0.00 95.00 1657.43
16時34分28秒 9 6.06 0.00 1.01 36.36 0.00 1.01 0.00 55.56 377.23
16時34分28秒 10 12.00 0.00 4.00 72.00 0.00 4.00 0.00 8.00 1692.08
16時34分28秒 11 6.93 0.00 1.98 36.63 0.00 1.98 0.00 52.48 481.19

16時34分28秒 CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
16時34分29秒 all 4.50 0.00 1.00 43.30 0.00 1.08 0.00 50.12 7258.00
16時34分29秒 0 0.00 0.00 0.00 1.00 0.00 0.00 0.00 99.00 1001.00
16時34分29秒 1 3.96 0.00 0.99 64.36 0.00 0.00 0.00 30.69 268.00
16時34分29秒 2 0.00 0.00 0.00 4.00 0.00 0.00 0.00 96.00 0.00
16時34分29秒 3 6.93 0.00 0.99 62.38 0.00 1.98 0.00 27.72 461.00
16時34分29秒 4 6.12 0.00 2.04 78.57 0.00 1.02 0.00 12.24 459.00
16時34分29秒 5 7.00 0.00 1.00 69.00 0.00 2.00 0.00 21.00 626.00
16時34分29秒 6 0.00 0.00 0.00 4.00 0.00 0.00 0.00 96.00 0.00
16時34分29秒 7 4.95 0.00 0.99 53.47 0.00 1.98 0.00 38.61 351.00
16時34分29秒 8 0.00 0.00 0.00 4.00 0.00 1.00 0.00 95.00 1740.00
16時34分29秒 9 4.95 0.00 0.99 39.60 0.00 0.99 0.00 53.47 216.00
16時34分29秒 10 11.11 0.00 3.03 77.78 0.00 4.04 0.00 4.04 1719.00
16時34分29秒 11 9.00 0.00 1.00 64.00 0.00 1.00 0.00 25.00 417.00

16時34分29秒 CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
16時34分30秒 all 4.00 0.00 0.75 45.80 0.00 1.00 0.00 48.46 6368.00
16時34分30秒 0 0.00 0.00 0.00 6.00 0.00 0.00 0.00 94.00 1001.00
16時34分30秒 1 3.00 0.00 1.00 56.00 0.00 1.00 0.00 39.00 253.00
16時34分30秒 2 1.00 0.00 0.00 3.00 0.00 0.00 0.00 96.00 0.00
16時34分30秒 3 6.12 0.00 1.02 52.04 0.00 1.02 0.00 39.80 383.00
16時34分30秒 4 5.88 0.00 0.98 78.43 0.00 2.94 0.00 11.76 473.00
16時34分30秒 5 8.91 0.00 2.97 73.27 0.00 1.98 0.00 12.87 1030.00
16時34分30秒 6 0.00 0.00 0.00 6.00 0.00 0.00 0.00 94.00 0.00
16時34分30秒 7 5.05 0.00 0.00 50.51 0.00 1.01 0.00 43.43 301.00
16時34分30秒 8 0.00 0.00 0.00 8.91 0.00 0.00 0.00 91.09 1528.00
16時34分30秒 9 4.04 0.00 0.00 59.60 0.00 0.00 0.00 36.36 306.00
16時34分30秒 10 7.77 0.00 1.94 71.84 0.00 2.91 0.00 15.53 731.00
16時34分30秒 11 6.00 0.00 1.00 80.00 0.00 2.00 0.00 11.00 365.00

16時34分30秒 CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
16時34分31秒 all 4.41 0.00 1.08 40.85 0.00 1.00 0.00 52.66 7587.00
16時34分31秒 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 1001.00
16時34分31秒 1 4.95 0.00 0.99 60.40 0.00 0.99 0.00 32.67 317.00
16時34分31秒 2 0.00 0.00 0.00 9.00 0.00 0.00 0.00 91.00 0.00
16時34分31秒 3 5.94 0.00 0.99 50.50 0.00 0.99 0.00 41.58 452.00
16時34分31秒 4 6.06 0.00 2.02 55.56 0.00 2.02 0.00 34.34 564.00
16時34分31秒 5 14.14 0.00 4.04 69.70 0.00 3.03 0.00 9.09 1712.00
16時34分31秒 6 0.00 0.00 0.00 14.14 0.00 0.00 0.00 85.86 0.00
16時34分31秒 7 4.95 0.00 0.99 53.47 0.00 0.99 0.00 39.60 456.00
16時34分31秒 8 0.00 0.00 0.00 2.02 0.00 0.00 0.00 97.98 1818.00
16時34分31秒 9 5.00 0.00 1.00 60.00 0.00 2.00 0.00 32.00 386.00
16時34分31秒 10 6.19 0.00 0.00 53.61 0.00 0.00 0.00 40.21 487.00
16時34分31秒 11 6.00 0.00 2.00 63.00 0.00 0.00 0.00 29.00 390.00
iowait 値を見る限り明らかにioDrive(80G)には余裕があるように見受けられます。
今回は時間切れでここまでとなりますが、ioDrive(80G)に関してはまだまだ余力があり、
チューニング次第で更に性能向上が見込めれるだけの様相は伺い知れました。
また触れる機会がありましたら次回はチューニングを行ってみて結果をBlogにあげてみたいと思います。

DWHで類似度計算

 こんにちは、ECナビ システム本部 システムソリューショングループの大竹(@tokosa)です。

ECナビではDWHのNetezzaを使用し、データマートを構築しております。

Netezzaに関しては、日本ネティーザのサイトをご覧下さい。

実際Netezzaの性能がどの位なのか?何が出来るのかをご紹介します。

Netezzaを使用して2つの集合の類似度を計る

2つの集合の類似度を計る方法としてTanimoto係数(Jaccard係数の拡張)があります。

式は下記の通り。

==================================================================
Tb = Nc/ (Na + Nb - Nc)
==================================================================
Tb:Tanimoto係数 (二値データ)
Na:Aのアイテム数 
Nb:Bのアイテム数 
Nc:共通集合

Tbは0~1の間で0に近ければ類似度が低く1に近づく程類似度が高くなります。

では、実際にDBで集計を行います。

テーブル構成

NETEZZA => d PUR_TEST

Attribute | Type | Modifier | Default Value -----------------+------------------------+----------+--------------- USER_ID | INTEGER | | ITEM_ID | INTEGER | | DATE | DATE | | distribute key (USER_ID) レコード数:50万 集計対象数(アイテム数):1000

テンポラリーテーブルは作成せず、自己結合でSQLを実行させます。

1.Nc=アイテム共通集合をもとめるSQL
  select m.ITEM_ID as MY_ITEM,o.ITEM_ID as OTHER_ITEM ,COUNT(distinct(o.USER_ID)) as match
  from PUR_TEST m INNER JOIN PUR_TEST o on m.ITEM_ID != o.ITEM_ID
  and m.USER_ID =o.USER_ID 
  group by m.ITEM_ID,o.ITEM_ID

2.Naのアイテム数
  select item_id,count(distinct(user_id)) NA from pur_test group by item_id

3.Nbのアイテム数
   select item_id,count(distinct(user_id)) NB from pur_test group by item_id

4.1~3の結果をTanimoto係数の公式へ。

 select MY_ITEM,NA,OTHER_ITEM,NB,NC,trunc(cast(NC as dec)/(NA+NB-NC),2) as T from (
   select m.ITEM_ID as MY_ITEM,o.ITEM_ID as OTHER_ITEM ,COUNT(distinct(o.USER_ID)) as NC
   from PUR_TEST m INNER JOIN PUR_TEST o on m.ITEM_ID != o.ITEM_ID
   and m.USER_ID =o.USER_ID 
   group by m.ITEM_ID,o.ITEM_ID
 ) ma,
 (select ITEM_ID,count(distinct(user_id)) NA from pur_test group by item_id) na,
 (select ITEM_ID,count(distinct(user_id)) NB from pur_test group by item_id) nb 
 WHERE 
 ma.MY_ITEM =na.ITEM_ID AND
 ma.OTHER_ITEM =nb.ITEM_ID 

単純に計算量はO(n^2)です。

アイテム数が1000位なら一瞬です。

実行時間================
real    0m6.680s
user    0m0.156s
sys     0m0.031s

アイテム数が10000超えると。。

実行時間================
real    29m53.769s
user    1m10.785s
sys     0m3.777s

まとめ

実際にはテンポラリテーブルを作成するべきです、アイテム毎のアイテム数(Na ,Nb) は先に集計しておくと良いでしょう。

Netzzaは自己結合も弱いのでこちらは別にするべきです。

今回はNetezzaの性能計る為、こんなSQLを書いています。

Netezzaにもstddev(標準偏差)、stddev_pop(母標準偏差)、variance(式の分散)、var_pop(母分散)などなど

解析関数が用意されています。協調フィルタリングの相関係数法もできますね。

記事検索
QRコード
QRコード