0

I have always set up machines to have a typical LAMP set up and recently I've been testing an external database set up with Google Cloud SQL. The performance hits I'm taking from it seem a bit unreasonable though.

The average load speed of my website with a local database is 0.04s. With a connection to Cloud SQL over SSL the load speed is 0.4s. ~10x slower, so I investigated this and learned about connection pooling and how PHP doesn't support it, but ODBC does and that's what my server seems to be using, so that's good but ~10x slower is still bad.

I tried setting up a Cloud SQL Proxy with the hope of that being a solution, but after setting it up earlier today it gave me a load speed of 5s. ~100x slower, so definitely not a solution.

Is there something that I can do to get a near local database speed using the Google Cloud Platform, should I use some other service for better performance, or do I just have to take this performance hit and live with it?

EDIT:
More information about the system. The web server is a compute instance on Google Cloud Compute. Both the Compute instance and the SQL instance are in the same zone using g1-small (1 vCPU, 1.7 GB memory).

It seems like the latency is about 1 millisecond.

Comparison of query runtimes: Local db on the left, Remote db on the right

Right now each of these queries is individually sent across the network, so using a multi query could save me a lot of time, assuming PHP actually sends all the query in a single network request.

Sunny J
  • 607
  • 3
  • 14
T..
  • 101
  • 3

1 Answers1

1

Assuming the Linux/Apache/PHP part of your stack runs on a local server and you are somewhere in the same hemisphere as your cloud database server...

But first, let's recap what is at play:

  1. Your PHP program sends the query to the database for execution.
  2. The database parses the query.
  3. The database executes the query and locate which rows to return.
  4. The database reads the rows from storage (disk) and returns them.
  5. The rows are transmitted to the PHP program.
  6. The PHP program processes the rows.

Let's assume that Google Cloud SQL is as efficient at doing 2, 3 and 4 as running MySQL on your hardware. There could be a difference particularly in step 4 if the Cloud SQL server is provisioned against slow storage but I don't know so we will ignore this factor for now.

So the factors to analyze are 1 (sending the query to the database) and 5 (sending the rows from the database to the PHP server).

When you run the database on the same box as your Apache/PHP server, there is virtually no delays in sending the query. The transmission of the rows from the database to the PHP program occurs over a Linux socket on the same host, so again, the speed is near instantaneous.

When you move your database on a different server than your Apache/PHP server, but within the same data center, you have 1) some networking delays for the query to get to the database (a negligible delay as the size is very small), and 5) the time it takes to transmit the results back to PHP. Depending how many rows are being returned, step 5 can take much longer than if the database was on the same host. However, this "much longer" is relative - it is really negligible in a normal data center with Gbps networking or better between servers.

Now, let's move the database server to Google Cloud. What's your connection to the Internet? Let's assume a high quality, low latency 100Mbps fiber optic connection to an ISP.

The latency of the network between your PHP server and the database just went from <1ms to >30ms - that's a 30x increase in latency. In practice, you'll see more than 30x increase because you're transmitting a lot more information - particularly at step 5 of returning the data from the database to the server.

The chart Latency Numbers Every Programmer Should Know, seen in many sites, articles and books, gives you good stable points to think with. Some places you can find it, in various forms.

Is there a solution? Without knowing more what you are trying to do, solve and produce, I would say the solution is simply to move the compute (PHP) to Google Cloud Platform, where it will be close to the database.

ETL
  • 6,513
  • 1
  • 28
  • 48