Jump to content

fastest way to convert large IPB tables over to innodb?


Recommended Posts

Someone have the procedure handy?

IPB 3.x is just getting slammed and I need to try to get these tables to stop locking up.

Our post table is 2244mb in size and our topic table is 22mb in size. Close to 4 million posts.

Would like to do it the fastest way possible because our forum can't be down for any significant amount of time.

Link to comment
Share on other sites

There's only really one way to do it:

ALTER TABLE ibf_posts ENGINE=InnoDB



It may take a while, so you're probably best to run that from the MySQL command line.

Link to comment
Share on other sites

Define "a while" LOL. I was reading that you can do a mysqldump and change the header in that dump file and than reimport the file which would be much faster - but I am a bi weary of doing this for various reasons...like actually being able to edit the gigantic file once it's been exported.

Link to comment
Share on other sites

I fixed the above problem and now have a new one.


ERROR 2013 (HY000) at line 588: Lost connection to MySQL server during query


I am about to give up converting now. Our forum is running on one server again and I had nearly 1000 people with no table locks. It's gotta be the table size.

Link to comment
Share on other sites

  • 8 months later...

OK I solved this one, make sure you run a backup.

in mysql> ALTER TABLE ibf_posts DROP index `post`

Then you can run the innodb command

for topics

ALTER TABLE ibf_topics DROP index `title`

It took 22 minutes to drop Full Text index on the topics table, so be patient (and run it in screen)

Link to comment
Share on other sites

I still have not converted over yet. VERY SOON!

I did a dry run a few weeks ago and it went smoothly. I just backed up the database while the forum was offline and then did an import onto the DB server. It took several hours to import everything but it worked which is what I was looking for.

Link to comment
Share on other sites


So far so good for me. Everything is running quite well.




Have you noticed any improvement?

Either in speed, or in random delays loading pages?

I'm thinking of going down this route too. I've got a my.cnf that I'm reasonably happy with for the moment, memcached in place, additional 2GB in my server (4GB total), PHP running through FastCGI, but I'm still getting - what I can only describe as "stalling" - so wondered if it's a table locking issue?

Oh, and sorry to hijack the thread but could someone just run me through the steps I'd need to take to convert all my tables to InnoDB (with the exception of sessions - of course!!).

For example, would I need to change the value of this variable in conf_global.php ( $INFO['mysql_tbl_type'] ) to 'InnoDB'; ?

Or do I need to make any further changes to my.cnf?

Thanks,
Shaun :D

PS. I really didn't expect to put this much work in, but I'm sort of enjoying it in a perverse geeky kind of way ... lol :blush:
Link to comment
Share on other sites

The biggest improvement I have seen is no zombie php processes, I have no idea why switching fixed it.

Server loads are about the same, page loads are about the same, but replying, posting and especially deleting are MUCH faster. I was having a lot of issues deleting posts, often it would time out. Now it does it almost instantly.

So far I have yet to notice any drawbacks and there is a great deal of speed improvement or for that matter outright usability in the forum.

There are some settings you can add to your my.cnf, it's easiest to use mysqltuner.pl to run you through them, I made the changes it suggested but noticed not difference.

Nothing needs to be changed in your conf file, you just need to run the ALTER TABLE ibf_posts ENGINE=InnoDB; for each table you want to convert.


I only converted the ibf_posts and ibf_topics so far.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...