Invision Community 4: SEO, prepare for v5 and dormant account notifications By Matt Monday at 02:04 PM
Vroom Posted June 12, 2015 Posted June 12, 2015 Hi, I just upgraded one of our forums to 4.0. There are 13 million posts. After the upgrade we are getting constant table locks causing the pages not to load for several minutes. Then again it is fast as usual till the next table lock hits. For example, right now: Id User Host db Command Time State Info 460355 mysql_suser localhost mysql_database Execute 2323 Sending data /*IteratorIterator::rewind:92*/ SELECT * FROM `forums_posts` WHERE pid > 12881963 ORDER BY post_date 464004 mysql_suser localhost mysql_database Execute 1348 Sending data /*IteratorIterator::rewind:92*/ SELECT * FROM `forums_posts` WHERE pid > 12881963 ORDER BY post_date 468058 mysql_suser localhost mysql_database Execute 327 Waiting for table level lock /*IPS\Patterns\_ActiveRecord::save:183*/ UPDATE `forums_posts` forums_posts SET `post`=?,`post_bwop 468086 mysql_suser localhost mysql_database Execute 320 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468090 mysql_suser localhost mysql_database Execute 319 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468117 mysql_suser localhost mysql_database Execute 308 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468153 mysql_suser localhost mysql_database Execute 302 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468218 mysql_suser localhost mysql_database Execute 289 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468292 mysql_suser localhost mysql_database Execute 272 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468313 mysql_suser localhost mysql_database Execute 267 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468371 mysql_suser localhost mysql_database Execute 252 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468425 mysql_suser localhost mysql_database Execute 228 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468524 mysql_suser localhost mysql_database Execute 195 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468535 mysql_suser localhost mysql_database Execute 191 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468693 mysql_suser localhost mysql_database Execute 147 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468701 mysql_suser localhost mysql_database Execute 144 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468711 mysql_suser localhost mysql_database Execute 140 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468715 mysql_suser localhost mysql_database Execute 139 Waiting for table level lock /*IPS\forums\extensions\core\Queue\_DeleteLegacyPosts::getProgress:46*/ SELECT COUNT(*) FROM `forums 468718 mysql_suser localhost mysql_database Execute 139 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468811 mysql_suser localhost mysql_database Execute 105 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468819 mysql_suser localhost mysql_database Execute 102 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468830 mysql_suser localhost mysql_database Execute 99 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468840 mysql_suser localhost mysql_database Execute 95 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468872 mysql_suser localhost mysql_database Execute 86 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468885 mysql_suser localhost mysql_database Execute 83 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468886 mysql_suser localhost mysql_database Execute 83 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 468935 mysql_suser localhost mysql_database Execute 73 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 469016 mysql_suser localhost mysql_database Execute 53 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 469045 mysql_suser localhost mysql_database Execute 48 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 469175 mysql_suser localhost mysql_database Execute 25 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 469182 mysql_suser localhost mysql_database Execute 24 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 469193 mysql_suser localhost mysql_database Execute 22 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 469209 mysql_suser localhost mysql_database Execute 17 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums 469223 mysql_suser localhost mysql_database Execute 14 Waiting for table level lock /*IPS\Content\_Item::_comments:1444*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forumsIn IPB 3 we never had a problem with table locks. Is there anything we can do about this? The site has become unusable as pages cant be loaded most of the time.Or will this go away as maybe some things get cached?
RevengeFNF Posted June 12, 2015 Posted June 12, 2015 With a board so big like yours, you should really consider moving your tables from MyISAM to Innodb, since with InooDB you don't have Table Locks, only Row Locks.
Vroom Posted June 13, 2015 Author Posted June 13, 2015 Is there a guide anywhere on how to change the database form MyIsam to Innodb for v4.0? Do I just manually do it in phpmyadmin with sql queries one by one or is there some steps I should take to do it properly?
genrobgen Posted June 13, 2015 Posted June 13, 2015 I'd be interested in some info regarding moving to InnoDB also.On my site with 2.5 million posts I'm getting similar table locks, particularly with a query like:/*IPS\Patterns\_ActiveRecordIterator::count:246*/ SELECT SQL_CALC_FOUND_ROWS forums_topics.*I had to write a script on a 30-second cron with automatically kills these queries if they take more than 30 seconds.
RevengeFNF Posted June 13, 2015 Posted June 13, 2015 Don't change to InnoDB in Big tables with phpmyadmin.Do you guys have access to your server with SSH?
Vroom Posted June 13, 2015 Author Posted June 13, 2015 Yes I have access to SSH. I will do some more searching here and see if any other advice. It would be nice if the admins had a guide for it. I am sure many are interested in this.
genrobgen Posted June 13, 2015 Posted June 13, 2015 Me too, I also have SSH. Googling for info suggests it might be pretty straight forward. But this is unfamiliar territory for me. I need to learn some more before I know if such a conversion is safe and what the implications are.
RevengeFNF Posted June 13, 2015 Posted June 13, 2015 Me too, I also have SSH. Googling for info suggests it might be pretty straight forward. But this is unfamiliar territory for me. I need to learn some more before I know if such a conversion is safe and what the implications are.Such conversions is completely safe. I was wunning IPB 3.4 with InnoDB and now with IPS4.In fact, InnoDB is much safer than MyISAM.You just need to enter in SSH and type mysqlThen type USE NAME_DB;Finally type: alter table ibf_forums_posts ENGINE=InnoDB;You need to do that for every table. But for now you could just do it to that one that is locking.
Vroom Posted June 14, 2015 Author Posted June 14, 2015 I see in the ipb config file there is this:$INFO['mysql_tbl_type'] = 'MyISAM';After converting, should we leave that as is or change it to say innodb?
RevengeFNF Posted June 14, 2015 Posted June 14, 2015 I think that definition is in the case in an upgrade or something, if the board needs to create a new table, it will create it using that config.
AutoItScript Posted June 14, 2015 Posted June 14, 2015 That option doesn't actually exist in IPB4 and is no longer required. If you create a new install you can see that it doesn't get created in conf_global.php.
jpg Posted June 14, 2015 Posted June 14, 2015 Just to add my five cents to this, yes, you'll get row level locking and that's great, but InnoDB requires a lot more resources than MyISAM, and tuning. You can easily tune MyISAM to get you 2-3 times performance, InnoDB requires a lot more careful consideration on parameters, but can get 10-50 times performance.You may in some instances get WORSE performance by just swapping to InnoDB because of the additional resources it requires. I wouldn't be just changing the storage engine without testing.In short/really generally/don't hate:MyISAM = Easy/Cheap. Really fast for bulk reads. Slow for bulk writes due to table locking.InnoDB = Harder/Expensive. Fast for bulk writes. Better true-text searching.I mix of storage engines may be appropriate but again, you get yourself into a bit of fun when it comes to database tuning in that scenario.
RevengeFNF Posted June 15, 2015 Posted June 15, 2015 Just to add my five cents to this, yes, you'll get row level locking and that's great, but InnoDB requires a lot more resources than MyISAM, and tuning. Tuning MyISAM might only get you 2-3 times performance, I've seen regularly tuning InnoDB can get 10-50 times performance.You may in some instances get WORSE performance by just swapping to InnoDB because of the additional resources it requires. I wouldn't be just changing the storage engine without testing.A Database in InnoDB optimized can be a lot faster than MyISAM. My site got a lot faster since i moved to InnoDB.If their sites got many visitors, each one of them will table lock when accessing something. Also InnoDB is much safer than MyISAM, you don't need to worry again with table repairs etc etc.
jpg Posted June 15, 2015 Posted June 15, 2015 I don't disagree with you regarding MyISAM/InnoDB, the general point I'm trying to get across is just swapping storage engines is not a silver bullet, like anything proper testing/configuration/tuning will be required to get desired results.I personally run nginx/php-fpm/MariaDB10 with a mix of Aria/XtraDB storage engines.
genrobgen Posted June 15, 2015 Posted June 15, 2015 Possibly silly question, if you swap to InnoDB then decide to swap back for some reason, that's no problem, right? I mean, it's not a one-way conversion, like an IPS upgrade is one-way?
RevengeFNF Posted June 15, 2015 Posted June 15, 2015 Possibly silly question, if you swap to InnoDB then decide to swap back for some reason, that's no problem, right? I mean, it's not a one-way conversion, like an IPS upgrade is one-way?Yes, no problem.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.