Database scaling techniques

[Cpvr]

What are some tactics that you use to scale and optimize your databases?

https://twitter.com/x/status/1929139516826177546

 

[Shawn Gossman]

How could we apply this to a forum?

Is it something we can do as individual forum owners? Or is it something that the platform developers would need to do on the backend?

 

[Cpvr]

How could we apply this to a forum?

Is it something we can do as individual forum owners? Or is it something that the platform developers would need to do on the backend?

Caching can be done via a forum owner. You can either install cloudflare or do caching via .htaccess.

Implementing redis or memcache on your server itself can help boast performance on the caching side as well.

Query optimization can be implementing by editing your database and adding “*indexes” to major queries. Such as, anything that pulls up a member id or post.

https://twitter.com/x/status/1929750148415922404

 

[Shawn Gossman]

Now is there any risk of doing any of this at all?

Should we be really backing up our DBs each time we do any of this? Is there a potential for a crash or corruption?

 

[Cpvr]

Now is there any risk of doing any of this at all?

Should we be really backing up our DBs each time we do any of this? Is there a potential for a crash or corruption?

Caching services? No. Setting up indexes won’t hurt your database either. However, you should always backup your databases and files prior to making direct changes.

@Tracy can probably chime in as well.

 

[B.F]

There a mod that can help with this friendly url something.

 

[Tracy]

Generally XF (and most decent scripts) already utilize the necessary indexes.
As for anytime you "tweak/touch" your database... you BETTER back it before you do. And I even suggest taking the site offline so that the DB cannot be written to during the time you may be tweaking it.
I personally use Redis cache on the server as well as the OpCache for PHP.
Then I push the utilization as much as I can of CloudFlare's free service for edge-caching of the content.




I could do a lot better on cached content if I was on the paid plan and spent some time tweaking it. But the issue with any dynamic site (which a forum is) consists of there is only a limited amount of static data to cache. And let's not mention that several of my "data served" are physical files that are not typically cached on the CloudFlare free level.

 

[Cpvr]

Generally XF (and most decent scripts) already utilize the necessary indexes.
As for anytime you "tweak/touch" your database... you BETTER back it before you do. And I even suggest taking the site offline so that the DB cannot be written to during the time you may be tweaking it.
I personally use Redis cache on the server as well as the OpCache for PHP.
Then I push the utilization as much as I can of CloudFlare's free service for edge-caching of the content.

View attachment 379
View attachment 380

I could do a lot better on cached content if I was on the paid plan and spent some time tweaking it. But the issue with any dynamic site (which a forum is) consists of there is only a limited amount of static data to cache. And let's not mention that several of my "data served" are physical files that are not typically cached on the CloudFlare free level.

I also installed Opcache on my server last night. Thank you for the advice bro! I didn’t know about Opcache prior.

I also use Redis along with Cloudflare. Opcache is awesome!