Advertisement:

Author Topic: smf_log_topics table HUGE (can't upgrade!)  (Read 94759 times)

Offline HunterP

  • Sr. Member
  • ****
  • Posts: 976
  • Gender: Male
  • SMF 2.0.4
    • Hulpverleningsforum Nederland
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #60 on: January 29, 2013, 12:54:09 PM »

Hi there,

What is the advantage of marking the topics/boards read above removing all entries for inactive members?

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,982
    • StoryBB/StoryBB on GitHub
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #61 on: January 29, 2013, 12:58:17 PM »
Well... marking a topic read adds one row to the log_topics table.

Marking a board read means you can remove all the rows for all the topics in that board and replace it with a single row in the boards read table.

Pruning inactive members' rows means you prune the rows for all of them, the advantage of which is that the table is smaller. Smaller tables are nearly always faster in every respect, not least when querying and joining.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline HunterP

  • Sr. Member
  • ****
  • Posts: 976
  • Gender: Male
  • SMF 2.0.4
    • Hulpverleningsforum Nederland
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #62 on: January 29, 2013, 01:46:44 PM »

Thanks Pete,

So there is no harm in pruning inactive members rows in these three columns?
Other than these members getting all messages unread *if* they ever arise from the dead :)

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,982
    • StoryBB/StoryBB on GitHub
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #63 on: January 29, 2013, 01:47:33 PM »
If they are suitably inactive, it does no harm to prune the rows - all it just means is that there is now more unread content for them if they do return.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline LaurieC

  • Jr. Member
  • **
  • Posts: 139
  • Gender: Female
  • I've been Hooked!
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #64 on: February 17, 2013, 12:28:11 PM »
Has anyone tried this with their 2.0.4 yet?
LaurieC

Offline whoey

  • Semi-Newbie
  • *
  • Posts: 69
  • Gender: Male
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #65 on: April 23, 2013, 06:57:07 PM »
doesn't seem to work for me anymore :(

I'm trying to migrate from punbb to SMF 2.0.4

It worked during the trial run, but I think that was before we patched from 2.0.3

Offline eliasr1990

  • Newbie
  • *
  • Posts: 1
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #66 on: August 01, 2013, 04:07:56 PM »
it works for me @ 2.0.4

Offline postpar

  • Semi-Newbie
  • *
  • Posts: 35
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #67 on: May 06, 2019, 11:42:11 AM »
I just want to add a data point and say I ran this, and it worked beautifully — cut 5 mb out of the 6.5 MB table. It had never been run over something like ten years.

I was a bit confused because it would not run from the command line; but it ran fine from the web.  I did have to change one line near the top to tell it what directory to use.

I've stripped permissions from it for safety, but it ran very nicely, very quickly, and very predictably! Thank you!

Using SMF 2.0.15 (since the last note was that it worked on 2.0.4, I thought it worth reopening the thread.)

Offline FishingManMatt

  • Semi-Newbie
  • *
  • Posts: 62
  • Gender: Male
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #68 on: November 23, 2019, 08:58:48 AM »
Hi!

I've just run the script on 2.0.15 and the number of records in two tables increased significantly :-\

smf_log_boards            from 39000 (820 kB) to 152000 (3,4 MB)
smf_log_mark_read     from 8000 (200 kB) to 136000 (3,1 MB)

Only smf_log_topics     descreased  from 1,600,000 (55 MB) to 1,260,000 (39 MB)

Do you think something has gone wrong and I should restore these tables from the backup which I made before running the script?

Offline shawnb61

  • Developer
  • SMF Hero
  • *
  • Posts: 1,591
    • sbulen on GitHub
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #69 on: November 23, 2019, 10:29:53 AM »
Yes, I recommend restoring if easy. 

I have been using an alternate utility found here:
https://github.com/sbulen/sjrbTools/blob/master/smf_read_inds_maint_2-0.php

This version deletes the records for folks who haven't logged on in a while.  You can specify the timeframe.  For a very large forum, you may need to increase the # if members to process at a time &/or run it multiple times.
« Last Edit: November 23, 2019, 10:39:59 AM by shawnb61 »
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline FishingManMatt

  • Semi-Newbie
  • *
  • Posts: 62
  • Gender: Male
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #70 on: November 24, 2019, 12:55:24 PM »
Many thanks, Shawn!

I will give it a try tonight and will post my results.

Offline FishingManMatt

  • Semi-Newbie
  • *
  • Posts: 62
  • Gender: Male
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #71 on: November 24, 2019, 08:25:02 PM »
Hm... This time:

smf_log_boards  # of records decreased (the size remained the same) from 39000 (820 kB) to 29000 (820 kB)
smf_log_mark_read  # of records and the size increased from 8000 (200 kB) to 19700 (450 kB)
smf_log_topics  # of records and the size decreased from 1,600,000 (55 MB) to 1,260,000 (51 MB)

Offline MobileCS

  • Jr. Member
  • **
  • Posts: 177
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #72 on: November 24, 2019, 10:15:22 PM »
Unfortunately, none of the addons clean the logs efficiently. When I ran them, It almost doubled the size of my logs.

I had to create my own script in order to solve the issue. I run it every few months to keep things clean.




If you can, create your own PHP script or hire someone to make one for you. It's really simple to do.

Code: [Select]
$days_ago = 365;
$not_visited = strtotime("-$days_ago days");

… connect to the database ...

$mysqli -> query("DELETE t1 FROM <tables> AS t1
INNER JOIN smf_members AS t2 ON t1.id_member = t2.id_member
WHERE t2.last_login < $not_visited");

Just repeat that SQL code for all the <tables> :

Code: [Select]
smf_log_boards
smf_log_topics
smf_log_mark_read
smf_log_notify
smf_themes

Of course this should go without saying, make sure to backup your database before running any script!

Offline shawnb61

  • Developer
  • SMF Hero
  • *
  • Posts: 1,591
    • sbulen on GitHub
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #73 on: November 24, 2019, 11:48:26 PM »
Hm... This time:

smf_log_boards  # of records decreased (the size remained the same) from 39000 (820 kB) to 29000 (820 kB)
smf_log_mark_read  # of records and the size increased from 8000 (200 kB) to 19700 (450 kB)
smf_log_topics  # of records and the size decreased from 1,600,000 (55 MB) to 1,260,000 (51 MB)

So it pruned about ~20% of your records. 

It may help to understand what those records do.  It's all about keeping the "new" indicators straight for users, letting them know what topics have been read or not read:
 - a log_boards is created every time a user views a board
 - a log_topics is created every time a user views a topic
 - log_mark_read records are created when you click on the mark read link on a board (or the mark all messages as read link on the main forum page); what that process does is delete individual log_topics records for the same board & put one "mark_read" record out there that says "the user has read this board up to here"

So...  If you ran the utility above with no changes to the settings, the utility would:
 - for users who have logged in within the last 3 months, does nothing
 - for users who have logged on between 3-12 months, does the equivalent of a 'mark all messages as read', deleting log_topics and creating a smaller set of log_mark_read
 - for users who haven't logged on in over a year, deletes log_boards, log_topics & log_mark read

BTW, it only defaults to processing 500 users at a time.  You may need to run it multiple times.  Not sure if that was clear.

If you ran it a bunch of times & still have ~1.2M log_topics, that's good news for your forum! You have a lot of users within the last 3 months reading lots of topics - about 1.2M recent user-topic views. 

That's a good thing.

(The problem with that older utility is that it marked all messages as read for everybody - whether they visited the board or not.  So it creates (#users * #boards * 2) records - an unfortunate cross product.) 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline FishingManMatt

  • Semi-Newbie
  • *
  • Posts: 62
  • Gender: Male
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #74 on: November 25, 2019, 02:14:56 PM »
Thanks for your reply, MobileCS!
Many thanks for the description, Shawn!

Now it's clear why the # of smf_log_mark_read increased.

BTW, it only defaults to processing 500 users at a time.  You may need to run it multiple times.  Not sure if that was clear.
Sure. I have 3600 users and run the script 4-5 x maybe.