Advertisement:

Author Topic: Issue With Database Table Size  (Read 583 times)

Offline justpaulo

  • Semi-Newbie
  • *
  • Posts: 60
Issue With Database Table Size
« on: January 11, 2019, 01:54:19 PM »
I just notice that on my SMF forum database I have a table with the name "Themes", this table alone have more than 200 MB and is probably slowing down my forum...

What is this table? And is it safe to empty this table?

Offline Illori

  • Project Manager
  • SMF Legend
  • *
  • Posts: 51,119
Re: Issue With Database Table Size
« Reply #1 on: January 11, 2019, 01:57:15 PM »
it has all the users settings in it, you should not clear that table or you will piss off your users.

Online Aleksi "Lex" Kilpinen

  • A Peculiar Finn
  • Lead Support Specialist
  • SMF Super Hero
  • *
  • Posts: 18,535
  • Gender: Male
  • Don't worry, I'm n00b friendly
    • Aleksi.Kilpinen on Facebook
    • LexArma on GitHub
    • aleksi-kilpinen on LinkedIn
    • There's No Place Like 127.0.0.1
Re: Issue With Database Table Size
« Reply #2 on: January 11, 2019, 02:02:21 PM »
How many members do you have? I mean, 200MB for that table is a whole lot.
A Finnish Support Specialist
 Happily running multiple SMF 2.0 installations.
  Fooling around with an i7 990X @ 3,47Ghz / 12Gb / Win 10 x64 / 3840x2160


How you can help SMF

"Before you allow people access to your forum, especially in an administrative position, you must be aware that that person can seriously damage your forum.
 Therefore, you should only allow people that you trust, implicitly, to have such access." -Douglas

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,367
    • StoryBB/StoryBB on GitHub
Re: Issue With Database Table Size
« Reply #3 on: January 11, 2019, 02:15:48 PM »
Don’t forget custom user fields live there too.
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.

Online Aleksi "Lex" Kilpinen

  • A Peculiar Finn
  • Lead Support Specialist
  • SMF Super Hero
  • *
  • Posts: 18,535
  • Gender: Male
  • Don't worry, I'm n00b friendly
    • Aleksi.Kilpinen on Facebook
    • LexArma on GitHub
    • aleksi-kilpinen on LinkedIn
    • There's No Place Like 127.0.0.1
Re: Issue With Database Table Size
« Reply #4 on: January 11, 2019, 02:17:41 PM »
Right, I did actually forget that - but still sounds like a lot.
A Finnish Support Specialist
 Happily running multiple SMF 2.0 installations.
  Fooling around with an i7 990X @ 3,47Ghz / 12Gb / Win 10 x64 / 3840x2160


How you can help SMF

"Before you allow people access to your forum, especially in an administrative position, you must be aware that that person can seriously damage your forum.
 Therefore, you should only allow people that you trust, implicitly, to have such access." -Douglas

Offline Illori

  • Project Manager
  • SMF Legend
  • *
  • Posts: 51,119
Re: Issue With Database Table Size
« Reply #5 on: January 11, 2019, 02:21:42 PM »
it also depends on how many themes are installed.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,367
    • StoryBB/StoryBB on GitHub
Re: Issue With Database Table Size
« Reply #6 on: January 11, 2019, 03:01:37 PM »
Yeah, themes x (Settings + custom fields) x users.
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.

Online Aleksi "Lex" Kilpinen

  • A Peculiar Finn
  • Lead Support Specialist
  • SMF Super Hero
  • *
  • Posts: 18,535
  • Gender: Male
  • Don't worry, I'm n00b friendly
    • Aleksi.Kilpinen on Facebook
    • LexArma on GitHub
    • aleksi-kilpinen on LinkedIn
    • There's No Place Like 127.0.0.1
Re: Issue With Database Table Size
« Reply #7 on: January 11, 2019, 03:05:17 PM »
Well, I might just be lucky with my themes table being around 1/100 of that. :D While my messages table is about 1,5Gb.
A Finnish Support Specialist
 Happily running multiple SMF 2.0 installations.
  Fooling around with an i7 990X @ 3,47Ghz / 12Gb / Win 10 x64 / 3840x2160


How you can help SMF

"Before you allow people access to your forum, especially in an administrative position, you must be aware that that person can seriously damage your forum.
 Therefore, you should only allow people that you trust, implicitly, to have such access." -Douglas

Offline justpaulo

  • Semi-Newbie
  • *
  • Posts: 60
Re: Issue With Database Table Size
« Reply #8 on: January 14, 2019, 09:09:19 AM »
How many members do you have? I mean, 200MB for that table is a whole lot.

I have currently around 1M users...

So Is there anything I can do to make this table more smaller?

Online Aleksi "Lex" Kilpinen

  • A Peculiar Finn
  • Lead Support Specialist
  • SMF Super Hero
  • *
  • Posts: 18,535
  • Gender: Male
  • Don't worry, I'm n00b friendly
    • Aleksi.Kilpinen on Facebook
    • LexArma on GitHub
    • aleksi-kilpinen on LinkedIn
    • There's No Place Like 127.0.0.1
Re: Issue With Database Table Size
« Reply #9 on: January 14, 2019, 09:14:49 AM »
Wow. That would explain it a bit.

Yeah, themes x (Settings + custom fields) x users.
That's about the best hint I can give you on size,
regarding performance on the other hand I'm sure someone else has a better idea than I do.
A Finnish Support Specialist
 Happily running multiple SMF 2.0 installations.
  Fooling around with an i7 990X @ 3,47Ghz / 12Gb / Win 10 x64 / 3840x2160


How you can help SMF

"Before you allow people access to your forum, especially in an administrative position, you must be aware that that person can seriously damage your forum.
 Therefore, you should only allow people that you trust, implicitly, to have such access." -Douglas

Offline Illori

  • Project Manager
  • SMF Legend
  • *
  • Posts: 51,119
Re: Issue With Database Table Size
« Reply #10 on: January 14, 2019, 09:18:19 AM »
how many themes do you have installed? of those how many do you allow your users to use?

Offline justpaulo

  • Semi-Newbie
  • *
  • Posts: 60
Re: Issue With Database Table Size
« Reply #11 on: January 14, 2019, 09:48:11 AM »
how many themes do you have installed? of those how many do you allow your users to use?

I'm only using one theme, so users also have one theme only...

Offline Illori

  • Project Manager
  • SMF Legend
  • *
  • Posts: 51,119
Re: Issue With Database Table Size
« Reply #12 on: January 14, 2019, 09:52:45 AM »
how do you know this table may be causing an issue?

Offline justpaulo

  • Semi-Newbie
  • *
  • Posts: 60
Re: Issue With Database Table Size
« Reply #13 on: January 15, 2019, 05:50:38 AM »
how do you know this table may be causing an issue?

I dont really know for sure, just guessing based on the table size since its bigger than the other tables...

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,367
    • StoryBB/StoryBB on GitHub
Re: Issue With Database Table Size
« Reply #14 on: January 15, 2019, 06:07:32 AM »
That’s the thing with performance, don’t guess at whether something is a problem, use profiling to establish if it is and what the problem really is.

A table being that big is not inherently a problem, however it might be worth looking at whether you want to house clean users because if you have a million users but your post table isn’t very big, you probably have a lot of inactive users or spammers that need cleaning out.
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.