News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Convert your tables to InnoDB

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

Previous topic - Next topic

Ben_S

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.

sirwoogie

Quote from: Ben_S on December 14, 2005, 06:01:06 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

Joshua Dickerson

Come work with me at Promenade Group



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?

Simplemachines Cowboy

This smoked my forum. Wicked fast now.
My SMF forum: The Open Range

Joshua Dickerson

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.
Come work with me at Promenade Group



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?

Webrunner

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

akalaze

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

Joshua Dickerson

Come work with me at Promenade Group



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?

Ben_S

Although thats one busy forum, would love to see it.
Liverpool FC Forum with 14 million+ posts.

ldk

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:

Quote from: Elmacik on October 16, 2005, 04:08:56 AM
i knew it... i asked for the second thingy that [unknown] posted :)

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/

Joshua Dickerson

Just change the table type to whatever you want.
Come work with me at Promenade Group



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?

jerm

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..

kezayah

Why convert my tables to InnoDB ???
I tried that but it was very very slow, it's better with MyIsam

H

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)
                             

Webrunner

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

Mike Bobbitt

Check your database consistency:

mysqlcheck --password=[password] --auto-repair --compress --medium-check --optimize --all-databases

Might save you problems down the road if there are issues...

Webrunner

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

Motorhead

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


minskog

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.

Motorhead

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

QuoteDROP 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 ???




Advertisement: