News:

Bored?  Looking to kill some time?  Want to chat with other SMF users?  Join us in IRC chat or Discord

Main Menu

smf_log_topics table HUGE (can't upgrade!)

Started by digit, December 21, 2007, 06:48:43 AM

Previous topic - Next topic

HunterP


Hi there,

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

Arantor

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.

HunterP


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 :)

Arantor

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.

LaurieC

Has anyone tried this with their 2.0.4 yet?
Make someone smile today...

whoey

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


postpar

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.)

FishingManMatt

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?

shawnb61

#69
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.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

FishingManMatt

Many thanks, Shawn!

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

FishingManMatt

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)

MobileCS

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.

$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> :

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!

shawnb61

Quote from: FishingManMatt 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)

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

FishingManMatt

Thanks for your reply, MobileCS!
Many thanks for the description, Shawn!

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

Quote from: shawnb61 on November 24, 2019, 11:48:26 PM
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.

Advertisement: