Convert your tables to InnoDB

Started by [Unknown], September 20, 2005, 04:31:53 PM

Previous topic - Next topic

Ben_S

Liverpool FC Forum with 14 million+ posts.

mark7144

Are you sure?

I don't have lots of posts happening at a time but I often get loads of people refreshing particular topics as they get updated (like a tennis match).. so is it beneficial for that?

Also, what's the benefit for converting the search tables to InnoDB?

mrhope

Would converting tables to InnoDB resolve connection problems? Getting a lot of reports of users getting the below error randomly.

Connection Problems
Sorry, SMF was unable to connect to the database. This may be caused by the server being busy. Please try again later.

Running on a shared MySQL 5. 0 server, getting an average of 400-600 connected users and currently have over 60,000 topics and over 400,000 posts.


青山 素子

No, it probably wouldn't. You might want to try persistent connections if you are on a dedicated solution (VPS or true dedicated). Do not enable it on shared hosting, it causes more problems than it solves on that type.
Motoko-chan
Director, Simple Machines

Note: Unless otherwise stated, my posts are not representative of any official position or opinion of Simple Machines.


mark7144

I'm on a VPS but I thought persistent connections was a feature that generally everyone suggests not doing?

mark7144

I converted the tables outlined in the first post to InnoDB and on average it added 500ms to each page load, so certainly wasn't beneficial for me.

青山 素子

How big is your board? InnoDB isn't as fast as MyISAM on SELECT statements, but is much better for high-transaction environments. If you run a board that doesn't get a lot of traffic, you won't get any benefit from switching.
Motoko-chan
Director, Simple Machines

Note: Unless otherwise stated, my posts are not representative of any official position or opinion of Simple Machines.


mark7144

#167
My board only gets a lot of traffic at match times. So most of the time it has around 80 users (based on 30mins) on but when a match is on it can peak too 600+ users.

So my situation is quite tricky - do I optimise for the general browsing which lasts most of the time or just for the peak times? I think the latter is the one I want to focus on though.

poolhall

Quote from: mark7144 on September 13, 2008, 07:21:59 AM
...but when a match is on it can peak too 600+ users....

Do you mean a soccer, football, boxing match, etc? If so, I would make an educated guess that the vast majority of visitors during the peak are viewing the same topic. Is it correct assumption? If it's the case, then InnoDB would not be beneficial for your because per the  [Unknown]'s explanation given in the first post visitors will still have to wait for the topic table to become available.

mark7144

Quote from: poolhall on September 13, 2008, 03:10:35 PM
Quote from: mark7144 on September 13, 2008, 07:21:59 AM
...but when a match is on it can peak too 600+ users....

Do you mean a soccer, football, boxing match, etc? If so, I would make an educated guess that the vast majority of visitors during the peak are viewing the same topic. Is it correct assumption? If it's the case, then InnoDB would not be beneficial for your because per the  [Unknown]'s explanation given in the first post visitors will still have to wait for the topic table to become available.
Your exactly right, it's a tennis match and they all refresh the same topic. So thank you for explaining why InnoDB is not useful to me :)

Although, I assume changing some of the tables to InnoDB is good? Like doing search so that it doesn't freeze the server.

青山 素子

You might benefit a lot from getting caching set up. You'll have a lot less hits on the database then, and much less falling under load. In addition, you can always move up to a higher cache level if you see you need it during a match.
Motoko-chan
Director, Simple Machines

Note: Unless otherwise stated, my posts are not representative of any official position or opinion of Simple Machines.


mark7144

I already have APC set up with SMF and I have query cache for MySQL... I've also never seen less queries being used when upping the cache level in SMF so it seems useless to me.

青山 素子

Quote from: mark7144 on September 14, 2008, 06:32:05 AM
I've also never seen less queries being used when upping the cache level in SMF so it seems useless to me.

Did you make sure to set a cache level and make sure SMF is detecting the engine? It should give about an 80% reduction in queries with the stock SMF.

Also, if this goes a bit further off-topic, I'll probably split this out to a new topic.
Motoko-chan
Director, Simple Machines

Note: Unless otherwise stated, my posts are not representative of any official position or opinion of Simple Machines.


mark7144

#173
The cache works but I'm saying I don't see a difference in queries used between level 1 and level 3 caching.

toy9b

from my understanding convert some table into innoDB will not related to 1.1.6 that I just upgraded, right. So it is mean "it is save to convert to innoDB on 1.1.6"???

Please advice. I got 152 (143 sleeping, 7 running, 2 zombie) and experienced several db crashes for a week now.

Thanks

青山 素子

It is safe to convert to InnoDB on any SMF version. It will not help prevent tables from crashing. Crashed tables are almost always a server issue.
Motoko-chan
Director, Simple Machines

Note: Unless otherwise stated, my posts are not representative of any official position or opinion of Simple Machines.


toy9b


Mai Pen Rai

I have a forum (only 3 months old) with 28.000 posts by 1800 members and around 1.400.000 pageviews per month.

Should I change to InnoDB?
Mai Pen Rai - means "No worries" in Thai

Mai Pen Rai.se

青山 素子

Quote from: IQgeek on September 24, 2008, 12:57:19 PM
Should I change to InnoDB?

Are you having issues with the speed of operations involving things like database posts? Are you noticing lots of locked queries (if you have checked)?

If yes: You might want to look at converting some of your tables to InnoDB.
If no: Leave things alone.
Motoko-chan
Director, Simple Machines

Note: Unless otherwise stated, my posts are not representative of any official position or opinion of Simple Machines.


Mai Pen Rai

I think its a no then.

I am running the site on a dedicated server with loads of power, does this explain the "no loss of speed"?
But then again I don't know if the site would be even faster with innoDB unless I try it.

Because I did have some complaints at first, but after upgrading the RAM the complaints stopped.
Mai Pen Rai - means "No worries" in Thai

Mai Pen Rai.se

Advertisement: