Overdue PR.

Startup and shutdown are quite different. I would consider them separately.

Startup

For startup, the biggest time consumers should be redo log apply and the buffer pool load. For the redo log apply, we cannot know in advance how much work there is to be done. MDEV-11027 introduced progress reporting for this, including a call to sd_notifyf().

Undo recovery (rolling back incomplete transactions) takes place in a background thread. Starting with MDEV-12323, we do report progress for that as well via sd_notifyf(). If the binlog is used, then the tc_heuristic_recover option can come into play, internally initiating XA COMMIT or XA ROLLBACK operations in some thread. I do not know if that would block user connections from starting at all. Commit is fast in InnoDB, while the rollback speed is proportional to the number of modified rows and the number of indexes.

Shutdown

In shutdown, there have been multiple hangs. Most recently, I fixed MDEV-15554.

There are different innodb_fast_shutdown modes. By default (innodb_fast_shutdown=1, a fast shutdown is done: all dirty pages are flushed to the buffer pool and a log checkpoint is created.

Some progress reporting for the flush phase would be nice. We know exactly how many dirty pages there are, so the progress measure would be linear.

There is also the crash-like innodb_fast_shutdown=2 which is supposed to complete instantly.

The slowest shutdown is innodb_fast_shutdown=0. It will:

1. Wait for all transactions to complete (starting with MDEV-12352, normal shutdown would abort the rollback of recovered transactions)
2. Purge the history of all completed transactions (well, except when it fails to do so due to MDEV-11802)
3. Empty the change buffer (formerly known as the insert buffer) by merging changes to secondary index leaf pages.

Only after these tasks, it becomes feasible to flush the dirty pages into the data files and to create the final redo log checkpoint.

quick note - for the purpose of systemd timing startup start from process creation and ends just before accepting the first connection.

• startup time ends: https://github.com/MariaDB/server/blob/10.3/sql/mysqld.cc#L6680
• The end shutdown time starts: https://github.com/MariaDB/server/blob/10.3/sql/mysqld.cc#L6882 until the final exit.

On startup I haven't included the buffer pool load because as I understand it it continues in the background while the server is processing queries.

I have included the buffer pool save on shutdown too.

Exact time need not be known. Only that a loop iteration can progress within a estimated maximium time.

@marko the startup has to consider Galera SST time - can be hours on larger datasets

rpizzi, SST scripts will be able to communicate just as easily as the server. The tricky bit is identifying the lower bits of the SST and enacting on it. Like for rsync I was thinking of enabling a progress monitoring and getting a subfunction that takes any output on stdout and issues EXTEND_TIMEOUT=... at that point.

something like:

if systemd-notify --booted && [ -n "${NOTIFY_SOCKET}" ]

then

  rsync_output_extendtimeout()

  {

    # some rate limiting / variable timeout progress

    while read input

   do

       systemd-notify EXTEND_TIMEMOUT=100000 

   done

  }

else

  rsync_output_extendtimeout() { [ 1 ]; }  > /dev/null

fi

...

     rsync --progress   |  rsync_output_extendtimeout

...

Edit: $NOTIFY_SOCKET is a unix socket or an abstract socket. Probably best just to use systemd-notify and check if $NOTIFY_SOCKET is set

Assistance in identifying other slow SST components and how to identify progress would be much appreciated.

I believe the script wsrep_sst_xtrabackup-v2 (or mariabackup equivalent) is just spawned via fork/exec, in this case in the parent process, run the loop you have mentioned until the child process completes.

Looks like this SST issue isn't new, just overlooked.
See item #8 of https://mariadb.com/kb/en/library/upgrading-from-mariadb-galera-cluster-100-to-mariadb-101/

Some cleanup, and testing

rpizzi, I added the timeout extension to InnoDB and XtraDB. Please file a separate ticket for the Galera snapshot transfer, maybe linked to MDEV-11035 or some other ticket on innodb_disallow_writes.

rpizzi, fraggeln in MDEV-11035 mentioned systemd timeout on startup, related to Galera rsync snapshot transfer failure. I wonder if this fix would address those problems, or if some further change is needed. (If yes, please file a separate ticket, as I suggested earlier.)

"Please file a separate ticket for the Galera snapshot transfer"
already there.... https://jira.mariadb.org/browse/MDEV-15606

It looks like the Galera issues were ultimately fixed in MDEV-17571.