Slow startup of mariadb

Issues related to applications and software problems and general support
Post Reply
Jean-Pierre
Posts: 9
Joined: 2020/05/31 16:36:25

Slow startup of mariadb

Post by Jean-Pierre » 2020/05/31 17:27:10

Hi all,

I have recently upgraded to Centos 8 on several computers (fresh installs). Doing so implied upgrading a mariadb database for switching from mariadb 5.5.64 to 10.3.17.

I see this is causing a long bootup :

Code: Select all

[root@optiplex doc]# systemd-analyze blame
         28.293s mariadb.service
          9.660s udisks2.service
          9.615s cups.service
          9.401s sssd.service
Similar times are experienced on all Centos 8 installations during bootup, but after the initialization phase, the response times for queries and updates are normal.

I do not really understand the only warning I can find :

Code: Select all

May 31 08:01:25 optiplex mysql-prepare-db-dir[1729]: Database MariaDB is probably initialized in /home/mysql already, nothing is done.
May 31 08:01:25 optiplex mysql-prepare-db-dir[1729]: If this is not the case, make sure the /home/mysql is empty before running mysql-prepare-db-dir.
I still have a Centos 7 installation on a slower computer with less memory, with the same database, and the bootup is much faster :

Code: Select all

[root@precision doc]# systemd-analyze blame
          8.998s mariadb.service
          5.265s kdump.service
          5.212s akmods.service
          4.785s tuned.service
The only similar issue pointed at by Google is about refreshening the time zone data, but this is for another version, ... and I do not understand the cure (probably not relevant).

Am I the only user to experience this ?

Any idea of what can I do to improve the bootup of mariadb ?

Jean-Pierre

gerry666uk
Posts: 58
Joined: 2020/02/10 19:06:06

Re: Slow startup of mariadb

Post by gerry666uk » 2020/06/01 20:10:59

You need to prevent it starting up automatically at boot time (for now), then stop mariadb, then reboot and see if the boot up is fast.
Assuming the boot up is now fast, the next step is to start mariadb manually, and watch all the log messages from both systemd, and also the mariadb log itself, and this should show where it slowing down. If you copied old binary data from mariadb 5.x and are now using it in 10.3, bad things could happen, but if you installed 10.3 clean and then imported from dumps, it should be ok.

Jean-Pierre
Posts: 9
Joined: 2020/05/31 16:36:25

Re: Slow startup of mariadb

Post by Jean-Pierre » 2020/06/03 12:34:27

Thanks for your reply.

First, from the mariadb log, when starting up today :

Code: Select all

2020-06-03  8:02:06 0 [Note] InnoDB: Completed initialization of buffer pool
2020-06-03  8:02:06 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-06-03  8:02:14 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-06-03  8:02:14 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-06-03  8:02:14 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-06-03  8:02:22 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-06-03  8:02:22 0 [Note] InnoDB: 10.3.17 started; log sequence number 497859003; transaction id 45991780
So it took 16 seconds creating rollback segments and building ./ibtmp1...

While closing down yesterday, I see :

Code: Select all

2020-06-02 22:18:31 0 [Note] InnoDB: Buffer pool(s) dump completed at 200602 22:18:31
2020-06-02 22:18:33 0 [Note] InnoDB: Shutdown completed; log sequence number 497859003; transaction id 45991778
2020-06-02 22:18:33 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-06-02 22:18:33 0 [Note] /usr/libexec/mysqld: Shutdown complete
So ibtmp1 has to be rebuilt because it is deleted on shutdown.

Now, disabling mariadb, then rebooting, then reenabling mariadb :

Code: Select all

[reboot]

[linux@optiplex doc]$ systemd-analyze blame
          9.468s sssd.service
          8.787s udisks2.service
          7.938s vdo.service
          7.841s NetworkManager-wait-online.service

[root@optiplex doc]# systemctl enable mariadb
Created symlink /etc/systemd/system/mysql.service  /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service  /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service  /usr/lib/systemd/system/mariadb.service.
[root@optiplex doc]# time systemctl start mariadb

real	0m3.469s
user	0m0.006s
sys	0m0.004s
This is quite fast !

The mariadb log shows :

Code: Select all

2020-06-03 13:37:51 0 [Note] InnoDB: Completed initialization of buffer pool
2020-06-03 13:37:51 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-06-03 13:37:52 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-06-03 13:37:52 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-06-03 13:37:52 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-06-03 13:37:52 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-06-03 13:37:52 0 [Note] InnoDB: Waiting for purge to start
2020-06-03 13:37:52 0 [Note] InnoDB: 10.3.17 started; log sequence number 497974104; transaction id 45992071
The sequence which lasted 16 seconds on bootup, is now performed in a couple of seconds !

Nothing special is seen in the systemd log :

Code: Select all

[root@optiplex doc]# journalctl
Jun 03 13:37:50 optiplex.localdomain systemd[1]: Starting MariaDB 10.3 database server...
Jun 03 13:37:51 optiplex.localdomain mysql-prepare-db-dir[2699]: Database MariaDB is probably initialized in /home/mysql already, nothing is done.
Jun 03 13:37:51 optiplex.localdomain mysql-prepare-db-dir[2699]: If this is not the case, make sure the /home/mysql is empty before running mysql-prepare-db-dir.
Jun 03 13:37:51 optiplex.localdomain mysqld[2738]: 2020-06-03 13:37:51 0 [Note] /usr/libexec/mysqld (mysqld 10.3.17-MariaDB) starting as process 2738 ...
Jun 03 13:37:54 optiplex.localdomain systemd[1]: Started MariaDB 10.3 database server.
It looks like the time is spent initializing some database file. The database is stored in /home which is in its own partition and assumed to not be accessed by other processes during bootup until a user logs in. Also /home is mounted and checked much earlier.

Note : I did a full fresh install of Centos8 (including mariadb), but the database was created fifteen years ago on mysql, and has been upgraded several times, whenever a newer system is installed.

Am I really the only user experiencing this long startup ?

Jean-Pierre

User avatar
TrevorH
Forum Moderator
Posts: 29662
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: Slow startup of mariadb

Post by TrevorH » 2020/06/03 12:55:35

If it takes 8 seconds to write a 12MB file then there is something quite seriously wrong with your disk subsystem. Is it using a floppy disk?
CentOS 6 will die in November 2020 - migrate sooner rather than later!
Info for USB installs on http://wiki.centos.org/HowTos/InstallFromUSBkey
CentOS 5 is dead, do not use it.
Full time Geek, part time moderator. Use the FAQ Luke

Jean-Pierre
Posts: 9
Joined: 2020/05/31 16:36:25

Re: Slow startup of mariadb

Post by Jean-Pierre » 2020/06/03 15:00:48

there is something quite seriously wrong with your disk subsystem
And the same defect on several computers ?
Is it using a floppy disk?
Would have to be a floppy disk with a gearbox for speeding after the startup is done...

Post Reply

Return to “CentOS 8 - General Support”