Question

Default wait_timeout set to 8 hours?!!!

Posted March 24, 2020 132 views
DigitalOcean Managed MySQL Database

After spinning up a 1vCPU / 1GB RAM / 10GB SSD MySQL managed database cluster, I’ve decided to check how the MySQL variables for the smallest version of this product were tuned.

This one caught my attention.

The following MySQL query shows the default wait_timeout variable.

show variables like 'wait%';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

The resulting value is in seconds. So yes, by default MySQL is set to wait 8+ hours before it severs the cord!

This may be good if you have long-running queries and actually want to wait for them (but even then, eight hours is absurd!) but terrible in most cases.

When a query is run, this value is set to 0 (meaning forever), but generally, this should be set to a very low value (5 seconds, for example, or maybe even less) to free up the connection for other processes.

I propose DO change this default value to perhaps 60 at most in their small scale default MySQL database cluster setups such as the entry level ones.

At the time of this writing, the following MySQL query is not permitted.

SET GLOBAL wait_timeout = 60;

Results in…

Error Code: 1227
Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
1 answer

I haven’t seen this specific setting, but after testing the managed database, I strongly recommended to DO to let us change some settings.

You can file a ticket and they might do it for you, but during my tests, I found this to be too much of a hassle for tuning. Also, the lack of deep metrics makes it just hard to tune anyway.

Crossing my fingers for some changes

Submit an Answer