fastest way to convert large IPB tables over to innodb?

[MarcusInMd]

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.

[Guest]

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.

[MarcusInMd]

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.

[MarcusInMd]

Tried to start the conversion tonight and got the following error message:

ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

after typing

ALTER TABLE ibf_posts ENGINE=InnoDB;

from the mysql command line.


How can I proceed?

[Dhillon]

In Admincp > Search set-up , Disable fulltext searching and try again.

[MarcusInMd]

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.

[phinsup]

I get this same error even though full text searching is disabled and I am using sphinx



ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

[phinsup]

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)

[MarcusInMd]

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.

[phinsup]

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

[MarcusInMd]

That's great to hear. I hope that my table locks with 1000+ plus hitting our DB server hard will be a thing of the past once we switch over to innodb.

[Clickfinity]

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:

[phinsup]

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.