Move to Innodb.....!

[Gauravk]

After spending almost two weeks in research and discussing my slow page load. VPS provider Godaddy blames IPB codes, IPB blames server (never ending chicken & egg story).

I have shortlisted two servers one with SSD in EU to serve faster pages in Dubai, second in Dubai but w/o SSD. Before I move on I want to try converting the tables to Innodb as that is the only option has changed from my previous PHPBB setup with same post, topics, traffic and server as it was on Innodb and new IPB is with MyIsam. Regarding the conversion please help answering the below concerns. Thanks in advance.

1. How to convert thru SSH (as i read in forums, most recommended way is thru SSH)
2. Any serious technical knowledge involved for this conversion?
3. Any loss of features (like similar topics) after converting to Innodb?
4. Is it safe to convert?
5. Is it reversible?
6. How much gain on speed I can expect about 50% lesser load/wait time for MySQL processing....?

[RevengeFNF]

1. I don't know, but i guess google can awser that.
2. I don't think so.
3. If you are on mysql 5.6, no problem because it supports fulltext search. If you are on Mysql 5.5 or older, innodb don't have fulltext search, that means you will loose the ability to make searchs on the board. You have 2 solutions. Upgrade your Mysql to 5,6, or install Sphinx. My personal opinion, is to install Sphinx, cause is much, much better.
4. Yes, its safe.
5. Yes, its reversible.
6. It depends, Innodb is faster in writes and a bit slower in reads. But the main advantage is that it doesn't have Table locks. Only row locks. That means if you have a busy server, with myisam you probably will have a lot of table locks, so with innodb you will gain much.

[Gauravk]

Thanks dude,

Will give it a go tonite, found this code to convert them: ALTER TABLE table_name ENGINE = InnoDB;

Only problem is I need to run above code for 200 times, any faster way exists to batch convert all tables in specified database....? Thanks.

[Aiwa]

Just FYI, while MySQL 5.6 supports fulltext searching with InnoDB, IP.Board does not... If you set your tables to InnoDB, IP.Board disables fulltext searching.

[Grumpy]

Hello again. Thought we established godaddy one sucks independent of the IPB... so, kinda silly for them to play the blame game. Though to the question of is phpbb lighter than ipb? maybe.

Anyway... to your questions.

1.

ALTER TABLE `table_name` ENGINE = INNODB;

is the actual code to convert a singlet able. You can pre-build the entire list of table names like...

ALTER TABLE `table_name1` ENGINE = INNODB;
ALTER TABLE `table_name2` ENGINE = INNODB;
ALTER TABLE `table_name3` ENGINE = INNODB;
...

Save all that into a file. And then you can run "mysql -u username -p databasename < filenamethatcontainsthesqlcommands.sql" in SSH.

You don't actually need to convert all tables to innodb either. It's fine to mix & match. Just convert tables that are frequently written to / ones that frequent table lock. In the realm of pure fetching, myisam is teeny bitty faster.

Also, sessions table should be MEMORY for even better than innodb. But even more so, rather than optimizing mysql, you're better off reducing usage of mysql by using user cache features unless you really are hitting myisam-dependent bottlenecks like table locks.

2. Not really. You can also do it via phpmyadmin if you want to be slightly friendlier.

3. You will lose full text search with your probable version (<5.6?). In any case, setting IPB to innodb in config will turn off full text search as well. If you want the best of both worlds, you need sphinx to do your search for you. It's better at pulling relevant results anyway.

4. No. It is not safe. Low chance, but risk does exist. You're doing a lot of writes and lot of reads. It's more prone to error than smaller transactions since the probability is that much higher... Though, you probably won't notice the minor errors even if it does happen. Backup first. And I suggest you temporarily turn off your site first. The conversion will take a long time if your site is big.

5. Yes. Run the same command as above but with ...ENGINE = MYISAM;

Alternatively, restore from your backup.

6. 50%? Nope. Installing user cache & sphinx probably would likely reduce mysql usage by over 50% though. Innodb is not a cure all solution. It only has the capability to remove myisam based bottlenecks, if you are facing them. You still have all the other problems. Optimization isn't a 1 step solution. It's a long and arduous process, but something very much worth it. Even if you do move and the new one has awesome performance, I still suggest you to optimize as much as you can. Faster is better & you get a better bang for your buck.

[Dmacleo]

3. You will lose full text search with your probable version (<5.6?). In any case, setting IPB to innodb in config will turn off full text search as well. If you want the best of both worlds, you need sphinx to do your search for you. It's better at pulling relevant results anyway.

while I am the farthest thing from a sphinx expert there is, I've been using for years and it is SO much better.

I deal with a lot of acronyms and 2 letter searches, as well as wildcard, and sphinx just works much better.

[Makoto]

Just so I know I'm not giving bad advice, is the MySQL engine config variable used as anything other than a FTS safety check? As far as I can remember, that's literally all it's for. If it's set to InnoDB, it doesn't let you use FTS, even if it's enabled in the ACP. If it's set to MyISAM, it will let you use FTS regardless of your engine types.

So by creating the fulltext indexes on your InnoDB tables and tricking IPB into thinking you're using MyISAM, FTS should function properly*. If this setting is used for anything else important, I would be interested in knowing what that is.

* To the extent that InnoDB's current FTS implementation actually functions. I've found at least one bug when using it.

---

Regardless of this, if you can use Sphinx, use it. Don't bother with fulltext searching.

[p4guru]

Just so I know I'm not giving bad advice, is the MySQL engine config variable used as anything other than a FTS safety check? As far as I can remember, that's literally all it's for. If it's set to InnoDB, it doesn't let you use FTS, even if it's enabled in the ACP. If it's set to MyISAM, it will let you use FTS regardless of your engine types.

So by creating the fulltext indexes on your InnoDB tables and tricking IPB into thinking you're using MyISAM, FTS should function properly*. If this setting is used for anything else important, I would be interested in knowing what that is.

* To the extent that InnoDB's current FTS implementation actually functions. I've found at least one bug when using it.

---

Regardless of this, if you can use Sphinx, use it. Don't bother with fulltext searching.

Why not edit the IP.Board detection code instead of the mysql config ?

[Grumpy]

... is the MySQL engine config variable used as anything other than a FTS safety check? As far as I can remember, that's literally all it's for.

Well. There is one more use, if that counts...

When creating new tables with upgrader/installer, it will make the tables with that engine.

Also, if unset in config, it will default to myisam.

I'm pretty sure that's the entire usage of it. Checked vs code base.

[Makoto]

Why not edit the IP.Board detection code instead of the mysql config ?

We're talking about the mysql_tbl_type setting in conf_global.php. Eg;

$INFO['mysql_tbl_type']                 =       'INNODB';

Well. There is one more use, if that counts...
When creating new tables with upgrader/installer, it will make the tables with that engine.

Also, if unset in config, it will default to myisam.

I'm pretty sure that's the entire usage of it. Checked vs code base.

Thanks, so setting it to MyISAM to force FTS support should be perfectly fine I imagine, Just be sure to check for tables that need to be updated when installing new applications.

[p4guru]

doh should of re-read the posts, thought you mean within my.cnf heh