Simple Machines Community Forum

SMF Support => Server Performance and Configuration => Topic started by: digit on December 21, 2007, 06:48:43 AM

Title: smf_log_topics table HUGE (can't upgrade!)
Post by: digit on December 21, 2007, 06:48:43 AM
My server seems to be getting hammered...   my isp says...  (after recently getting my own server)...

Quote
QuoteOn Thu, 20 Dec 2007, >I< wrote:

|# It slowed to a crawl...  I saw it, and my mod reported it at the same time...
|# 3 of us are seeing the same slow downs at the same time, and we are in
|# Arkansas, New York and France.

My ISP responded:

Yes, that was because you were getting slammed.   I noticed the slow
down also but it did not die because of PECL problems.   The update
may not solve your problem.  Once again you have installed so much
frilly and fancy stuff that the you are taxing the server again.   
Also your DB and forums are just constantly growing and unless you
keep the system trimmed, things will just get worse.

You once had a glass which got full so we got you a bucket.  Now
that bucket is getting full.

When I look at my forum in phpmyadmin, I see my smf_log_topics table is 455 megs, with almost 900,000 records.  My smf_messages table is also HUGE... 682 megs!

What is the difference between the smf_messages table and the smf_personal_messages table (which is 38 megs!)

I am unable to optimize the smf_log_topics table.   >:(

Please advise.  It is currently in maintenance mode, while I TRY to optimize that table.

Also - is there a way to delete old PM's by date?  (if that is one of my problems)


THANKS!



Title: Re: smf_log_topics table HUGE (causing MY slow downs?)
Post by: digit on December 21, 2007, 12:45:53 PM
I would like to add more thing.  When I have Persistent connections selected, MySQL spits out quite a few errors like this...

071221  9:23:14 [Warning] Aborted connection 1024781 to db: 'forum' user: 'mysite' host: 'localhost' (Got an error reading communication packets)
071221  9:23:15 [Warning] Aborted connection 1024316 to db: 'forum' user: 'mysite' host: 'localhost' (Got an error reading communication packets)
071221  9:23:16 [Warning] Aborted connection 1023860 to db: 'forum' user: 'mysite' host: 'localhost' (Got an error reading communication packets)
071221  9:23:17 [Warning] Aborted connection 1024340 to db: 'forum' user: 'mysite' host: 'localhost' (Got an error reading communication packets)

My ISP just doubled the simultaneous servers to 300 that the server will allow.  I am HOPING that will help.

Any advice or comments woul be appreciated.

Thanks!


Title: Re: smf_log_topics table HUGE (causing MY slow downs?)
Post by: karlbenson on December 21, 2007, 12:53:38 PM
I can explain a bit about each table

The SMF_Messages is the content to all your forum.
Basically each individual post.
Depending on the length of each post and of course how many posts you have on your forum it will obviously be quite large.

The smf_topic table is then just a reference table.

SMF_personal_messages is the same ,but for PM's.
(note pm's are split over 2 tables),

The log_topics basically stores the position that each member is upto in each topic
Title: Re: smf_log_topics table HUGE (causing MY slow downs?)
Post by: digit on December 21, 2007, 01:01:01 PM
Quote from: karlbenson on December 21, 2007, 12:53:38 PM
The log_topics basically stores the position that each member is upto in each topic

I don't know what you mean - can you please explain?  Is this rather unimportant data then?
Title: Re: smf_log_topics table HUGE (causing MY slow downs?)
Post by: karlbenson on December 21, 2007, 01:06:37 PM
In this topic there are 4 posts. (including this response)

So far in this topic you have only read upto the 3rd message (your post above) which has the msg id of 1352041
So for you it has stored a row in the log topics table to say
User 3624 (you)
Has read upto msg 1352041
In this topic 212330

So SMF can automatically place you reading the topic FROM where you've read upto.
Title: Re: smf_log_topics table HUGE (causing MY slow downs?)
Post by: digit on December 21, 2007, 03:47:45 PM
SO - I am looking at my MySQL processes right now...  my server is hung....

I see a TON of these....

    1277  mysite       localhost      forum       189  Query REPLACE INTO smf_log_topics (ID_MSG, ID_MEMBER, ID_TOPIC) VALUES (919067, 29487, 119670)                                                                       
    1091  mysite       localhost      forum       196  Query REPLACE INTO smf_log_topics (ID_MSG, ID_MEMBER, ID_TOPIC) VALUES (919067, 20368, 125769)                                                                       
    1477  mysite       localhost      forum       196  Query REPLACE INTO smf_log_topics (ID_MSG, ID_MEMBER, ID_TOPIC) VALUES (919067, 20368, 125769)                                                                       
     638  mysite       localhost      forum       203  Query UPDATE smf_log_topics SET ID_MSG = 919067 WHERE ID_MEMBER = 2 AND ID_TOPIC = 125668 LIMIT 1                                                                   
    1226  mysite       localhost      forum       206  Query UPDATE smf_log_topics SET ID_MSG = 919067 WHERE ID_MEMBER = 19852 AND ID_TOPIC = 125636 LIMIT 1                                                               
    1356  mysite       localhost      forum       211  Query REPLACE INTO smf_log_topics (ID_MSG, ID_MEMBER, ID_TOPIC) VALUES (919067, 30657, 125725)                                                                       
    1201  mysite       localhost      forum       245  Query REPLACE INTO smf_log_topics (ID_MSG, ID_MEMBER, ID_TOPIC) VALUES (919067, 14886, 125767)                                                                       
    1006  mysite       localhost      forum       246  Query REPLACE INTO smf_log_topics (ID_MSG, ID_MEMBER, ID_TOPIC) VALUES (919067, 29908, 125603)                                                                       
    1162  mysite       localhost      forum       246  Query REPLACE INTO smf_log_topics (ID_MSG, ID_MEMBER, ID_TOPIC) VALUES (919067, 14886, 125767)                                                                       
    1243  mysite       localhost      forum       246  Query REPLACE INTO smf_log_topics (ID_MSG, ID_MEMBER, ID_TOPIC) VALUES (919067, 14886, 125767)                                                                       
    1325  mysite       localhost      forum       247  Query REPLACE INTO smf_log_topics (ID_MSG, ID_MEMBER, ID_TOPIC) VALUES (919067, 19852, 124141)                                                                       
    1248  mysite       localhost      forum       254  Query UPDATE smf_log_topics SET ID_MSG = 919067 WHERE ID_MEMBER = 11039 AND ID_TOPIC = 124182 LIMIT 1                                                               
    1337  mysite       localhost      forum       268  Query REPLACE INTO smf_log_topics (ID_MSG, ID_MEMBER, ID_TOPIC) VALUES (919067, 7633, 125686)                                                                       
    1475  mysite       localhost      forum       279  Query REPLACE INTO smf_log_topics (ID_MSG, ID_MEMBER, ID_TOPIC) VALUES (919067, 19852, 124147)                                                                       
    1476  mysite       localhost      forum       283  Query UPDATE smf_log_topics SET ID_MSG = 919067 WHERE ID_MEMBER = 18999 AND ID_TOPIC = 125638 LIMIT 1                                                               
    1218  mysite       localhost      forum       288  Query REPLACE INTO smf_log_topics (ID_MSG, ID_MEMBER, ID_TOPIC) VALUES (919067, 14913, 125609)                                                                       
                                                                                       

If I emptied the log_topics table, what would happen?
Title: Re: smf_log_topics table HUGE (causing MY slow downs?)
Post by: Ben_S on December 22, 2007, 08:48:04 AM
Everything would be marked as unread.

Have you optimized this server, is log_topics InnoDB, if not it's hardly a surprise you are having such problems.
Title: Re: smf_log_topics table HUGE (causing MY slow downs?)
Post by: digit on December 22, 2007, 09:04:32 AM
Quote from: Ben_S on December 22, 2007, 08:48:04 AM
Everything would be marked as unread.

Have you optimized this server, is log_topics InnoDB, if not it's hardly a surprise you are having such problems.

Yeah, I finally figured out all topics would be marked as unread - after I slept on it!

http://www.simplemachines.org/community/index.php?topic=212537.0

Is it possible to trim that table as I mention in that post?

...and yes, it is innoDB, was wondering if that was the best, and yes, I optimized it - but it takes forever and doesn't help!


Thanks!!!!
Title: Re: smf_log_topics table HUGE (causing MY slow downs?)
Post by: Ben_S on December 23, 2007, 06:35:49 PM
Who ate my long reply :(

Can you post the answers to the sticky and I'll try to help you get it optimized. I posted a longish reply earlier but it seems to have gone :(
Title: Re: smf_log_topics table HUGE (causing MY slow downs?)
Post by: digit on August 19, 2008, 04:44:24 AM
I am not sure why the above post was edit/delete...  or the link above deleted.  I am back with THIS ISSUE!   >:(  (I could have used the info above!)

I am CURRENTLY running upgrade.php... I've been at it at least ONE HOUR.  >:(

The script is hanging when it gets to the step where the smf_log_topics table is getting copied to a tmp table.

localhost   forum   Query   533   copy to tmp table   ALTER TABLE smf_log_topics CHANGE COLUMN ID_MSG ID_MSG INT( 10 ) UNSIGNED NOT NULL DEFAULT '0' 

This is the second time I have tried, without sccess to get past this step.

My smf_log_topics table is...

records: 9,125,535  type: InnoDB  size: 441.5 MB

How can I get past this without emptying the entire table?

Thanks in advance.


Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: karlbenson on August 19, 2008, 11:29:49 AM
Hey Digit, it might not be exactly what you are after, however I've attached here a script I wrote for my forum to reduce the no. of rows in log_topics using ssi to mark ALL boards as READ for inactive users (thats every single board, even ones they can't currently see). 
But can also be easily to configured to mark ALL boards as read for everyone (as I did eventually on my own forum to get the most benefit).

By default does 30 users per iteration (then refreshes itself)
By default 90 days inactivity
(both variables can be changed just inside the script)

Marking inactive users (30 days) unread saved me less than I'd hoped. So I marked all topics for everyone read, and it saved me over 600k entries. I'm sure this would be millions on your boards.  It might be an inconvenience over the short term.  But the performance seems worth it.

Here are the totals for my forum.

Before
8000 records - Log_boards
8000 records - Log_mark_read
833,000 records - Log_topics
(total 849,000)

Marked all boards for inactive users (30 days)
Before
100,000 records - Log_boards
93,000 records - Log_mark_read
612,000 records - Log_topics
(total 815,000)

Marked ALL boards read for everyone
114,000 records - Log_boards
114,000 records - Log_mark_read
0 records - Log_topics
(total 128,000)

Always backup your database before running any script like this. just in case.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: vbgamer45 on August 19, 2008, 09:16:29 PM
Awesome script karlbenson seems like it can be really useful for large boards.
Seems kind of odd to me that log_boards and log_mark_read have the same number of entires. I would have thought log_boards would been a lot smaller that mark_read
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: kizer on August 21, 2008, 07:37:45 PM
So your saying that the forum adds to the database for topics not read? So when a topic is read reduces the size of the database?

If thats the case it would appear that I should run your script just to reduce the size of my database for inactive members since they aren't around anyways. I have members that haven't been around for a year and I'd bet their accounts are just getting larger and larger until they check the all is read.

Am i nuts or misunderstanding what your saying?

The only reason Im worrying about this is Im sorta a clean freak when it comes to the forum and when I have inactive users I'd normally delete them, but if I can just house clean its even better.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: karlbenson on August 21, 2008, 07:52:17 PM
No, Smf stores where you have read upto in a topic (eg msg100 in topic=20)
It also stores a variable per-board when you've read every topic in that board for the highest message you've read in that board. (this is because if you can mark a board as read it it can replace one entry per topic, with one for that board).

So across the forum, by marking all boards read for inactive users you save alot of database entries.
However the ones where you save the most are for your active users, as they often read 90% of topics, so that means alot of variables, but not complete, so they be replaced by variables.

Think of it this way.
10 boards with 10 topics in each.
If a user has read 9 topics out of 10 in each board.
Thats 90 rows in the log_topics table.
But 0 entries in the log_boards table

Inactive users might have only visited 1 topic out of 10 in each board.s
Thats 10 rows in the log_topics table.

However if you mark them all as read.
For inactive users you would really only replace 10 rows in the log_topics table with 10 in the log_boards table.
For active users though you would SAVE 80 rows.

Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: kizer on August 21, 2008, 10:41:56 PM
Ok, so I should probably encourage my members to click the mark all topics read on the front page when they have read all messages even if they have read them all so it clears all the variables and makes the entries smaller.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: metallica48423 on September 17, 2008, 06:55:46 AM
Did you still require assistance with this?

Of course, as a site grows it will eventually outgrow the constraints of its container -- the same is true for almost anything that continuously grows!

I would be looking into options to optimize your server's operations moreso than worrying about huge database size -- at least at first.  What exact problem are you having with upgrade?
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: RobertMfromLI on February 16, 2009, 03:44:40 PM
karlbenson,

Thank you!

-Robert
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: matasanos on February 16, 2009, 04:00:08 PM
Please help
i have a problem

i ran that script...and my DB has grow..too much

now i gave those values:

smf_log_boards             2,563,570             MyISAM     utf8_general_ci     59.3 MB

smf_log_mark_read        2,549,090             MyISAM     utf8_general_ci     58.1 MB

smf_log_search_words  2,685,130              MyISAM     utf8_unicode_ci     61.6 MB


and i only have 85098 Posts in 12825 Topics by 28202 Members


please help!
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: Vekseid on February 16, 2009, 04:33:22 PM
For thirty thousand members that's too much?

I should add 'prune nullos' to one of the optimization tips. How many zero post members do you have?
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: karlbenson on February 16, 2009, 04:35:53 PM
The script marks every board as read for all users.
12825 * 28202
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: matasanos on February 16, 2009, 04:44:17 PM
Quote from: Vekseid on February 16, 2009, 04:33:22 PM
For thirty thousand members that's too much?

I should add 'prune nullos' to one of the optimization tips. How many zero post members do you have?

Quote from: regularexpression on February 16, 2009, 04:35:53 PM
The script marks every board as read for all users.
12825 * 28202

ok, thanks
so i understand, the best option is to prune nullos members, ok!!
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: John Eric on February 25, 2009, 02:06:46 PM
better solution rather to delete that wasteful useless featuring 'unread foolishness'
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: JimM on March 14, 2009, 09:24:10 PM
Marking this as solved.  Topic was started in 2007.  Please start a new topic if you have this same issue.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: digit on September 13, 2009, 10:11:56 AM
Quote from: karlbenson on August 19, 2008, 11:29:49 AM
Hey Digit, it might not be exactly what you are after, however I've attached here a script

I don't know how I missed this - but I did -THANKS!  'am running it now :)   8) :P

Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: Owdy on September 26, 2009, 04:26:56 PM
Quote from: karlbenson on August 19, 2008, 11:29:49 AM
Hey Digit, it might not be exactly what you are after, however I've attached here a script I wrote for my forum to reduce the no. of rows in log_topics using ssi to mark ALL boards as READ for inactive users (thats every single board, even ones they can't currently see). 
But can also be easily to configured to mark ALL boards as read for everyone (as I did eventually on my own forum to get the most benefit).
You should make a mod about this. Setting in admin panel :)
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: MultiformeIngegno on February 06, 2010, 06:24:25 PM
Will the karlbenson's script work on 2.0 RC2?
I think it's really, really useful!! Thanks!!!!
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: JimM on February 07, 2010, 12:47:59 PM
There are 2 versions attached to this post.  As always make a backup before trying this on you forum.

http://www.simplemachines.org/community/index.php?topic=212330.msg1667071#msg1667071
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: Piwaille on February 17, 2010, 04:49:07 AM
Hello !
I've just read that topic (coming from Twenty-four things you can do to make SMF go faster because my forum is growing up quite a lot)
Quote from: Owdy on September 26, 2009, 04:26:56 PMYou should make a mod about this. Setting in admin panel :)
I do agree ! A nice mod would be a great thing ;D
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: waruna on February 26, 2010, 07:49:19 AM
Hello guys. How can I run the 2.x_inactiveusers_markallboardsread.php?
Just simply put the file into my root directory and go to myforum.com/2.x_inactiveusers_markallboardsread.php?

Thanks in advance.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: JimM on March 02, 2010, 06:58:57 PM
Quote from: waruna on February 26, 2010, 07:49:19 AM
Hello guys. How can I run the 2.x_inactiveusers_markallboardsread.php?
Just simply put the file into my root directory and go to myforum.com/2.x_inactiveusers_markallboardsread.php?

Thanks in advance.

You would upload this script to the same location as your forum and then point your browser to it.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: Kimmie on April 18, 2010, 10:19:43 PM
I noticed this thread and was wondering if this script would be useful to run periodically as sort of my "regular maintenance" if you will, on my site, for the sheer fact that I have some members who have not visited in a while. I'm using SMF 2.0 RC1.2 so I assume that I would use the 2.x script
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: humbleworld on May 30, 2010, 11:26:59 AM
Can someone guide me where to upload this script of karl? how shall I use the script?
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: JimM on May 31, 2010, 12:19:47 AM
Quote from: JimM on March 02, 2010, 06:58:57 PM
Quote from: waruna on February 26, 2010, 07:49:19 AM
Hello guys. How can I run the 2.x_inactiveusers_markallboardsread.php?
Just simply put the file into my root directory and go to myforum.com/2.x_inactiveusers_markallboardsread.php?

Thanks in advance.

You would upload this script to the same location as your forum and then point your browser to it.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: humbleworld on May 31, 2010, 02:10:13 AM
Jim, thank you for the guide. Appreciate it.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: ɔɔɔɔɔɔuɥoɾ on June 24, 2010, 01:40:41 PM
Using it now, very nice, taking a while, but its not me having to do it :P

Thanks
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: Kimmie on June 29, 2010, 03:40:33 PM
do we need to throw our site into maintenance mode before running this or can we leave it open?
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: xenovanis on June 29, 2010, 03:43:41 PM
Quote from: Kimmie on June 29, 2010, 03:40:33 PM
do we need to throw our site into maintenance mode before running this or can we leave it open?

You could throw it in maintenance mode, however I believe there's a point where the upgrader does so automatically. Not sure though, it has been a while since I upgraded.

For your users it's probably best to throw it in maintenance mode, so they know they can't access the forum for a while. This will also prevent their posts being blocked during the upgrade of the database.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: Kimmie on June 29, 2010, 03:52:33 PM
I think I might have posted incorrectly and it made it confusing hehe. I meant it in terms of running the script, not actually upgrading. Sorry :)
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: xenovanis on June 29, 2010, 03:57:06 PM
Nah, not your fault. I'm really a bit slow today.  :D I should have read the previous posts to realise your question was about the script.

Sorry, can't help you there.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: Kimmie on June 30, 2010, 10:23:37 PM
Not a problem we all hav those days :)

I will probably put it in maintenance mode just to be on the safe side - atleast until I have run it that first time and know that nothing gets messed up.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: ɔɔɔɔɔɔuɥoɾ on June 30, 2010, 11:36:33 PM
Quote from: Kimmie on June 29, 2010, 03:40:33 PM
do we need to throw our site into maintenance mode before running this or can we leave it open?

I didn't, and nothing bad happened :)
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: R.Bourne on August 16, 2010, 09:43:43 PM
Thanks for the script.


FWIW, I've added it as cron job to run once a month :P
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: polowest on October 20, 2010, 09:47:43 PM
Hi,
i try to load the script 1.1.x_inactiveusers_markallboardsread.php on my forum,
but the server report this error:

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator, [email protected] and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.
Why?

Regards.

Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: 青山 素子 on October 20, 2010, 11:20:00 PM
Check your server error log.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: IvoLeite on January 20, 2011, 06:18:27 AM
Hello  :) .

I've used this tool several times and I think it's very usefull, althought I can't use right now. When I try to used, it redirects to the SMF community (this forum). I've tried both versions with the same result. I've the SMF 2.0 RC4.

Can someone help me? Tks.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: willerby on January 20, 2011, 05:20:20 PM
I'd like to use this for 2.0RC4 but unsure given above post... will it work (don't want to try on a live site!!)
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: RobertMfromLI on January 20, 2011, 11:39:21 PM
Quote from: laetabi on January 20, 2011, 05:20:20 PM
I'd like to use this for 2.0RC4 but unsure given above post... will it work (don't want to try on a live site!!)


I'm running it on v2.0rc4. It is taking very long on this version of SMF, and I've had to change it to one member at a time. We have roughly 115,000 posts and 10,000 members. On some, it "flew" through them, but then on others, it would take 10-20 times the amount of time.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: willerby on February 03, 2011, 06:04:39 PM
Worked extremely well on 2.0 RC4 on a forum with 4500 members. Thanks all.

Completed 50 members at a time, with 30 days set for inactive users and script ran in less than five minutes
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: orlandoryo on March 19, 2011, 11:49:28 PM
I know it says 2.x but just wanted to make sure so... what about RC5?
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: _Ziggy_ on April 05, 2011, 03:42:26 PM
Quote from: orlandoryo on March 19, 2011, 11:49:28 PM
I know it says 2.x but just wanted to make sure so... what about RC5?

Works great on my RC5 forums!!!


Thanks Karlbenson!!!
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: samurai-lupin on June 24, 2011, 04:03:38 PM
Hello guys,

I need a cheap oppinion what happened to my tables ... as they almost became twice the size after having run the script?!

I thought it would be a good idea to run the mark topics read script for inactive users. Before I run it I had these tables sizes:

49 MB - /var/lib/mysql/atanua_boards/smf_log_boards.ibd
45 MB - /var/lib/mysql/atanua_boards/smf_log_mark_read.ibd

AFTER having run the script they are now at:

70,6M - /var/lib/mysql/atanua_boards/smf_log_boards.ibd
60,6M - /var/lib/mysql/atanua_boards/smf_log_mark_read.ibd

I was hoping to downsize my tables instead of making them grow!? Did I get this wrong?

Thanks
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: Something like that on June 24, 2011, 06:40:57 PM
InnoDB table space never shrinks. If you do an operation that ends up expanding the table space, the only way to shrink the space is to delete and re-create the table (or convert to MyISAM and back).
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: 青山 素子 on June 24, 2011, 07:32:51 PM
Quote from: samurai-lupin on June 24, 2011, 04:03:38 PM
I was hoping to downsize my tables instead of making them grow!? Did I get this wrong?

If you have a lot of inactive users that never view the board and a lot of topics and boards, your table size will increase as those users will now have entries in your database where they wouldn't before. For larger forums with lots of topics, you might see a size decrease, but the average forum will likely have no or even negative benefit.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: samurai-lupin on June 25, 2011, 10:02:57 AM
Hm, thank you for your replies. I do not understand then why it is explicitly recomended to use this script in the "24 Things" Topic when it will have negative impact on most boards. As far as I am concerned I now ended up with  the opposite that I had intended.

Is there a way to juist drop the entries from the log_topics, log_boards tables?
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: DeepBlueGXP on July 07, 2011, 12:31:21 PM
I get this error when trying to run this on SMF 1.1.14

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator, [email protected] and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.


--------------------------------------------------------------------------------

Apache mod_fcgid/2.3.6 mod_auth_passthrough/2.1 mod_bwlimited/1.4 FrontPage/5.0.2.2635 Server at www.kappaperformance.com Port 80
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: Illori on July 07, 2011, 12:32:43 PM
chmod the file 644
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: DeepBlueGXP on July 07, 2011, 12:39:42 PM
Quote from: Illori on July 07, 2011, 12:32:43 PM
chmod the file 644
Thanks, that fixed it :)
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: h78509 on September 07, 2011, 12:46:14 PM
Quote from: samurai-lupin on June 25, 2011, 10:02:57 AM
Hm, thank you for your replies. I do not understand then why it is explicitly recomended to use this script in the "24 Things" Topic when it will have negative impact on most boards. As far as I am concerned I now ended up with  the opposite that I had intended.

Is there a way to juist drop the entries from the log_topics, log_boards tables?
Same thing happened to me. I don't understand why there are no BIG RED LETTERS as a warning that this script could likely hurt the performance, not improve it.

Also, I'd like to ask the same question you just did.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: Mayhem30 on September 10, 2011, 09:12:18 PM
Quote from: R.Bourne on August 16, 2010, 09:43:43 PM
Thanks for the script.


FWIW, I've added it as cron job to run once a month :P

I thought you could only run this in a web browser .. am I wrong?
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: Illori on September 10, 2011, 09:37:12 PM
since php is a process you can run in the commandline you can setup a cron job to run the script automatically.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: HunterP 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?
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: Arantor 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.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: HunterP 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 :)
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: Arantor 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.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: LaurieC on February 17, 2013, 12:28:11 PM
Has anyone tried this with their 2.0.4 yet?
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: whoey 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
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: eliasr1990 on August 01, 2013, 04:07:56 PM
it works for me @ 2.0.4
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: postpar 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.)
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: FishingManMatt 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?
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: shawnb61 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.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: FishingManMatt on November 24, 2019, 12:55:24 PM
Many thanks, Shawn!

I will give it a try tonight and will post my results.
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: 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)
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: MobileCS 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.

(https://i.imgur.com/awmu7bN.png)


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!
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: shawnb61 on November 24, 2019, 11:48:26 PM
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.) 
Title: Re: smf_log_topics table HUGE (can't upgrade!)
Post by: FishingManMatt 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.

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.