Slow query log on IPB 3

[ixalon]

Hi all,

I know this type of question often never gets an answer (because there's just so much that could be causing it) but I'm having really severe performance problems on http://forums.se-nse.net/ since upgrading to IPB3 at the weekend.

I'm running all tables as InnoDB (apart from sessions on heap) and using XCache 1.2.2. I've enabled the MySQL slow query log and the culprit seems to be the queries of the form:

SELECT t.tid, t.title, t.posts, t.start_date as post_date, t.views, t.title_seo, t.starter_name,m.member_id, m.members_display_name, m.members_seo_name

FROM ibf_topics t

LEFT JOIN ibf_forums f ON ( f.id=t.forum_id )

LEFT JOIN ibf_members m ON ( m.member_id=t.starter_id )

WHERE t.approved=N and t.state != 'S' and (t.state != 'S') AND t.forum_id IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,...,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) 

ORDER BY t.tid DESC LIMIT N,N

I'm seeing average (and total) query times for queries like the above like:

Count: 870  Time=61.67s (53654s)  Lock=0.03s (26s)  Rows=9.3 (8120)

Count: 45  Time=55.44s (2495s)  Lock=0.00s (0s)  Rows=10.0 (450)

Count: 419  Time=49.16s (20599s)  Lock=0.00s (1s)  Rows=9.8 (4120)

Count: 37  Time=46.86s (1734s)  Lock=0.00s (0s)  Rows=10.0 (370)

Count: 7  Time=40.57s (284s)  Lock=0.00s (0s)  Rows=10.0 (70)

Hope someone can suggest what I could try to resolve this.

Cheers,
Chris

[ixalon]

Good old cardboard consultancy. The moment I posted this I wondered if the extra data in the IBP3 vs IBP2 query was pushing them over one of the MySQL cache limits. Upping them a little seems to have solved the problem.