Issue With Database Table Size

Started by justpaulo, January 11, 2019, 01:54:19 PM

Previous topic - Next topic

justpaulo

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?

Illori

it has all the users settings in it, you should not clear that table or you will piss off your users.

Aleksi "Lex" Kilpinen

How many members do you have? I mean, 200MB for that table is a whole lot.
Slava
Ukraini!


"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

How you can help SMF

Arantor

Don't forget custom user fields live there too.

Aleksi "Lex" Kilpinen

Right, I did actually forget that - but still sounds like a lot.
Slava
Ukraini!


"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

How you can help SMF

Illori

it also depends on how many themes are installed.

Arantor

Yeah, themes x (Settings + custom fields) x users.

Aleksi "Lex" Kilpinen

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.
Slava
Ukraini!


"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

How you can help SMF

justpaulo

Quote from: 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.

I have currently around 1M users...

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

Aleksi "Lex" Kilpinen

Wow. That would explain it a bit.

Quote from: Arantor on January 11, 2019, 03:01:37 PM
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.
Slava
Ukraini!


"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

How you can help SMF

Illori

how many themes do you have installed? of those how many do you allow your users to use?

justpaulo

Quote from: 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?

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

Illori

how do you know this table may be causing an issue?

justpaulo

Quote from: Illori on January 14, 2019, 09:52:45 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...

Arantor

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.

Advertisement: