Details
-
Task
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
Description
To quote Daniel Black from MDEV-5536:
If something like a max_idle_execution time maybe. There are avenues for integration with RuntimeMaxSec and extending timeout of runtime, and/or purely terminating the listening execution loop if no new connections are happening or queries running.
But please, new issue and state the requirement first.
Documentation will be coming in the next week or so https://mariadb.com/kb/en/systemd/
Use case: Socket activation of MariaDB is useful in several environments, including database farms and development stacks. If you value launching MariaDB lazily, though, you're likely to value putting it back into the "lazy launching" state after a period of idleness.
Put another way, if you're willing to launch MariaDB on the first access after boot, why wait until rebooting (most likely) to put it back into that state? Whether MariaDB is stopped ought not depend on whether the last query or a reboot occurred most recently, especially after days of time.
Implementation: Given that shutting down MariaDB on idleness makes the most sense when there's a way to wake it up again, I agree that using RuntimeMaxSec with socket activation is a sensible implementation path. It seems like the service could leverage EXTEND_TIMEOUT_USEC when it receives a query, consistent with the direction Daniel Black suggested above.
To avoid invoking EXTEND_TIMEOUT_USEC excessively, I would consider only sending it if the last invocation was more than some number of seconds ago (or, perhaps, by proportion of the configured idle shutdown duration). This will barely impact the delay before shutdown but spare systemd an incredible volume of extension requests when the database is active.
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
| Description |
To quote Daniel Black from {quote}If something like a max_idle_execution time maybe. There are avenues for integration with RuntimeMaxSec and extending timeout of runtime, and/or purely terminating the listening execution loop if no new connections are happening or queries running. But please, new issue and state the requirement first. Documentation will be coming in the next week or so https://mariadb.com/kb/en/systemd/{quote} *Use case:* Socket activation of MariaDB is useful in several environments, including database farms and development stacks. If you value launching MariaDB lazily, though, you're likely to value putting it back into the "lazy launching" state after a period of idleness. Put another way, if you're willing to launch MariaDB on the first access after book, why wait until rebooting to put it back into that state? Given that shutting down MariaDB on idleness makes the most sense when there's a way to wake it up again, I agree that using RuntimeMaxSec with socket activation is a sensible implementation path. It seems like the service could leverage EXTEND_TIMEOUT_USEC when it receives a query, consistent with the direction Daniel Black suggested above. To avoid invoking EXTEND_TIMEOUT_USEC excessively, I would consider only sending it if the last invocation was more than some number of seconds ago (or, perhaps, by proportion of the configured idle shutdown duration). This will barely impact the delay before shutdown but spare systemd an incredible volume of extension requests when the database is active. |
To quote Daniel Black from {quote}If something like a max_idle_execution time maybe. There are avenues for integration with RuntimeMaxSec and extending timeout of runtime, and/or purely terminating the listening execution loop if no new connections are happening or queries running. But please, new issue and state the requirement first. Documentation will be coming in the next week or so https://mariadb.com/kb/en/systemd/{quote} *Use case:* Socket activation of MariaDB is useful in several environments, including database farms and development stacks. If you value launching MariaDB lazily, though, you're likely to value putting it back into the "lazy launching" state after a period of idleness. Put another way, if you're willing to launch MariaDB on the first access after book, why wait until rebooting to put it back into that state? *Implementation:* Given that shutting down MariaDB on idleness makes the most sense when there's a way to wake it up again, I agree that using RuntimeMaxSec with socket activation is a sensible implementation path. It seems like the service could leverage EXTEND_TIMEOUT_USEC when it receives a query, consistent with the direction Daniel Black suggested above. To avoid invoking EXTEND_TIMEOUT_USEC excessively, I would consider only sending it if the last invocation was more than some number of seconds ago (or, perhaps, by proportion of the configured idle shutdown duration). This will barely impact the delay before shutdown but spare systemd an incredible volume of extension requests when the database is active. |
| Description |
To quote Daniel Black from {quote}If something like a max_idle_execution time maybe. There are avenues for integration with RuntimeMaxSec and extending timeout of runtime, and/or purely terminating the listening execution loop if no new connections are happening or queries running. But please, new issue and state the requirement first. Documentation will be coming in the next week or so https://mariadb.com/kb/en/systemd/{quote} *Use case:* Socket activation of MariaDB is useful in several environments, including database farms and development stacks. If you value launching MariaDB lazily, though, you're likely to value putting it back into the "lazy launching" state after a period of idleness. Put another way, if you're willing to launch MariaDB on the first access after book, why wait until rebooting to put it back into that state? *Implementation:* Given that shutting down MariaDB on idleness makes the most sense when there's a way to wake it up again, I agree that using RuntimeMaxSec with socket activation is a sensible implementation path. It seems like the service could leverage EXTEND_TIMEOUT_USEC when it receives a query, consistent with the direction Daniel Black suggested above. To avoid invoking EXTEND_TIMEOUT_USEC excessively, I would consider only sending it if the last invocation was more than some number of seconds ago (or, perhaps, by proportion of the configured idle shutdown duration). This will barely impact the delay before shutdown but spare systemd an incredible volume of extension requests when the database is active. |
To quote Daniel Black from {quote}If something like a max_idle_execution time maybe. There are avenues for integration with RuntimeMaxSec and extending timeout of runtime, and/or purely terminating the listening execution loop if no new connections are happening or queries running. But please, new issue and state the requirement first. Documentation will be coming in the next week or so https://mariadb.com/kb/en/systemd/{quote} *Use case:* Socket activation of MariaDB is useful in several environments, including database farms and development stacks. If you value launching MariaDB lazily, though, you're likely to value putting it back into the "lazy launching" state after a period of idleness. Put another way, if you're willing to launch MariaDB on the first access after boot, why wait until rebooting (most likely) to put it back into that state? Whether MariaDB is stopped ought not depend on whether the last query or a reboot occurred most recently, especially after days of time. *Implementation:* Given that shutting down MariaDB on idleness makes the most sense when there's a way to wake it up again, I agree that using RuntimeMaxSec with socket activation is a sensible implementation path. It seems like the service could leverage EXTEND_TIMEOUT_USEC when it receives a query, consistent with the direction Daniel Black suggested above. To avoid invoking EXTEND_TIMEOUT_USEC excessively, I would consider only sending it if the last invocation was more than some number of seconds ago (or, perhaps, by proportion of the configured idle shutdown duration). This will barely impact the delay before shutdown but spare systemd an incredible volume of extension requests when the database is active. |
| Assignee | Daniel Black [ danblack ] |
| Component/s | Server [ 13907 ] |
I elected to leave systemd extend timeout out of it, it seem to be putting in too many moving parts.
Suggestions on default time under socket activation welcome.
serg are you ok with the concept - https://github.com/MariaDB/server/commit/15c402efbbfec927d438b35bc7c13fc05d1a700d ?
Does this max-idle make any sense whatsoever without systemd socket activation?
If not (as I suspect) — let's hide it as much as possible. At least in non-systemd builds. But also in systemd builds, if not socket activated — if that can be achieved reasonably easy.
Does this max-idle make any sense whatsoever without systemd socket activation?
It might still be useful, but not easily. We use an out-of-band method for starting MariaDB that doesn't use socket activation, and there are various container orchestration approaches that can scale to zero by doing similar things. A database shutting itself down on extended idleness might still be helpful for scaling down to zero without having to monitor/poll MariaDB to look for signs of idleness (as we do now).
That said, most of these container platforms don't encapsulate services in systemd, so they'd need support for a notify-style interface that can process EXTEND_TIMEOUT_USEC to take advantage of this.
As far as I understand, scaling down is only useful if it can scale up on the next connection attempt. How can one do it without systemd socket activation?
Technically, you can have some kind of a connection proxy/router that monitors live servers, but in that case it's better to start/stop servers from the proxy anyway.
> As far as I understand, scaling down is only useful if it can scale up on the next connection attempt.
Agreed.
> How can one do it without systemd socket activation?
We expose a service on the host for MariaDB that can handle requests to wake the server. The client accessing the server has code that can request wake-up of the server if the socket can't connect. This works for our needs, but it wouldn't work for an arbitrary MariaDB client.
We've been moving to a client-side proxy in front of MariaDB for various reasons. We could move wake-up logic there, but we wouldn't want it to handle shutdown because a client-side proxy only knows when the database is down (and needs to start) but lacks enough information (in many cases) to know that the database has been idle. The proxy is only privy to the data flowing through it and what it has permission to access on the backend.
The database, on the other hand, has perfect ability to observe its own idleness.
| Fix Version/s | 10.6 [ 24028 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
serg can I please get a review on bb-10.6-danielblack-MDEV-25282-Auto-shutdown-on-idle-when-socket-activated
Updated: mtr tests now included.
| Assignee | Daniel Black [ danblack ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Priority | Minor [ 4 ] | Major [ 3 ] |
| Assignee | Sergei Golubchik [ serg ] | Daniel Black [ danblack ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Workflow | MariaDB v3 [ 120553 ] | MariaDB v4 [ 131746 ] |
review; https://lists.launchpad.net/maria-developers/msg12820.html
updated branch: bb-10.8-danielblack-MDEV-25282-Auto-shutdown-on-idle-when-socket-activated
needs windows diff and testing, but for now, just running it through bb.
| Fix Version/s | 10.9 [ 26905 ] | |
| Fix Version/s | 10.6 [ 24028 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Labels | energy |
| Fix Version/s | 10.11 [ 27614 ] |
| Fix Version/s | 10.9 [ 26905 ] |
| Labels | energy | energy foundation |
| Rank | Ranked higher |
Thanks davidstrauss, I was thinking of the same sort of excessive invocation of sd_notify protection.
I did get some advice to make sure that the socket was still useful for restart.