Guys, I need some guidance here I have a SQL db,

where I store the status of my users and their data, I won't have more than 100 users because of the nature of this particular business, so this won't be a huge database, 10MB at most. And If I'm extremely lucky, 100MB in years.

The problem is that those users are making a lot of http requests to my API and this API writes to the database and retrieve data from it.

So the I/O is a bit high. API is consuming about 70% of my VPS CPU

I'm trying to reduce this CPU usage as many transactions are really repetitive.

But, before trying to optimize my queries, I was thinking on using a cache approach, I mean, MEMORY tables

But I have never used them, so I understand those tables are actually stored on RAM and as soon as I restart the system, the data is gone

I thought I could persist that data to the SSD every 5 mins or so, but afaik, I just can dump tables to a .sql file and then restore them when the system is restarted (drop/truncate tables and restore them from the dumped files)

Is this the only way of doing it? I thought they were other options of doing this (persisting the data)

The other approach I come with was to load the data from the database when the API starts and keep it there (in RAM) and persist changes each 5 mins or so to avoid using the I/O that much (I'm thinking I/O requests is what makes this really slow, despite the query quality)

So, what do you suggest? I haven't used Redis or anything like that before neither

PS: Sorry by my bad english, this is not my main lang

What queries are causing the load to be high? For a start caching the read queries in redis is a good idea

if the results of the queries are so similar that a cache would help it sounds like your queries are bad or you somehow made it so the DB can't optimize anything

I know my queries are bad, but I just thought of a quick improvement, I will try this approach first (optimization of queries)

well a cache is another process running so if your DB is hogging all resources it's not gonna be faster

Maybe you are just missing some index or something?

Which database?

Hey, could you tell us what you have done?

Hello! Since that day, the problem keeps happening so I decided to rewrite the entire system lol I'm about to finish it, near 70% It was better that way. I wrote better sql queries and a better business logic. Now I don't know if the problem will be fixed after this, I really hope so lol I'll post my results after I finish it

What is your tech stack if I may ask?

.NET for everything Blazor for frontend and Windows Forms for Windows Users, but with the new system I'm ditching Windows Forms to make it multiplatform, so the same Blazor web app will do the job, and for backend just a ASP.NET Web API with MariaDB, but with the new system I'm replacing it with PostgreSQL, ah and Worker Services to handle some tasks