Simple Machines Community Forum

SMF Support => SMF 2.0.x Support => Topic started by: justpaulo on January 11, 2019, 01:54:19 PM

Title: Issue With Database Table Size
Post by: justpaulo 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?
Title: Re: Issue With Database Table Size
Post by: Illori 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.
Title: Re: Issue With Database Table Size
Post by: Aleksi "Lex" Kilpinen on January 11, 2019, 02:02:21 PM
How many members do you have? I mean, 200MB for that table is a whole lot.
Title: Re: Issue With Database Table Size
Post by: Arantor on January 11, 2019, 02:15:48 PM
Don’t forget custom user fields live there too.
Title: Re: Issue With Database Table Size
Post by: Aleksi "Lex" Kilpinen on January 11, 2019, 02:17:41 PM
Right, I did actually forget that - but still sounds like a lot.
Title: Re: Issue With Database Table Size
Post by: Illori on January 11, 2019, 02:21:42 PM
it also depends on how many themes are installed.
Title: Re: Issue With Database Table Size
Post by: Arantor on January 11, 2019, 03:01:37 PM
Yeah, themes x (Settings + custom fields) x users.
Title: Re: Issue With Database Table Size
Post by: Aleksi "Lex" Kilpinen 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.
Title: Re: Issue With Database Table Size
Post by: justpaulo 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?
Title: Re: Issue With Database Table Size
Post by: Aleksi "Lex" Kilpinen 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.
Title: Re: Issue With Database Table Size
Post by: Illori 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?
Title: Re: Issue With Database Table Size
Post by: justpaulo 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...
Title: Re: Issue With Database Table Size
Post by: Illori on January 14, 2019, 09:52:45 AM
how do you know this table may be causing an issue?
Title: Re: Issue With Database Table Size
Post by: justpaulo 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...
Title: Re: Issue With Database Table Size
Post by: Arantor 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.