Please Help! SQL + Caching optimization

[vlom31]

Hi all,

I am implementing a live feed on my xenforo homepage, meaning that it shows a list of recent posts, updated in real time, resulting in some heavy sql query(ies) every 1 second..

I am just testing it myself right now, so 1 user, and it's already putting the CPU under pressure, the way it is done I think the number of simultaneous queries will be demultiplied by the number of users seeing that page, which is obviously not ideal.

The thing is, recent posts is a static data, so no matter what user queries it it'll send the same answer, so i'm thinking there must be a way to make it so the data is cached somehow, resulting in 1 single query/sec (instead 1*nbr_of_users) every 1 second ..

Would using redis as cache accomplish this ? if not what other solutions do I have ?

I would immensly appreciate help on this.

@Xon @RoldanLT @Kier @Mike ... anyone ^^

I should mention that I have a ton of RAM that can be used if necessary.


Mehdi

 

[m1ne]

Redis.
BD Cache.

 

[vlom31]

Thanx @Solidus , I was looking at Xon's redis cache addon, and DB tech Optimize as a matter of fact, just want to make sure this is the right solution for what i'm trying to accomplish ..


Mehdi

 

[Ernest L. Defoe]

Also make sure you are using something other than the default SQL cause that will put some heavy load on your SQL server. I recommend something like MariaDB or Percona which uses MariaDB. They will both be better than default SQL.

 

[vlom31]

Thanx @Ernest L. Defoe , I did switch to mariadb after reading a lot about it here and on TAZ


Mehdi

 

[rdn]

For that kind of feature, I'm using this by the way: https://xenforo.com/community/resources/live-feed.2901/
Do not reload it every second, every 10 seconds is enough and looks real-time.