Your Database Engine of Choice for posts and topics tables?

[MarcusInMd]

We have a site with lots of traffic and since moving to IPB 3 have significant issues once we get up around 1700 users online at once with lots of concurrent posting/reading. Thinking of switching some of our production database tables over to InnoDB. Seems like posts and topics would be the ideal since these seem to lock up the most frequently and cause high server load issues.

I have heard mixed reviews on InnoDB when being used with IPB.

What do you use?

[MarcusInMd]

19 views and 1 vote?

What a joke!

[3DKiwi]

Perhaps we're not all geeks and wouldn't have a clue? All I know is my forum runs on MySQL.

How many of those users are guests? My guess is a fair chunk of them of them are search engines. Have you added a delay to your robots.txt file to slow them down? Some of us with busy forums found after upgrading to version 3 that search engines were going nuts indexing our sites.

3DKiwi

[MarcusInMd]

Perhaps we're not all geeks and wouldn't have a clue? All I know is my forum runs on MySQL.

How many of those users are guests? My guess is a fair chunk of them of them are search engines. Have you added a delay to your robots.txt file to slow them down? Some of us with busy forums found after upgrading to version 3 that search engines were going nuts indexing our sites.

3DKiwi

This is not really the case in our situation. About 1400 of them were registered members. Shame, I figured most that use this software have an idea of at least some of the technical attributes of the software and would at least know what database engine they are using. Guess not.

[Morrigan]

While some of us know, some of us also don't. I had to google the terms to have any idea of what you were even talking about. I just use the default but I don't have a board with a lot of constantly active members.

[bfarber]

I generally recommend myisam.

Now, I know some users with a lot of experience and really large boards swear by InnoDB. And that's fine. But in my experience, any gains you get from the row-level locking feature is offset by the simple fact that innodb is slower than myisam, no matter what way you cut it. Innodb tables are 3x the size of myisam tables (the physical files on disk) and as you can imagine, that means accessing the tables is naturally slower. Sure, there is row-level locking, but post and topic table locking is not *generally* problematic enough to see much gain by using innodb.

If you are seeing a lot of post table or topic table locking, I'd wonder what is causing it, specifically. Do you have any custom hooks, mods or applications installed? Things like "recent posts" on the board index would cause this sort of thing to happen, for instance (which is why we don't ship with such a hook).

You would also benefit from using sphinx search instead of fulltext searching, if you aren't already. This would give you much bigger performance gains than switching tables to innodb, in my opinion.

[Panupat]

Can you switch IPB to use other database than mySQL?

[MarcusInMd]

I generally recommend myisam.

Now, I know some users with a lot of experience and really large boards swear by InnoDB. And that's fine. But in my experience, any gains you get from the row-level locking feature is offset by the simple fact that innodb is slower than myisam, no matter what way you cut it. Innodb tables are 3x the size of myisam tables (the physical files on disk) and as you can imagine, that means accessing the tables is naturally slower. Sure, there is row-level locking, but post and topic table locking is not *generally* problematic enough to see much gain by using innodb.

If you are seeing a lot of post table or topic table locking, I'd wonder what is causing it, specifically. Do you have any custom hooks, mods or applications installed? Things like "recent posts" on the board index would cause this sort of thing to happen, for instance (which is why we don't ship with such a hook).

You would also benefit from using sphinx search instead of fulltext searching, if you aren't already. This would give you much bigger performance gains than switching tables to innodb, in my opinion.

Hey Brandon,
This is Marcus from eastern. :) I think we are out of options here and Innodb is worth a shot. I successfully converted over one of our backups on a test forum and it worked. Incidentally, the size on disk was actually just a little over twice the size as the MyIsam table.

I am going to probably do this conversion in the next few weeks but unless we have a serious hurricane season, we probably won't know the results of anything I do with the database until next winter unfortunately. :(

I am going to also redeploy our database server with an upgraded version of Mysql so hopefully the extra server will help as well next season. At the rate we are growing we will probably be pushing over 2000 in 15 minutes next winter. One server held up well except for the table locking issues.

[MarcusInMd]

Can you switch IPB to use other database than mySQL?

There is a driver for MS-SQL available from IPB for a fee. Several people are developing a Postgre driver and it works from what I hear but you can't convert your data over yet with it. Postgre would be ideal from everything I have read as it's supposed to be leaps and bounds above what Mysql is capable of.