XF 1.5 Time Lags and Indexing

[vFranky]

Hello,

we have upgraded to a very fast server hardware and XenForo runs most of the time at perfect speed. But we still get time lags of around 10 seconds every few hours. My webhoster has analyzed the problem. He says, that the server itself isn't working to capacity, but our MySQL database seems to be busy sometimes. The webhoster says I should use MySQL indexing to solve the problem.

Questions: How can I set up (additional) indexing to speed up XenForo? Is it useful?

I thought that XenForo is optimzed out of the box.

Kind regards and thank you in advance
Frank

 

[Mike]

The recommendation given isn't really actionable. How did your host come to that conclusion? What queries did he see being a problem?

 

[vFranky]

I gave them the exact date and time of some XenForo time lags, each around 10 seconds. They analyze the problem and found that the server isn't busy (images was loaded at the same time), but the MySQL database seems to need to much time to find the record, they say. The advice me to use indexes.

They don't actually mention specific queries.

What to do? Any idea? Would indexing help? Or impossible?

 

[Mike]

If an index would help, we'd need to know what columns to add the index to. To get that, we'd need to know the query (or queries) that are triggering the problem. You'd need to identify that via a slow query log or viewing the MySQL process list while the issue is happening.

 

[vFranky]

My host identified the following tables as xf_thread_read, xf_search, xf_post as as too slow.
Do we need to know the exact queries?

 

[Mike]

Yes. Specific queries will be needed to know if an index is useful.

It's worth noting that if MySQL itself hasn't been tuned -- there are a lot of configuration options -- then that should really be looked into: https://www.percona.com/blog/2013/09/20/innodb-performance-optimization-basics-updated/ While I don't know the specs of your server, it doesn't look like there's a huge amount of data, so optimizing the InnoDB will likely go a long way.