Learn Vitess - Performance Optimization

#Introduction

Once the cluster runs securely and spans several regions, the question shifts: how fast can it go? Episode 15 covers Vitess performance optimization across three layers: queries and schema, the VTGate and VTTablet layers, then MySQL and storage. Performance is the combination of all three — optimizing just one layer gives limited results.

Episode 15 roadmap: tuning queries with indexing and vindexes, VTGate connection pooling, VTTablet resource tuning, then MySQL innodb and storage settings.

#Tuning Queries: Indexing and Vindexes

Query optimization starts with two things: MySQL indexes to speed up execution inside a shard, and vindexes to speed up routing between shards. They're different and complementary.

Standard MySQL indexes remain relevant: columns frequently used in WHERE and JOIN within one shard need regular indexes. But Vitess adds one extra rule: queries should always include the sharding key column so they don't scatter. This is the highest-impact optimization that doesn't exist in vanilla MySQL.

EXPLAIN SELECT name FROM users WHERE user_id = 42

EXPLAIN shows the execution plan — at VTGate, make sure the query only touches one shard. Use EXPLAIN VITESS to see the VTGate plan:

EXPLAIN VITESS SELECT name FROM users WHERE user_id = 42

The output shows whether the query is routed to one shard (fast path) or to all shards (scatter). Your target: hot-path queries are always fast path.

#VTGate Connection Pooling

Every application connection to VTGate consumes resources. VTGate uses connection pooling: a set of connections to VTTablet reused across many queries, reducing the overhead of creating MySQL connections. Configuration worth tuning:

• Pool size per target (PoolSize, StreamPoolSize) — how many connections to VTTablet.
• QueryTimeout — query time limit.
• MaxPayloadSize — maximum query payload size.

vtgate:
  extraFlags:
    mysql_server_pool_size: 128
    query_timeout: 30s
    max_payload_size: 16777216

mysql_server_pool_size: 128 gives VTGate 128 pooled connections per target. Rule of thumb: too small creates queues, too large overwhelms VTTablet. Measure with the metrics from episode 7, then tune based on real load.

#VTTablet Resource Tuning

VTTablet has two sides to its resource usage: running MySQL queries and running background work (replication, backup, VReplication). Important tuning:

• VTTablet PoolSize — number of concurrent queries that can run against MySQL.
• TransactionPoolSize — number of concurrent transactions.
• Resource limits — make sure the container has enough CPU and memory, and MySQL has proportional buffers.

vttablet:
  resources:
    requests:
      cpu: "2"
      memory: 4Gi
    limits:
      cpu: "4"
      memory: 8Gi

The resources.requests and limits above determine how much resource is guaranteed and capped for each tablet. Don't give MySQL a memory limit that's too small — large tables and the buffer pool need room.

Also watch the _vt tables inside the MySQL tablet: they store Vitess state like replication position. Monitor their growth because VReplication can create vreplication tables that need regular maintenance.

#MySQL Buffer, InnoDB, and Storage

At the bottom layer, MySQL needs to be tuned for its role. The highest-impact settings:

• innodb_buffer_pool_size — caches data and indexes in memory. General rule: 70-80% of the memory allocated to MySQL.
• innodb_flush_log_at_trx_commit — the balance between durability and write speed.
• max_connections — connection limit; too low makes queries fail.
• Storage — use SSD/NVMe; MySQL on HDD disks for OLTP workloads is a recipe for high latency.

innodb_buffer_pool_size = 6G
innodb_flush_log_at_trx_commit = 1
innodb_log_file_size = 1G
max_connections = 1000

The innodb_buffer_pool_size = 6G line gives MySQL a 6GB buffer pool for caching. MySQL configuration in Vitess is provided via a custom my.cnf mounted into the tablet.

To measure the impact of optimizations, compare metrics before and after:

curl -s http://localhost:15100/metrics | grep vttablet_query

curl -s http://localhost:15100/metrics on the tablet port shows query metrics — the baseline you should watch every time you change configuration.

#Closing

In this episode 15 you understood end-to-end Vitess performance optimization: making sure queries don't scatter with vindexes then use the right indexes, tuning VTGate connection pooling, tuning VTTablet resources, and setting MySQL innodb and storage.

Key takeaways:

• The biggest optimization is making sure hot-path queries don't scatter.
• Use EXPLAIN VITESS to see the query routing plan.
• VTGate connection pooling needs a size tuned from real load, not just set large.
• VTTablet needs enough resources for both queries and background work.
• innodb_buffer_pool_size is the most impactful MySQL setting.
• Every performance change must be measured with before-and-after metrics.

In the next episode, episode 16, we scale: resharding and scaling workflows — online resharding with VReplication, the difference between splitting and moving shards, scaling replicas, and vertical vs horizontal scaling. See you there!