Using InnoDB exclusively

[tomreyn]

Hi,

I'd like to setup SMF 2.0.4 with all tables using the InnoDB storage engine. I tried this on a production system before but sadly this caused some functional issues and triggered mysql errors, too. Sadly I don't have those errors available now, but I assume this can be a known issue. If not, I'll be able to convert those tables back to InnoDB and thus generate more of those errors.

Currently, the following tables are set to MYISAM because of these issues:
smf_boards
smf_log_httpBL
smf_oasl_identities
smf_oasl_users

Were all of those using InnoDB then:

• it would be impossible to move boards around using the admin panel; whle their position would change on the admin preview, saving it would not actually change the board order displayed on the actual forum
• the httpBL mod would not be functional: users would not get blacklisted and it would not show any output on the admin panel

Am I describing a known issue, and are you aware of a workaround or, preferrably, fix for those when using InnoDB?
Should I convert those tables back to InnoDB to run into errors and report those here?

Update #1: fixed typo (aused -> caused)
Update #2: Here's the complete MySQL schema we're currently using: http://paste.debian.net/248355/

[Arantor]

smf_boards -> can be set to InnoDB if you check out the '24 things to do to make SMF faster' thread, whereupon the changes to the table are discussed (by making the board order effectively the actual PRIMARY key and then making id_board a UNIQUE key) which would solve the problems.

As far as the others, that would imply there is a bug with the httpBL mod, it's certainly nothing I've heard of before. The oasl tables I don't know about at all.

[tomreyn]

Thanks Arantor, that's very helpful again. I've modified the boards table as indicated on the performance topic and dropped the oasl_* tables which are of the "open social" mod which we're not currently using (apparently it's uninstall routine does not drop those tables).

So this leaves us only with log_httpBL for now. I'll see if I can get instructions on how to make it work with InnoDB on the mods' topic here on the SMF boards.

Update: My report on the httpBL issue is now posted at http://www.simplemachines.org/community/index.php?topic=366399.msg3522363

[tomreyn]

Actually there are more issues which were introduced with the switch to InnoDB. The MYSQL error log reports these warnings, all of which refer to the smf_log_* tables:

Code Select Expand

Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.

We'd indeed like to use the binary log as part of MySQL file backups (using xtrabackup), so it'd mean a lot to me to be able to iron these out.

Here's a complete stack of those warnings: http://paste.debian.net/hidden/5aa7f15c/

[Arantor]

What type of search are you using?

[tomreyn]

A "custom index" currently sized 125,680 KB. It was recreated after switching to InnoDB. On the "search method" box, both options are unchecked.

(This CAPTCHA is driving me crazy.)

[Arantor]

The CAPTCHA goes away once you hit 10 posts.

Hmm, that's curious. I don't know why it shouldn't work - but it also makes me wonder if you shouldn't consider a move to something like Sphinx.

[tomreyn]

Can you point me to more information on Sphinx and how to integrate it? I've searched the boards here and find a couple references to the "bug boards" forums, but I can't seem those anywhere, maybe they have been archived since?

One more post now. ;-)

[Arantor]

The big boards haven't been archived. They're just not publicly accessible unless you apply, for reasons I have yet to understand.

[tomreyn]

Hmm, this is non-obvious to me, too. Do you know how to apply for access to this area?

[Arantor]

Profile > Group Membership

Yes, it's very obvious. >_<

[tomreyn]

Mildly.
Sadly we have only 78,002 posts on http://forum.megaglest.org currently, so we don't qualify, and I guess I'm stuck with the half-broken search.

[Arantor]

Hmmm. I'd ask anyway, pointing to this thread. Right now search is broken, I don't know why it's broken and it seems to me that switching to Sphinx is the best way forward (and it's more future-proof as you grow)

One thing I would add... http://www.simplemachines.org/community/index.php?topic=293441.0 doesn't mention that all tables should be converted - in fact at the time of writing, the author was using a multi-million post forum while still keeping MyISAM tables in some places. Going all InnoDB is not necessarily any kind of magic bullet.

It is entirely possible that SMF needs code changes to make it work properly

[tomreyn]

Sure, I don't expect InnoDB to work as a magic bullet. I guess that with the current DB scheme (which was written for MYISAM) it may well make things a bit slower here and there (and faster elsewhere). However, it scales, which MYISAM clearly does not.

[Arantor]

MyISAM can scale given the right workload. So can InnoDB, again given the right workload.

[Deaks]

tomreyn, do you still need assistance? if so can you tell us what you have tried, I have marked this as solved as you have not been on in 2 weeks

[Kindred]

Runic,

He needs access to the Sphinx details...  Did he ever submit a request?

[tomreyn]

Sorry for not replying here back in April, I was new to this forum then and assumed that I'd receive e-mail notifications (by default) when someone replies to topics I started.

As an update, we have now set all tables to InnoDB (thanks to help by Arantor, if I recall correctly), with the notable exceptions of:

smf_log_floodcontrol: "MEMORY"
smf_log_httpBL: MyISAM (since it would return critical errors when set to InnoDB, but this is not a default table)

The forum is rather slow, some requests even time out. I assume this is due to http_BL and limited RAM (4 GB shared between web hosting and database), though. It surely was faster on MyISAM, but I technically prefer InnoDB.