The {history} table in Drupal doesn't scale at all [#766670]

The {history} table in Drupal doesn't scale at all. When a user registers all nodes are marked as unread and the only thing that will mark them as read is if you insert a entry for every node into the {history} table for that current user. On my setup I have about 2k users and 6000 nodes, so that means my {history} table need to hold 12 millions(!!!) entries in order to keep track of unread nodes for only 2k users. That's insane...

I thought Drupal compared the users created table with the node table in order to determine if a user is newer as the content and then just mark it automatically as read, without having to insert thousands of rows into the database. However, I'm wrong... and each user registering on a Drupal powered forum will see all nodes/topics as unread untill the "mark as read button" i clicked (which inserts some hundred of thousand rows, depending on how many nodes you have).

This is giving me a very hard time. I teach Drupal at my University in Norway, but I really can't find a good way to solve this matter. I've already got three emails from my host regarding missuses of resources because of Drupal's heavy load on the database.

The only thing I can think of would be to mark nodes as new in the users session. Meaning that is a user signs out of the site, the list of unread topics get cleared. New users og in general most users are not interested in having old nodes marked as new anyway...

I really hope the community can help me with this matter, I'm quite desperate on what to do....

Comments

Comment #1

NeoID CreditAttribution: NeoID commented 12 April 2010 at 17:22

Priority:

Critical

» Minor

The solution is so simple, yet so bad documented.
I would love to see variables such as define('NODE_NEW_LIMIT', time() - 7*24*60*60); or such variables in the settings.php. I think that would help a lot of users...

Comment #2

rickvug CreditAttribution: rickvug commented 10 February 2011 at 10:38

Version:

6.16

» 8.x-dev

This is still a valid issue. There are some massive Drupal sites out there. What is really needed here is a plug-able backend for history data so that something like Mongo could be swapped in. Somewhat related to this cleanup suggestion #1029708: History table for any entity. The entire system needs some work.

Comment #3

rickvug CreditAttribution: rickvug commented 10 February 2011 at 10:39

Priority:

Minor

» Normal

Comment #4

illogicz CreditAttribution: illogicz commented 30 April 2011 at 22:10

How about this.

When there is no history entry for a given node+user, don't consider any comments in it as new.

This would mean that when someone first registers and views the site, no comments at all are considered new. Removing the need for huge database inserts to set them all as read. When someone does view a node, and then a new comment is posted, there will be an entry in the history and consider it new. Viewing a topic would be similar to subscribing to it. This also seems better functionally, because users are only interested in nodes they have read and know about.

And when a "set all as read" action is fired, it would only need to update those history entries that are already in the database. Seems like a very simple and scalable solution to this problem. NODE_NEW_LIMIT could be set to be quite a bit longer.

The only downside is that if someone has viewed a node more than 'NODE_NEW_LIMIT' ago and cron deletes the entry it will not be considered new when someone does post. I can definitely live with this, especially with a longer NODE_NEW_LIMIT, not sure what the thoughts are for D8.

Comment #5

illogicz CreditAttribution: illogicz commented 1 May 2011 at 09:41

I made this change in forum.module line 914 (D7)

+ if($history < NODE_NEW_LIMIT){
      $topic->new_replies = comment_num_new($topic->nid, $history);
      $topic->new = $topic->new_replies || ($topic->last_comment_timestamp > $history);
+ } else {
+      $topic->new_replies = 0;
+      $topic->new = false;	
+ }

Does what I described in my last post.

Creating a function to mark nodes as read should be pretty straight forward as well.

Comment #6

illogicz CreditAttribution: illogicz commented 1 May 2011 at 10:02

Hmm, I just realized this only works for new comments.
Not actually setting the topics themselves as read. Which was the original issue.

Comment #7

geerlingguy CreditAttribution: geerlingguy commented 21 July 2011 at 15:43

Subscribe.

Comment #8

mototribe CreditAttribution: mototribe commented 17 September 2011 at 23:30

Comment #9

mototribe CreditAttribution: mototribe commented 17 September 2011 at 23:30

Comment #10

dgtlmoon CreditAttribution: dgtlmoon commented 20 October 2012 at 10:32

Comment #11

dawehner

German

CreditAttribution: dawehner as a volunteer commented 6 January 2016 at 09:38

Component:

11 January 2016 at 15:51

Version:

8.6.x-dev

» 8.8.x-dev

Drupal 8.6.x will not receive any further development aside from security fixes. Bug reports should be targeted against the 8.8.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.9.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Comment #20

geek-merlin

they/omg

German

Freiburg, Germany

CreditAttribution: geek-merlin at MachbarMacher commented 29 March 2020 at 23:53

Status:

Active

» Closed (cannot reproduce)

I can not reproduce this with current D8.9. I had some items in my history table, created a new user, and the table did not change. Guess this was D7 behavior.