Questions[mysql8](server)

I'm seeing an odd error in the mysqld.service status notes...

The syntax '--ssl=off' is deprecated and will be removed in a future release. Please use --tls-version=invalid instead.

The thing is I've disabled SSL using skip_ssl in the /etc/my.cnf and I don't see any vars relating to SSL defined in /etc/my.cnf.d/*. I've also checked in /etc/systemd/system/mysqld.service.d/override.conf and the only thing I've set is LimitNOFILE.

The --ssl=off setting is a command line start up variable, so where could this error be coming from? Is there a new way to disable SSL other than skip_ssl?

I'm on Rockly Linux 8, and MySQL 8.0.26.

The pertaining documentation, including the deprecation note can be found here: https://dev.mysql.com/doc/refman/8.0/en/server-options.html

Today I tried to update ubuntu from 18.04 LTS to 20.04 LTS. with do-release-uprade

After upgrade completed, I tried to start mysql with service mysql start

The result is

Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

And then I tried to check with systemctl status mysql.service

The result is

mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: failed (Result: exit-code) since Fri 2021-04-09 17:45:14 +07; 1min 52s ago
Process: 13824 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=1/FAILURE)

Apr 09 17:45:14 systemd[1]: Failed to start MySQL Community Server.
Apr 09 17:45:14 systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
Apr 09 17:45:14 systemd[1]: Stopped MySQL Community Server.
Apr 09 17:45:14 systemd[1]: mysql.service: Start request repeated too quickly.
Apr 09 17:45:14 systemd[1]: mysql.service: Failed with result 'exit-code'.
Apr 09 17:45:14 systemd[1]: Failed to start MySQL Community Server.

I cannot access to mysql anymore because service cannot start.

I do not want to lost any database. I do not have any backup, too.

Is backup /var/lib/mysql is enough? (Anything else that I should backup?)

I am very confuse. What should I do next?

1. Downgrade to Ubuntu 18.04 Or
2. Find the way to reinstall mysql 5.7 for Ubuntu 20.04 Or
3. Try upgrade mysql to 8. (but I cannot start mysql service this time)

Why ubuntu 20.04 cannot support mysql 5.7.33?

Thanks for any help.

I have a laravel application which I've installed on a 1GB standard droplet running ubuntu 20.4, nginx, MySQL 8 and php 7.4

The application isn't even live yet and I notice it's already using over 60% memory - 68%

Below is a snap shot of the current high memory running processes. Is this level of memory usage normal for a laravel application which is not even live i.e. limited load?

top - 15:47:51 up 1 day, 23:53,  1 user,  load average: 0.00, 0.01, 0.00
Tasks: 100 total,   3 running,  97 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.3 us,  0.3 sy,  0.0 ni, 99.3 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
MiB Mem :    981.3 total,     89.5 free,    612.3 used,    279.5 buff/cache
MiB Swap:      0.0 total,      0.0 free,      0.0 used.    200.7 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
  815 mysql     20   0 1306168 408208   2892 S   0.3  40.6  18:20.97 mysqld
 2259 www-data  20   0  244024  38424  25504 S   0.0   3.8   0:05.27 php-fpm7.4
  388 root      19  -1  106512  38196  37008 S   0.0   3.8   0:06.24 systemd-journal
 2265 www-data  20   0  244032  37988  25108 S   0.0   3.8   0:05.14 php-fpm7.4
 2257 www-data  20   0  244004  37780  24856 S   0.0   3.8   0:05.22 php-fpm7.4
  988 root      20   0  125160  30236   4640 S   0.3   3.0   2:03.41 php
  741 root      20   0  649232  22700   6672 S   0.0   2.3   0:17.61 snapd
  544 root      rt   0  280180  17976   8184 S   0.0   1.8   0:11.26 multipathd
  743 root      20   0   31348  16972   2468 S   0.0   1.7   0:31.63 supervisord
  738 root      20   0  238392  13208   7244 S   0.0   1.3   0:09.45 php-fpm7.4
  726 do-agent  20   0  559692  11852   5560 S   0.0   1.2   0:20.79 do-agent
  825 root      20   0  108036  11804   4160 S   0.0   1.2   0:00.10 unattended-upgr
  736 root      20   0   29220  11364   3708 S   0.0   1.1   0:00.11 networkd-dispat
    1 root      20   0  168752  10208   5772 S   0.0   1.0   0:12.03 systemd
15532 sammy     20   0   18556   8488   6948 S   0.0   0.8   0:00.08 systemd
  778 www-data  20   0   71004   8276   3552 S   0.0   0.8   0:02.71 nginx
15522 root      20   0   13760   8256   6816 S   0.0   0.8   0:00.01 sshd
  623 systemd+  20   0   23912   6756   2752 S   0.0   0.7   0:00.60 systemd-resolve
15634 osama     20   0   13892   5912   4452 S   0.0   0.6   0:00.06 sshd
  747 root      20   0   16860   5564   4552 S   0.0   0.6   0:02.54 systemd-logind
15635 sammy     20   0   10032   5104   3412 S   0.0   0.5   0:00.04 bash
15537 sammy     20   0  169820   4572      0 S   0.0   0.5   0:00.00 (sd-pam)
  763 root      20   0   12160   4504   3572 S   0.0   0.4   0:01.33 sshd
  621 systemd+  20   0   18544   4292   3276 S   0.0   0.4   0:01.11 systemd-network
  659 root      20   0   18880   3808   2528 S   0.0   0.4   0:01.92 systemd-udevd

My concern is once the application goes live and the load increases, more database connection it going to run out of memory. I know I can resize the droplet and increase the memory or set up some swap space but is this amount of memory usage normal for an unused laravel application?

How can I optimize the high memory usage processes such as mysql, niginx, php. Looks like mysql8 is the main culprit hogging all the memory. Below are the mysql8 settings:

#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld]
#
# * Basic Settings
#
user            = mysql
# pid-file      = /var/run/mysqld/mysqld.pid
# socket        = /var/run/mysqld/mysqld.sock
# port          = 3306
# datadir       = /var/lib/mysql


# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system- variables.html#sysvar_tmpdir
# tmpdir                = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size         = 16M
# max_allowed_packet    = 64M
# thread_stack          = 256K

# thread_cache_size       = -1

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP

# max_connections        = 151

# table_open_cache       = 4000

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file        = /var/log/mysql/query.log
# general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
# slow_query_log                = 1
# slow_query_log_file   = /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
# server-id             = 1
# log_bin                       = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds    = 2592000
max_binlog_size   = 100M
# binlog_do_db          = include_database_name
# binlog_ignore_db      = include_database_name   

Any tips and advice much appreciate as this is the first time I'm using a vps.

*** UPDATE ***

Further to advice from tim, switching off mysql performance schema has made a huge difference I now get the following:

top - 10:33:26 up 5 days, 18:39,  1 user,  load average: 0.01, 0.03, 0.00
Tasks: 114 total,   1 running, 107 sleeping,   6 stopped,   0 zombie
%Cpu(s):  0.7 us,  0.3 sy,  0.0 ni, 98.7 id,  0.3 wa,  0.0 hi,  0.0 si,  0.0 st
MiB Mem :    981.3 total,    253.8 free,    360.9 used,    366.7 buff/cache
MiB Swap:      0.0 total,      0.0 free,      0.0 used.    449.2 avail Mem

I am trying to install mysqldump on a Azure hosted Ubuntu VM.

During the build process I attempt the following command:

sudo apt-get update
sudo apt-get install -y --no-install-recommends mysql-client-8.0

This results in the following error:

Unable to locate package mysql-client-8.0

When I look at the package on packages.ubuntu.com, it is listed under Ubuntu Focal 20.04LTS (https://packages.ubuntu.com/focal/mysql-client-8.0)

If I change the installation command to:

sudo apt-get update
sudo apt-get install -y --no-install-recommends mysql-client

It installed mysql-client-5.7 which isn't running properly. Is the mysql-client version for the appropriate database tied to the Ubuntu server? Should the 5.7 client work correctly with MySQL 8.0?

Apparently I started a SELECT query 20 days ago that never finished. It kept running even though the client had disconnected, and ran for so long that one of the undo logs grew to 230 gb in size. (The database in question is 320 gb of data). I've force killed the query, but the undo log just won't shrink back to normal size, causing the disk to be full.

For the record, this is the sizes on disk:

root@the-db:/var/lib/mysql# du -h undo_00* erik_*
11G undo_001
244G    undo_002
1.5G    erik_temporary_undo_004.ibu
22G erik_undo_003.ibu

One interesting thing is that all undo logs are pretty massive?

I've tried to make sense the documentation, but can't get it to work: https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html

Version running:

root@the-db:/var/lib/mysql# mysql --version
mysql  Ver 8.0.18 for Linux on x86_64 (MySQL Community Server - GPL)

Automatic truncation

If I understand correctly, to have the purge thread truncate an undo log automatically, three condition needs to be met:

Step 1. The undo log needs to be larger than @@innodb_max_undo_log_size, which is:

mysql> SELECT @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
|                 1073741824 |
+----------------------------+

Step 2. @@innodb_undo_log_truncate needs to be ON

mysql> select @@innodb_undo_log_truncate;
+----------------------------+
| @@innodb_undo_log_truncate |
+----------------------------+
|                          1 |
+----------------------------+

Step 3. There needs to be a total of 2 active undo logs Automated truncation of undo tablespaces requires a minimum of two active undo tablespaces (not sure if these means 2 active before one is taken out of rotation for truncating).

mysql> SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
+-------------------------+------------+----------+
| NAME                    | SPACE_TYPE | STATE    |
+-------------------------+------------+----------+
| erik_temporary_undo_003 | Undo       | active   |
| erik_temporary_undo_004 | Undo       | active   |
| innodb_undo_001         | Undo       | active   |
| innodb_undo_002         | Undo       | inactive |
+-------------------------+------------+----------+

According to above, we have 3 active and 4 total undo logs.

However, nothing seems to be happening.

Manual truncation

There is also a manual process for truncating a log, which has another set of prerequisites.

Step 1 and 2 are the same for automatic truncation.

Step 3 is different in that Manual truncation of undo tablespaces requires a minimum of three active undo tablespaces (still not sure if this means 3 active before one is taken out of rotation). To get around my uncertainty, I created 2 extra undo table spaces.

CREATE UNDO TABLESPACE erik_temporary_undo_003 ADD DATAFILE 'erik_undo_003.ibu';
CREATE UNDO TABLESPACE erik_temporary_undo_004 ADD DATAFILE 'erik_temporary_undo_004.ibu';

Step 4 is to deactivate the table space needing a truncation:

ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;

If I understand correctly this will trigger truncation and after it is completed the STATUS will be empty. Again, here is the current status:

mysql> SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
+-------------------------+------------+----------+
| NAME                    | SPACE_TYPE | STATE    |
+-------------------------+------------+----------+
| erik_temporary_undo_003 | Undo       | active   |
| erik_temporary_undo_004 | Undo       | active   |
| innodb_undo_001         | Undo       | active   |
| innodb_undo_002         | Undo       | inactive |
+-------------------------+------------+----------+

It was set to inactive about 3 days ago, and it hasn't changed since.

One weird thing is that according to the following query, there are only 2 active undo logs, when the previous query shows 3. Am I missing something?

mysql> SHOW STATUS LIKE 'Innodb_undo_tablespaces%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_undo_tablespaces_total    | 4     |
| Innodb_undo_tablespaces_implicit | 2     |
| Innodb_undo_tablespaces_explicit | 2     |
| Innodb_undo_tablespaces_active   | 2     |
+----------------------------------+-------+

Shouldn't Innodb_undo_tablespaces_active be 3 at least?

Frequency

The documentation mentions innodb_purge_rseg_truncate_frequency as a way to get the purge thread to run more often. The default is 128 and the example shows setting it to 32. What this actually means in terms of time is very unclear. It just mentions "every 32 runs".

To increase that frequency, decrease the innodb_purge_rseg_truncate_frequency setting. For example, to have the purge thread look for undo tabespaces once every 32 timees[sic] that purge is invoked, set innodb_purge_rseg_truncate_frequency to 32.

For good measure, I've set it to 1.

mysql> show variables like "%truncate%";
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 1     |
| innodb_undo_log_truncate             | ON    |
+--------------------------------------+-------+

Metrics

I just recently understood how to get the InnoDB metrics:

mysql> set global innodb_monitor_enable = all;

And will update with metrics when they become available.

Relevant configs

mysql> show variables like "%undo%";
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_encrypt  | OFF        |
| innodb_undo_log_truncate | ON         |
| innodb_undo_tablespaces  | 2          |
+--------------------------+------------+

mysql> show variables like "%truncate%";
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 1     |
| innodb_undo_log_truncate             | ON    |
+--------------------------------------+-------+

What am I missing to get MySQL to truncate the undo log size?

I just assume that truncating means that the actual file on disk will shrink. Maybe it means something else?