[SOLVED] Caching data to spare mysql queries

Issue

I have a PHP application that is executed up to one hundred times simultaneously, and very often. (its a telegram anti-spam bot with 250k+ users)
The script itself makes various DB calls (tickers update, counters etc.) but it also load each time some more or less ‘static’ data from the database, like regexes or json config files.
My script is also doing image manipulation, so the server’s CPU and RAM are sometimes under pressure.

Some days ago i ran into a problem, the apache2 OOM-Killer was killing the mysql server process due to lack of avaible memory. The mysql server were not restarting automaticaly, leaving my script broken for hours.

I already made some code optimisations that enabled my server to breathe, but what i’m looking now, is to have some caching method to store data between script executions, with the possibility to update them based on a time interval.
First i thought about flat file where i could serialize data, but i would like to know if it is a good idea or not regarding performances.

In my case, is there a benefit of using caching data over mysql queries ?
What are the pro/con, regarding speed of access, speed of execution ?
Finaly, what caching method should i implement ?
I know that the simplest solution is to upgrade my server capacity, I plan to do so anytime soon.

Server is running Debian 11, PHP 8.0
Thank you.

Solution

If you could use a NoSQL to provide those queries it would speed up dramatically.

Now if this is a no go, you can go old school and keep that "static" data in the filesystem.

You can then create a timer of your own that runs, for example, every 20 minutes to update the files.

When you ask info regarding speed of access, speed of execution the answer will always be "depends" but from what you said it would be better to access the file system that being constantly querying the database for the same info…

Answered By – Carlos Alves Jorge

Answer Checked By – Jay B. (BugsFixing Admin)

Leave a Reply

Your email address will not be published.