Never Lose Sleep Over Long-Running Queries Again: Introducing statement_timeout in CrateDB 5.4
Are you frequently grappling with queries that run longer than anticipated, perhaps accidentally initiated by you or another user? It's a common scenario, a minor miscalculation or an unexpected data anomaly leading to a query that runs for hours, hogging precious system resources. These situations can be not just frustrating but also disrupt your data workflows.
Fortunately, we've addressed this issue in our latest release. With the new statement_timeout setting, you can now define the maximum duration for any database statement, enhancing your control over query execution.
Understanding statement_timeout
The statement_timeout setting is a new safeguard against runaway queries. It sets an upper time limit for any statement execution before it gets canceled. By default, this value is set to 0, which means queries can run indefinitely as they did in previous versions. However, in CrateDB 5.4, you can modify this interval to suit your requirements, up to a maximum of approximately 24 days (2147483647 milliseconds).
You can modify statement_timeout at the session level. For instance, if you're working on a task that should not exceed five minutes, you can set the session statement_timeout as follows:
SET SESSION statement_timeout = '5m';
This change is specific to the current session and does not affect other sessions.
If you're looking to implement a time limit policy on a larger scale, CrateDB 5.4 offers a cluster-wide statement_timeout setting. This will set a default statement_timeout for all new sessions in the entire cluster. You can change the cluster-wide setting as follows:
SET GLOBAL PERSISTENT statement_timeout = '30m';
With this command, the default statement_timeout for all new sessions in your CrateDB cluster is set to 30 minutes.
Setting Session Settings with PostgreSQL Wire Protocol Clients
With CrateDB 5.4, we've added support for PostgreSQL wire protocol clients to set session settings via a new "options" property in the startup message. This feature can be incredibly handy when you want to set up specific settings for the duration of a session.
Let's illustrate how you can implement this using JDBC. To set a session-level statement_timeout, you can use the options parameter when establishing a connection:
Properties properties = new Properties(); properties.setProperty("user", "crate"); properties.setProperty("options", "statement_timeout='5m'"); try (Connection conn = DriverManager.getConnection("jdbc:crate://localhost:5432/", properties)) { // Your logic here }
In this example, the "options" property in the connection string is used to set the statement_timeout to 5 minutes for the current JDBC session. This setting will apply to all queries run within this session, offering an effective way to control resource usage.
The introduction of the statement_timeout setting in CrateDB 5.4 significantly improves your control over query executions. It's a key tool for optimizing resource usage and maintaining smooth, efficient data workflows. Yet, finding the right statement_timeout setting isn't a one-shot process. Regular monitoring and performance analysis of your queries are still crucial to identify and fine-tune long-running queries, so that they don’t need to timeout.
For more information, please refer to the official CrateDB documentation here (session setting) and here (cluster setting).
We hope you find this feature as useful as we intended it to be. Stay tuned for more updates and enhancements. Got more questions or just want to chat about these shiny new features? Jump into our CrateDB Community! We're also hosting upcoming office hours where we'll delve deep into these new features and more. Stay tuned to our CrateDB Community for the schedule.