-1

I want to add a new row to my db table on page views when the user view a page.

This could easily be done by incrementing the number of views for a certain page in pages table.

However, I want to have more control over the click-spammers ;D. I mean, I would like to add a new row in my table called e.g. page_views_all only if there was no previous record with the same IP or the time of the previous record for the same page_id and the same IP was no less then 5 minutes. So, the same article view could be added only every 5 minutes and if the user with the same IP click on the same article (e.g. checking new comments and refreshing the page) many times, then this is not counted as a click and it's not added as a new row to the database.

And I was thinking about creating 3 tables and I would like to hear from you what do you think about my idea and how can I make it better.

So, I would like to have 3 tables:

1) page_views_all (for storing every page_view; except the same IP only every 5 minutes limit I was talking above)

2) page_views_grouped_by_page_id (CRON will run a script to check page_views_all table every 30 or 60 minutes and group all rows by id and count them and store number of page views for every page id; This way we can reduce the table size many many times, because we don't take users in count only pages)

3) page_views_by_day a simple table with only one record per day (run everyday at midnight via CRON; will be sorting all page views and number of unique visitors etc.for this day; expecting only 365 records a year)

And maybe a 4th table ;) page_views_domain (again by cron and store the total amount of page_views for all the time from the website start.

What do you think about this approach? Is there any simpler solution for MySQL? It's important that I want to save visitor's IP, user agent, time of the visit, page_id, maybe also previous_page_id(referrer). I am using Disqus and no logged in users in my system so storing a user_id is not happening in my case, just IP address.

The philosophy behind so many tables for storing page_views is that I want by website to be fast and by serving users with results from table 2 (page_views_grouped_by_page_id) or table 3 (page_views_by_day) and not from the huge table 1 I can speed up the website quite a bit, at least I think so.

One thing I am not sure of is, if making a query to the first and biggest table that stare every page_view via CRON will not slow down the website? If I will do it e.g. every 30 minutes or every 1 hour? What do you think? Will setting limit to last 10000 rows (depending on how many views will people make in 30 or 60 minutes ) and counting and grouping them will help. Because, after a time there could be millions of rows, if not billion. Who knows how popular the page will become ;). If this could help I was thinking about creating a dynamic variable in a separate table that would contain how many pages should be checked by cron, depending on previous results. But, I think I am getting too complicated here and I shouldn't worry about that right now ;).

PHP and setting CRON is not a problem I can programm all the functionality. The only concern of mine is how to structure the tables for storing the data.

UPDATE: I would like to use db checks because if I would use cookies or sessions, if the user use another browser (e.g. first time Firefox and after a minute Chrome) if I was checking only sessions and cookies he would be counted two times. But using the db check I see his IP and time is only 2 minutes for that page, so he needs to wait.

ANOTHER BIG CONCERN OF MINE:

The checking process before inserting new row bothers me a little. Because I will need to check at least 1000 or more latest rows from the biggest table where all these page_views are stored. And I am not sure if this could not slow down my page, because on every page reload I will access this table and if I have 200 or more people online I don't know what can happen? Will it be OK? The pages are cached as html so the views are presented cached, but the checking in the db before the page output will still hit the db.

ONE GOOD THING THOUGH ;)

I need to make the db check/insert only for my articles (article detail page). I don't need to count homepage, categories or about us page visits. I can get that info from Google Analytics if I want to later. No need to have that info accurate for users every 30 or 60 minutes.

  • Any idea if this approach is ok, or I should try some other approach? – Derfder Dec 26 '13 at 19:23
  • I am a little surprised by the lack of people willing to give me some advice what should I do and what I shouldn't. – Derfder Dec 30 '13 at 10:06
8
+50

Take the 'click' UTC time and transform it into an INT with a 5 minute resolution (eg. seconds since 01/01/1970 divided by 60*5). Make this value and the IP a composite unique key. Use your DB platform syntax for insert with graceful handling of duplicate key violation (MERGE, INSERT ... ON DUPLICATE KEY). Cache IPs with timestamp in the app server for extra credit (not required for correctness, but avoiding a no-op DB round-trip is always good).

This way the insert is the check. No need for extra checks.

As for the per page/per day stuff, that does not belong on the OLTP db. Consider it DW and approach it as DW. There are many tools that can help (eg. columnstores).

This could easily be done by incrementing the number of views for a certain page in pages table

No, absolutely not. this is the sure-shot recipe for disaster because of the update contention. Seen it done 1 million times, only to be pulled back very time the visit rate reached enough to notice what a disaster it is (it works perfectly for the 1 view per minute sites...). Keeping an accurate and performant page-view counter under heavy load is actually quite tricky.

As a general comment, using IPs is always a bad idea. Many users sit behind corporate access points and all users in that corp share a few exit IPs. You will see them all as one (or a few).

Last point: most folk nowaday get such info by crunching the access_log from all front-ends in Hadoop or similar.

  • Please, explain it more in detail if possible, tahnk you. – Derfder Dec 30 '13 at 10:04
  • This answer seems to contradict itself when it talks about IP. The first paragraph talks about making the value and IP a composite key, but later on it says it is a bad idea to even use it. So, what's the answer? – John61590 Feb 20 '17 at 18:49
2

I agree with @Remus' last point - most people use Hadoop for access_log crunching and store the result in MySQL. You can also have apache write a custom log for you with only the fields you require, and export environment variables from the application for Apache to save (if required).

For prior art, I would recommend taking a look at how OpenX (advertising server) has solved this problem through various versions:

  • In 2.6 they had a raw impressions table and a series of summary tables. It didn't scale.
  • In 2.8 they introduced bucket based logging. It requires far less space since it does ON DUPLICATE KEY UPDATE count=count+1 per ad region/per time period, but I would say it's also painful to admin since there is no easy way to regenerate or audit stats.

Shameless plug: I wrote about this in a blog post. A couple of jobs ago I was using this to log/serve ~1 billion ads/month.

  • OpenX is buggy and prone to trojans e.g. zdnet.com/… – Derfder Dec 30 '13 at 10:05
  • 1
    It's hard to disagree with that statement, but I'm not suggesting you use it.. just observe that they needed to solve the same problem. – Morgan Tocker Dec 31 '13 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.