Help With Table Level Locking

[PranK_merged]

I am seriously considering converting ibf_posts, ibf_topics and some others to Innodb as over the past few days I have had to restart mysqld a dozen times due to SHOW PROCESSLIST showing 60 or 70 queries locked...

While I understand that a lot of you think that MyISAM should be the engine for IPB I cant see how to get around the locks while using it.

I'd love some suggestions or help.

SPECS;
Dual, dual core Xeon
2GB memory
4 x SAS 10k drives (Raid 10)

Board has 3.4M posts, 35K members. Averages 300 online at a time (spiking to 500ish).

Thanks in advance.

Christian

[AndyF]

Moving topic to Server Management, Resources, Optimization :)

[bfarber]

I don't think 2GB is quote enough to cover 3.4M posts. With table-level locking (as you've seen) all it would take is one expensive query (say for example, a search for "home" or some term that would come up a lot on your site) that would cause any other table querying against posts, and subsequently members, and subsequently sessions (and so on) to lock.

2 main suggestions I would give are (and I assume you have a dedicated server)

1) Put (at least) 2 more GB of memory in the box (this is assuming it's a one time fee for your host, and not some monthly cost - hosts differ on this). Update my.cnf afterwards to reflect settings for the increased amount of memory.

2) Install Sphinx search engine and set IPB to use that.

[bfarber]

Oh, and if you install Sphinx, you an safely set ibf_posts and ibf_topics to InnoDB. The reason we suggest MyISAM is for fulltext searching, which is more efficient than Manual searching. However Sphinx indexes the posts separately and doesn't care what engine you use.

[AtariAge]

Oh, and if you install Sphinx, you an safely set ibf_posts and ibf_topics to InnoDB. The reason we suggest MyISAM is for fulltext searching, which is more efficient than Manual searching. However Sphinx indexes the posts separately and doesn't care what engine you use.

I just installed Sphinx. Would it be advantageous for me to convert ibf_posts and ibf_topics to InnoDB? I have 1.4 million posts. Will it take a long time for MySQL to convert the tables (especially ibf_posts)?

Thanks,

..Al

[PranK_merged]

Hi bfarber, I only just saw the responses here. Sorry for not replying earlier.

I wen ahead and changed a few tables to Innodb after posting this issue (ibf_messages, ibf_attachments, ibf_topics + others). I've had sphinx running for quite a while now, so that was no biggie. I havent converted ibf_posts yet because I havent hacked the board to use sphinx for the 'new posts / posts like' features yet, but I plan to do that soon.

Either way, with those few tables as Innodb, load has almost disappeared.

Thanks for the help!

[Bobsuch]

^ Prank,

So you had sphinx already installed, and converting the tables to Innodb lowered the load?

[PranK_merged]

^ Prank,

So you had sphinx already installed, and converting the tables to Innodb lowered the load?

Innodb has been a godsend! It has helped dramatically. I just need to convert ibf_posts and the other tables now.

Christian

[Bobsuch]

Would converting from Innodb --> MyISAM or MyISAM --> Innodb cause any side effecst?

[PranK_merged]

I havent seen any.
You'll need to change some innodb specific variables in my.cnf (or your host will) and keep in mind that innodb uses approx 3 times the disk space than ISAM and it also takes forEVER to reload from a dump.

Christian

[PranK_merged]

I keep seeing this query in MyTop and it takes a LONG time to run!

Query SELECT msg_id FROM ibf_message_text WHERE msg_deleted_count >= msg_sent_to_count

[bfarber]

I just installed Sphinx. Would it be advantageous for me to convert ibf_posts and ibf_topics to InnoDB? I have 1.4 million posts. Will it take a long time for MySQL to convert the tables (especially ibf_posts)?

Thanks,

..Al

Yes, it will take a long time. With 1.4 million posts probably about 30 minutes to an hour, and no one can access the site during that time. Don't try it from phpmyadmin, if you can avoid it.

Would converting from Innodb --> MyISAM or MyISAM --> Innodb cause any side effecst?

See Prank's response. Also, if you don't use sphinx, you would have to use manual search on an innodb table set.

I havent seen any.

You'll need to change some innodb specific variables in my.cnf (or your host will) and keep in mind that innodb uses approx 3 times the disk space than ISAM and it also takes forEVER to reload from a dump.

Christian

Good points to note. ;) Especially the 3x the disk space.

I keep seeing this query in MyTop and it takes a LONG time to run!

Query SELECT msg_id FROM ibf_message_text WHERE msg_deleted_count >= msg_sent_to_count

That query is run when a PM is deleted, as I recall. I've seen it show up in slow query logs too.

[PranK_merged]

I'm a slacker with these responses, sorry.
bfarber, thanks again for the help - I am planning on doing the innodb conversion to ibf_posts tonight, so i'll report my progress. :)

Christian