PranK_merged Posted November 11, 2007 Share Posted November 11, 2007 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 Link to comment Share on other sites More sharing options...
AndyF Posted November 11, 2007 Share Posted November 11, 2007 Moving topic to Server Management, Resources, Optimization :) Link to comment Share on other sites More sharing options...
bfarber Posted November 12, 2007 Share Posted November 12, 2007 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. Link to comment Share on other sites More sharing options...
bfarber Posted November 12, 2007 Share Posted November 12, 2007 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. Link to comment Share on other sites More sharing options...
AtariAge Posted November 16, 2007 Share Posted November 16, 2007 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 Link to comment Share on other sites More sharing options...
PranK_merged Posted November 21, 2007 Share Posted November 21, 2007 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! Link to comment Share on other sites More sharing options...
Bobsuch Posted November 21, 2007 Share Posted November 21, 2007 ^ Prank, So you had sphinx already installed, and converting the tables to Innodb lowered the load? Link to comment Share on other sites More sharing options...
PranK_merged Posted November 21, 2007 Share Posted November 21, 2007 ^ 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 Link to comment Share on other sites More sharing options...
Bobsuch Posted November 21, 2007 Share Posted November 21, 2007 Would converting from Innodb --> MyISAM or MyISAM --> Innodb cause any side effecst? Link to comment Share on other sites More sharing options...
PranK_merged Posted November 21, 2007 Share Posted November 21, 2007 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 Link to comment Share on other sites More sharing options...
PranK_merged Posted November 21, 2007 Share Posted November 21, 2007 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 Link to comment Share on other sites More sharing options...
bfarber Posted November 21, 2007 Share Posted November 21, 2007 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. Link to comment Share on other sites More sharing options...
PranK_merged Posted December 2, 2007 Share Posted December 2, 2007 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.