이 PHP / MySQL 뉴스 피드를 어떻게 개선 할 수 있습니까?

이 PHP / MySQL 뉴스 피드를 어떻게 개선 할 수 있습니까?

---

이것이 최선의 해결책이 아니라는 것을 알고 있다고 말하면서 즉시 시작하겠습니다. 나는 그것이 복잡하고 기능의 해킹이라는 것을 알고 있습니다. 하지만 그것이 내가 여기있는 이유입니다!

이 질문 / 작업은 Facebook의 뉴스 피드 제작자 인 Andrew Bosworth와 Quora에 대한 토론을 기반으로합니다 .

나는 일종의 뉴스 피드 를 만들고있다 . 그것은 전적으로 내장되어있어 PHP하고 MySQL.

---

MySQL

피드의 관계형 모델은 두 개의 테이블로 구성됩니다. 하나의 테이블은 활동 로그로 작동합니다. 실제로 이름은 activity_log. 다른 테이블은 newsfeed입니다. 이 테이블은 거의 동일합니다.

로그에 대한 스키마 입니다activity_log(uid INT(11), activity ENUM, activity_id INT(11), title TEXT, date TIMESTAMP)

... 그리고 피드 의 스키마 는 newsfeed(uid INT(11), poster_uid INT(11), activity ENUM, activity_id INT(11), title TEXT, date TIMESTAMP)입니다.

사용자가 질문과 같이 뉴스 피드와 관련된 작업을 수행 할 때마다 즉시 활동 로그에 기록됩니다 .

---

뉴스 피드 생성

그런 다음 매 X 분 (현재 5 분, 15-30 분 후에 변경됨)마다 아래 스크립트를 실행 하는 크론 작업 을 실행합니다. 이 스크립트는 데이터베이스의 모든 사용자를 반복하고 해당 사용자의 모든 친구에 대한 모든 활동을 찾은 다음 해당 활동을 뉴스 피드에 기록합니다.

현재 SQL활동을 방해하는 것은 (에서 호출 됨 ActivityLog::getUsersActivity()) LIMIT 100성능 * 이유로 부과됩니다. * 내가 무슨 말을하는지 아는 것은 아닙니다.

<?php

$user = new User();
$activityLog = new ActivityLog();
$friend = new Friend();
$newsFeed = new NewsFeed();

// Get all the users
$usersArray = $user->getAllUsers();
foreach($usersArray as $userArray) {

  $uid = $userArray['uid'];

  // Get the user's friends
  $friendsJSON = $friend->getFriends($uid);
  $friendsArray = json_decode($friendsJSON, true);

  // Get the activity of each friend
  foreach($friendsArray as $friendArray) {
    $array = $activityLog->getUsersActivity($friendArray['fid2']);

    // Only write if the user has activity
    if(!empty($array)) {

      // Add each piece of activity to the news feed
      foreach($array as $news) {
        $newsFeed->addNews($uid, $friendArray['fid2'], $news['activity'], $news['activity_id'], $news['title'], $news['time']);
      }
    }
  }
}

---

뉴스 피드 표시

클라이언트 코드에서 사용자의 뉴스 피드를 가져올 때 다음과 같은 작업을 수행합니다.

$feedArray = $newsFeed->getUsersFeedWithLimitAndOffset($uid, 25, 0);

foreach($feedArray as $feedItem) {

// Use a switch to determine the activity type here, and display based on type
// e.g. User Name asked A Question
// where "A Question" == $feedItem['title'];

}

---

뉴스 피드 개선

이제 뉴스 피드를 개발하기위한 모범 사례에 대한 제한된 이해를 용서하십시오. 그러나 내가 cron 작업을 실행하고 있다는 의미에서 제한된 쓰기시 팬 아웃 이라고하는 제한된 버전이되기 위해 사용하는 접근 방식을 이해합니다. 사용자의 뉴스 피드에 직접 쓰는 대신 중간 단계로. 그러나 이것은 사용자의 뉴스 피드가로드시 컴파일되는 것이 아니라 정기적으로 컴파일된다는 점에서 풀 모델과는 매우 다릅니다.

This is a large question that probably deserves a large amount of back and forth, but I think it can serve as a touchstone for many important conversations that new developers like myself need to have. I'm just trying to figure out what I'm doing wrong, how I can improve, or how I should maybe even start from scratch and try a different approach.

One other thing that bugs me about this model is that it works based on recency rather than relevancy. If anyone can suggest how this can be improved to work relevancy in, I would be all ears. I'm using Directed Edge's API for generating recommendations, but it seems that for something like a news feed, recommenders won't work (since nothing's been favorited previously!).

---

Really cool question. I'm actually in the middle of implementing something like this myself. So, I'm going to think out loud a bit.

Here's the flaws I see in my mind with your current implementation:

1. You are processing all of the friends for all users, but you will end up processing the same users many times due to the fact that the same groups of people have similar friends.

2. If one of my friends posts something, it won't show up on my news feed for at most 5 minutes. Whereas it should show up immediately, right?

3. We are reading the entire news feed for a user. Don't we just need to grab the new activities since the last time we crunched the logs?

4. This doesn't scale that well.

The newsfeed looks like the exact same data as the activity log, I would stick with that one activity log table.

If you shard your activity logs across databases, it will allow you to scale easier. You can shard your users if you wish as well, but even if you have 10 million user records in one table, mysql should be fine doing reads. So whenever you lookup a user, you know which shard to access the user's logs from. If you archive your older logs every so often and only maintain a fresh set of logs, you won't have to shard as much. Or maybe even at all. You can manage many millions of records in MySQL if you are tuned even moderately well.

I would leverage memcached for your users table and possibly even the logs themselves. Memcached allows cache entries up to 1mb in size, and if you were smart in organizing your keys you could potentially retrieve all of the most recent logs from the cache.

This would be more work as far as architecture is concerned, but it will allow you to work in real-time and scale out in the future...especially when you want users to start commenting on each posting. ;)

Did you see this article?

http://bret.appspot.com/entry/how-friendfeed-uses-mysql

---

Would you add statistical keywording? I made a (crude) implementation via exploding the body of my document, stripping HTML, removing common words, and counting the most common words. I made that a few years ago just for fun (as with any such project, the source is gone), but it worked for my temporary test-blog/forum setup. Maybe it will work for your news feed...

---

between you can use user flags and caching. Lets say, have a new field for user as last_activity. Update this field whenever user enters any activity. Keep a flag, till what time you have fetched the feeds lets say it feed_updated_on.

Now update function $user->getAllUsers(); to return only users that have last_activity time later than feed_updated_on. This will exclude all the users that doesnt have any activity log :). Similar process for the users friends.

You can also use caching like memcache or file level caching.

Or use some nosql DB for storing all the feeds as one document.

---

I'm trying to build a Facebook-style news feed on my own. Instead of creating another table to log users' activities, I calculated the 'edge' from the UNION of posts, comments etc.

With a bit of mathematics, I calculate the 'edge' using an exponential decay model, with time-elapsed being the independent variable, taking account the number of comments, likes, etc each post has to formulate the lambda constant. The edge will decrease fast at first but gradually flattens to almost 0 after a few days (but will never reach 0)

When showing the feed, each edge is multiplied using RAND(). Posts with higher edge will appear more often

This way, more popular posts have higher probability to appear in the news feed, for a longer time.

---

Instead of running a cron job, a post-commit script of some sort. I don't know specifically what the capabilities of PHP and MySQL are in this regard - if I recall correctly MySQL InnoDB allows more advanced features than other varieties but I don't remember if there are things like triggers in the latest version.

anyway, a simple variety that doesn't rely on a lot of database magic:

when user X adds content:

1) do an asynchronous call from your PHP page after the database commit (async of course so that the user viewing the page doesn't have to wait for it!)

The call starts an instance of your logical script.

2) the logic script goes only through the list of friends [A,B,C] of the user who committed the new content (as opposed to list of everyone in the DB!) and appends the action of user X to feeds for each of these users.

You could just store these feeds as straight-up JSON files and append new data to the end of each. Better of course to keep the feeds in cache with a backup to filesystem or BerkeleyDB or Mongo or whatever you like.

This is just a basic idea for feeds based on recency, not relevance. You COULD store the data sequentially in this manner and then do additional parsing on a per-user basis to filter by relevance, but this is a hard problem in any application and probably not one that can be easily addressed by an anonymous web user without detailed knowledge of your requirements ;)

jsh

참고 URL : https://stackoverflow.com/questions/4162020/how-can-i-improve-this-php-mysql-news-feed