13 replies to this topic

[♥ Adam ♠]

So which does Invision use InnoDB, MyISAM, a little of both, or other?

I ask as I'm tweaking things on my server and before we consider a move, we'd like to optimize things before hand.

We prefer Percona (http://www.percona.com/) which makes for a very optimized MySQL alternative.

I have found that even out of the box, before tweaking, it uses far less memory & cpu load. Percona is optimized more for InnoDB, but can still run all other function as MySQL would.

[mat206]

It uses MyISAM by default, but on my install everything was converted to InnoDB and it works much, much better.

[♥ Adam ♠]

It uses MyISAM by default, but on my install everything was converted to InnoDB and it works much, much better.

Wonders if the staff could comment on this ideal and how this would play out in 3.2

[Mark]

It depends on many factors, but MyISAM is usually the better choice for most tables.
On this site, we use MyISAM for everything apart from the sessions table, which uses memory.


Of course, if you're using Percona, which you say is optimized more for InnoDB - you may find that the better choice for you.

[yacenty]

we are using both, most tables are myissam but some (3 or 4) are innodb - after this switch performance is much better, but of course we need more disk space

[yacenty]

after switch from pure mysql to percona we get performance boost about 40%

[♥ Adam ♠]

after switch from pure mysql to percona we get performance boost about 40%

Glad my post made your consider the idea and thus was helpful

(Also was good to know that such benefits would work with IPB)

[yacenty]

we did it already long time ago

[bfarber]

In general, we find most sites run better under MyISAM. This is an off-the-cuff observation and not a hard and fast rule.

We recommend the sessions table be switched to memory engine because it will generally offer much better performance, and if you lose the contents in a shutdown it is not important. Of course this requires that your my.cnf file be configured properly to allow for this (certain config options need to be high enough to support the entire table in memory).

Many of our customers use innodb without issue, and will swear by it. With the right configuration, innodb will work just fine. IP.Board does not rely on nor expect any specific database engine to be in use, outside of the capability of FULLTEXT indexes on the posts and topics table. If you use innodb for these tables, we would usually recommend setting up and using Sphinx for searching, to offset this.

[.Nuno.]

after switch from pure mysql to percona we get performance boost about 40%

Hi,

Did you saw any performance using MYISAM in Percona?
Do I get and advantage if all my tables are MYISAM?

Thanks

[yacenty]

like I said before we already had 3 tables in innodb (users, post and images), we do not have any comparision between all myisam tables and some innodb tables,
it was really hard for my technical admin to belive that percona has such big improvment for us

[BigStamp]

Just to bring this back to the forefront, now that Innodb has full text searching, and its the default engine going forward.

Is MYISAM still the recommended default?

[bfarber]

Just to bring this back to the forefront, now that Innodb has full text searching, and its the default engine going forward.

Is MYISAM still the recommended default?

It is important to keep in mind that the vast majority of our customers do not update MySQL very often, so regardless of current changes, our recommendation remains the same (for now) because of the fact that most of our customers still use older versions of MySQL where the previous statements made still apply.

However, it should be noted that IP.Board works fine under either engine, assuming MySQL is configured properly. As I said before, many of our larger clients have been using InnoDB for years and will swear by it.

[⚔ Kirito]

Hang around in #MySQL on Freenode long enough and you'll have a bunch of people encouraging you to make the switch from MyISAM to InnoDB.

My site was experiencing a few performance issued on my old server. Mainly with posts and messages. You could submit a status update or post and it would "sit" there for a good while before actually posting at times. I belive this is what eventually caused members to repeatedly make accidental double posts.

I'm not entirely sure if this was related to MySQL or something else on my server, but since making the switch to a new box with a RAID 1 configuring I decided to make some changes.

(Some technical specs of my current dedicated server for comparison)

Quad Core E3-1230 @ 3.2GHz (3.6GHz with turbo boost)
8 GB REG ECC DDR3 RAM
Software RAID 1
Dual 2TB Enterprise Grade SATA II
25 TB Transfer (1 Gbps Uplink)
Debian 64 bit (Latest Stable)

I'm currently using the latest stable release of MariaDB on my new server instead of Debian's MySQL. I switched everything over from MyISAM to InnoDB, minus sessions which I switch to MEMORY. I upped the innodb_buffer_pool_size to 5GB (with my server having 8GB of memory) and made a few other small tweaks to things such as the innodb log file size, but haven't really dived into optimizing or fine tuning anything other than that.

I'm still using Apache, though I debated switching over to NGiNX a few times.

At best, I run a medium sized forum that barely gets any traffic in comparison to many of the site's out there. I have around 11,000 validated members and a few hundred thousand posts on my community.

As far as realistic traffic goes, this might offer a bit more insight:
 analytics.png   100.48K   57 downloads

Ever since making the transfer, the first thing I noticed was the initial reading and rendering of pages was slightly slower in comparison to MyISAM.

This piqued curiosity a bit, so I asked about this in MySQL earlier and found a bit of useful information on this.
Unlike MyISAM, InnoDB does not store an index cardinality value in its tables. Instead, InnoDB computes a cardinality for a table the first time it accesses it after startup. With a large number of tables, this might take significant time. It is the initial table open operation that is important, so to “warm up” a table for later use, access it immediately after startup by issuing a statement such as SELECT 1 FROM tbl_nameLIMIT 1.

So basically, InnoDB takes longer than MyISAM does to warm up. That's not really a realistic drawback, but it's something you're likely to notice if you make a switch.

After the MySQL server is left up and running for a bit, all connections made after that are still quite fast. There may be a marginal difference in reading speed in comparison to MyISAM (0.08 seconds vs. 0.1-0.1.1 seconds), but making posts on the site is now nearly instant. Everywhere. All the time. No lockups or hangups anywhere. Everything just posts instantly. So even if the reading speed is marginally slower than MyISAM on pages, the increase in writing performance has made it well worth the switch for me.

I also ended up setting Sphinx up on my server and am relatively happy with it as well. Though it has been stated that one of the main drawbacks of Sphinx is that it has no real-time search support, with Sphinx 2 being released (the documentation still refers to Sphinx 0.9), it claims to now ship "with real-time indexes support."

On that note, the 15 minute gap is really a non-issue for me. While others may absolutely need (or simply want) on the spot updated search results for their forum, I really don't. If my search results are only updated every 15 minutes, I'm fine with that.

All in all, I'm fine with IP.Board using MyISAM by default. The fact that it fully supports InnoDB is enough for me to be happy. I'm happy I'm able to freely play around with this stuff and find what best suits my needs and not have to simply rely on the defaults set in place if I don't want to.

On an unrelated note, I still have a bunch of PostgreSQL fanatics trying to bash me for using MySQL..