InnoDB choking on big topics

[Sergey_SV]

Hi, 

Recently I have converted most of the tables in my database from Myisam to InnoDB (IPB 4.1.19.2,  10.1.21-MariaDB  ). But got the strage "aftereffect": 90% of the community works is very fast, but if I click to the very long topic (350+pages) for the last unreat post (for example) - then the server really start crawling and it even possibe to get the 504 or 502 error. Before the conversion everything was ok.

Does anybody have a hint which parameters to finetune in Mysql to resolve this?

 

 

[Numbered]

2 hours ago, Sergey_SV said:

Hi, 

Recently I have converted most of the tables in my database from Myisam to InnoDB (IPB 4.1.19.2,  10.1.21-MariaDB  ). But got the strage "aftereffect": 90% of the community works is very fast, but if I click to the very long topic (350+pages) for the last unreat post (for example) - then the server really start crawling and it even possibe to get the 504 or 502 error. Before the conversion everything was ok.

Does anybody have a hint which parameters to finetune in Mysql to resolve this?

Very much possible cases may cause this behaviors. Without a lot of diagnostic questions i can recommend to temporary use of NewRelic service. It can trace all requests and return info about most time comsuming mysql queries (with a traceback where it was called).

Here is my example of some diagnostic with them (MySQL debug will be same):

 

[Sergey_SV]

I tried to optimise it, but still really puzzeled

I still have probems with viewing threads with more than 5000 posts by authorized users. Time of loading page is about 40 seconds!
Non authorized users hasn't such problem - page load just about 2-3 seconds.
In slow log we have record:

/*IteratorIterator::rewind:52*/ SELECT forums_posts.*, forums_topics.* FROM `ibf_forums_posts` AS `forums_posts` STRAIGHT_JOIN `ibf_forums_topics` AS `forums_topics` ON forums_posts.topic_id=forums_topics.tid AND forums_topics.approved < 2 STRAIGHT_JOIN `ibf_core_permission_index` AS `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_topics.forum_id AND (( FIND_IN_SET(15,perm_2) ) OR perm_2='*' ) LEFT JOIN `ibf_forums_forums` AS `forums_forums` ON forums_topics.forum_id=forums_forums.id AND ( forums_forums.password IS NULL OR ( FIND_IN_SET(15,forums_forums.password_override) ) ) AND forums_forums.min_posts_view<=2 WHERE topic_id=157698 AND nbpinned=1 ORDER BY post_date asc LIMIT 10;

# Query_time: 42.833080 Lock_time: 0.000019 Rows_sent: 0 Rows_examined: 2966929

Innodb parameters:
innodb_buffer_pool_size         = 32G
innodb_additional_mem_pool_size = 32M
innodb_file_io_threads          = 8
innodb_lock_wait_timeout        = 50
innodb_log_buffer_size          = 8M
innodb_flush_log_at_trx_commit  = 2
innodb_flush_method             = O_DIRECT
innodb_large_prefix=1
innodb_file_format = Barracuda
innodb_file_per_table = 1

Can somebody advice what parameter we have to adjust to decrease time in query?