4

I've created a script to cleanup InnoDB storage engine. Everything works fine except that creating the ib_logfile0 and ib_logfile1 files when innodb_log_file_size is set to 1GB takes a while which causes /etc/init.d/mysql to timeout and report a failure, although in the background MySQL ends up starting normally.

Starting mysql

$ /etc/init.d/mysql start
* Starting MariaDB database server mysqld  [fail]

Meanwhile in the logs

$ tail -f /var/log/mysql.err
120426 11:19:55  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 1024 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
120426 11:20:07  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 1024 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
[...]
120426 11:20:24 [Note] /usr/sbin/mysqld: ready for connections.
# Version: '5.5.23-MariaDB-mariadb1~oneiric-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution

I am looking for a way to prevent the startup script from timing out. When I look at /etc/init.d/mysql, I can see the ${MYSQLD_STARTUP_TIMEOUT} variable but don't know where it is set. I've also been looking in the documentation for such an option but couldn't find it.

Q1: Can I specify a custom startup timeout as a parameter to /etc/init.d/mysql ?
Q2: What option can I modify to change the server startup timeout in the configuration?

Community
  • 1
Max
  • 3,373
  • 15
  • 51
  • 71
  • The real question is why is it taking MariaDB over 900 seconds to start up. If you are running this on a 4800rpm laptop drive I would expect it to start up in < 60 seconds. Something else is wrong. Also is it really creating the log files every time? This should only happen when you initialize the database. – Levi Apr 27 '12 at 03:14
  • @Levi probably not his case, but when using a cluster, the MySQL/MariaDB start times can take quite a while as they need to sync with each other, which is my case. And the syncing failing because of the service timeout causes some really nasty side effects on the donor node (you have to kill, and restart it) – Brian Leishman Jul 24 '17 at 15:10

3 Answers3

3

I know this is an old question, but I'm posting in case anyone else is looking for a better/different answer. I've been having a similar problem, but when installing upgrades. Ours takes a long time to start as it is part of a Galera cluster and needs to copy over what it missed while it was down.

To start run:

MYSQLD_STARTUP_TIMEOUT=900 /etc/init.d/mysql start

or on a system with service, like Ubuntu:

sudo MYSQLD_STARTUP_TIMEOUT=900 service mysql start

And in my case, for upgrades call:

sudo MYSQLD_STARTUP_TIMEOUT=900 apt-get dist-upgrade
Luke Cousins
  • 377
  • 1
  • 3
  • 18
  • You are amazing, that timeout causes the donor node to get stuck in perma donor mode, so I have to kill, restart, pray, etc. to get the cluster up – Brian Leishman Jul 24 '17 at 15:12
1

You can use the following option on startup:

# 900 is the default, 0 won't wait at all
--service-startup-timeout=900

In your init script or my.cnf it would look like:

service_startup_timeout=900

More documentation is located here http://dev.mysql.com/doc/refman/5.5/en/mysql-server.html

To time the startup script to see if it's taking the new timeout value, use Linux's time command in conjunction

# time /etc/init.d/mysql start

Compare the reported execution time with the timeout value, see if it changes when you change the timeout, if not, then the option is either not working as expected (maybe MariaDB doesn't support it) or it's declared incorrectly in the config.

sreimer
  • 2,168
  • 14
  • 17
  • Seems plausible to me, +1 !!! @user64204 you could just set `service_startup_timeout=9000` in /etc/init.d/mysql. The time increase is necessary since the InnoDB Log Files must not only be created but also formatted. – RolandoMySQLDBA Apr 26 '12 at 16:42
  • unfortunately adding `--service-startup-timeout=N` to `/etc/init.d/mysqld start` makes no difference. I'm running `MySQL 5.5.22` – Max Apr 27 '12 at 07:01
  • does it always exit after the same amount of time? – sreimer Apr 27 '12 at 14:49
  • Also, I had incorrectly said that 0 will wait indefinitely which is not the case, 0 causes it to not wait at all. – sreimer Apr 27 '12 at 14:57
1

On modern distributions systemd is used to start the service. If you have tried MYSQLD_STARTUP_TIMEOUT and it has not worked, you are probably using this. The /etc/init.d/mysql script is no longer used, so MYSQLD_STARTUP_TIMEOUT has no effect.

You need to find your mariadb.service file. In our case, it did not contain a timeout so the MariaDB default was being used. Just add:

TimeoutStartSec = 0

In the [Service] section, and it will never time out.

It would be a good idea to create your own config file containing this so it doesn't get overwritten by later re-installs.

On ubuntu 18.04, you will find this file in 

/lib/systemd/system/mariadb.service

Put your own drop in file in

/etc/systemd/system/mariadb.service.d

Remember to run systemctl daemon-reload after adding the timeout somewhere (and maybe check /var/log/syslog to see if the reload was successful)

Gerald Schneider
  • 19,757
  • 8
  • 52
  • 79
Dave Hindle
  • 11
  • 2