XF 1.4 Performance Optimization

[Frank Cano]

Hi,

We run a smallish forum but it has been around for a long time and thus, the SQL db is large and since we are small, we are on a budget host site. Looking to improve the SQL query speed. Any advice to get us pointed in the right direction would be greatly appreciated.

Some questions along this line that we have already had:
- does closing old threads improve query response time?
- is there any optimizations we can do to the DB?
- if we wanted to archive off all the old threads, how would we go about this?

Thanks!
Frank

 

[Paul B]

does closing old threads improve query response time?

No.

is there any optimizations we can do to the DB?

Not really, although it's not clear what you mean by "optimizations".

if we wanted to archive off all the old threads, how would we go about this?

There is no archive function so nowhere to archive them to.

 

[thedude]

Have someone knowledgeable take a look at your MySQL (or MariaDB) .cnf file. Maybe it's optimized for MyISAM and not InnoDB. Maybe the buffer pool is too small. Maybe a lot of different things.

To the db structure itself there's nothing really you can or should do like Brogan mentioned, but definitely try tuning the db engine.

 

[Frank Cano]

Thank you all for the quick advice.

There is no archive function so nowhere to archive them to.

Granted, but I could make two copies of the database and in one delete everything older than 2 years and in the other everything newer. Then for the older db, start up a second forum on a subdomain pointing to the old DB for those wishing to do searches for prior references.

Poor man's archive function.

Have someone knowledgeable take a look at your MySQL (or MariaDB) .cnf file. Maybe it's optimized for MyISAM and not InnoDB. Maybe the buffer pool is too small. Maybe a lot of different things.

To the db structure itself there's nothing really you can or should do like Brogan mentioned, but definitely try tuning the db engine.

Yea, we are on a budget hosting company that won't let us hack the SQL my.cnf file. Gonna likely change that.

 

[Paul B]

Then for the older db, start up a second forum on a subdomain pointing to the old DB for those wishing to do searches for prior references.

That would require two licenses and it doesn't actually make any difference as far as performance is concerned.

It's also not possible to selectively move content to another database, so how would you do that on a rolling basis?

 

[DurtySonuva]

Would it make sense to split all of the old posts, put them into a separate 'archived' forum that isn't called on in the news or recent posts function?

 

[Frank Cano]

Would it make sense to split all of the old posts, put them into a separate 'archived' forum that isn't called on in the news or recent posts function?

That would be ideal if that functionality existed.