Advertisement:
NameCheap

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

Offline digit

  • Sophist Member
  • *****
  • Posts: 1,364
  • Gender: Male
smf_log_topics table HUGE (can't upgrade!)
« 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
Quote
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!



« Last Edit: February 04, 2011, 07:02:30 AM by digit »
Happily using a heavily modified 1.1.16 version of SMF!

2748011 Posts in 320998 Topics by 50986 Members


SOLD my website - thanks it was a good run - they converted to vbadvanced. (and screwed it up good!)

Offline digit

  • Sophist Member
  • *****
  • Posts: 1,364
  • Gender: Male
Re: smf_log_topics table HUGE (causing MY slow downs?)
« Reply #1 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!


Happily using a heavily modified 1.1.16 version of SMF!

2748011 Posts in 320998 Topics by 50986 Members


SOLD my website - thanks it was a good run - they converted to vbadvanced. (and screwed it up good!)

Offline karlbenson

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 15,584
  • Gender: Male
    • @mortonssols on Twitter
    • Criminal Solicitors
Re: smf_log_topics table HUGE (causing MY slow downs?)
« Reply #2 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
« Last Edit: December 21, 2007, 01:03:15 PM by karlbenson »

Offline digit

  • Sophist Member
  • *****
  • Posts: 1,364
  • Gender: Male
Re: smf_log_topics table HUGE (causing MY slow downs?)
« Reply #3 on: December 21, 2007, 01:01:01 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?
Happily using a heavily modified 1.1.16 version of SMF!

2748011 Posts in 320998 Topics by 50986 Members


SOLD my website - thanks it was a good run - they converted to vbadvanced. (and screwed it up good!)

Offline karlbenson

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 15,584
  • Gender: Male
    • @mortonssols on Twitter
    • Criminal Solicitors
Re: smf_log_topics table HUGE (causing MY slow downs?)
« Reply #4 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.

Offline digit

  • Sophist Member
  • *****
  • Posts: 1,364
  • Gender: Male
Re: smf_log_topics table HUGE (causing MY slow downs?)
« Reply #5 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?
« Last Edit: December 21, 2007, 05:16:57 PM by digit »
Happily using a heavily modified 1.1.16 version of SMF!

2748011 Posts in 320998 Topics by 50986 Members


SOLD my website - thanks it was a good run - they converted to vbadvanced. (and screwed it up good!)

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,678
  • xxx
Re: smf_log_topics table HUGE (causing MY slow downs?)
« Reply #6 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.
Liverpool FC Forum with 9 million+ posts.

Offline digit

  • Sophist Member
  • *****
  • Posts: 1,364
  • Gender: Male
Re: smf_log_topics table HUGE (causing MY slow downs?)
« Reply #7 on: December 22, 2007, 09:04:32 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!!!!
« Last Edit: December 22, 2007, 09:06:36 AM by digit »
Happily using a heavily modified 1.1.16 version of SMF!

2748011 Posts in 320998 Topics by 50986 Members


SOLD my website - thanks it was a good run - they converted to vbadvanced. (and screwed it up good!)

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,678
  • xxx
Re: smf_log_topics table HUGE (causing MY slow downs?)
« Reply #8 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 :(
Liverpool FC Forum with 9 million+ posts.

Offline digit

  • Sophist Member
  • *****
  • Posts: 1,364
  • Gender: Male
Re: smf_log_topics table HUGE (causing MY slow downs?)
« Reply #9 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.


Happily using a heavily modified 1.1.16 version of SMF!

2748011 Posts in 320998 Topics by 50986 Members


SOLD my website - thanks it was a good run - they converted to vbadvanced. (and screwed it up good!)

Offline karlbenson

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 15,584
  • Gender: Male
    • @mortonssols on Twitter
    • Criminal Solicitors
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #10 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.
« Last Edit: August 19, 2008, 11:32:44 AM by karlbenson »

Offline vbgamer45

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 16,477
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #11 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
Community Suite - Take your forum to the next level built for SMF, Gallery,Store,Classfieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Latest Mod:
EzPortal - Portal System for SMF
Newsletter Pro SMF Gallery Pro SMF Classifieds SMF Store

Offline kizer

  • Jr. Member
  • **
  • Posts: 383
  • Gender: Male
    • Links4Jeeps.com
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #12 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.
« Last Edit: August 21, 2008, 07:39:57 PM by kizer »
Own a Jeep? Links4Jeeps.com

Offline karlbenson

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 15,584
  • Gender: Male
    • @mortonssols on Twitter
    • Criminal Solicitors
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #13 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.


Offline kizer

  • Jr. Member
  • **
  • Posts: 383
  • Gender: Male
    • Links4Jeeps.com
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #14 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.
Own a Jeep? Links4Jeeps.com

Offline metallica48423

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 19,842
  • Gender: Male
  • Professional Multislacker!
    • Zentendo
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #15 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?
Justin O'Leary
Ex-Project Manager
Ex-Lead Support Specialist

Quote
Microsoft wants us to "Imagine life without walls"...
I say, "If there are no walls, who needs Windows?"

Useful Links:
Online Manual!
How to Help us Help you   
Search
Settings Repair Tool
     

Offline RobertMfromLI

  • Jr. Member
  • **
  • Posts: 112
  • Gender: Male
  • Clueless since 1968...
    • Star Trek New Voyages: Kirk's Five Year Mission Continues
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #16 on: February 16, 2009, 03:44:40 PM »
karlbenson,

Thank you!

-Robert
Star Trek New Voyages: Kirk's Five Year Mission Continues
Line Producer - Webmaster - Forum Admin - Contributing Producer - Gaffer

Star Trek Phase 2 - Enemy: Starfleet Released!

Offline matasanos

  • Jr. Member
  • **
  • Posts: 267
    • Lords-Beer.net
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #17 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!

Offline Vekseid

  • Full Member
  • ***
  • Posts: 549
  • Gender: Male
    • Elliquiy Adult Role Playing Forums
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #18 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?
Adult Role Playing Forums - - Over five million posts - - Elliquiy's LAMP configuration (maybe NSFW)

Blog about Forums and Servers - - Twenty things to make Simple Machines Forum go faster

Private/Instant Message requests for free support will be ignored.

Offline karlbenson

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 15,584
  • Gender: Male
    • @mortonssols on Twitter
    • Criminal Solicitors
Re: smf_log_topics table HUGE (can't upgrade!)
« Reply #19 on: February 16, 2009, 04:35:53 PM »
The script marks every board as read for all users.
12825 * 28202