Vitess Slow Log Analysis: Find and Fix Slow Queries (2026)

Vitess, the database clustering system for MySQL, surfaces a wealth of diagnostic information, but its "slow log" is a goldmine for performance tuning. This isn’t just a list of slow queries; it’s a window into how Vitess is interacting with your MySQL instances and where bottlenecks are forming.

Let’s say you’re seeing intermittent query latency or your application is complaining about slow responses. The first place to look is Vitess’s vtgate and vttablet logs for slow query entries. These logs, by default, capture queries that exceed a certain threshold (e.g., 100ms).

Here’s a breakdown of what you’ll see and how to diagnose and fix the common culprits.

The Vitess Slow Log Entry

A typical slow log entry might look something like this:

I1101 10:00:00.123456 12345 12345 vttablet/vttablet.go:1234] [query] id=1000 ts="2023-11-01 10:00:00.123456" client_addr="10.0.1.2:54321" query="SELECT * FROM users WHERE id = ?" duration=512ms bind_vars={id: 12345} tablet_uid=101 sql_mode="..."

Key fields here are:

• duration: The time taken for the query to execute.
• query: The SQL statement.
• bind_vars: The parameters passed to the query.
• tablet_uid: The ID of the specific MySQL instance that executed the query.

Common Causes and Fixes

1. Inefficient SQL Query:

   • Diagnosis: The query itself is poorly written. This is the most common cause. Look for SELECT *, missing WHERE clauses, or complex joins that aren’t optimized. The bind_vars help you see the actual query being run.
   • Fix: Rewrite the SQL. For example, if you see SELECT * FROM orders WHERE order_date BETWEEN ? AND ? and this is slow, ensure you have an index on order_date. If it’s a complex join, consider if it can be broken down or if indexes are missing on join columns.
   • Why it works: A well-indexed and structured SQL query allows the database to find the requested data much faster, minimizing disk I/O and CPU usage.

2. Missing or Ineffective Indexes:

   • Diagnosis: The query is fine, but the underlying MySQL table lacks appropriate indexes. The EXPLAIN command on the specific MySQL instance (using vtctlclient Execute or directly connecting to the MySQL instance identified by tablet_uid) will reveal a full table scan (type: ALL).
   • Fix: Add an index. For a query like SELECT name FROM users WHERE email = ?, if email is not indexed, run:

     ALTER TABLE users ADD INDEX idx_email (email);
     

   • Why it works: Indexes create a lookup structure (like a B-tree) that allows the database to quickly locate rows based on column values, avoiding the need to scan the entire table.

3. Vitess vtgate Bottleneck:

   • Diagnosis: The duration in the slow log is high, but the MySQL query execution time (which you can measure by running EXPLAIN and then the query directly on the tablet) is low. This suggests vtgate is taking too long to process the request, route it, or aggregate results. Check vtgate’s CPU and network utilization.
   • Fix: Scale out vtgate instances. Increase the number of vtgate pods or VMs.
   • Why it works: Distributing the query routing and aggregation load across more vtgate instances reduces the processing burden on any single instance, allowing it to handle requests more quickly.

4. Vitess vttablet Bottleneck:

   • Diagnosis: The duration is high, and the MySQL query execution time is also high. However, the average query execution time on that specific tablet is high, and the vttablet itself is showing high CPU or memory usage. This indicates the vttablet process is struggling to manage connections or process the query.
   • Fix: Scale out vttablet instances for that shard. If you have a single vttablet for a shard, add more replicas. Ensure the vttablet has sufficient resources (CPU, RAM). You might also need to tune MySQL parameters on the underlying instance.
   • Why it works: More vttablet instances can handle more concurrent requests, and ensuring adequate resources prevents the vttablet process from becoming a CPU or memory bottleneck itself.

5. MySQL Instance Overload:

   • Diagnosis: The duration is high, and the MySQL query execution time is high. Monitoring metrics for the specific tablet_uid show high CPU, high I/O wait, or a large number of connections. The SHOW GLOBAL STATUS LIKE 'Threads_running'; on the MySQL instance will be consistently high.
   • Fix:
     • Scale Up MySQL: Increase the CPU, RAM, or I/O capabilities of the underlying MySQL server.
     • Sharding/Resharding: If the overload is due to data volume, Vitess’s sharding capabilities can distribute the data and load across multiple MySQL instances. This might involve resharding your existing keyspace.
     • Connection Pooling: Ensure your application is using effective connection pooling to avoid constantly opening and closing connections, which adds overhead. Vitess’s vttablet has built-in connection management, but application-level pooling is still crucial.
   • Why it works: A more powerful MySQL server can process queries faster. Sharding distributes the load, and efficient connection management reduces the overhead on the MySQL server.

6. Network Latency Between vtgate and vttablet:

   • Diagnosis: The duration is high, but both vtgate and the MySQL instance appear healthy. The slow log might show high duration for many queries across different tablet_uids. Check network latency metrics between your vtgate deployment and your vttablet pods/VMs.
   • Fix: Optimize network configuration. This could involve ensuring vtgate and vttablet are in the same network region/availability zone, or investigating network hardware issues.
   • Why it works: Faster network communication between the query router (vtgate) and the database handler (vttablet) directly reduces the time spent waiting for data or acknowledgments.

7. Long-Running Transactions:

   • Diagnosis: Slow queries appear to be part of larger transactions that are taking a long time to commit or rollback. This can block other queries from acquiring necessary locks. Check the information_schema.INNODB_TRX table on the affected MySQL instance.
   • Fix: Identify and optimize the slow parts of these long-running transactions. Break them down into smaller, more manageable transactions. Ensure proper transaction isolation levels are used.
   • Why it works: Shorter transactions reduce the window for lock contention and improve overall concurrency, allowing more queries to proceed without waiting.

The Next Hurdle

Once you’ve addressed query performance and Vitess/MySQL resource issues, you’ll likely start seeing more detailed errors from Vitess’s health checks, such as tablet server is not healthy or specific connection errors, indicating that the underlying components are now under scrutiny for their own availability.