cloud sql(proxy) very slow in php codeigneiter

Question

i have a mysql connection using clud sql from Googlle cloud platform that i am using in my php(codeigneiter) application and i connect through cloud sql proxy. this instance has the following resources : 4 vcpu and 16 RAM.

the problem i have is that doing a querys speed test i have noticed that it is slow, i am comparing it with a machine with the same resources and local mysql and the speed is higher.

mysql proxy sql : 11 seconds

mysql local : 1.5 seconds as you can see, the problem is the sql proxy.

this is the code of the test that I am doing

Translated with www.DeepL.com/Translator (free version)

public function testSQL()
    {
        $time_start = microtime(true);
        for ($i=0; $i < 10000; $i++) {
            $sql = "SELECT * FROM test";
            $sqlResult = $this->db->query($sql)->result_array();        
        }
        $time_end = microtime(true);
        $time = $time_end - $time_start;
        echo $time;

    }

note that the version of php is 7.2 and the version of mysql in the instance and in mysql local is 8

When using the proxy, you are getting latency for all the various "connections" the proxy setup has to make .. To be honest, with a load of 10,000 queries, I am surprised it isn't higher than 11 seconds. — Zak, Jul 13 '22 at 16:18
hi @Zak thanks for answering. I did the test removing the sql proxy and connecting directly giving permissions to the public ip from the cloud sql instance. the result is almost the same, taking 8 seconds doing 10 thousand queries. do you have any idea what would be the best way to connect to cloud sql ? — user7649760, Jul 13 '22 at 16:41
Honestly 8 seconds isn't bad for a cloud SQL server for 10,000 queries .. If you think about it, every query has to make a connection, make a query, garbage collect, close connection. It's the "connection", and "close connection", that's bringing your latency. Honestly, I don't think this is the best test. If you have a program that's going to hammer queries, you need to be using functions and stored procedures on the MySQL Server so that all the work is done on the server side vs all that network traffic. — Zak, Jul 13 '22 at 16:49

score 0 · Answer 1 · answered Jul 14 '22 at 13:42

I think the data is being misinterpreted. Here's how I interpret you timings and the Comments:

1.5 seconds query time. (By the way, most of that is in overhead, not the actual SELECT)
6.5 seconds in network latency to the cloud.
3.0 seconds for the Proxy layer.

If you really have a client that needs to perform thousands of queries per second, I recommend looking at the queries -- combine them, batch them, put them in a stored proc, or even find a way not to need thousands.

Also, if you have multiple clients, each doing some of the 10000 queries, you will find that the "queries per second" will go up significantly for local and for cloud, with and without proxy. (But I don't recommend trying more than a few dozen clients; throughput eventually stalls, and latency suffers terribly.)

cloud sql(proxy) very slow in php codeigneiter

1 Answers1