Kubernetes部署MySQL写入操作响应缓慢问题排查请求

Let’s break down why your MySQL writes are crawling while queries stay snappy—here are the key areas to investigate, tailored to your deployment setup:

1. Storage Class (RBD) Performance Bottlenecks #

Your setup uses rbd-default (Ceph RBD block storage), which is often the culprit for slow writes in MySQL (a random I/O-heavy workload):

• Check RBD Replication & Media: Ceph RBD defaults to 3 replicas, which requires waiting for all replicas to acknowledge writes. If your cluster uses HDDs instead of SSDs, random write performance will plummet. Verify your Ceph cluster’s storage tier and replication settings.
• Optimize Mount Options: Add mount options to your PVC template to reduce write overhead. For example, discard (for thin provisioning) or noatime (disables access time logging) can help. Update your volumeClaimTemplates like this:

  volumeClaimTemplates:
  - metadata:
      name: mysql-persistent-storage
    spec:
      accessModes: [ "ReadWriteOnce" ]
      storageClassName: rbd-default
      mountOptions:
        - discard
        - noatime
      resources:
        requests:
          storage: 10Gi
  

• Test Storage I/O: Run a write performance test directly in the Pod to confirm storage is the bottleneck:

  kubectl exec -it mysql-0 -- fio --name=random-write --ioengine=posixaio --rw=randwrite --bs=4k --numjobs=1 --size=1G --iodepth=1 --runtime=60 --time_based --end_fsync=1
  

  Look for low IOPS (e.g., <100 random 4k writes) or high latency—this confirms storage is limiting writes.

2. Default MySQL Configuration Isn’t Optimized for K8s #

MySQL 8.0’s default settings prioritize data safety over speed, which can cripple write performance on block storage:

• Adjust InnoDB Flush Settings: The innodb_flush_log_at_trx_commit parameter controls how often logs are written to disk. Default value 1 forces a disk flush on every transaction—great for safety, but terrible for speed. Try setting it to 2 (flushes logs to OS cache every second, then to disk) or 0 (OS handles flushing) for a massive speed boost (note the trade-off in data durability):
  Add this env var to your container spec:

  env:
  - name: MYSQL_ROOT_PASSWORD
    value: password
  - name: MYSQL_INNODB_FLUSH_LOG_AT_TRX_COMMIT
    value: "2"
  

• Tune Buffer Pool Size: The innodb_buffer_pool_size should be set to ~70% of your Pod’s allocated memory (e.g., 1.4GB for a 2GB RAM Pod). This reduces disk writes by keeping more data in memory. Set it via an env var:

  - name: MYSQL_INNODB_BUFFER_POOL_SIZE
    value: "1400M"
  

• Sync Binlog Tuning: The sync_binlog parameter (default 1) syncs binary logs on every transaction. Setting it to 100 or 0 (disable sync) can reduce write overhead.

3. NodePort Network Overhead (External Connection Slowness) #

Your external connection via NodePort is far slower than Pod-internal operations, pointing to network forwarding bottlenecks:

• Check Kube-Proxy Mode: If your cluster uses iptables mode for kube-proxy, large numbers of rules can cause latency. Switching to IPVS mode significantly improves NodePort forwarding performance.
• Test Cluster-internal Access: Connect directly to the Pod’s IP (from another cluster Pod) and run write operations. If this is faster than NodePort, the issue is definitely with NodePort forwarding.
• Verify Client-to-Node Network: Check if your Visual Studio client is on a network with high latency to the Kubernetes nodes. Use ping or traceroute to measure RTT between your client and the node hosting the MySQL Pod.

4. Node & Pod Resource Constraints #

• Check Node Load: Use kubectl top node to see if the node hosting your MySQL Pod is under heavy CPU, memory, or disk I/O load. A busy node will throttle Pod performance.
• Monitor Pod Disk I/O: Run kubectl exec -it mysql-0 -- iostat -x 1 in the Pod to watch for high %util on the /var/lib/mysql mount point—this means the disk is fully saturated.

Quick Validation Steps #

1. First, test storage I/O to rule out the most common bottleneck.
2. Temporarily set innodb_flush_log_at_trx_commit=2 and see if write speed improves.
3. Compare Pod-internal vs. NodePort write times to isolate network issues.

内容的提问来源于stack exchange，提问作者Gill Varghese Sajan