[google-cloud-sql-discuss] Re: Cloud SQL memory increased

Hello Muhammad,

Thanks for your reply.

For spikes of I/O Read Write, it would be helpful to review the operations / queries at the same time period, which might provide some information on what is driving the spike and if it is causing any increase of resource usage (CPU or Memory).

As Alexis has partly explained, Cloud SQL instances are tuned to allow MySQL to use as much of the available memory as possible to store indexes and data. Some of the allocated memory is never released because MySQL re-uses it while it is running. Hence, in general, the overall memory usage may not possibly be an issue if it is stable. 

MySQL also exposes server status variables [1] that could be reviewed for any performance issues. And you are also welcome to open tickets to GCP tech support to look into your project and have the issue further investigated. 

Hope it helps.

[1]: https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html

On Friday, July 17, 2020 at 8:27:57 AM UTC-4, Muhammad Dani wrote:

Hi Yananc,

Thanks for your replied.

1. Regarding IO Read Write, I'm still dont get it, for example in metrics above there's spike on July 10, let say it spiked at 10am. So what I need to do to fix ? is it increasing the machine (CPU & Memory) or re-configure some MYSQL variables ?

2. Alexis mention above, I already increase the disk space till 40%, so disk usage currently under 80%, but memory still on 80%-85%. there's no decreased, am I missed something to configure ?

__

Dani

On Thursday, July 16, 2020 at 11:53:51 PM UTC+7 yananc wrote:

Hello Muhammad,

As to your question about how to observe and measure the 'I/O write or read Operations', the read operations metric is the number of read operations served from disk that do not come from cache per second and the write operations metric records the number of write operations to disk. As Alexis has pointed out, a larger machine type comes along with a larger cache, making it possible to serve more requests directly from cache instead of from disk, hence reducing the latency as well as the read operations to disk.

From the screenshots you shared, you could monitor the amount of the operations within a specified time frame. This might help to locate any peak value or pattern abnormalities in order to diagnose related issues. One point to pay extra attention is the sampling interval (in your case '3 hr interval') because different time intervals might flatten out some extreme values, possibly hiding important data.

Hope it helps!

On Thursday, July 16, 2020 at 8:30:51 AM UTC-4, Muhammad Dani wrote:

Hi Alexis,

Thanks you so much for your reply and support, I will try it first to enlarge the disk space. 

But I'm still don't get it to measure about "I/O write or read Operation", can you help to share more how do I oberved and measure it ?

Here I attach metic of our IOPS :

__

Dani

On Thursday, July 16, 2020 at 2:50:32 AM UTC+7 Alexis (Google Cloud Support) wrote:

Hello Muhammad,

I'll try to answer the best I can.

MySQL is known to try an take as much memory as possible to store indexes and data. Sometimes as much as 80% of the RAM. It does that in order to provide good response times during heavy I/O operations. This means that as you increase your memory, you will probably always see high memory usage until the amount of data is below 80% of its RAM, then you might see it drop below 80% usage.

I think a good indicator to tell how your database is performing would be the IOPS. The closest visible metric I can think of is "read/write operations" under this[1] article. It says the following quote: " You can use this metric to help you understand whether your instance is correctly sized for your environment. If needed, you can move to a larger machine type to serve more requests from cache and reduce latency. "

It's also important to note that your network throughput and desk throughput is proportionally sized according to your instance. For example, if you go to create a Cloud SQL instance, under "Machine type and storage", there is network throughput and disk throughput that automatically scales depending on the instance you pick.

I hope this helps you.

[1] https://cloud.google.com/sql/docs/mysql/instance-info#available_metrics

On Wednesday, July 15, 2020 at 8:41:21 AM UTC-4, Muhammad Dani wrote:

Hi Everyone,

Sorry I have question, I'm using Cloud SQL (MYSQL) in GCP, more than last 2 weeks the memory increased, my question why the spike not decreased again, the trend now is flat since 2 weeks. how do I check in detail the causes and how do I fix it ?

__

Dani

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/bfacf439-3d0b-46af-9392-8ad512298365o%40googlegroups.com.