Cackleコメントシステム：分析方法

みなさんこんにちは！ Cackleクラウドサービスに関する以前の投稿で、アーキテクチャ、テクノロジ、およびワークロード全般について説明しました。 今日、このような重い負荷と既に蓄積された情報（2011年から30,000,000件のコメント） の状況で、Cackleコメントシステムの詳細な分析を行った方法を共有したいと思います 。 統計を収集する私たちの方法は普遍的であり、分析の開発という課題に直面しているが、どこから始めればよいかわからないすべての人々にとって、実用的な面で興味深いと思います。

あまり入力がない

Cackleのコメントシステムは 2011年から運用されているため、約3,000万件のコメントが蓄積されています。 1日あたり約100,000が発行され、ピーク時、1秒あたり、同時に1,000に達します。



データベース内のこのすべては、15 GBのサイズでコメントテーブルに保存されます。 このテーブルは、クライアントのサイトの識別子であるsite_idフィールドによってインデックスが作成されます。 合計35,000のサイトが登録されています。 コメント数が最も多いのは、rusvesna.su（9百万）、svpressa.ru（80万）、3dnews.ru（50万）、carambatv.ru（45万）です。



分析要件は、各サイトの日次統計の収集と更新です。

• コメント（合計、公開済み、保留中、スパム、削除済み）;
• 好き嫌い;
• 匿名およびSSOのソーシャルプロバイダーからのコメント（単一の承認メカニズムで承認）。

これはすべて、サイトに関係なく、大規模であろうと非常に小規模であろうと、迅速に機能するはずです。

サーバー側-PostgreSQL

前に書いたように、データベースとして、ロシアとヨーロッパに分散したいくつかのデータセンターへのレプリケーションを備えたPostgreSQLがあります。 PostgreSQLは負荷に対処し、コメント統計を収集するためのビジネスロジックの主要コンポーネントとして選ばれたのは彼でした。



つまり、収集は2段階で行われます。



クライアントが管理パネルにログインし、サイトの1つを選択して、アナリティクスに移動するとします。

1.選択したサイトにコメントがあり、それらに統計がない場合、PostgreSQLは必要なすべてのパラメーターに関する統計の初期コレクションに対してSQLクエリを実行します。

2.次に、同じSQLクエリがskedulに配置され、データを更新するために15分ごとに実行されます。

1.すべてのコメントの統計の初期収集

そのため、選択されたサイト（site_id）、すべて（合計）、承認済み、保留中、スパム、削除されたコメント、いいね（up）について、コメントの毎日の統計を収集する必要があります。 、嫌い（ダウン）、および各ソーシャルプロバイダー（vk、ok、fb、tw、gpなど）、anonymous（匿名）またはSSO（sso）ユーザーのコメントおよびmc_userテーブル（usersテーブル） 。 comment.createdフィールドは、コメントが作成された時刻です。

SELECT DATE_TRUNC('day', t.created) as day, :siteId as site_id, COUNT(t) as total, SUM(CASE WHEN t.status = 1 THEN 1 ELSE 0 END) as approved, SUM(CASE WHEN t.status = 0 THEN 1 ELSE 0 END) as pending, SUM(CASE WHEN t.status = 3 THEN 1 ELSE 0 END) as spam, SUM(CASE WHEN t.status = 2 THEN 1 ELSE 0 END) as deleted, SUM(CASE WHEN t.rating > 0 THEN 1 ELSE 0 END) as up, SUM(CASE WHEN t.rating < 0 THEN 1 ELSE 0 END) as down, SUM(CASE WHEN t.provider = 'vkontakte' THEN 1 ELSE 0 END) as vk, SUM(CASE WHEN t.provider = 'odnoklassniki' THEN 1 ELSE 0 END) as ok, SUM(CASE WHEN t.provider = 'facebook' THEN 1 ELSE 0 END) as fb, SUM(CASE WHEN t.provider = 'twitter' THEN 1 ELSE 0 END) as tw, ... SUM(CASE WHEN t.provider = 'sso' THEN 1 ELSE 0 END) as sso, SUM(CASE WHEN t.anonym > 0 THEN 1 ELSE 0 END) as anonym FROM ( SELECT c.id, c.created, c.status, c.rating, c.anonym, u.provider FROM comment c LEFT JOIN mc_user u ON c.author = u.id WHERE c.site_id = :siteId ) t GROUP BY DATE_TRUNC('day', t.created) ORDER BY DATE_TRUNC('day', t.created);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

クエリ実行時間の分布は、サイトのコメントの数に依存し、次のようになります。

1.最大100,000のコメント-最大5秒のリクエスト時間。

2. 1,000,000までのコメント-1分までのリクエスト時間。

3.最大9,000,000のコメント-最大2分間のリクエスト時間。



毎回このSQLを追跡することは自殺であるため、このクエリで受信したデータを格納するために追加のテーブルを作成する必要があることは明らかです。

 CREATE TABLE comment_stats ( day date NOT NULL, site_id bigint NOT NULL, total integer, approved integer, pending integer, spam integer, deleted integer, up integer, down integer, vk integer, ok integer, fb integer, tw integer, ... sso integer, anonym integer, CONSTRAINT comment_stats_pkey PRIMARY KEY (day, site_id) );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

次に、最初のSQLクエリで、INSERT INTO comment_statsを追加します。

 INSERT INTO comment_stats SELECT DATE_TRUNC('day', t.created) as day, :siteId as site_id, COUNT(t) as total, SUM(CASE WHEN t.status = 1 THEN 1 ELSE 0 END) as approved, ...
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

次に、comment_statsテーブルから直接分析データを取得します。

select * from comment_stats where site_id = :siteId



。

2.データ更新

データの更新中に問題が発生しました。 複数の大規模なサイトが分析に接続すると、データベースのパフォーマンスが失われるため、最初の統計収集のSQLを常に取得して実行することはできません。



最も簡単で効果的なソリューションは、毎日の最大コメントIDを格納するcomment_statsテーブルに新しいcomment_idフィールドを追加することです。 データを更新すると、統計情報の収集はこのIDで始まります。 これらすべてを考慮して、最初のリクエストを変更します。

 INSERT INTO comment_stats SELECT DATE_TRUNC('day', t.created) as day, :siteId as site_id, --   id     MAX(t.id) as comment_id, COUNT(t) as total, SUM(CASE WHEN t.status = 1 THEN 1 ELSE 0 END) as approved, ... SUM(CASE WHEN t.anonym > 0 THEN 1 ELSE 0 END) as anonym FROM ( SELECT c.id, c.created, c.status, c.rating, c.anonym, u.provider FROM comment c LEFT JOIN mc_user u ON c.author = u.id WHERE c.site_id = :siteId --    id    comment_id   comment_stats AND c.id > (SELECT COALESCE(MAX(comment_id), 0) FROM comment_stats WHERE site_id = :siteId) ) t GROUP BY DATE_TRUNC('day', t.created) ORDER BY DATE_TRUNC('day', t.created);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

誰かが気づいた場合、このCOALESCE(MAX(comment_id), 0)



構造により、データの更新だけでなく、最初の統計収集も1回要求できます。 つまり、comment_statsに何もない場合、0を返し、コレクションはsite_idのコメントテーブル全体を調べ、データがある場合、コレクションは最後のcomment_idからのみ開始します。



このフォームではデータ更新リクエストが機能しないことを除いて、すべては問題ありません。 すでに収集された統計の最初の呼び出し以降、既存の秘密キーcomment_stats_pkeyを使用してデータを挿入しようとすると、例外が発生します。 言い換えると、15分経過した初期統計を収集し、comment_statsの条件id> last comment_idで開始されたデータ更新のスコープを開始しました。



非常に簡単な解決策があります（ INSERTのルールなし）-データの更新を要求する前に、comment_statsテーブルの最後の行を削除します。

 DELETE FROM comment_stats WHERE site_id = :siteId AND day IN (SELECT day FROM comment_stats WHERE site_id = :siteId ORDER BY day DESC LIMIT 1)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

説明したメカニズムは非常にシンプルで効果的です。 リレーショナルデータベース内のオブジェクトのほとんどすべての統計を収集するのに適した単純なロジックがあります。 たとえば、同じ2つのSQLクエリ（削除、コレクション）を使用して、モデレーター、出版物（投稿）から統計を収集し、すぐに同じ分析がCackleレビューレビューシステムに表示されます。

クライアント側-HighCharts

クライアントのブラウザでは、 HighChartsを使用してグラフを表示します。 これは、有料のチャートライブラリです（商用プロジェクト用）。 HighChartsを選択する前に、多くの類似のフレームワークを検討しましたが、どれも優れていませんでした。



私が最も気に入った点は、33,000千ポイントでも遅れがないこと、モバイルの適応性、時間間隔の巧妙な縮小、統合の容易さ、優れたAPIです。 ちなみに、彼らは割引を持っているスタートアップのために、あなたは手紙でそれを求めることができます。





これが最終的にどのように見えるかのいくつかのスクリーンショット。













上記のテクノロジーまたは当社のシステムについてさらに質問がある場合は、 hi @ cackle.meに喜んでお答えします。

ご清聴ありがとうございました！