Optimizing connection to GCP mysql from cloud run service?

Question

I have a spring boot application running on cloud run, so far I only had to add the spring cloud gcp mysql

    <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-gcp-starter-sql-mysql</artifactId>
        <version>1.2.8.RELEASE</version>
    </dependency>

dependency in my POM, and configure my application.yml file to set database name, connection name etc, and it runs fine locally and on cloud run.

My application.yml:

spring:
    cloud:
        gcp:
            sql:
                enabled: true
                database-name: pos_database
                instance-connection-name: pos-sys:asia-southeast2:pos-server-database
    datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: ***
        password: ***
        hikari:
            maximum-pool-size: 20

However I realized cold start performance has taken a hit, because on startup the socket factory connects to the database instance via SSL socket:

2021-05-31 13:10:07.152  INFO 1539 --- [onnection adder] c.g.cloud.sql.core.CoreSocketFactory     : 
Connecting to Cloud SQL instance [pos-sys:asia-southeast2:owl-server-database] via SSL socket.

and i get a bunch of lines just repeating

2021-05-31 13:10:09.461  INFO 1539 --- [connection adder] c.g.cloud.sql.core.CoreSocketFactory     : 
Connecting to Cloud SQL instance [pos-sys:asia-southeast2:pos-server-database] via SSL socket.

I know there is a faster way to connect then the application is running on the cloud, I have been following this tutorial so far:

https://cloud.google.com/sql/docs/mysql/connect-run

But i'm very confused on the last part where it says I have to connect with unix socket, is this a docker thing or within my application? where does the ConnectionPoolContextListener.java file have to go? It also says in a comment within the file itself not to use this for java users, and to instead use

Cloud SQL JDBC Socket Factory But when I go to that link it says to add a dependency to for mysql-connector, but isnt that already included in spring-gcp-starter-mysql? It also says make a connection string in this format:

jdbc:mysql:///<DATABASE_NAME>?cloudSqlInstance=<INSTANCE_CONNECTION_NAME>&socketFactory=com.google.cloud.sql.mysql.SocketFactory&user=<MYSQL_USER_NAME>&password=<MYSQL_USER_PASSWORD> 

But doesnt mention where do I put this?

So to summarise:

1. I have a cloud mysql instance, with the admin api enabled.

2. I did the Enable connecting to a Cloud SQL in my cloud run by selecting my db instance.

3. I am very confused by the documentation on what the next step is and what to do next.

Answer 1

Cloud Run provide a Unix domain socket when configured with a Cloud SQL instance - it's a file that can be used to connect to a database. You are using the Cloud SQL Java connector, which allows you to bypass using the Unix socket (which is usually preferred on Java, since Unix sockets aren't natively supported).

Instead to improve your cold start time, I recommend doing two things:

1. Reduce the number of connections in your pool. While the optimal number varies greatly between applications, 20 is almost certainly way more than you need. As a rule of thumb, try 2 * the number of cores used as your starting value, and increase/decrease as needed. Hikari uses maximumPoolSize to do this.

2. Adjust the number of starting connections in your pool. Hikari offers minimumIdle, which sets the minimum number of idle connections in the pool, and up to maximumPoolSize. While Hikari recommends not setting this value (so you have a fixed pool), setting it to 0 means your pool won't establish connections on startup. This means your application will start faster, but will take longer to get a connection from the pool on average.