最近ではSEO施策を立案する際にもビッグデータを用いて自社サイトや競合サイト分析する事があり、社内の分析官はデータベースソフトウェアを使ってデータを解析しています。
ある程度のデータ量であればMySQLで解析できますが、億単位のレコード数をガリガリ分析するにはMySQLは向いていません。
弊社の事業上、様々なクライアント様のデータをスポットで分析する事があり、同一のテーブルで永続的に分析する事が無い為、1つのテーブルやデータベースに対してチューニング施策をする事も難しいのが現状です。 永続的に利用しないテーブルでもさほどチューニングを必要とせず膨大なデータガリガリ分析する為にはデータベースそのものの見直しが必要となりました。
今回は弊社が分析の際に利用しているデータベースソフトウェアを説明したいと思います。
そもそも、なぜRDBで分析する必要があるのか? Excelは非常に強力なツールであり、大半の分析はExcelで略完結します。 初めからデータの可視化に必要なグラフや関数も揃っている為、小さなデータの分析には常にExcelを用いています。
しかし、大規模サイトのユーザー回遊ログデータや1年間のSERPs変動データなど、データの量そのものが大きい場合、Excelでは分析ができません。
Excelは最大100万レコードまでデータを集計できますが、それ以上のデータは処理できません。又、データ量が多いほど集計に多くの時間(CPU)を必要とし、集計するだけで数時間かかってしまったという事もあります。
ビッグデータの場合、異なるテーブル(データ)を結合して分析する場面があり、関数であるVLOOKUPを利用する機会が多いですが、データ量が増えれば増えるほどVLOOKでのつなぎ合わせ作業は遅くなります。
「集計」作業に多くの時間を要してしまい、最も重要な「分析」にはあまり時間を割けれていない事もあり、Excel以外のソフトウェアで集計作業を行なう必要がありました。
MySQLなどのRDBは使わないのか? 元々はMySQLにデータをロードして集計作業を行っていました。
多数のドキュメントや書籍があるMySQLは使い勝手も良く、データ分析の際には必ずと言って良いほど利用していたRDBでしたが、このMySQLでさえデータ量が増えれば増えるほど処理速度が低下し、集計作業が中々終わらないという事が何度もありました。
特に、レコード数が1億を超えた辺りから急激に速度が低下する様になり、膨大なデータでは中々集計が終わらない事も多々ありました。もちろん、インデックスを貼り直したり、テーブルの構造を見直す事で速度は改善されましたが、それでもサクサクと分析できるほどではありませんでした。
レコード数が数億以上のデータはMySQLでも集計に時間を要してしまい、分析業務に支障をきたす様になり、新たなデータ解析基盤を検討する事にしました。
データウェアハウスサービスや他DBを検討 以前、エントリー「マーケティング担当者だけで完結できる!BigQueryでクローラーの回遊URL抽出と分析 」でも取り上げたGoogle BigQueryは革新的なデータウェアハウスサービスであり、弊社でも頻度高く利用してきました。
しかし、PaaSである事からスキャンするカラムやレコード数に応じて金額も跳ね上がり、コストの面で無視できないデメリットもありました。
利用料金も安く、分析官が使い慣れたSQL文で解析できるDHWを再検討する事にしました。
●Elastic・Redshift SQLによるデータ抽出には制限があるものの解析系でも最近注目されつつあるElasticや、BigQuery的な立ち位置のAWS Redshiftなども検討しましたが、どれもMySQLとの互換が無く、また導入(社内育成)にも時間がかかる事から見送りました。
●BlazingDB BlazingDB とは、GPUで動くデータベースソフトウェアであり、膨大なデータを高速で集計出来ることを強みとして謳っています。
BlazingDB社によると、MySQLと比較し最大350倍、AWS Redshiftと比較しても30倍も高速との事で、その集計速度を強調しているソフトウェアです。
SQL文で利用でき、弊社が課題としていた速度に注目して開発されたDBソフトウェアであった為、非常に魅力的ではありましたは、まだリリースされてからの期間が短く、ソフトウェアとしての信頼性が不明確であった為、残念ながら見送りました。
●MemSQL インメモリデータベースのMemSQLも同じくその処理速度を強みとして謳っているデータベースソフトウェアです。
以前は有料版の商用バージョンのみ公開していましたが、Ver4から無料で利用できるコミュニティーエディションを公開しており、手軽に導入する事が出来るようになりました。
又、海外では既に多くの商業サイトでの利用実績があり、資金調達もして開発に注力している為、信頼性もある程度あると考えられます。
又、業務上大量に集計作業をする事を考えると、IaaSやPaaSでは無くオンプレミスで利用した方が費用も抑えられる可能性も考えられる事から、MemSQLはオンプレミスで利用できるのも魅了の一つと言えるでしょう。
合わせてMySQLとの互換性もあり、BIツールなどから略カスタマイズ無しでMySQLの感覚で利用できるのも強みの一つでしょう。
その他様々なDHWやRDBソフトウェアがありましたが、MemSQLを試験的に導入してみることにしました。
MemSQLを使ってみる それでは早速MemSQLを使ってみたいと思います。
今回は試験的に利用する事もあり、取り急ぎAWS EC2にインストールしました。インメモリデータベースである事から、インスタンスはm4.2xlargeを選択しました。
SSHでログインしてインストールします。
cd /tmp
curl -O http://download.memsql.com/memsql-ops-5.7.1/memsql-ops-5.7.1.tar.gz
tar -xzf memsql-ops-5.7.1.tar.gz
cd memsql-ops-5.7.1
sudo ./install.sh
[y]と入力し、Enterを押します。
インストールが終了するとGUIの管理画面へのURLが表示されますので、URLをクリックします。
WebGUIの管理画面には初期に設定したクラスターが1つ表示されています。
左メニューの「SpeedTest」をクリックし、実際のDB処理スピードを計測してみましょう。
上図はスピードテストの結果です。
このテストでは膨大なデータを大量に挿入&読み込み、その速度を測っています。 今回のテストでは100万行の挿入を1秒で行ない、3000万行のスキャンを0.3秒で行った事になります。
これであれば、ある程度のスピード化には期待が持てそうです。
インポート画面から既存のMySQLDBサーバーに直接アクセスし、データをインポートする事も出来きます。
インポートが終わると、メモリ上にインポートしたデータと近い容量が表示されている事が分かります。
今回はAlgowatch のデータ過去1年分を移行してみました。 ※レコード数は1億を超えます
MemSQLのパフォーマンステスト MemSQL上のチューニングやクラスターの設定は殆ど行っていない状態でパフォーマンステストを行って見たいと思います。
今回は同じタイプのインスタンス上に構築したMariaDBサーバーと比較してみたいと思います。
特定ドメインの平均検索順位、ヒットKW数、Visibility合計値を日別で集計 今回はNaverまとめ(matome.naver.jp)の順位推移、ヒットKW数、Visibility合計値を日別で集計するSQLを回してみました。
MemSQLでは1.27秒で終了するのに対し、MariaDBでは48.95secでした。
昨年の6月からのデータを再集計し直す事で、過去のSEO評価推移を調査する事が出来ます。
昨年の11月後半から急激に順位が上昇している事が分かります。これは何が要因か確認してみしょう。
特定ドメインのhttps://ページの順位推移 GoogleがHTTPSを優遇するアルゴリズムを適用して以降、多くのWebサイトが常時HTTPS化に切り替えています。
今回の調査対象であるNaverまとめのHTTPS化状況も確認してみましょう。
Algowatch内のデータからNaverまとめがHTTPS化して以降の平均検索順位、ヒットKW数、Visibility合計値を日別で集計します。
カラム名「url」にURLが格納されている為、前方一致で「LIKE ‘https://%’」と検索しました。
MariaDBでは75秒かかるのに対し、MemSQLでは2秒未満と非常に短時間で集計できました。LIKE検索もMemSQLは極めて早いようです。 部分一致やフレーズ一致検索もこのスピード感で出来ると、スポットで分析したい時も時間をかけること無く出来そうです。
HTTPとHTTPSのURLの合計想定集客数とヒットKW数をデイリーで集計し、グラフ化したものが下記の図です。
2016年11月19日あたりからHTTPからHTTPSへインデックスが急に移り始め、ヒットキーワード数および想定流入数も急激に以降しています。
又、HTTPからHTTPSに切り替わる歳に大きく流入数、ヒットKW数共に増加している事から、Naverまとめも一定のSEO効果を獲得した可能性が高いと見ることが出来ます。
参考:2016年11月16日 – 常時SSL化(WebサイトのHTTPS化)に対応いたしました
特定ドメインのディレクトリ別SEO評価値推移 ディレクトリやページによってSEO評価は異なり、サイト全体の流入数に与えるインパクト(度合い)も異なってきます。
今回調査したNaverまとめでは、記事ページにあたるディレクトリ「/odai/」配下と、一覧ページにあたるディレクトリ「/topic/」があります。(※その他のページもありますが、今回はこの2つに絞って集計)
ページ種類毎にSEO評価値がどう変化してきたか集計・解析してみます。
先ず/topic/配下の合計値を集計すべく、カラム「URL」に対してLIKEで部分一致検索をかけます。
LIKE検索の部分一致の場合、インデックスが効かないため検索が非常に遅くなりますが、MemSQLでは2秒以内と非常に高速に集計できました。MariaDBは1分以上かかる結果となりました。前方一致のみならず部分一致検索でもある程度MemSQLは検索スピードが担保出来るようです。
上の図は /odai/配下 と /topic/配下 のVisibility値とヒットキーワード数、平均順位のデイリー推移です。
記事主体のサイトな為、当然の結果ではありますが、流入数の多くを牽引しているのは/odai/配下でした。また、11月の流入数の増加の多くは/odai/配下が牽引しており、Naverまとめに於いてSEO流入の大半は/odai/配下(キュレート記事)が担っている事が分かります。
その他、キーワード単位やLP単位などSQLを用いて様々な集計がMemSQLを使う事で高速かつ手軽に出来るようになりました。
最後に まだMemsqlの情報は少なく、使い方やクラスターの利用方法によってはMySQLよりも速度が低下する事もあるようです。
一概にMemSQLが最も早く優れているとは言えませんが、手軽に導入できる上、分析業務に支障をきたさない速度で集計が出来るのは非常に魅力的なデータベースソフトウェアだと思いました。
今回はAWS上で利用しましたが、ローカルエリアネットワークにサーバーを立て利用する事で使用料金を抑えつつ、データをダウンロードする時間も短縮出来るかと思います。