Summary:
Original commit: b31b9ca5c4827e253bbf4aed4122afe05dddab5a / D44044
We currently have an issue where small, frequently-updated tables get analyzed too frequently by auto-analyze. This is an issue for tables that do not grow but are frequently modified by `UPDATE`s.
Auto-analye is triggered by counting the number of mutations (rows inserted, updated, or deleted) for each table and triggering an `ANALYZE` when the mutation count is greater than 20% of the total number of rows in the table.
As an example, let's say we have a table with 1 million rows and our app is doing 100 operations per second.
If a table is growing, the frequency of analyzes naturally slows down—with 100 inserts per second, it would take 2,000 seconds to go from 1 million to 1.2 million rows and trigger an analyze. The next analyze would then only be triggered when we hit 1.44 million rows (2,400 seconds).
However, if a table is small and not growing, its mutation count is constantly over the threshold, causing auto-analyze to trigger very frequently. For example, a 1,000-row table with 100 updates/sec will trigger an analyze on the table whenever the auto-analyze service wakes up (every 10 seconds by default).
In addition to the 20% tuple mutation threshold, we also add a time-based cooldown that must also be satisfied in order for each table to be analyzed. This cooldown starts at 10 seconds and doubles each time the table is analyzed, and it has a maximum value of one day.
So after the first analyze on a table, we have to wait 10 seconds before the second one can trigger. After the second analyze triggers, we have to wait another 20 seconds before the third one can trigger. See the below chart for the maximum number of auto-analyzes permitted by the new schedule
| Run # | Time since start | Interval just elapsed |
|-------|------------------|-----------------------|
| 0 | 0 d 00:00:00 | 0 s |
| 1 | 0 d 00:00:10 | 10 s |
| 2 | 0 d 00:00:30 | 20 s |
| 3 | 0 d 00:01:10 | 40 s |
| 4 | 0 d 00:02:30 | 80 s |
| 5 | 0 d 00:05:10 | 160 s |
| 6 | 0 d 00:10:30 | 320 s |
| 7 | 0 d 00:21:10 | 640 s |
| 8 | 0 d 00:42:30 | 1 280 s |
| 9 | 0 d 01:25:10 | 2 560 s |
| 10 | 0 d 02:50:30 | 5 120 s |
| 11 | 0 d 05:41:10 | 10 240 s |
| 12 | 0 d 11:22:30 | 20 480 s |
| 13 | 0 d 22:45:10 | 40 960 s |
| 14 | 1 d 21:30:30 | 81 920 s |
| 15 | 2 d 21:30:30 | 86 400 s |
Note: For simplicity, this table assumes that the mutations are always above the threshold; for some tables, once the cooldown has elapsed, we still have to wait for the threshold to to be crossed before analyze is triggered. (In that case, the cooldown does not affect the behavior of auto-analyze).
This diff repurposes the previously-unused `last_analyze_info` JSONB column in the auto-analyze service CQL table, which has one row per table. Each JSON column stores the timestamps of previous auto-analyze runs on that column as well as the cooldown until the next auto-analyze can run on that table. Here's an example:
```
lang=json
{
"history": [
{
"timestamp": 1709234567890123,
"cooldown": 10000000
},
{
"timestamp": 1709234577890123,
"cooldown": 20000000
},
{
"timestamp": 1709234587890123,
"cooldown": 40000000
}
]
}
```
We limit the history to store the most recent 100 events for each table to prevent the table from growing indefinitely.
When the auto-analyze service wakes up, it reads the most recent history entry and only analyzes the table if `now() > timestamp + cooldown`.
The minimum & maximum cooldowns and the scale factors are controlled by the following runtime flags:
| Behavior | Flag | Default |
| ------ | ----- | ---- |
| Minimum cooldown | `ysql_cluster_level_mutation_persist_interval_ms` (also controls frequency of auto-analyze service wake-ups) | 10 seconds |
| Maximum cooldown | `ysql_auto_analyze_max_cooldown_per_table` | 1 day |
| Minimum cooldown | `ysql_auto_analyze_min_cooldown_per_table` | 10 seconds |
| Cooldown scale factor | `ysql_auto_analyze_cooldown_per_table_scale_factor` | 2 |
`tostring.h` declares
```
lang=c++
std::string ToString(const std::chrono::steady_clock::time_point& time_point);
```
twice but doesn't define the function; this diff removes the extra declaration and adds the implementation to `tostring.cc`.
Jira: DB-17217
Test Plan:
```
./yb_build.sh release --sj --cxx-test pg_auto_analyze-test
```
Reviewers: sanketh, yguan
Reviewed By: yguan
Subscribers: yql
Differential Revision: https://phorge.dev.yugabyte.com/D46239