Cloudsql-proxy: CloudSQL Proxy slow with PHP when got high traffic.

[ALTELMA]

I try to configure out for a slow connection from PHP to CloudSQL via Proxy, running on GKE.
So I think it causes from PHP, but I saw some logs such as

New connection for "xxxxxx" and wait for a while around 4-5 seconds the connection is closed.
However, I don't want to blame CloudSQL, but Anyone can confirm the root cause not come from it.

FYI, I already ask this question at Google Forum, but no one can help me :(

[kurtisvg]

Hi @ALTELMA - Can you please clarify by what you mean by "slow connection"?

If you believe the issue is related to the proxy, it helps to have some comparison with/without or at least steps to reproduce.

[ALTELMA]

Sorry for a little information. So I have migrated out the application from the old one that maintenance by another company to a new infrastructure with a new change such as Cloud SQL etc.

After we finished the migration look like a performance dropped. I try to do a load test using WRK compare between the old version and the new version of infra.

Test with a URL to get user profile by id:
OLD:
Total Request: 9000 - 9500
RPS: around 79

New:
Total Request: 210
RPS: 34

Now I am trying to find what is the bottleneck?
For another question, I wonder about k8s config between the app and CloudSQL container should relate to this issue?

apiVersion: apps/v1
kind: Deployment
metadata:
  name: backend
spec:
  replicas: 1
  selector:
    matchLabels:
      app: backend
  strategy:
    type: RollingUpdate
    rollingUpdate:
      maxSurge: 1
      maxUnavailable: 1
  template:
    metadata:
      labels:
        app: backend
    spec:
      restartPolicy: Always
      containers:
        - name: backend
          image: asia.gcr.io/<project-name>/app-image:latest
          imagePullPolicy: Always
          ports:
            - name: http
              containerPort: 80
          volumeMounts:
            - name: cloud-storage-volume
              mountPath: /var/www/storage/keys
              readOnly: true
          env:
            - name: DB_USERNAME
              valueFrom:
                secretKeyRef:
                  name: database
                  key: db-username
            - name: DB_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: database
                  key: db-password
          resources:
            requests:
              memory: "256Mi"
              cpu: "100m"
            limits:
              memory: "3G"
              cpu: "100m"

        - name: cloudsql-proxy
          image: gcr.io/cloudsql-docker/gce-proxy:1.14
          command:
            [
              "/cloud_sql_proxy",
              "-instances=<project-name>:<region>:<instance-connection-name>-1=tcp:5432",
              "-credential_file=/secrets/cloudsql/credentials.json",
            ]
          securityContext:
            runAsUser: 2
            allowPrivilegeEscalation: false
          volumeMounts:
            - name: cloudsql-secrets-volume
              mountPath: /secrets/cloudsql
              readOnly: true
          resources:
            requests:
              memory: "256Mi"
              cpu: "100m"
            limits:
              memory: "3G"
              cpu: "100m"
      volumes:
        - name: cloudsql-secrets-volume
          secret:
            secretName: cloudsql-instance-credentials

What is the best practice for this? Should be create a new service for CloudSQL or not?

[kurtisvg]

I'm going to mark this as closed since there isn't anything to suggest that it's an issue with the Cloud SQL proxy. If you find any evidence to suggest the proxy is causing problems, we can revist.

Generally when connecting from k8s, I recommend running the proxy in a side car (like you are doing). This distributes the point of failure for the proxy and helps scale resources proportionately to the number of pods your application is using.

While it's hard to say definitely without more information, but my guess is your problem is your limits.cpu configurations. You have each container limited to 100m, which is .1 CPU. My guess is your original infrastructure provided significantly more CPU, which is the cause of variance in the performance.

[ALTELMA]

Yes, you right!! I do something stupid. So this issue not related to the Cloud SQL proxy. It just happened from k8s configuration. Anyway thanks for your suggestions :)

[lgelfan]

@kurtisvg - are there best practice recommendations for setting the resource specs for the proxy? I rarely see it spec'd (possibly to avoid situations like this one) and when I do, it's usually pretty low. In the Rimusz chart, e.g., I see the requests/limits set to :

Memory: 100/150Mi, CPU: 100/150m

which seems low on the limit side.

I realize it's highly dependent on use case and in the sidecar approach, the resource specs on the primary app (assuming those are properly tuned) will _likely_ cover the total use. I.e., the app is more likely to hit a resource limit under heavy load than the proxy, and it would then kill the pod. However, I want to make sure we don't accidentally resource-starve the node in case something goes wrong.

[kurtisvg]

@kurtisvg - are there best practice recommendations for setting the resource specs for the proxy? I rarely see it spec'd (possibly to avoid situations like this one) and when I do, it's usually pretty low. In the Rimusz chart, e.g., I see the requests/limits set to :

Memory: 100/150Mi, CPU: 100/150m

which seems low on the limit side.

I realize it's highly dependent on use case and in the sidecar approach, the resource specs on the primary app (assuming those are properly tuned) will _likely_ cover the total use. I.e., the app is more likely to hit a resource limit under heavy load than the proxy, and it would then kill the pod. However, I want to make sure we don't accidentally resource-starve the node in case something goes wrong.

Unfortunately I don't have an advice besides running in for a while with your application under load and taking note of average/max/min resource consumption. The proxy's use of resources is essentially a function of how heavily it is used - more connections means more memory and more data sent means more CPU usage. This will highly vary on the application.