** Description changed:
[Impact]
mysql-server-5.7.mysql.service (bionic) and mysql-
server-8.0.mysql.service (focal) have a TimeoutSec=600. This has the
effect of killing the MySQL process if this timeout is reached.
Very large databases can exceed the 600s timeout, and a safe tradeoff
between timing out at some point and waiting long enough to accommodate
large/huge databases does not seem to exist.
This issue has been fixed in Debian and in Ubuntu >= Hirsute by
disabling the timeout (TimeoutSec=infinity).
[Test Plan]
This is probably the most interesting bit of the SRU.
In order to test this for real, as opposed to testing systemd's
TimeoutSec, we need to make mysql very slow when it loads its tables.
One way it to actually have huge tables, but I have no idea on how big
they'd need to be. The other way is to slow down access to
/var/lib/mysql at the I/O level. Something on these lines:
- apt install mysql-server-8.0
- systemctl stop mysql
- cd /var/lib
- mv mysql mysql.bak
- truncate -s 300M mysql.blk
- losetup --show --find mysql.blk
+ apt install mysql-server-8.0
+ systemctl stop mysql
+ cd /var/lib
+ mv mysql mysql.bak
+ truncate -s 300M mysql.blk
+ losetup --show --find mysql.blk
- dmsetup create slowdev --table \
- "0 $(blockdev --getsz /dev/loopX) delay /dev/loopX 0 100"
- # With /dev/loopX as printed by losetup, 100 = 100ms r/w delay
- # See: https://www.kernel.org/doc/Documentation/device-mapper/delay.txt
+ dmsetup create slowdev --table \
+ "0 $(blockdev --getsz /dev/loopX) delay /dev/loopX 0 100"
+ # With /dev/loopX as printed by losetup, 100 = 100ms r/w delay
+ # See: https://www.kernel.org/doc/Documentation/device-mapper/delay.txt
- mkfs.ext4 /dev/mapper/slowdev
- mkdir mysql
- mount /dev/mapper/slowdev mysql
- chown mysql:mysql mysql
- cp -av mysql.bak/* mysql
+ mkfs.ext4 /dev/mapper/slowdev
+ mkdir mysql
+ mount /dev/mapper/slowdev mysql
+ chown mysql:mysql mysql
+ cp -av mysql.bak/* mysql
- systemctl start mysql # slow!
+ time systemctl start mysql # slow!
By tuning the delay parameter it is possible to trigger the timeout with
the pre-SRU package, and verify that post-SRU it can load in more than
10 minutes.
Note: this can be tested in an LXD VM but it requires booting the
linux-image-generic kernel, as linux-image-kvm doesn't ship the dm delay target.
The lxd-agent won't work, just ssh-import-id and ssh in.
(I think this is overkill for this SRU, but it's a quite general way to
make stuff slow. I'm sure it will come useful in other cases!)
[Where problems could occur]
The TimeoutSec=infinity syntax is supported by the systemd versions in
all the supported releases of Ubuntu, so this won't be a problem.
Then only change in behavior due to this change will happen on systems
where the timeout is reached, and mysql is thus killed. In these cases
the database server wouldn't be running in any case, but there could be
cases of bad or overgrown databases (e.g. because of a runaway script
adding infinite data) where the timeout is doing the right thing,
preventing mysql from consuming system resources. In these already
broken systems TimeoutSec=infinity may increase the breakage. This won't
affect working production systems.
[Development Fix]
[Stable Fix]
The same fix is already already landed in Hirsute, Impish and Debian
unstable.
[Original Description]
MySQL on 20.04 has TimeoutSec set to 600 (IIRC) in the systemd script.
This has the effect of killing the MySQL process if this timeout is
reached.
IMHO this is a Very Bad Idea. A database server process should only be
force killed by a user action.
I would prefer that the server had unlimited time to cleanly shutdown
and startup (eg if recovering).
Our DB is about 500GB with some very large tables (for us at least) eg.
250GB and we've had more than a few unfortunate delays as a result of
delayed startup caused by recoveries because MySQL was killed
prematurely.
Because MySQL 8.0 has reduced the default logging level, it was not
clear to me that the process was being force killed.
I believe the MySQL team are of the same view as me per
https://bugs.mysql.com/bug.php?id=91423:
```
[12 Jul 2019 15:57] Paul Dubois
Posted by developer:
Fixed in 8.0.18.
On Debian, long InnoDB recovery times at startup could cause systemd
service startup failure. The default systemd service timeout is now
disabled (consistent with RHEL) to prevent this from happening.
```
** Also affects: mysql-5.7 (Ubuntu)
Importance: Undecided
Status: New
** No longer affects: mysql-5.7 (Ubuntu Eoan)
** No longer affects: mysql-5.7 (Ubuntu Focal)
** No longer affects: mysql-5.7 (Ubuntu Bionic)
** Changed in: mysql-5.7 (Ubuntu)
Status: New => Triaged
** No longer affects: mysql-8.0 (Ubuntu Bionic)
** Changed in: mysql-5.7 (Ubuntu)
Assignee: (unassigned) => Paride Legovini (paride)
--
You received this bug notification because you are a member of Ubuntu
Bugs, which is subscribed to Ubuntu.
https://bugs.launchpad.net/bugs/1882527
Title:
mysql timeoutsec results in killing mysql process
To manage notifications about this bug go to:
https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1882527/+subscriptions
--
ubuntu-bugs mailing list
[email protected]
https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
