upgraded to 4.0, getting tons of table locks

[Vroom]

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 `forums

In 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]

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]

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]

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]

Don't change to InnoDB in Big tables with phpmyadmin.

Do you guys have access to your server with SSH?

[Vroom]

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]

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]

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 mysql

Then 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]

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]

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]

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]

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]

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]

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]

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]

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.