Allow Mysql Slow Query log on read-only replicas
As a mysql administrator, I would like to be able to enable the slow query log on a read-only replica.
Current state.
The slow query log can have one of two output destinations. Log file, and the mysql.slowlog table on the server itself. Docs for reference
https://dev.mysql.com/doc/refman/8.0/en/log-destinations.html
Log Output:
This currently cannot be configured for Aiven managed Mysql services. This is because customers do not have access to the underlying filesystem for service nodes.
Table Output:
This is the output destination used by Aiven hosted Mysql services, for the same reasoning above.
This means that when an Aiven managed Mysql service enables the slow query log through advanced config, the output destination will be the mysql.slowlog table.
https://docs.aiven.io/docs/products/mysql/reference/advanced-params#slow-query-log
This is a problem for read replicas, because the slow query log cannot be written to. The following error is produced in such cases on the read replica server.
"[Server] Failed to write to mysql.slow_log: The MySQL server is running with the --read-only option so it cannot execute this statement (cannot open table for slow log)"
Because of the above reasons, it is currently impossible for a customer to enable and use the slow query log on a read replica. Any slow query logging would have to be done on the main service.
Read replicas have a fundamentally different type of workload compared to main nodes. so monitoring slow queries on the main service will might not be a valid approach, depending on use case and setup.
For these reasons, a feature allowing the slow query log to be enabled on a read replica would be beneficial.