All geek questions in one place

The most effective way to calculate the "popularity" of objects on a website

Good, that's why I create a website where people can post news, comments, questions, etc. People can also evaluate all of these objects, most of them, share them, etc. The site is PHP + MySQL. I wrote a script in PHP that does the following:

  • Grab all the comments and ratings added to them in the last 5 minutes. Add an entry to the "popularity" table with a change in popularity for each comment object.
  • Grab all the news and points / views / favorites / promotions added to them. Calculate the popularity of each news story (taking into account the change in popularity of the comments attached to them from step 1), and insert the record in the popularity table with the change in popularity for each news item.
  • Repeat step 2 for questions and other types of objects.

I tried to run this script (this is actually the symfony task) every 5 minutes with a cron job, and PHP began to choke and eat all of my server resources.

What is the preferred way to run background analytics script that calculates new data based on the data in the MySQL database and then inserts the calculated data into the database? I am sure that some basic procedures are missing here. I should note that the database is on another server, and this server had no problems with resources. The problem, apparently, boils down to the fact that PHP is suffocating on the application server passing through objects, calculating popularity (simple calculations) and inserting it into the database.

thank

- Change

How to replicate a database to a server used only for calculations. I could run the popularity script on a calculation server with a replicated database and insert the calculated popularity records into a live database. Of course, this would be a little delayed, but it was not a huge deal. I am not sure if this will fix the problem with consuming PHP resources.

+3
3

, , , , . , sql web . JOINs , .

, . (, () , , WHERE comments.news_id = news.id AND rating.comment_id = comments.id( , ...)) - , , mysql . sql . gazillion , CPU RAM. , : php mysql . Mysql , . - , ... , mysqli http://php.net/manual/en/mysqli.multi-query.php

+1

, MySQL , , . , 5 . , .

0

Instead of running it as a cron job, you can simply update the popularity each time an action is performed that changes it. For example, when a user adds a comment or evaluates an item as soon as this is done, you then update the item’s popularity.

0
source

Source: https://habr.com/ru/post/1794157/

More articles:


All Articles