Memory usage is increasing by bits

[小川純平]

I'm running Cloud SQL second generation with legacy CakePHP app.

The app was running on CentOS 5, and MySQL was also running in the same OS instance.

Following is my Memory usage graph of Cloud SQL. It is increasing by bits.

(At about 18:00, I restarted instance and memory usage is decreased, but memory usage started to increase again)

In my app, I found some bad SQL queries such as N+1 problem. I think there are more bad DB access.

I will fix them later, but for now, I want to prevent increasing.

Can anyone give me an advice to prevent increasing memory?

I found I didn't configure query_cache_limit, query_cache_size, and query_cache_type, so I will add them when I'm ready to restart DB instance.

Should that prevent this increasing memory?

# If Cloud SQL team kindly help me and need to investigate my instance,

# I have sent email titled "Cannot change root password" from og...@growaspeople.org to clou...@google.com at March 1st

# and my GCP project name is written in it. instance name is production (different from one written in the email)

Thanks in advance,

Jumpei

[小川純平]

Hmm…

Image looks not attached to email.
Plz see it on web... https://groups.google.com/forum/#!topic/google-cloud-sql-discuss/ksLq38dEglU

[Vadim Berezniker]

Hi Jumpei,

In general, I would not worry about the overall memory usage. 

The instances are tuned to allow mysql to use as much of the available memory as possible.

Some of the allocated memory is never released because mysql re-uses it while it's running.

If you are worried about performance, you can look at some of the status variables exposed by mysql (http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html) that could give you an idea of how memory is being used. 

--
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-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/e2976aa1-9451-4752-a34e-a0455c8147de%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jumpei Ogawa]

Hi Vadim,

Thank you for your help.

First, I found I used old Cloud SQL Proxy client on the app server.
I modified my Ansible script so that latest client is installed, then
performance degrade is fixed. Now it works as fast as it was.

Old Ansible script had downloaded from
https://dl.google.com/cloudsql/cloud_sql_proxy
and now it downloads from
https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64

I also edited script:

- /usr/local/bin/cloud_sql_proxy -dir=/tmp/cloudsql -fuse &
+ /usr/local/bin/cloud_sql_proxy -dir=/tmp/cloudsql
-instances="[PROJECT_NAME]:asia-east1:[INSTANCE_NAME]" &



Next, let me tell you an important info that I forgot to write in first message.
At June 9, 18:00 (in the graph; it's Japan time), the memory usage is
reached to (almost) 100%, and the Cloud SQL is inaccessible from app
servers. That's why I increased machine spec and restarted Cloud SQL
instance.

After I run latest Cloud SQL Proxy client, memory usage is still
increasing. (But less fast than before I use old client)

I worry if it happens again.
Out-of-date Cloud SQL Proxy client possibly caused the inaccessibility
at June 9, 18:00,
but in case the cause wasn't client, the Cloud SQL instance should down again.

15GB memory is allocated to the Cloud SQL instance and Currently about
14GB is used.
According to the graph, memory usage will reach 15GB at June 12, 1:00
JST == June 11, 16:00 UTC.

I will keep watching the instance so I can immediately restart the
instance when it's not available,
but if you know other possible cause or some info else, I'm appreciate
if you tell me that.

Again, thank you for your kind support :)

> You received this message because you are subscribed to a topic in the

> Google Groups "Google Cloud SQL discuss" group.

> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/google-cloud-sql-discuss/ksLq38dEglU/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to

> google-cloud-sql-d...@googlegroups.com.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/google-cloud-sql-discuss/CABDU3p2z508de8qHFFCSak3vGBXhnFwgs0c5d6McMf2RG64Keg%40mail.gmail.com.

>
> For more options, visit https://groups.google.com/d/optout.

--
Jumpei Ogawa
Software Engineer, GrowAsPeople < http://growaspeople.org >

小川 純平
一般社団法人 GrowAsPeople (グロウ アズ ピープル)
ソフトウェアエンジニア