0

We have an Google App Engine (standard) app that uses Google Cloud SQL and it seems like the database performance is not optimal. I'm trying to determine what is reasonable performance and if we have any problems in our setup.

Our database is not very big. A backup is about 80MB. The database contains only discreet data...ie. no blobs.

Here is an example of some performance observations. Doing one thousand randome selects on a table with about 10,000 items takes about 40 seconds. This is done on a test JSP page that is part of the application. Now if I do this on my local development laptop we get about a ten times improvement (5 seconds).

Not sure if it is reasonable to compare laptop vs GAE+GCS.

BTW, we are using first gen GCS (MySQL 5.5).

Any tips on how we can track down performance issues would be appreciated.

Thanks, Mike

Mike Dee
  • 558
  • 1
  • 5
  • 13

1 Answers1

2

You can go on console.cloud.google.com > SQL > Select your instance > Overview section. Set the interval at 1 minute / 60 minutes at all. Then do a test that runs for 15 minutes. You will be able to check where the problem comes from (CPU, RAM, Disk usage, ...).

Just few explanations which could help you:

  1. If you do not have a dedicated vCPU, take one, or Google create a delay between requests. More requests there is, more delay you will get.
  2. If you want good performances, you need to have a SSD storage.
  3. There is a little lattency between Google Cloud and your application, so you should not do a loop of a thousand request, but one request getting all the results.
  4. You cannot compare your computer with GCP instances, it is just ... different ! Sometimes better, sometimes worst (most of the time much better ^^).
  5. You should upgrade your MySQL version as 5.5 to 5.7, but I don't think the problem comes from here.
ThisIsMyName
  • 887
  • 7
  • 14
  • 1
    Additionally, 2nd gen instances are much preferred over 1st gen. – kurtisvg Jul 30 '19 at 15:32
  • We have a dedicated vCPU. I even tried upgrading to a machine with more memory. Had no effect on performance. Didn't think it would since our DB is not that big. We have SSD storage already. Regarding latency, I made sure our GAE & GCS zones are the same (us-central). Upgrading from MySQL 5.5 to 5.7 made no difference. One thing that did have a 65% improvement was the use of a db connection pool. On my local PC a thousand random queries dropped from 4.5 seconds to 1.5 seconds. On GAE, performance went from 45 seconds to 15 seconds. Seems like GAE performance should be better. – Mike Dee Jul 30 '19 at 18:57
  • I'm considering use of a connection pool. Prior attempts to use a CP didn't work though. Prior to Java 8 support on GAE, threads weren't allowed and most CPs made use of threads. Plus 1st gen MySQL on GCS would put DB to sleep when no usage, which would make CP pooled connections stale. – Mike Dee Jul 30 '19 at 18:59
  • The connection pool is a good pratice, good idea. Increasing memory won't change anything, but increasing vCPU should help you, did you try it? – ThisIsMyName Jul 31 '19 at 07:43
  • I'll try increasing vCPU. I've tried CPs in the past and there were issues. But it may work now. I've summarized prior issues here: https://stackoverflow.com/questions/57280384/connection-pool-with-google-app-engine-and-google-cloud-sql – Mike Dee Jul 31 '19 at 14:49