Issue MySQL memory usage still rising even after installing Memcached

[Blooblabb21]

Last month my server crashed because I ran out of MySQL memory, I run a video streaming site that usually runs up to 7 SQL queries per watched-video, but in the last few weeks I have optimised my queries and used memcached to cache every video so that now only 2 queries are run per watched-video. However I have still seen no improvement in memory usage. I have verified that memcache is installed and is working, so why does it feel like no pressure has been alleviated?

plesk memory usage after installing memcached:


plesk mysql usage after memcached:

 

[Blooblabb21]

updated mysql usage stats

 

[Bitpalast]

There is no reason why an SQL server should occupy less RAM only because another software caches its results.

 

[Blooblabb21]

There is no reason why an SQL server should occupy less RAM only because another software caches its results.

I don't understand. I thought RAM usage only increases when SQL queries are being made. If significantly less queries are being made why would the SQL RAM usage still remain high? I was literally told to use Memcache to solve this issue.

 

[websavers]

Your graph *does* show more lower dips in MySQL memory usage after memcached was installed. This implies that those more frequent dips in lower memory usage are repeated reads, while the high peaks that match the memory usage from before likely correspond with writes to the DB, as writes aren't cached by the nature of what they are.

The only way to know for certain though is to:

1. Ask the database software developer (MySQL or MariaDB probably?), or
2. Run more detailed tests that analyze results from shell after each individual query is manually run

I'm willing to bet that unless you do (2) the devs probably aren't going to help much.

 

[websavers]

I don't understand. I thought RAM usage only increases when SQL queries are being made. If significantly less queries are being made why would the SQL RAM usage still remain high? I was literally told to use Memcache to solve this issue.

Another thing to note, as Peter is indicating above, the initial query still needs to be made before the results are cached, so the SQL server still needs to occupy memory with the results of that query. Those are then saved by memcached for future equivalent queries. Unless you've got some config in place that is telling the SQL server to not cache those results itself (most SQL servers do have their own read query cache) then it's likely going to hold on to that memory as part of its cache.

The only resource that you should see a reduction in with memcached without additional tuning is CPU usage since the SQL server no longer actually needs to run those queries on subsequent identical queries.

 

[Blooblabb21]

Your graph *does* show more lower dips in MySQL memory usage after memcached was installed. This implies that those more frequent dips in lower memory usage are repeated reads, while the high peaks that match the memory usage from before likely correspond with writes to the DB, as writes aren't cached by the nature of what they are.

Would "writing" to the database be things like INSERT and UPDATE queries? If so then that can't be the problem because people rarely interact with the website in that way. It's 90% VIEW queries.

1. Ask the database software developer (MySQL or MariaDB probably?), or
2. Run more detailed tests that analyze results from shell after each individual query is manually run

I'm the primary software developer although my hosting providers are the ones that have access to the linux back-end. What kind of tests am I supposed to run? Can it be done within plesk or do i need to ask my hosting providers to do it.

 

[Blooblabb21]

Another thing to note, as Peter is indicating above, the initial query still needs to be made before the results are cached, so the SQL server still needs to occupy memory with the results of that query. Those are then saved by memcached for future equivalent queries. Unless you've got some config in place that is telling the SQL server to not cache those results itself (most SQL servers do have their own read query cache) then it's likely going to hold on to that memory as part of its cache.

Yes I already know this, but I have implemented code that automatically caches a video after it is watched the first time and then uses the cached results for all subsequent views. At this point every video on the site is cached, so I expected to see a massive drop in mysql usage instead I'm still seeing spikes, why? Even if the dips are lower than before, why are there still huge spikes.

Unless you've got some config in place that is telling the SQL server to not cache those results itself (most SQL servers do have their own read query cache) then it's likely going to hold on to that memory as part of its cache.

As i understand it, memcached takes up 64MB of RAM automatically, hense the huge spike in plesk memory usage after installing (which i pictured above). I dont understand what this has to do with mysql. each cache shouldnt take up more mysql ram, it should be part of the 64MB allocated memcached ram.

 

[websavers]

I'm the primary software developer although my hosting providers are the ones that have access to the linux back-end. What kind of tests am I supposed to run? Can it be done within plesk or do i need to ask my hosting providers to do it.

When I indicated to contact the software devs, I meant for the SQL server. Plesk just bundles it in. Your questions can only receive answers from the MariaDB developer community.

Yes I already know this, but I have implemented code that automatically caches a video after it is watched the first time and then uses the cached results for all subsequent views. At this point every video on the site is cached, so I expected to see a massive drop in mysql usage instead I'm still seeing spikes, why? Even if the dips are lower than before, why are there still huge spikes.

Honestly that just sounds like your cache isn't working so it has to go to SQL anyway. But This is just a guess. You'll need to debug further to find out more: asking here isn't going to help you at all as this is either 1) a bug in your caching solution, or 2) a bug in MariaDB or it could just be how it's designed to work.

Either way the solution must come from a developer elsewhere, not your control panel's forum...

 

[mow]

Yes I already know this, but I have implemented code that automatically caches a video after it is watched the first time and then uses the cached results for all subsequent views. At this point every video on the site is cached, so I expected to see a massive drop in mysql usage instead I'm still seeing spikes, why? Even if the dips are lower than before, why are there still huge spikes.

You should definitely see a drop in mysql usage, meaning CPU load should drop. But, as others already mentioned, this does not necessarily correlate with memory usage, which in turn does not depend on the frequency of queries.

As i understand it, memcached takes up 64MB of RAM automatically, hense the huge spike in plesk memory usage after installing (which i pictured above). I dont understand what this has to do with mysql. each cache shouldnt take up more mysql ram, it should be part of the 64MB allocated memcached ram.

mysql has its own caches. mysql doesn't know or care about memcached's caches.