smf_log_topics table HUGE (can't upgrade!)

[digit]

My server seems to be getting hammered...   my isp says...  (after recently getting my own server)...

On 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!

[digit]

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!

[karlbenson]

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

[digit]

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?

[karlbenson]

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.

[digit]

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?

[Ben_S]

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.

[digit]

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!!!!

[Ben_S]

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

[digit]

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.

[karlbenson]

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.

[vbgamer45]

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

[kizer]

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.

[karlbenson]

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.

[kizer]

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.

[metallica48423]

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?

[RobertMfromLI]

karlbenson,

Thank you!

-Robert

[matasanos]

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!

[Vekseid]

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?

[karlbenson]

The script marks every board as read for all users.
12825 * 28202