Advertisement:

Author Topic: Convert your tables to InnoDB  (Read 224408 times)

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #160 on: September 09, 2008, 07:28:32 PM »
Then do it.
Liverpool FC Forum with 14 million+ posts.

Offline mark7144

  • Sophist Member
  • *****
  • Posts: 1,174
Re: Convert your tables to InnoDB
« Reply #161 on: September 10, 2008, 05:38:11 AM »
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?

Offline mrhope

  • Semi-Newbie
  • *
  • Posts: 87
    • Computer Hope
Re: Convert your tables to InnoDB
« Reply #162 on: September 11, 2008, 06:55:11 AM »
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.


Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,074
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: Convert your tables to InnoDB
« Reply #163 on: September 11, 2008, 11:06:27 AM »
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.


Offline mark7144

  • Sophist Member
  • *****
  • Posts: 1,174
Re: Convert your tables to InnoDB
« Reply #164 on: September 11, 2008, 11:18:50 AM »
I'm on a VPS but I thought persistent connections was a feature that generally everyone suggests not doing?

Offline mark7144

  • Sophist Member
  • *****
  • Posts: 1,174
Re: Convert your tables to InnoDB
« Reply #165 on: September 12, 2008, 08:24:11 PM »
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.

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,074
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: Convert your tables to InnoDB
« Reply #166 on: September 12, 2008, 09:18:36 PM »
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.


Offline mark7144

  • Sophist Member
  • *****
  • Posts: 1,174
Re: Convert your tables to InnoDB
« Reply #167 on: September 13, 2008, 07:21:59 AM »
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.
« Last Edit: September 13, 2008, 07:33:57 AM by mark7144 »

Offline poolhall

  • Jr. Member
  • **
  • Posts: 316
Re: Convert your tables to InnoDB
« Reply #168 on: September 13, 2008, 03:10:35 PM »
...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.

Offline mark7144

  • Sophist Member
  • *****
  • Posts: 1,174
Re: Convert your tables to InnoDB
« Reply #169 on: September 13, 2008, 04:47:43 PM »
...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.

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,074
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: Convert your tables to InnoDB
« Reply #170 on: September 13, 2008, 10:01:02 PM »
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.


Offline mark7144

  • Sophist Member
  • *****
  • Posts: 1,174
Re: Convert your tables to InnoDB
« Reply #171 on: September 14, 2008, 06:32:05 AM »
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.

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,074
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: Convert your tables to InnoDB
« Reply #172 on: September 14, 2008, 01:20:20 PM »
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.


Offline mark7144

  • Sophist Member
  • *****
  • Posts: 1,174
Re: Convert your tables to InnoDB
« Reply #173 on: September 14, 2008, 02:02:02 PM »
The cache works but I'm saying I don't see a difference in queries used between level 1 and level 3 caching.
« Last Edit: September 14, 2008, 02:03:38 PM by mark7144 »

Offline toy9b

  • Semi-Newbie
  • *
  • Posts: 11
Re: Convert your tables to InnoDB
« Reply #174 on: September 18, 2008, 10:01:02 PM »
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

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,074
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: Convert your tables to InnoDB
« Reply #175 on: September 18, 2008, 10:46:53 PM »
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.


Offline toy9b

  • Semi-Newbie
  • *
  • Posts: 11
Re: Convert your tables to InnoDB
« Reply #176 on: September 18, 2008, 10:51:45 PM »
thanks Motoko-chan

Offline Mai Pen Rai

  • Semi-Newbie
  • *
  • Posts: 49
    • Mai Pen Rai.se
Re: Convert your tables to InnoDB
« Reply #177 on: September 24, 2008, 12:57:19 PM »
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

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,074
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: Convert your tables to InnoDB
« Reply #178 on: September 24, 2008, 01:07:02 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.


Offline Mai Pen Rai

  • Semi-Newbie
  • *
  • Posts: 49
    • Mai Pen Rai.se
Re: Convert your tables to InnoDB
« Reply #179 on: September 24, 2008, 01:19:06 PM »
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