Re: [google-cloud-sql-discuss] Re: Major change to InnoDB Pages Written after update

Hey Vishnu and Geoffrey,

At this point, it appears either that the update which caused an observed uptick in writes / second on the order of 1.0 write as opposed to 0.0 was either a case of A) more accurate monitoring picking up an actual write rate or B) an artifact of monitoring introduced by the first update and addressed by the second, where the actual writes were always ~ 0.0.

Since we never uncovered any potential cause behind the 1 write / second figure, it appears that the issue could have simply been one of monitoring, with the actual rate being at 0 but simply reporting at 1. This is just a hypothesis, though.

At any rate, even if there were a baseline of 1 write / second, this has a near-zero impact on performance and can safely be ignored. At the very least, if monitoring shows 0 / second and the user is not aware of any traffic which would cause a write, the monitoring data is in line with what we'd expect to see.

If anybody has any insight into the InnoDB engine on what might cause 1 write / second, feel free to jump in the thread. I've looked into InnoDB internals quite a bit and can't quite find any reason why an instance would have a regular 1 write / second, but maybe I missed some subtlety or edge condition which might cause this.

Cheers,

Nick
Cloud Platform Community Support

On Tuesday, March 7, 2017 at 4:50:53 PM UTC-5, Vishnu Fafat wrote:

Hi,

Actually the behavior I mentioned will not show up in InnoDB pages written yet. It will probably start showing up in April 2017.
Its not clear to me what could've caused the increase in number of InnoDB pages written to your instance.

On Tue, Mar 7, 2017 at 1:11 PM, 'paynen' via Google Cloud SQL discuss <google-cloud-sql-discuss@googlegroups.com> wrote:
That's very interesting. Vishnu, can you comment further on the probable cause behind this behaviour?

On Tuesday, March 7, 2017 at 3:19:27 PM UTC-5, Geoffrey Arnold wrote:
Quick follow-up: it looks like another update went out 3/3 which resolved the issue:

On Fri, Mar 3, 2017 at 5:58 PM, 'Vishnu Fafat' via Google Cloud SQL discuss <google-cloud-sql-discuss@googlegroups.com> wrote:
Hi,

We periodically write to your instance (approx once per second) to ensure that its working. From the public documentation page [0]:

The Number of Writes metric is the number of write operations to disk. Write activity is generated even if your application is not active, because Cloud SQL instances write to a system table approximately every second (except for replicas).

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

On Fri, Mar 3, 2017 at 3:05 PM, Geoffrey Arnold <geoffrey.arnold@gmail.com> wrote:
Hey Nick, that seems to explain it. Thank you so much for taking a look!

FWIW, I've been unable to capture anything useful from `SHOW FULL PROCESSLIST` other than Sleeping connections, so I definitely think it's something outside of normal database usage like the change you mentioned.

On Thu, Mar 2, 2017 at 2:46 PM, 'paynen' via Google Cloud SQL discuss <google-cloud-sql-discuss@googlegroups.com> wrote:
Hey Geoffrey,

Thanks for emailing me some information about your instance.

I studied the information provided carefully but unfortunately it doesn't show any information which could point decisively to the cause of the 1 write per second. However, having looked into the nature of the update which was applied, I believe there is an explanation there.

Looking at other instances with very similar activity profiles to your instance, we also saw a sudden adjustment in many metrics around that time. Part of the update contained a change to how statistics are gathered, so as to make them more granular. A more granular sampling of the write rate could definitely tend to alter the reported statistics slightly, as seen.

So, the jump up is nothing to worry about, and as said before the 1 write per second is harmless at any rate. To speculate deeper about its ultimate origin would require some very expert InnoDB knowledge to assess, which unfortunately I'm not qualified to provide, although I can assist with Cloud SQL itself and have a working knowledge of InnoDB (which in this case falls short of explaining the origin of the 1 page write / second).

One possible way to get an insight into the nature of the write is to run "SHOW FULL PROCESSLIST\G" during a time when there's no other activity other than the 1 page write / second.

Let me know if you've got any further questions and I'll be happy to help!

Cheers,

Nick
Cloud Platform Community Support

On Wednesday, March 1, 2017 at 8:16:14 PM UTC-5, Geoffrey Arnold wrote:
Thanks Nick, appreciated.

On Wed, Mar 1, 2017 at 6:18 PM, 'paynen' via Google Cloud SQL discuss <google-cloud-sql-discuss@googlegroups.com> wrote:
Hey Geoffrey,

This is just an update to let you know we're still looking into this.

Regards,

Nick
Cloud Platform Community Support

On Friday, February 24, 2017 at 1:19:01 PM UTC-5, paynen wrote:
Hey Geoffrey,

To check whether there's any process running which might be responsible for the approx 1 page-write/s rate, you could see about running "SHOW FULL PROCESSLIST\G" during those times with zero user activity and yet a 1 page-write/s rate reporting.

This Q&A on the DBA Stack Exchange site has an excellent reply examining all the different possible sources of writes in the InnoDB engine.

Running "SHOW ENGINE INNODB STATUS;" during the times with zero user activity and yet a 1 page-write/s rate reporting could also be useful in analyzing what's happening.

Feel free to analyze the results of your investigations alone or send them along via replying to that same email I had sent. I'd be very interested to take a look at the data.

I'll be taking a look to see if I can learn more about what the update contained, and to what extent it could have contributed to this. At any rate, one page write per second is nothing that will impact performance on your app, so there's nothing to worry about in the meantime.

Cheers,

Nick
Cloud Platform Community Support

On Friday, February 24, 2017 at 12:38:25 PM UTC-5, Geoffrey Arnold wrote:
Thanks for taking a look. The largest table on this instance is about 6K rows and 19MB in size.

I already replied to your other email, but just ran SHOW TABLE STATUS on each of the schemas. I'll include that output in the other thread.

On Thu, Feb 23, 2017 at 5:38 PM, 'paynen' via Google Cloud SQL discuss <google-cloud-sql-discuss@googlegroups.com> wrote:
Hey Geoffrey,

There are many moving parts involved in the InnoDB engine which might lead to an increase or decrease of page writes. Without knowing anything more about your system, it's hard to say what might be the cause.

I've sent an email to you through our support portal to request your Project ID and the instance name so we can see about taking a closer look at what update was applied. You may want to also gather information about the sizes of your tables, the kinds of queries run, the frequencies with which they're run, etc. for your reply.

I'll be happy to post here any further progress we make as we look into this.

Cheers,

On Thursday, February 23, 2017 at 12:06:58 PM UTC-5, geoffrey.arnold@gmail.com wrote:
Hello,

It appears that on February 4 at 11:13 PM CT our Cloud SQL instance received an update. Since then we've seen a major change to the InnoDB Pages Written metric. Screenshots attached. Any help with where to begin troubleshooting would be greatly appreciated.

Thanks in advance,
Geoff.

--
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/0c57b551-be97-478e-9f87-1146506f0055%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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/6e4fc9d9-21bc-495f-ac4b-5f6646191281%40googlegroups.com.

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

--
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/G2OHsk_fb4U/unsubscribe.
To unsubscribe from this group and all its topics, 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/269f7747-43a7-455d-b5f7-199d9cfec098%40googlegroups.com.

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

--
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/CAF38860rCcu4s3rkgQBWM_doe3-Cwo%2B50fo6hYc8yTovE%2BFWsg%40mail.gmail.com.

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

--
Vishnu Fafat

--
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/CAPVxmMDzrOqh3WsznyVKAfO6Vq%2Be3ndJtdejaf7ofRKZe1e7CA%40mail.gmail.com.

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

--
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/5fdfddcd-58ba-4199-8722-2a9a6519f71d%40googlegroups.com.

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

--
Vishnu Fafat