Advertisement:

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

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #20 on: December 14, 2005, 06:01:06 AM »
Should be fine with 1.0x, obviously you will need to replace the pm_recipients with im_recipients, as for 1.1, most of it is fine, although log_search probably no longer exists.
Liverpool FC Forum with 14 million+ posts.

Offline sirwoogie

  • Semi-Newbie
  • *
  • Posts: 99
  • Gender: Male
    • BYOAC
Re: Convert your tables to InnoDB
« Reply #21 on: January 15, 2006, 11:18:57 AM »
Should be fine ... as for 1.1, most of it is fine, although log_search probably no longer exists.

Yes, it's split now into 5. My question is which table is most appropriate to convert now in the 1.1 area?

  • smf_log_search_messages
  • smf_log_search_results
  • smf_log_search_subjects
  • smf_log_search_topics
  • smf_log_search_words

Offline Joshua Dickerson

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,777
  • Gender: Male
    • joshuaadickerson on GitHub
    • joshuaadickerson on LinkedIn
Re: Convert your tables to InnoDB
« Reply #22 on: January 17, 2006, 09:57:17 PM »
All
Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

Offline Simplemachines Cowboy

  • Jr. Member
  • **
  • Posts: 354
  • KA-CLICK
    • The Open Range
Re: Convert your tables to InnoDB
« Reply #23 on: January 17, 2006, 10:13:05 PM »
This smoked my forum. Wicked fast now.
My SMF forum: The Open Range

Offline Joshua Dickerson

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,777
  • Gender: Male
    • joshuaadickerson on GitHub
    • joshuaadickerson on LinkedIn
Re: Convert your tables to InnoDB
« Reply #24 on: January 22, 2006, 12:47:49 PM »
Just as a note, there are tables that shouldn't be changed to InnoDB because they would be faster using MyISAM. For instance the 'boards' table. Unless you have 10,000 boards that are getting new posts 3 times a second. In which case, you are going to need a lot more than software changes.
Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

Offline Webrunner

  • Full Member
  • ***
  • Posts: 525
  • Gender: Male
    • Vrouwenpower
Re: Convert your tables to InnoDB
« Reply #25 on: January 23, 2006, 11:15:32 AM »
how about 100 boards getting new messages 3 times a second?
There is a difference between knowing the path and walking the path.

=========================================
Vrouwen Power! | Sprintweb: No nonsense e-Business consultancy

Offline akalaze

  • Newbie
  • *
  • Posts: 3
Re: Convert your tables to InnoDB
« Reply #26 on: January 23, 2006, 05:48:08 PM »
Thanks for this advice I did do the changes today and it made
a very big difference. I run a rather large adult board with an
average of 80 to 120 new members per day and about 2000
people constantly online.

The forum started to slow down a lot so I was looking for
improvements and found that thread ;)

Well it took a bit longer than 10 minutes (about 1 1/2 hours
but it was well worth it. Can't thank you enough!!!

akalaze

Offline Joshua Dickerson

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,777
  • Gender: Male
    • joshuaadickerson on GitHub
    • joshuaadickerson on LinkedIn
Re: Convert your tables to InnoDB
« Reply #27 on: January 23, 2006, 08:21:43 PM »
how about 100 boards getting new messages 3 times a second?
It might.
Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #28 on: January 23, 2006, 08:26:23 PM »
Although thats one busy forum, would love to see it.
Liverpool FC Forum with 14 million+ posts.

Offline ldk

  • Jr. Member
  • **
  • Posts: 332
  • Gender: Female
    • Craftster.org
Re: Convert your tables to InnoDB
« Reply #29 on: January 31, 2006, 03:28:56 PM »
Can someone just confirm how to "undo" this mySql command below?! I converted to InnoDB tables today it's killing my server for some reason. :'( So I know how to change the tables back to MyISAM but I don't know how to undo this command which I also ran:

i knew it... i asked for the second thingy that [unknown] posted :)
Code: [Select]
DROP TABLE smf_log_floodcontrol;
CREATE TABLE smf_log_floodcontrol (
  ip char(16) NOT NULL,
  logTime int(10) unsigned NOT NULL default '0',
  PRIMARY KEY (ip),
  KEY logTime (logTime)
) TYPE=HEAP;
see SMF put to the test at http://www.craftster.org/

Offline Joshua Dickerson

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,777
  • Gender: Male
    • joshuaadickerson on GitHub
    • joshuaadickerson on LinkedIn
Re: Convert your tables to InnoDB
« Reply #30 on: January 31, 2006, 06:52:11 PM »
Just change the table type to whatever you want.
Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

Offline jerm

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 7,250
Re: Convert your tables to InnoDB
« Reply #31 on: February 02, 2006, 04:18:52 PM »
do you think it would be possible for the list to be updated?
i have converted a bunch of my tables to InnoDB, but i want to make sure i hvae the correct ones done..

Offline kezayah

  • Jr. Member
  • **
  • Posts: 107
    • http://ze-forum.org
Re: Convert your tables to InnoDB
« Reply #32 on: February 07, 2006, 11:06:30 AM »
Why convert my tables to InnoDB ???
I tried that but it was very very slow, it's better with MyIsam

Offline H

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 21,662
  • Gender: Male
Re: Convert your tables to InnoDB
« Reply #33 on: February 07, 2006, 11:31:42 AM »
It would be nice if a dev or support member could update the first post with instructions that definitely work for 1.1 and 1.0.X
-H
Former Support Team Lead
                              I recommend:
Namecheap (domains)
Fastmail (e-mail)
Linode (VPS)
                             

Offline Webrunner

  • Full Member
  • ***
  • Posts: 525
  • Gender: Male
    • Vrouwenpower
Re: Convert your tables to InnoDB
« Reply #34 on: February 07, 2006, 05:27:08 PM »
i am noticing a lot more database errors and "strange bugs" since i switched them tables to innodb.
I am considering changing them back. :(
There is a difference between knowing the path and walking the path.

=========================================
Vrouwen Power! | Sprintweb: No nonsense e-Business consultancy

Offline Mike Bobbitt

  • Full Member
  • ***
  • Posts: 597
    • Army.ca
Re: Convert your tables to InnoDB
« Reply #35 on: February 07, 2006, 06:06:40 PM »
Check your database consistency:

Code: [Select]
mysqlcheck --password=[password] --auto-repair --compress --medium-check --optimize --all-databases
Might save you problems down the road if there are issues...

Offline Webrunner

  • Full Member
  • ***
  • Posts: 525
  • Gender: Male
    • Vrouwenpower
Re: Convert your tables to InnoDB
« Reply #36 on: February 08, 2006, 03:48:38 AM »
I just converted them back and did a myisamchk -r.
I see thst some indices aren't correctly set but those are from the standard SMF config.
Should i change those?
There is a difference between knowing the path and walking the path.

=========================================
Vrouwen Power! | Sprintweb: No nonsense e-Business consultancy

Offline Motorhead

  • Jr. Member
  • **
  • Posts: 220
  • Gender: Male
    • Steve's Offroad and Landrover Site
Re: Convert your tables to InnoDB
« Reply #37 on: February 16, 2006, 04:30:44 AM »
Would changing the tables like suggested above, put any greater load on a server..

I'm on a VPS and I've noticed my memory useage seems to have gone up and my server loads seem to have increased...this didn't happen straight after the change maybe a couple of days after, but I'm just trying to eliminate possible causes for my server loads


Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Convert your tables to InnoDB
« Reply #38 on: February 16, 2006, 11:35:34 AM »
Houston we have a problem:

I have a very large forum (over 1.400.000 posts/ 1.5 gb database on disk ), and I convert database to innodb to try o give it more perfomance.

Forums Version: 1.0.6.
Machine: P4 2.8 ht, hd 80 gb SATA 150, 1 Gb ram ECC. (Dell 1750)
Mysql version: 4.1.11a
S.O.: Debian sarge

But now the server are overload all time: Load average: 54.92 54.98 42.79


Any tip to tune the innodb ? I need to make it myisam?

I think that the problem es hard disk access.

Thanks in advance.

Offline Motorhead

  • Jr. Member
  • **
  • Posts: 220
  • Gender: Male
    • Steve's Offroad and Landrover Site
Re: Convert your tables to InnoDB
« Reply #39 on: February 16, 2006, 04:04:06 PM »
MMmm looks like InnoDB was casuing me higher server loads I've switched back to MyIsam and they seem to be lower

this bit though

Quote
DROP TABLE smf_log_floodcontrol;
CREATE TABLE smf_log_floodcontrol (
  ip char(16) NOT NULL,
  logTime int(10) unsigned NOT NULL default '0',
  PRIMARY KEY (ip),
  KEY logTime (logTime)
) TYPE=HEAP;

How do I lose it or revert back to whatever it changed ???