In this blog post, we are going to use "Vibe Coding" to build an application and explore Readyset QueryPilot, a tool designed to automatically analyze and cache queries in a MySQL workload.
The goal is to prove that even for automated queries with no human interaction, QueryPilot can automate query caching and improve performance. QueryPilot automatically identifies which queries should be cached and recommends the appropriate strategy, either deep caching or shallow caching. This automation delivers optimized performance without manual intervention, application code changes, or ongoing tuning.
The first objective is to establish a connection between our MySQL instance and Readyset QueryPilot.
QueryPilot operates as a layer that automates query caching for MySQL. It continuously updates caches and routes optimized queries, aiming to reduce the resources spent on manual performance tuning. For technical insight into how Readyset achieves this, you can review the blog post: Behind the Magic: How Readyset Speeds Up Queries with Streaming Dataflow.
QueryPilot is now officially generally available (GA). For this demonstration, I am using the free account provided by Readyset for a 2-month trial period.
This is a free account provided by Readyset for a 2-month trial period. We will use the QueryPilot option.
Establishing the connection requires creating a user with the necessary permissions for Readyset to replicate data. I created the following user in my database:
| mysql> create user readyset_repl@'%' identified by 'readyset_repl';Query OK, 0 rows affected (0.02 sec)mysql> grant all privileges on *.* to readyset_repl@'%' with grant option;Query OK, 0 rows affected (0.01 sec) |
|---|
After creating the user, input these credentials into the Readyset QueryPilot UI:
To confirm that Readyset has successfully connected and is actively monitoring our database for changes, we can check the MySQL process list and look for the connections created by the user we configured earlier. This is a great way to verify the replication link.
| mysql> show processlist;+----+-----------------+--------------------------------------------------------+-----------+-------------+------+-----------------------------------------------------------------+------------------+---------+-----------+---------------+| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined | +----+-----------------+--------------------------------------------------------+-----------+-------------+------+-----------------------------------------------------------------+------------------+---------+-----------+---------------+| 6 | event_scheduler | localhost | NULL | Daemon | 992 | Waiting on empty queue | NULL | 992236 | 0 | 0 | | 10 | root | localhost | employees | Query | 0 | init | show processlist | 0 | 0 | 0 | | 30 | readyset_repl | ec2-18-217-86-71.us-east-2.compute.amazonaws.com:41136 | employees | Sleep | 4 | | NULL | 4078 | 0 | 0 | | 40 | readyset_repl | ec2-18-217-86-71.us-east-2.compute.amazonaws.com:37378 | employees | Binlog Dump | 213 | Source has sent all binlog to replica; waiting for more updates | NULL | 212905 | 0 | 0 | | 54 | readyset_repl | ec2-3-21-232-209.us-east-2.compute.amazonaws.com:40022 | employees | Sleep | 0 | | NULL | 602 | 0 | 0 | | 71 | readyset_repl | ec2-3-21-232-209.us-east-2.compute.amazonaws.com:36060 | employees | Binlog Dump | 188 | Source has sent all binlog to replica; waiting for more updates | NULL | 188051 | 0 | 0 | +----+-----------------+--------------------------------------------------------+-----------+-------------+------+-----------------------------------------------------------------+------------------+---------+-----------+---------------+6 rows in set, 1 warning (0.00 sec) |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---- | ---- | -- | ------- | ---- | ----- | ---- | -------- | ---------- | -------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ---------------- | --------- | ---- | ------ | --- | ---------------------- | ---- | ------ | - | - | ---- | ---- | --------- | --------- | ----- | - | ---- | ---------------- | - | - | - | ---- | -------------- | ------------------------------------------------------ | --------- | ----- | - | | ---- | ---- | - | - | ---- | -------------- | ------------------------------------------------------ | --------- | ----------- | --- | --------------------------------------------------------------- | ---- | ------ | - | - | ---- | -------------- | ------------------------------------------------------ | --------- | ----- | - | | ---- | --- | - | - | ---- | -------------- | ------------------------------------------------------ | --------- | ----------- | --- | --------------------------------------------------------------- | ---- | ------ | - | - | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
The presence of the readyset_repl user running the Binlog Dump command indicates a successful connection. QueryPilot is now ready to begin analyzing our queries. We can also confirm in the QueryPilot interface that the cluster is healthy and QueryPilot is enabled.
Now, let's get into the "Vibe Coding." I asked Google Gemini 3.0 Pro to create a Go application that simulates a CRM being used by different departments.
- The Prompt: You can view the prompt I used to generate the application here.
- The Code: The full Go code created by Gemini is available here.
The only modification I made to the AI-generated code was changing the DBHost. Instead of connecting directly to the MySQL database, I pointed it to the QueryPilot instance. No further optimizations were made to the queries or the logic.
| // --- 1. CONFIGURATION ---const ( DBUser = "readyset_repl" DBPass = "readyset_repl" // DBHost = "54.92.204.66" <-- Direct MySQL DBHost = "vini-0e85fbceec19f4b057317113.readyset.cloud" // <-- QueryPilot DBPort = "3306" DBName = "employees") |
|---|
I ran the simulation with the following parameters:
| --- STARTING POOLED CRM SIMULATION ---Requests : 1000Concurrency : 20Strategy : Persistent Connection Pool (Reuse)==================================================CRM POOLED PERFORMANCETotal Time : 16.0053 sThroughput (QPS) : 62.48Successful : 1000Errors : 0 |
|---|
Once the script started, I immediately saw traffic reflected in the QueryPilot console. After the system processed the workload, the queries began to cache automatically. In the bottom right of the dashboard, I observed 9 queries cached.
A look at the Cached tab in QueryPilot reveals two primary types of caches, and this is where QueryPilot really shines. Instead of the tedious work of manually determining which queries to cache, QueryPilot handles the analysis and identification for you and recommends the best caching strategy available.
Deep Cache: Deep caching stores the query’s results inside Readyset’s dataflow graph, allowing Readyset to maintain incrementally updated and consistent results that support efficient low-latency reads at scale.
Shallow Cache: Shallow caches are applied at the query layer and return quicker responses for common queries, reducing load on Readyset and the primary database.
In this AI-generated workload of 10 distinct query patterns:
- 8 queries were established as "Deep Cache."
- 1 query utilized "Shallow Cache."
We observed a divergence in system behavior as the workload intensity increased.
In the initial tests with 1,000 total requests, both setups performed similarly, with QueryPilot showing a slight advantage as concurrency moved from 50 to 200 threads.
However, the performance profiles separated significantly when the load was increased to 5,000 requests. At this volume, Readyset QueryPilot outperformed the direct MySQL connection by a wide margin.
And the trend persisted and became more pronounced at 8,000 requests:
While this was a specific stress test rather than a formal benchmark, it illustrates how an intermediate caching layer can protect the primary database from resource exhaustion, particularly when dealing with unoptimized queries generated by AI tools or ORMs.
This experiment demonstrates that introducing a caching layer like QueryPilot into the data path can stabilize read-heavy workloads that might otherwise overwhelm a standard MySQL instance. It also reduces operational overhead by removing the need to hand-select and manage cached queries, which is often one of the most time-consuming parts of performance tuning.
For teams where business logic takes precedence over deep database tuning—or for deployments relying on automated code generation—this architecture offers a way to manage performance without code refactoring. QueryPilot serves as an alternative to vertical scaling or manual query optimization when facing high concurrency. It also improves stability under peak load without requiring schema changes, index redesigns, or application modifications, making it a lower-effort path to scale compared to overprovisioning database hardware.
If you are interested in the mechanics of query caching and database scaling, you may find these blog posts relevant:
Readyset Team
Readyset
Tanmay Sinha