grooverdan
commented
5 months ago try a higher memlock limit.
https://github.com/MariaDB/mariadb-docker?tab=readme-ov-file#getting-help
Closed taw123 closed 5 months ago
grooverdan
commented
5 months ago try a higher memlock limit.
https://github.com/MariaDB/mariadb-docker?tab=readme-ov-file#getting-help
taw123
commented
5 months ago Strangely enough... the issue seems to have resolved itself! (normally I really don't like when things change for reasons unknown to me but after having dealt with this matter for ages I guess I will accept the fact the issue went away WITHOUT changing the memlimit from the config above).
Thanks for commenting and perhaps the good will that resolved the issue? (MAY have updated ver as I did try several pulls trying to resolve the issue). Assuming you don't see any reason to keep this open I'll close. 👍
04/23/2024
07:05:39 PM
2024-04-23 19:05:39 0 [Note] mariadbd: Shutdown complete
04/23/2024
07:05:39 PM
04/23/2024
07:05:51 PM
2024-04-23 19:05:51-07:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:11.3.2+maria~ubu2204 started.
04/23/2024
07:05:52 PM
2024-04-23 19:05:52-07:00 [Note] [Entrypoint]: MariaDB upgrade not required
04/23/2024
07:05:52 PM
2024-04-23 19:05:52 0 [Note] Starting MariaDB 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 source revision 068a6819eb63bcb01fdfa037c9bf3bf63c33ee42 as process 1
04/23/2024
07:05:52 PM
2024-04-23 19:05:52 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
04/23/2024
07:05:52 PM
2024-04-23 19:05:52 0 [Note] InnoDB: Using transactional memory
04/23/2024
07:05:52 PM
2024-04-23 19:05:52 0 [Note] InnoDB: Number of transaction pools: 1
04/23/2024
07:05:52 PM
2024-04-23 19:05:52 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
04/23/2024
07:05:52 PM
2024-04-23 19:05:52 0 [Note] InnoDB: Using liburing
04/23/2024
07:05:52 PM
2024-04-23 19:05:52 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
04/23/2024
07:05:52 PM
2024-04-23 19:05:52 0 [Note] InnoDB: Completed initialization of buffer pool
04/23/2024
07:05:52 PM
2024-04-23 19:05:52 0 [Note] InnoDB: Buffered log writes (block size=512 bytes)
04/23/2024
07:05:52 PM
2024-04-23 19:05:52 0 [Note] InnoDB: Resetting space id's in the doublewrite buffer
04/23/2024
07:05:52 PM
2024-04-23 19:05:52 0 [Note] InnoDB: End of log at LSN=174044843200
04/23/2024
07:05:53 PM
2024-04-23 19:05:53 0 [Note] InnoDB: Opened 3 undo tablespaces
04/23/2024
07:05:53 PM
2024-04-23 19:05:53 0 [Note] InnoDB: 128 rollback segments in 3 undo tablespaces are active.
04/23/2024
07:05:53 PM
2024-04-23 19:05:53 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
04/23/2024
07:05:53 PM
2024-04-23 19:05:53 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
04/23/2024
07:05:53 PM
2024-04-23 19:05:53 0 [Note] InnoDB: log sequence number 174044843200; transaction id 2563675
04/23/2024
07:05:53 PM
2024-04-23 19:05:53 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
04/23/2024
07:05:53 PM
2024-04-23 19:05:53 0 [Note] Plugin 'FEEDBACK' is disabled.
04/23/2024
07:05:53 PM
2024-04-23 19:05:53 0 [Note] Plugin 'wsrep-provider' is disabled.
04/23/2024
07:05:53 PM
2024-04-23 19:05:53 0 [Note] Server socket created on IP: '0.0.0.0'.
04/23/2024
07:05:53 PM
2024-04-23 19:05:53 0 [Note] Server socket created on IP: '::'.
04/23/2024
07:05:54 PM
2024-04-23 19:05:54 0 [Note] mariadbd: Event Scheduler: Loaded 0 events
04/23/2024
07:05:54 PM
2024-04-23 19:05:54 0 [Note] mariadbd: ready for connections.
04/23/2024
07:05:54 PM
Version: '11.3.2-MariaDB-1:11.3.2+maria~ubu2204' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution
04/23/2024
07:06:01 PM
2024-04-23 19:06:01 0 [Note] InnoDB: Buffer pool(s) load completed at 240423 19:06:01
04/23/2024
07:07:01 PM
2024-04-23 19:07:01 3 [Warning] Aborted connection 3 to db: 'unconnected' user: 'unauthenticated' host: '172.29.20.1' (This connection closed normally without authentication)
glad to see the recovery even if by unknown reasons (i'm not a fan of magic solutions, but its better than non-solutions). Thanks for getting back. docker insepct mariadb could show a different image sha256 or maybe the kernel version changed.
taw123
commented
5 months ago We are unfortunately both right to be wary of "magical" thinking.....
The issue has returned. I know and I current as data below will show as pull update just this am.
Let me start again by saying I know this is a common issue reported by many and clearly called out as insufficient memory available for ring buffer. I honestly have done A LOT of research on this including even potentially esoteric issues like respecting ulimit memlock configurations by different kernels and spawned pid's... In the end I am still stumped.
I don't want to spam this post with to much of my diagnostics and risk valuable data being buried or worst yet, just to much time needed for anyone to respond so this is brief but I can provide more if you have ANY questions.
I tried to take your advise, and bumping the Memlock several time without seeing any change in the error I verified that my increase in Memlock is INDEED being honored in the container (see below- first run was with memlock: "542488" (per reported warning), second run I doubled it)
[~] # docker exec -it MariaDB bash
groups: cannot find name for group ID 911
I have no name!@MariaDB:/$ ulimit -l
265
I have no name!@MariaDB:/$ exit
exit
[~] # docker exec -it MariaDB bash
groups: cannot find name for group ID 911
I have no name!@MariaDB:/$ ulimit -l
529
MariaDB:/$ ulimit -a
real-time non-blocking time (microseconds, -R) unlimited
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 256708
max locked memory (kbytes, -l) 529
max memory size (kbytes, -m) unlimited
open files (-n) 65535
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 256708
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
Error/Warning from log:
2024-05-02 15:37:13-07:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:11.3.2+maria~ubu2204 started.
2024-05-02 15:37:14-07:00 [Note] [Entrypoint]: MariaDB upgrade not required
2024-05-02 15:37:14 0 [Note] Starting MariaDB 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 source revision 068a6819eb63bcb01fdfa037c9bf3bf63c33ee42 as process 1
2024-05-02 15:37:14 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2024-05-02 15:37:14 0 [Note] InnoDB: Using transactional memory
2024-05-02 15:37:14 0 [Note] InnoDB: Number of transaction pools: 1
2024-05-02 15:37:14 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2024-05-02 15:37:14 0 [Warning] mariadbd: io_uring_queue_init() failed with ENOMEM: try larger memory locked limit, ulimit -l, or https://mariadb.com/kb/en/systemd/#configuring-limitmemlock under systemd (262144 bytes required)
2024-05-02 15:37:14 0 [Warning] InnoDB: liburing disabled: falling back to innodb_use_native_aio=OFF
2024-05-02 15:37:14 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
2024-05-02 15:37:14 0 [Note] InnoDB: Completed initialization of buffer pool
2024-05-02 15:37:14 0 [Note] InnoDB: Buffered log writes (block size=512 bytes)
2024-05-02 15:37:14 0 [Note] InnoDB: Resetting space id's in the doublewrite buffer
2024-05-02 15:37:15 0 [Note] InnoDB: End of log at LSN=178110059765
2024-05-02 15:37:17 0 [Note] InnoDB: Opened 3 undo tablespaces
2024-05-02 15:37:17 0 [Note] InnoDB: 128 rollback segments in 3 undo tablespaces are active.
2024-05-02 15:37:17 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
2024-05-02 15:37:17 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
2024-05-02 15:37:17 0 [Note] InnoDB: log sequence number 178110059765; transaction id 2623358
2024-05-02 15:37:17 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2024-05-02 15:37:17 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-05-02 15:37:17 0 [Note] Plugin 'wsrep-provider' is disabled.
2024-05-02 15:37:17 0 [Note] Server socket created on IP: '0.0.0.0'.
2024-05-02 15:37:17 0 [Note] Server socket created on IP: '::'.
2024-05-02 15:37:18 0 [Note] mariadbd: Event Scheduler: Loaded 0 events
2024-05-02 15:37:18 0 [Note] mariadbd: ready for connections.
Version: '11.3.2-MariaDB-1:11.3.2+maria~ubu2204' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution
2024-05-02 15:37:22 0 [Note] InnoDB: Buffer pool(s) load completed at 240502 15:37:22
In the interest of diagnostics I installed MySQLTuner-perl. Output of it in verbose mode at thee bottom. For some reason the script is complaining about the version of MariaDB I am running though it appears to me to be current from the MaraDB -V command, however I am still learning the tool so I am guessing this is an unrelated red-herring so I'm not letting it sidetrack me in resolving this issue.
Relevant config data below (so you don't have to dig through my previous post). When asking for help or reporting a bug believe burden is non the reporter to at least TRY to make it easy for others to help you
Compose
MariaDB:
container_name: MariaDB
image: mariadb:latest
restart: always
hostname: MariaDB.local
ports:
- "3306:3306/tcp"
user: 1029:911
ulimits:
memlock: "542488" # complains about needing 262144 (I tried bumping up to 382144 aka- ulimit -l shows 373 then tried doubling the system recommendation to no avail)
environment:
- TZ=America/Los_Angeles
- MARIADB_AUTO_UPGRADE=True
volumes:
- /share/DockerAppData/MariaDB/data:/var/lib/mysql
- /share/DockerAppData/MariaDB/config/:/etc/mysql/conf.d
- /etc/localtime:/etc/localtime:roMariaDB container:
[~] # docker exec -it MariaDB bash
groups: cannot find name for group ID 911
I have no name!@MariaDB:/$ id
uid=1029 gid=911 groups=911Container Inspect:
[~] # docker inspect MariaDB
[
{
"Id": "a048c76fba45f6d478fd62e2fe1ebe47af8d0d5f7b4effcc6f9e22c81898b2bb",
"Created": "2024-05-02T20:07:03.475492176Z",
"Path": "docker-entrypoint.sh",
"Args": [
"mariadbd"
],
"State": {
"Status": "running",
"Running": true,
"Paused": false,
"Restarting": false,
"OOMKilled": false,
"Dead": false,
"Pid": 23408,
"ExitCode": 0,
"Error": "",
"StartedAt": "2024-05-02T20:07:05.782213172Z",
"FinishedAt": "0001-01-01T00:00:00Z"
},
"Image": "sha256:465bc4da7f09154d5e38fd1374734688fd78842b6b01790a69ac3e4a5abed2e1",
"ResolvConfPath": "/share/ZFS530_DATA/.qpkg/container-station/docker/containers/a048c76fba45f6d478fd62e2fe1ebe47af8d0d5f7b4effcc6f9e22c81898b2bb/resolv.conf",
"HostnamePath": "/share/ZFS530_DATA/.qpkg/container-station/docker/containers/a048c76fba45f6d478fd62e2fe1ebe47af8d0d5f7b4effcc6f9e22c81898b2bb/hostname",
"HostsPath": "/share/ZFS530_DATA/.qpkg/container-station/docker/containers/a048c76fba45f6d478fd62e2fe1ebe47af8d0d5f7b4effcc6f9e22c81898b2bb/hosts",
"LogPath": "/share/ZFS530_DATA/.qpkg/container-station/docker/containers/a048c76fba45f6d478fd62e2fe1ebe47af8d0d5f7b4effcc6f9e22c81898b2bb/a048c76fba45f6d478fd62e2fe1ebe47af8d0d5f7b4effcc6f9e22c81898b2bb-json.log",
"Name": "/MariaDB",
"RestartCount": 0,
"Driver": "zfs",
"Platform": "linux",
"MountLabel": "",
"ProcessLabel": "",
"AppArmorProfile": "docker-default",
"ExecIDs": null,
"HostConfig": {
"Binds": [
"/share/DockerAppData/MariaDB/data:/var/lib/mysql:rw",
"/share/DockerAppData/MariaDB/config/:/etc/mysql/conf.d:rw",
"/etc/localtime:/etc/localtime:ro"
],
"ContainerIDFile": "",
"LogConfig": {
"Type": "json-file",
"Config": {
"max-file": "10",
"max-size": "10m"
}
},
"NetworkMode": "286b27687b5a93d767e6ec2cf5cc2d63d7f6351c670f3fe8181a6799154e50b8",
"PortBindings": {
"3306/tcp": [
{
"HostIp": "",
"HostPort": "3306"
}
]
},
"RestartPolicy": {
"Name": "always",
"MaximumRetryCount": 0
},
"AutoRemove": false,
"VolumeDriver": "",
"VolumesFrom": null,
"CapAdd": null,
"CapDrop": null,
"CgroupnsMode": "host",
"Dns": null,
"DnsOptions": null,
"DnsSearch": null,
"ExtraHosts": [],
"GroupAdd": null,
"IpcMode": "private",
"Cgroup": "",
"Links": null,
"OomScoreAdj": 0,
"PidMode": "",
"Privileged": false,
"PublishAllPorts": false,
"ReadonlyRootfs": false,
"SecurityOpt": null,
"UTSMode": "",
"UsernsMode": "",
"ShmSize": 67108864,
"Runtime": "runc",
"ConsoleSize": [
0,
0
],
"Isolation": "",
"CpuShares": 0,
"Memory": 0,
"NanoCpus": 0,
"CgroupParent": "",
"BlkioWeight": 0,
"BlkioWeightDevice": null,
"BlkioDeviceReadBps": null,
"BlkioDeviceWriteBps": null,
"BlkioDeviceReadIOps": null,
"BlkioDeviceWriteIOps": null,
"CpuPeriod": 0,
"CpuQuota": 0,
"CpuRealtimePeriod": 0,
"CpuRealtimeRuntime": 0,
"CpusetCpus": "",
"CpusetMems": "",
"Devices": null,
"DeviceCgroupRules": null,
"DeviceRequests": null,
"KernelMemory": 0,
"KernelMemoryTCP": 0,
"MemoryReservation": 0,
"MemorySwap": 0,
"MemorySwappiness": null,
"OomKillDisable": false,
"PidsLimit": null,
"Ulimits": [
{
"Name": "memlock",
"Hard": 542488,
"Soft": 542488
},
{
"Name": "nofile",
"Hard": 65535,
"Soft": 65535
}
],
"CpuCount": 0,
"CpuPercent": 0,
"IOMaximumIOps": 0,
"IOMaximumBandwidth": 0,
"MaskedPaths": [
"/proc/asound",
"/proc/acpi",
"/proc/kcore",
"/proc/keys",
"/proc/latency_stats",
"/proc/timer_list",
"/proc/timer_stats",
"/proc/sched_debug",
"/proc/scsi",
"/sys/firmware",
"/sys/devices/virtual/powercap"
],
"ReadonlyPaths": [
"/proc/bus",
"/proc/fs",
"/proc/irq",
"/proc/sys",
"/proc/sysrq-trigger"
]
},
"GraphDriver": {
"Data": {
"Dataset": "zpool1/zfs530/zfs5300001/a860ef4ccbf62f27fa05521e1602b4ac5708fb81503bf62359f0f7daacaa708f",
"Mountpoint": "/share/ZFS530_DATA/.qpkg/container-station/docker/zfs/graph/a860ef4ccbf62f27fa05521e1602b4ac5708fb81503bf62359f0f7daacaa708f"
},
"Name": "zfs"
},
"Mounts": [
{
"Type": "bind",
"Source": "/share/DockerAppData/MariaDB/data",
"Destination": "/var/lib/mysql",
"Mode": "rw",
"RW": true,
"Propagation": "rprivate"
},
{
"Type": "bind",
"Source": "/share/DockerAppData/MariaDB/config",
"Destination": "/etc/mysql/conf.d",
"Mode": "rw",
"RW": true,
"Propagation": "rprivate"
},
{
"Type": "bind",
"Source": "/etc/localtime",
"Destination": "/etc/localtime",
"Mode": "ro",
"RW": false,
"Propagation": "rprivate"
}
],
"Config": {
"Hostname": "MariaDB.local",
"Domainname": "",
"User": "1029:911",
"AttachStdin": false,
"AttachStdout": true,
"AttachStderr": true,
"ExposedPorts": {
"3306/tcp": {}
},
"Tty": false,
"OpenStdin": false,
"StdinOnce": false,
"Env": [
"MARIADB_AUTO_UPGRADE=True",
"TZ=America/Los_Angeles",
"PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin",
"GOSU_VERSION=1.17",
"LANG=C.UTF-8",
"MARIADB_VERSION=1:11.3.2+maria~ubu2204"
],
"Cmd": [
"mariadbd"
],
"Image": "mariadb:latest",
"Volumes": {
"/var/lib/mysql": {}
},
"WorkingDir": "",
"Entrypoint": [
"docker-entrypoint.sh"
],
"OnBuild": null,
"Labels": {
"com.docker.compose.config-hash": "60f410b71b347276e1308454c4b262248ba195ba1841dc89f06aad6335f9946c",
"com.docker.compose.container-number": "1",
"com.docker.compose.depends_on": "",
"com.docker.compose.image": "sha256:465bc4da7f09154d5e38fd1374734688fd78842b6b01790a69ac3e4a5abed2e1",
"com.docker.compose.oneoff": "False",
"com.docker.compose.project": "db_stack",
"com.docker.compose.project.config_files": "/data/compose/35/docker-compose.yml",
"com.docker.compose.project.working_dir": "/data/compose/35",
"com.docker.compose.replace": "1d3362c233d42a0296d77c50573b03632cc7ae79aca7b00a4b3bb1f7c640a9e4",
"com.docker.compose.service": "MariaDB",
"com.docker.compose.version": "2.20.2",
"org.opencontainers.image.authors": "MariaDB Community",
"org.opencontainers.image.base.name": "docker.io/library/ubuntu:jammy",
"org.opencontainers.image.description": "MariaDB Database for relational SQL",
"org.opencontainers.image.documentation": "https://hub.docker.com/_/mariadb/",
"org.opencontainers.image.licenses": "GPL-2.0",
"org.opencontainers.image.ref.name": "ubuntu",
"org.opencontainers.image.source": "https://github.com/MariaDB/mariadb-docker",
"org.opencontainers.image.title": "MariaDB Database",
"org.opencontainers.image.url": "https://github.com/MariaDB/mariadb-docker",
"org.opencontainers.image.vendor": "MariaDB Community",
"org.opencontainers.image.version": "11.3.2"
}
},
"NetworkSettings": {
"Bridge": "",
"SandboxID": "d8198bb40450f85cb8612b675a09c9f0e0270fef7bd44145595833eff1698ad4",
"HairpinMode": false,
"LinkLocalIPv6Address": "",
"LinkLocalIPv6PrefixLen": 0,
"Ports": {
"3306/tcp": [
{
"HostIp": "0.0.0.0",
"HostPort": "3306"
}
]
},
"SandboxKey": "/var/run/docker/netns/d8198bb40450",
"SecondaryIPAddresses": null,
"SecondaryIPv6Addresses": null,
"EndpointID": "",
"Gateway": "",
"GlobalIPv6Address": "",
"GlobalIPv6PrefixLen": 0,
"IPAddress": "",
"IPPrefixLen": 0,
"IPv6Gateway": "",
"MacAddress": "",
"Networks": {
"dbnet": {
"IPAMConfig": null,
"Links": null,
"Aliases": [
"MariaDB",
"MariaDB",
"a048c76fba45",
"MariaDB.local"
],
"NetworkID": "286b27687b5a93d767e6ec2cf5cc2d63d7f6351c670f3fe8181a6799154e50b8",
"EndpointID": "56c00b7fbeaf84e50608cc8ef7134337cdcfd16ae822eda756343d716534ba2f",
"Gateway": "172.29.20.1",
"IPAddress": "172.29.20.4",
"IPPrefixLen": 25,
"IPv6Gateway": "",
"GlobalIPv6Address": "",
"GlobalIPv6PrefixLen": 0,
"MacAddress": "02:42:ac:1d:14:04",
"DriverOpts": null
}
}
}
}
]MariaDB version
root@MariaDB:/tmp# mariadb -V
mariadb from 11.3.2-MariaDB, client 15.2 for debian-linux-gnu (x86_64) using EditLine wrappermySQLtuner run IN MariaDB container (exec'd into container as ROOT not the normal SQL user that runs MariaDB)
[~] # docker exec -it -u root MariaDB bash
root@MariaDB:/# id
uid=0(root) gid=0(root) groups=0(root)
./mysqltuner.pl --user root --pass XXXXX --verbose --checkversion
>> MySQLTuner 2.5.3
* Jean-Marie Renouard <jmrenouard@gmail.com>
* Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 3.8G (Tables: 32)
[--] Data in Aria tables: 32.0K (Tables: 1)
[!!] Total fragmented tables: 1
[!!] Your MySQL version 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 is EOL software. Upgrade soon!
-------- System Linux Recommendations --------------------------------------------------------------
Look for related Linux system recommendations
[--] Ubuntu 22.04.4 LTS
[--] Machine type : Physical machine
sh: 1: ping: not found
[--] Internet : Connected
[--] Number of Core CPU : 6
[--] Operating System Type : GNU/Linux
[--] Kernel Release : 5.10.60-qnap
[--] Hostname : MariaDB.local
[--] Network Cards :
sh: 1: ifconfig: not found
[--] Internal IP : 172.29.20.4
sh: 1: ifconfig: not found
[--] HTTP client found: /usr/bin/wget
[--] External IP : 135.180.67.176
[--] Name Servers : 127.0.0.11
[--] Logged In users :
[--] Ram Usages in MB :
[--] total used free shared buff/cache available
[--] Mem: 64216 51206 12586 58 423 12249
[--] Swap: 63266 12118 51148
[--] Load Average :
[--] top - 14:17:49 up 9 days, 19:21, 0 users, load average: 5.70, 4.94, 4.74
[--] System Uptime :
[--] 14:17:49 up 9 days, 19:21, 0 users, load average: 5.70, 4.94, 4.74
[OK] There is at least one CPU dedicated to database server.
[OK] There is at least 1 Gb of RAM dedicated to Linux server.
[--] User process except mysqld used 39M RAM.
[--] Other user process except mysqld used less than 15% of total physical memory 0.06% (39M / 62G)
-------- Filesystem Linux Recommendations ----------------------------------------------------------
[!!] mount point /usr/share/zoneinfo/Etc/UTC is using 90 % total space (385.88 MB / 432.00 MB)
[--] mount point /etc/hosts is using 1 % total space (186.20 MB / 1.16 TB)
[--] mount point /var/lib/mysql is using 60 % total space (297.39 GB / 500.00 GB)
[--] mount point / is using 1 % of max allowed inodes
[--] mount point /usr/share/zoneinfo/Etc/UTC is using 1 % of max allowed inodes
[--] mount point /etc/hosts is using 1 % of max allowed inodes
[--] mount point /var/lib/mysql is using 1 % of max allowed inodes
-------- Kernel Information Recommendations --------------------------------------------------------
[--] Information about kernel tuning:
[--] fs.aio-max-nr = 65536
[--] fs.aio-nr = 2209
[--] fs.nr_open = 1048576
[--] fs.file-max = 6571623
[--] sunrpc.tcp_fin_timeout = 15
[--] sunrpc.tcp_max_slot_table_entries = 65536
[--] sunrpc.tcp_slot_table_entries = 2
[--] vm.swappiness = 25
[!!] Swappiness is > 10, please consider having a value lower than 10
[--] TCP slot entries is > 100.
[!!] Max running total of the number of max. events is < 1M, please consider having a value greater than 1M
[--] Max Number of open file requests is > 1M.
-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file doesn't exist
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Database Metrics --------------------------------------------------------------------------
[--] There is 2 Database(s).
[--] All User Databases:
[--] +-- TABLE : 32
[--] +-- VIEW : 0
[--] +-- INDEX : 62
[--] +-- CHARS : 3 (utf8mb4, utf8mb3)
[--] +-- COLLA : 3 (utf8mb4_general_ci, utf8mb4_unicode_ci, utf8mb3_bin)
[--] +-- ROWS : 15304135
[--] +-- DATA : 1.8G(47.04%)
[--] +-- INDEX : 2.0G(52.96%)
[--] +-- SIZE : 3.8G
[--] +-- ENGINE: 1 (InnoDB)
[--] Database: HomeAssistant
[--] +-- TABLE : 13
[--] +-- VIEW : 0
[--] +-- INDEX : 37
[--] +-- CHARS : 3 (utf8mb4)
[--] +-- COLLA : 2 (utf8mb4_general_ci, utf8mb4_unicode_ci)
[--] +-- ROWS : 15304135
[--] +-- DATA : 1.8G(47.04%)
[--] +-- INDEX : 2.0G(52.96%)
[--] +-- TOTAL : 3.8G
[--] +-- ENGINE: 1 (InnoDB)
[--] +-- ENGINE InnoDB : 13 TABLE(s)
[!!] Index size is larger than data size for HomeAssistant
[!!] 2 different collations for database HomeAssistant
[OK] 1 engine for HomeAssistant database.
[--] Charsets for HomeAssistant database table column: utf8mb4
[OK] HomeAssistant table column(s) has same charset defined for all text like column(s).
[--] Collations for HomeAssistant database table column: utf8mb4_unicode_ci, utf8mb4_general_ci
[!!] HomeAssistant table column(s) has several collations defined for all text like column(s).
[--] Database: pmadb
[--] +-- TABLE : 19
[--] +-- VIEW : 0
[--] +-- INDEX : 25
[--] +-- CHARS : 3 (utf8mb3)
[--] +-- COLLA : 1 (utf8mb3_bin)
[--] +-- ROWS : 0
[--] +-- DATA : 304.0K(76.00%)
[--] +-- INDEX : 96.0K(24.00%)
[--] +-- TOTAL : 400.0K
[--] +-- ENGINE: 1 (InnoDB)
[--] +-- ENGINE InnoDB : 19 TABLE(s)
[OK] 1 collation for pmadb database.
[OK] 1 engine for pmadb database.
[--] Charsets for pmadb database table column: utf8mb3
[OK] pmadb table column(s) has same charset defined for all text like column(s).
[--] Collations for pmadb database table column: utf8mb3_bin, utf8mb3_general_ci
[!!] pmadb table column(s) has several collations defined for all text like column(s).
-------- Table Column Metrics ----------------------------------------------------------------------
[--] MySQL and Percona version 8.0 and greater have removed PROCEDURE ANALYSE feature
[--] Disabling colstat parameter
[--] Dumpdir:
[--] Database: HomeAssistant
[--] +-- TABLE: events
[--] +-- TYPE: InnoDB
[--] +-- Index ix_events_context_id - Cols: context_id - Type: BTREE
[--] +-- Index ix_events_context_id_bin - Cols: context_id_bin - Type: BTREE
[--] +-- Index ix_events_data_id - Cols: data_id - Type: BTREE
[--] +-- Index ix_events_event_type_id_time_fired_ts - Cols: event_type_id,time_fired_ts - Type: BTREE
[--] +-- Index ix_events_time_fired_ts - Cols: time_fired_ts - Type: BTREE
[--] +-- Index PRIMARY - Cols: event_id - Type: BTREE
[--] +-- Column events.event_id: INT(11) NOT NULL
[--] +-- Column events.event_type: VARCHAR(64) NULL
[--] +-- Column events.event_data: LONGTEXT NULL
[--] +-- Column events.origin: VARCHAR(32) NULL
[--] +-- Column events.origin_idx: SMALLINT(6) NULL
[--] +-- Column events.time_fired: DATETIME(6) NULL
[--] +-- Column events.time_fired_ts: DOUBLE NULL
[--] +-- Column events.context_id: VARCHAR(36) NULL
[--] +-- Column events.context_user_id: VARCHAR(36) NULL
[--] +-- Column events.context_parent_id: VARCHAR(36) NULL
[--] +-- Column events.data_id: INT(11) NULL
[--] +-- Column events.context_id_bin: TINYBLOB NULL
[--] +-- Column events.context_user_id_bin: TINYBLOB NULL
[--] +-- Column events.context_parent_id_bin: TINYBLOB NULL
[--] +-- Column events.event_type_id: INT(11) NULL
[--] +-- TABLE: event_data
[--] +-- TYPE: InnoDB
[--] +-- Index ix_event_data_hash - Cols: hash - Type: BTREE
[--] +-- Index PRIMARY - Cols: data_id - Type: BTREE
[--] +-- Column event_data.data_id: INT(11) NOT NULL
[--] +-- Column event_data.hash: BIGINT(20) NULL
[--] +-- Column event_data.shared_data: LONGTEXT NULL
[--] +-- TABLE: event_types
[--] +-- TYPE: InnoDB
[--] +-- Index ix_event_types_event_type - Cols: event_type - Type: BTREE
[--] +-- Index PRIMARY - Cols: event_type_id - Type: BTREE
[--] +-- Column event_types.event_type_id: INT(11) NOT NULL
[--] +-- Column event_types.event_type: VARCHAR(64) NULL
[--] +-- TABLE: migration_changes
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: migration_id - Type: BTREE
[--] +-- Column migration_changes.migration_id: VARCHAR(255) NOT NULL
[--] +-- Column migration_changes.version: SMALLINT(6) NOT NULL
[--] +-- TABLE: recorder_runs
[--] +-- TYPE: InnoDB
[--] +-- Index ix_recorder_runs_start_end - Cols: start,end - Type: BTREE
[--] +-- Index PRIMARY - Cols: run_id - Type: BTREE
[--] +-- Column recorder_runs.run_id: INT(11) NOT NULL
[--] +-- Column recorder_runs.start: DATETIME(6) NOT NULL
[--] +-- Column recorder_runs.end: DATETIME(6) NULL
[--] +-- Column recorder_runs.closed_incorrect: TINYINT(1) NOT NULL
[--] +-- Column recorder_runs.created: DATETIME(6) NOT NULL
[--] +-- TABLE: schema_changes
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: change_id - Type: BTREE
[--] +-- Column schema_changes.change_id: INT(11) NOT NULL
[--] +-- Column schema_changes.schema_version: INT(11) NULL
[--] +-- Column schema_changes.changed: DATETIME(6) NOT NULL
[--] +-- TABLE: states
[--] +-- TYPE: InnoDB
[--] +-- Index ix_states_attributes_id - Cols: attributes_id - Type: BTREE
[--] +-- Index ix_states_context_id - Cols: context_id - Type: BTREE
[--] +-- Index ix_states_context_id_bin - Cols: context_id_bin - Type: BTREE
[--] +-- Index ix_states_last_updated_ts - Cols: last_updated_ts - Type: BTREE
[--] +-- Index ix_states_metadata_id_last_updated_ts - Cols: metadata_id,last_updated_ts - Type: BTREE
[--] +-- Index ix_states_old_state_id - Cols: old_state_id - Type: BTREE
[--] +-- Index PRIMARY - Cols: state_id - Type: BTREE
[--] +-- Column states.state_id: INT(11) NOT NULL
[--] +-- Column states.entity_id: VARCHAR(255) NULL
[--] +-- Column states.state: VARCHAR(255) NULL
[--] +-- Column states.attributes: LONGTEXT NULL
[--] +-- Column states.event_id: INT(11) NULL
[--] +-- Column states.last_changed: DATETIME(6) NULL
[--] +-- Column states.last_changed_ts: DOUBLE NULL
[--] +-- Column states.last_updated: DATETIME(6) NULL
[--] +-- Column states.last_updated_ts: DOUBLE NULL
[--] +-- Column states.old_state_id: INT(11) NULL
[--] +-- Column states.attributes_id: INT(11) NULL
[--] +-- Column states.context_id: VARCHAR(36) NULL
[--] +-- Column states.context_user_id: VARCHAR(36) NULL
[--] +-- Column states.context_parent_id: VARCHAR(36) NULL
[--] +-- Column states.origin_idx: SMALLINT(6) NULL
[--] +-- Column states.context_id_bin: TINYBLOB NULL
[--] +-- Column states.context_user_id_bin: TINYBLOB NULL
[--] +-- Column states.context_parent_id_bin: TINYBLOB NULL
[--] +-- Column states.metadata_id: INT(11) NULL
[--] +-- Column states.last_reported_ts: DOUBLE NULL
[--] +-- TABLE: states_meta
[--] +-- TYPE: InnoDB
[--] +-- Index ix_states_meta_entity_id - Cols: entity_id - Type: BTREE
[--] +-- Index PRIMARY - Cols: metadata_id - Type: BTREE
[--] +-- Column states_meta.metadata_id: INT(11) NOT NULL
[--] +-- Column states_meta.entity_id: VARCHAR(255) NULL
[--] +-- TABLE: state_attributes
[--] +-- TYPE: InnoDB
[--] +-- Index ix_state_attributes_hash - Cols: hash - Type: BTREE
[--] +-- Index PRIMARY - Cols: attributes_id - Type: BTREE
[--] +-- Column state_attributes.attributes_id: INT(11) NOT NULL
[--] +-- Column state_attributes.hash: BIGINT(20) NULL
[--] +-- Column state_attributes.shared_attrs: LONGTEXT NULL
[--] +-- TABLE: statistics
[--] +-- TYPE: InnoDB
[--] +-- Index ix_statistics_start - Cols: start - Type: BTREE
[--] +-- Index ix_statistics_start_ts - Cols: start_ts - Type: BTREE
[--] +-- Index ix_statistics_statistic_id_start_ts - Cols: metadata_id,start_ts - Type: BTREE
[--] +-- Index PRIMARY - Cols: id - Type: BTREE
[--] +-- Column statistics.id: INT(11) NOT NULL
[--] +-- Column statistics.created: DATETIME(6) NULL
[--] +-- Column statistics.created_ts: DOUBLE NULL
[--] +-- Column statistics.metadata_id: INT(11) NULL
[--] +-- Column statistics.start: DATETIME(6) NULL
[--] +-- Column statistics.start_ts: DOUBLE NULL
[--] +-- Column statistics.mean: DOUBLE NULL
[--] +-- Column statistics.min: DOUBLE NULL
[--] +-- Column statistics.max: DOUBLE NULL
[--] +-- Column statistics.last_reset: DATETIME(6) NULL
[--] +-- Column statistics.last_reset_ts: DOUBLE NULL
[--] +-- Column statistics.state: DOUBLE NULL
[--] +-- Column statistics.sum: DOUBLE NULL
[--] +-- TABLE: statistics_meta
[--] +-- TYPE: InnoDB
[--] +-- Index ix_statistics_meta_statistic_id - Cols: statistic_id - Type: BTREE
[--] +-- Index PRIMARY - Cols: id - Type: BTREE
[--] +-- Column statistics_meta.id: INT(11) NOT NULL
[--] +-- Column statistics_meta.statistic_id: VARCHAR(255) NULL
[--] +-- Column statistics_meta.source: VARCHAR(32) NULL
[--] +-- Column statistics_meta.unit_of_measurement: VARCHAR(255) NULL
[--] +-- Column statistics_meta.has_mean: TINYINT(1) NULL
[--] +-- Column statistics_meta.has_sum: TINYINT(1) NULL
[--] +-- Column statistics_meta.name: VARCHAR(255) NULL
[--] +-- TABLE: statistics_runs
[--] +-- TYPE: InnoDB
[--] +-- Index ix_statistics_runs_start - Cols: start - Type: BTREE
[--] +-- Index PRIMARY - Cols: run_id - Type: BTREE
[--] +-- Column statistics_runs.run_id: INT(11) NOT NULL
[--] +-- Column statistics_runs.start: DATETIME(6) NOT NULL
[--] +-- TABLE: statistics_short_term
[--] +-- TYPE: InnoDB
[--] +-- Index ix_statistics_short_term_start - Cols: start - Type: BTREE
[--] +-- Index ix_statistics_short_term_start_ts - Cols: start_ts - Type: BTREE
[--] +-- Index ix_statistics_short_term_statistic_id_start_ts - Cols: metadata_id,start_ts - Type: BTREE
[--] +-- Index PRIMARY - Cols: id - Type: BTREE
[--] +-- Column statistics_short_term.id: INT(11) NOT NULL
[--] +-- Column statistics_short_term.created: DATETIME(6) NULL
[--] +-- Column statistics_short_term.created_ts: DOUBLE NULL
[--] +-- Column statistics_short_term.metadata_id: INT(11) NULL
[--] +-- Column statistics_short_term.start: DATETIME(6) NULL
[--] +-- Column statistics_short_term.start_ts: DOUBLE NULL
[--] +-- Column statistics_short_term.mean: DOUBLE NULL
[--] +-- Column statistics_short_term.min: DOUBLE NULL
[--] +-- Column statistics_short_term.max: DOUBLE NULL
[--] +-- Column statistics_short_term.last_reset: DATETIME(6) NULL
[--] +-- Column statistics_short_term.last_reset_ts: DOUBLE NULL
[--] +-- Column statistics_short_term.state: DOUBLE NULL
[--] +-- Column statistics_short_term.sum: DOUBLE NULL
[--] Database: pmadb
[--] +-- TABLE: pma__bookmark
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: id - Type: BTREE
[--] +-- Column pma__bookmark.id: INT(10) UNSIGNED NOT NULL
[--] +-- Column pma__bookmark.dbase: VARCHAR(255) NOT NULL
[--] +-- Column pma__bookmark.user: VARCHAR(255) NOT NULL
[--] +-- Column pma__bookmark.label: VARCHAR(255) NOT NULL
[--] +-- Column pma__bookmark.query: TEXT NOT NULL
[--] +-- TABLE: pma__central_columns
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: db_name,col_name - Type: BTREE
[--] +-- Column pma__central_columns.db_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__central_columns.col_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__central_columns.col_type: VARCHAR(64) NOT NULL
[--] +-- Column pma__central_columns.col_length: TEXT NULL
[--] +-- Column pma__central_columns.col_collation: VARCHAR(64) NOT NULL
[--] +-- Column pma__central_columns.col_isNull: TINYINT(1) NOT NULL
[--] +-- Column pma__central_columns.col_extra: VARCHAR(255) NULL
[--] +-- Column pma__central_columns.col_default: TEXT NULL
[--] +-- TABLE: pma__column_info
[--] +-- TYPE: InnoDB
[--] +-- Index db_name - Cols: db_name,table_name,column_name - Type: BTREE
[--] +-- Index PRIMARY - Cols: id - Type: BTREE
[--] +-- Column pma__column_info.id: INT(5) UNSIGNED NOT NULL
[--] +-- Column pma__column_info.db_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__column_info.table_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__column_info.column_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__column_info.comment: VARCHAR(255) NOT NULL
[--] +-- Column pma__column_info.mimetype: VARCHAR(255) NOT NULL
[--] +-- Column pma__column_info.transformation: VARCHAR(255) NOT NULL
[--] +-- Column pma__column_info.transformation_options: VARCHAR(255) NOT NULL
[--] +-- Column pma__column_info.input_transformation: VARCHAR(255) NOT NULL
[--] +-- Column pma__column_info.input_transformation_options: VARCHAR(255) NOT NULL
[--] +-- TABLE: pma__designer_settings
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: username - Type: BTREE
[--] +-- Column pma__designer_settings.username: VARCHAR(64) NOT NULL
[--] +-- Column pma__designer_settings.settings_data: TEXT NOT NULL
[--] +-- TABLE: pma__export_templates
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: id - Type: BTREE
[--] +-- Index u_user_type_template - Cols: username,export_type,template_name - Type: BTREE
[--] +-- Column pma__export_templates.id: INT(5) UNSIGNED NOT NULL
[--] +-- Column pma__export_templates.username: VARCHAR(64) NOT NULL
[--] +-- Column pma__export_templates.export_type: VARCHAR(10) NOT NULL
[--] +-- Column pma__export_templates.template_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__export_templates.template_data: TEXT NOT NULL
[--] +-- TABLE: pma__favorite
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: username - Type: BTREE
[--] +-- Column pma__favorite.username: VARCHAR(64) NOT NULL
[--] +-- Column pma__favorite.tables: TEXT NOT NULL
[--] +-- TABLE: pma__history
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: id - Type: BTREE
[--] +-- Index username - Cols: username,db,table,timevalue - Type: BTREE
[--] +-- Column pma__history.id: BIGINT(20) UNSIGNED NOT NULL
[--] +-- Column pma__history.username: VARCHAR(64) NOT NULL
[--] +-- Column pma__history.db: VARCHAR(64) NOT NULL
[--] +-- Column pma__history.table: VARCHAR(64) NOT NULL
[--] +-- Column pma__history.timevalue: TIMESTAMP NOT NULL
[--] +-- Column pma__history.sqlquery: TEXT NOT NULL
[--] +-- TABLE: pma__navigationhiding
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: username,item_name,item_type,db_name,table_name - Type: BTREE
[--] +-- Column pma__navigationhiding.username: VARCHAR(64) NOT NULL
[--] +-- Column pma__navigationhiding.item_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__navigationhiding.item_type: VARCHAR(64) NOT NULL
[--] +-- Column pma__navigationhiding.db_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__navigationhiding.table_name: VARCHAR(64) NOT NULL
[--] +-- TABLE: pma__pdf_pages
[--] +-- TYPE: InnoDB
[--] +-- Index db_name - Cols: db_name - Type: BTREE
[--] +-- Index PRIMARY - Cols: page_nr - Type: BTREE
[--] +-- Column pma__pdf_pages.db_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__pdf_pages.page_nr: INT(10) UNSIGNED NOT NULL
[--] +-- Column pma__pdf_pages.page_descr: VARCHAR(50) NOT NULL
[--] +-- TABLE: pma__recent
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: username - Type: BTREE
[--] +-- Column pma__recent.username: VARCHAR(64) NOT NULL
[--] +-- Column pma__recent.tables: TEXT NOT NULL
[--] +-- TABLE: pma__relation
[--] +-- TYPE: InnoDB
[--] +-- Index foreign_field - Cols: foreign_db,foreign_table - Type: BTREE
[--] +-- Index PRIMARY - Cols: master_db,master_table,master_field - Type: BTREE
[--] +-- Column pma__relation.master_db: VARCHAR(64) NOT NULL
[--] +-- Column pma__relation.master_table: VARCHAR(64) NOT NULL
[--] +-- Column pma__relation.master_field: VARCHAR(64) NOT NULL
[--] +-- Column pma__relation.foreign_db: VARCHAR(64) NOT NULL
[--] +-- Column pma__relation.foreign_table: VARCHAR(64) NOT NULL
[--] +-- Column pma__relation.foreign_field: VARCHAR(64) NOT NULL
[--] +-- TABLE: pma__savedsearches
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: id - Type: BTREE
[--] +-- Index u_savedsearches_username_dbname - Cols: username,db_name,search_name - Type: BTREE
[--] +-- Column pma__savedsearches.id: INT(5) UNSIGNED NOT NULL
[--] +-- Column pma__savedsearches.username: VARCHAR(64) NOT NULL
[--] +-- Column pma__savedsearches.db_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__savedsearches.search_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__savedsearches.search_data: TEXT NOT NULL
[--] +-- TABLE: pma__table_coords
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: db_name,table_name,pdf_page_number - Type: BTREE
[--] +-- Column pma__table_coords.db_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__table_coords.table_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__table_coords.pdf_page_number: INT(11) NOT NULL
[--] +-- Column pma__table_coords.x: FLOAT UNSIGNED NOT NULL
[--] +-- Column pma__table_coords.y: FLOAT UNSIGNED NOT NULL
[--] +-- TABLE: pma__table_info
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: db_name,table_name - Type: BTREE
[--] +-- Column pma__table_info.db_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__table_info.table_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__table_info.display_field: VARCHAR(64) NOT NULL
[--] +-- TABLE: pma__table_uiprefs
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: username,db_name,table_name - Type: BTREE
[--] +-- Column pma__table_uiprefs.username: VARCHAR(64) NOT NULL
[--] +-- Column pma__table_uiprefs.db_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__table_uiprefs.table_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__table_uiprefs.prefs: TEXT NOT NULL
[--] +-- Column pma__table_uiprefs.last_update: TIMESTAMP NOT NULL
[--] +-- TABLE: pma__tracking
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: db_name,table_name,version - Type: BTREE
[--] +-- Column pma__tracking.db_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__tracking.table_name: VARCHAR(64) NOT NULL
[--] +-- Column pma__tracking.version: INT(10) UNSIGNED NOT NULL
[--] +-- Column pma__tracking.date_created: DATETIME NOT NULL
[--] +-- Column pma__tracking.date_updated: DATETIME NOT NULL
[--] +-- Column pma__tracking.schema_snapshot: TEXT NOT NULL
[--] +-- Column pma__tracking.schema_sql: TEXT NULL
[--] +-- Column pma__tracking.data_sql: LONGTEXT NULL
[--] +-- Column pma__tracking.tracking: SET('UPDATE','REPLACE','INSERT','DELETE','TRUNCATE','CREATE DATABASE','ALTER DATABASE','DROP DATABASE','CREATE TABLE','ALTER TABLE','RENAME TABLE','DROP TABLE','CREATE INDEX','DROP INDEX','CREATE VIEW','ALTER VIEW','DROP VIEW') NULL
[--] +-- Column pma__tracking.tracking_active: INT(1) UNSIGNED NOT NULL
[--] +-- TABLE: pma__userconfig
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: username - Type: BTREE
[--] +-- Column pma__userconfig.username: VARCHAR(64) NOT NULL
[--] +-- Column pma__userconfig.timevalue: TIMESTAMP NOT NULL
[--] +-- Column pma__userconfig.config_data: TEXT NOT NULL
[--] +-- TABLE: pma__usergroups
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: usergroup,tab,allowed - Type: BTREE
[--] +-- Column pma__usergroups.usergroup: VARCHAR(64) NOT NULL
[--] +-- Column pma__usergroups.tab: VARCHAR(64) NOT NULL
[--] +-- Column pma__usergroups.allowed: ENUM('Y','N') NOT NULL
[--] +-- TABLE: pma__users
[--] +-- TYPE: InnoDB
[--] +-- Index PRIMARY - Cols: username,usergroup - Type: BTREE
[--] +-- Column pma__users.username: VARCHAR(64) NOT NULL
[--] +-- Column pma__users.usergroup: VARCHAR(64) NOT NULL
-------- Table structures analysis -----------------------------------------------------------------
[OK] All tables get a primary key
[OK] All tables are InnoDB tables
[OK] All columns are UTF-8 compliant
-------- Indexes Metrics ---------------------------------------------------------------------------
[--] Worst selectivity indexes:
[--] Index: ix_events_context_id(context_id)
[--] +-- COLUMN : HomeAssistant.events
[--] +-- NB SEQS : 1 sequence(s)
[--] +-- NB COLS : 1 column(s)
[--] +-- CARDINALITY : 1 distinct values
[--] +-- NB ROWS : 159462 rows
[--] +-- TYPE : BTREE
[--] +-- SELECTIVITY : 0.00%
[!!] ix_events_context_id(context_id) has a low selectivity
[--] Index: ix_states_context_id(context_id)
[--] +-- COLUMN : HomeAssistant.states
[--] +-- NB SEQS : 1 sequence(s)
[--] +-- NB COLS : 1 column(s)
[--] +-- CARDINALITY : 1 distinct values
[--] +-- NB ROWS : 12392475 rows
[--] +-- TYPE : BTREE
[--] +-- SELECTIVITY : 0.00%
[!!] ix_states_context_id(context_id) has a low selectivity
[--] Index: ix_statistics_start(start)
[--] +-- COLUMN : HomeAssistant.statistics
[--] +-- NB SEQS : 1 sequence(s)
[--] +-- NB COLS : 1 column(s)
[--] +-- CARDINALITY : 1 distinct values
[--] +-- NB ROWS : 583012 rows
[--] +-- TYPE : BTREE
[--] +-- SELECTIVITY : 0.00%
[!!] ix_statistics_start(start) has a low selectivity
[--] Index: ix_statistics_short_term_start(start)
[--] +-- COLUMN : HomeAssistant.statistics_short_term
[--] +-- NB SEQS : 1 sequence(s)
[--] +-- NB COLS : 1 column(s)
[--] +-- CARDINALITY : 1 distinct values
[--] +-- NB ROWS : 1085335 rows
[--] +-- TYPE : BTREE
[--] +-- SELECTIVITY : 0.00%
[!!] ix_statistics_short_term_start(start) has a low selectivity
[--] Index: ix_statistics_short_term_statistic_id_start_ts(metadata_id)
[--] +-- COLUMN : HomeAssistant.statistics_short_term
[--] +-- NB SEQS : 1 sequence(s)
[--] +-- NB COLS : 2 column(s)
[--] +-- CARDINALITY : 80 distinct values
[--] +-- NB ROWS : 1085335 rows
[--] +-- TYPE : BTREE
[--] +-- SELECTIVITY : 0.01%
[!!] ix_statistics_short_term_statistic_id_start_ts(metadata_id) has a low selectivity
[--] Index: ix_statistics_statistic_id_start_ts(metadata_id)
[--] +-- COLUMN : HomeAssistant.statistics
[--] +-- NB SEQS : 1 sequence(s)
[--] +-- NB COLS : 2 column(s)
[--] +-- CARDINALITY : 117 distinct values
[--] +-- NB ROWS : 583012 rows
[--] +-- TYPE : BTREE
[--] +-- SELECTIVITY : 0.02%
[!!] ix_statistics_statistic_id_start_ts(metadata_id) has a low selectivity
[--] Index: ix_events_event_type_id_time_fired_ts(event_type_id)
[--] +-- COLUMN : HomeAssistant.events
[--] +-- NB SEQS : 1 sequence(s)
[--] +-- NB COLS : 2 column(s)
[--] +-- CARDINALITY : 31 distinct values
[--] +-- NB ROWS : 159462 rows
[--] +-- TYPE : BTREE
[--] +-- SELECTIVITY : 0.02%
[!!] ix_events_event_type_id_time_fired_ts(event_type_id) has a low selectivity
[--] Index: ix_states_metadata_id_last_updated_ts(metadata_id)
[--] +-- COLUMN : HomeAssistant.states
[--] +-- NB SEQS : 1 sequence(s)
[--] +-- NB COLS : 2 column(s)
[--] +-- CARDINALITY : 11870 distinct values
[--] +-- NB ROWS : 12392475 rows
[--] +-- TYPE : BTREE
[--] +-- SELECTIVITY : 0.10%
[!!] ix_states_metadata_id_last_updated_ts(metadata_id) has a low selectivity
[--] Index: ix_statistics_short_term_start_ts(start_ts)
[--] +-- COLUMN : HomeAssistant.statistics_short_term
[--] +-- NB SEQS : 1 sequence(s)
[--] +-- NB COLS : 1 column(s)
[--] +-- CARDINALITY : 10435 distinct values
[--] +-- NB ROWS : 1085335 rows
[--] +-- TYPE : BTREE
[--] +-- SELECTIVITY : 0.96%
[!!] ix_statistics_short_term_start_ts(start_ts) has a low selectivity
[--] Index: ix_statistics_start_ts(start_ts)
[--] +-- COLUMN : HomeAssistant.statistics
[--] +-- NB SEQS : 1 sequence(s)
[--] +-- NB COLS : 1 column(s)
[--] +-- CARDINALITY : 8573 distinct values
[--] +-- NB ROWS : 583012 rows
[--] +-- TYPE : BTREE
[--] +-- SELECTIVITY : 1.47%
[!!] ix_statistics_start_ts(start_ts) has a low selectivity
[--] Indexes per database:
[--] Database: HomeAssistant
[--] +-- INDEX : events.ix_events_context_id
[--] +-- COLUMNS : context_id
[--] +-- CARDINALITY: 1
[--] +-- INDEX : events.ix_events_context_id_bin
[--] +-- COLUMNS : context_id_bin
[--] +-- CARDINALITY: 79731
[--] +-- INDEX : events.ix_events_data_id
[--] +-- COLUMNS : data_id
[--] +-- CARDINALITY: 39865
[--] +-- INDEX : events.ix_events_event_type_id_time_fired_ts
[--] +-- COLUMNS : event_type_id,time_fired_ts
[--] +-- CARDINALITY: 159493
[--] +-- INDEX : events.ix_events_time_fired_ts
[--] +-- COLUMNS : time_fired_ts
[--] +-- CARDINALITY: 159462
[--] +-- INDEX : events.PRIMARY
[--] +-- COLUMNS : event_id
[--] +-- CARDINALITY: 159462
[--] +-- INDEX : event_data.ix_event_data_hash
[--] +-- COLUMNS : hash
[--] +-- CARDINALITY: 31999
[--] +-- INDEX : event_data.PRIMARY
[--] +-- COLUMNS : data_id
[--] +-- CARDINALITY: 31999
[--] +-- INDEX : event_types.ix_event_types_event_type
[--] +-- COLUMNS : event_type
[--] +-- CARDINALITY: 30
[--] +-- INDEX : event_types.PRIMARY
[--] +-- COLUMNS : event_type_id
[--] +-- CARDINALITY: 30
[--] +-- INDEX : migration_changes.PRIMARY
[--] +-- COLUMNS : migration_id
[--] +-- CARDINALITY: 4
[--] +-- INDEX : recorder_runs.ix_recorder_runs_start_end
[--] +-- COLUMNS : start,end
[--] +-- CARDINALITY: 138
[--] +-- INDEX : recorder_runs.PRIMARY
[--] +-- COLUMNS : run_id
[--] +-- CARDINALITY: 69
[--] +-- INDEX : schema_changes.PRIMARY
[--] +-- COLUMNS : change_id
[--] +-- CARDINALITY: 3
[--] +-- INDEX : states.ix_states_attributes_id
[--] +-- COLUMNS : attributes_id
[--] +-- CARDINALITY: 774529
[--] +-- INDEX : states.ix_states_context_id
[--] +-- COLUMNS : context_id
[--] +-- CARDINALITY: 1
[--] +-- INDEX : states.ix_states_context_id_bin
[--] +-- COLUMNS : context_id_bin
[--] +-- CARDINALITY: 12392475
[--] +-- INDEX : states.ix_states_last_updated_ts
[--] +-- COLUMNS : last_updated_ts
[--] +-- CARDINALITY: 12392475
[--] +-- INDEX : states.ix_states_metadata_id_last_updated_ts
[--] +-- COLUMNS : metadata_id,last_updated_ts
[--] +-- CARDINALITY: 12404345
[--] +-- INDEX : states.ix_states_old_state_id
[--] +-- COLUMNS : old_state_id
[--] +-- CARDINALITY: 12392475
[--] +-- INDEX : states.PRIMARY
[--] +-- COLUMNS : state_id
[--] +-- CARDINALITY: 12392475
[--] +-- INDEX : states_meta.ix_states_meta_entity_id
[--] +-- COLUMNS : entity_id
[--] +-- CARDINALITY: 1359
[--] +-- INDEX : states_meta.PRIMARY
[--] +-- COLUMNS : metadata_id
[--] +-- CARDINALITY: 1359
[--] +-- INDEX : state_attributes.ix_state_attributes_hash
[--] +-- COLUMNS : hash
[--] +-- CARDINALITY: 1039736
[--] +-- INDEX : state_attributes.PRIMARY
[--] +-- COLUMNS : attributes_id
[--] +-- CARDINALITY: 1039736
[--] +-- INDEX : statistics.ix_statistics_start
[--] +-- COLUMNS : start
[--] +-- CARDINALITY: 1
[--] +-- INDEX : statistics.ix_statistics_start_ts
[--] +-- COLUMNS : start_ts
[--] +-- CARDINALITY: 8573
[--] +-- INDEX : statistics.ix_statistics_statistic_id_start_ts
[--] +-- COLUMNS : metadata_id,start_ts
[--] +-- CARDINALITY: 583129
[--] +-- INDEX : statistics.PRIMARY
[--] +-- COLUMNS : id
[--] +-- CARDINALITY: 583012
[--] +-- INDEX : statistics_meta.ix_statistics_meta_statistic_id
[--] +-- COLUMNS : statistic_id
[--] +-- CARDINALITY: 141
[--] +-- INDEX : statistics_meta.PRIMARY
[--] +-- COLUMNS : id
[--] +-- CARDINALITY: 141
[--] +-- INDEX : statistics_runs.ix_statistics_runs_start
[--] +-- COLUMNS : start
[--] +-- CARDINALITY: 10510
[--] +-- INDEX : statistics_runs.PRIMARY
[--] +-- COLUMNS : run_id
[--] +-- CARDINALITY: 10510
[--] +-- INDEX : statistics_short_term.ix_statistics_short_term_start
[--] +-- COLUMNS : start
[--] +-- CARDINALITY: 1
[--] +-- INDEX : statistics_short_term.ix_statistics_short_term_start_ts
[--] +-- COLUMNS : start_ts
[--] +-- CARDINALITY: 10435
[--] +-- INDEX : statistics_short_term.ix_statistics_short_term_statistic_id_start_ts
[--] +-- COLUMNS : metadata_id,start_ts
[--] +-- CARDINALITY: 1085415
[--] +-- INDEX : statistics_short_term.PRIMARY
[--] +-- COLUMNS : id
[--] +-- CARDINALITY: 1085335
[--] Database: pmadb
[--] +-- INDEX : pma__bookmark.PRIMARY
[--] +-- COLUMNS : id
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__central_columns.PRIMARY
[--] +-- COLUMNS : db_name,col_name
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__column_info.db_name
[--] +-- COLUMNS : db_name,table_name,column_name
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__column_info.PRIMARY
[--] +-- COLUMNS : id
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__designer_settings.PRIMARY
[--] +-- COLUMNS : username
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__export_templates.PRIMARY
[--] +-- COLUMNS : id
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__export_templates.u_user_type_template
[--] +-- COLUMNS : username,export_type,template_name
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__favorite.PRIMARY
[--] +-- COLUMNS : username
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__history.PRIMARY
[--] +-- COLUMNS : id
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__history.username
[--] +-- COLUMNS : username,db,table,timevalue
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__navigationhiding.PRIMARY
[--] +-- COLUMNS : username,item_name,item_type,db_name,table_name
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__pdf_pages.db_name
[--] +-- COLUMNS : db_name
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__pdf_pages.PRIMARY
[--] +-- COLUMNS : page_nr
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__recent.PRIMARY
[--] +-- COLUMNS : username
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__relation.foreign_field
[--] +-- COLUMNS : foreign_db,foreign_table
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__relation.PRIMARY
[--] +-- COLUMNS : master_db,master_table,master_field
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__savedsearches.PRIMARY
[--] +-- COLUMNS : id
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__savedsearches.u_savedsearches_username_dbname
[--] +-- COLUMNS : username,db_name,search_name
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__table_coords.PRIMARY
[--] +-- COLUMNS : db_name,table_name,pdf_page_number
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__table_info.PRIMARY
[--] +-- COLUMNS : db_name,table_name
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__table_uiprefs.PRIMARY
[--] +-- COLUMNS : username,db_name,table_name
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__tracking.PRIMARY
[--] +-- COLUMNS : db_name,table_name,version
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__userconfig.PRIMARY
[--] +-- COLUMNS : username
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__usergroups.PRIMARY
[--] +-- COLUMNS : usergroup,tab,allowed
[--] +-- CARDINALITY: 0
[--] +-- INDEX : pma__users.PRIMARY
[--] +-- COLUMNS : username,usergroup
[--] +-- CARDINALITY: 0
-------- Views Metrics -----------------------------------------------------------------------------
-------- Triggers Metrics --------------------------------------------------------------------------
-------- Routines Metrics --------------------------------------------------------------------------
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'HomeAssistant_user'@% does not specify hostname restrictions.
[!!] User 'root'@% does not specify hostname restrictions.
[--] There are 620 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1h 10m 39s (11 q [0.003 qps], 8 conn, TX: 60K, RX: 1K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory : 62.7G
[--] Max MySQL memory : 861.2M
[--] Other process memory: 40.3M
[--] Total buffers: 417.0M global + 2.9M per thread (151 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[--] Global Buffers
[--] +-- Key Buffer: 128.0M
[--] +-- Max Tmp Table: 16.0M
[--] Query Cache Buffers
[--] +-- Query Cache: OFF - DISABLED
[--] +-- Query Cache Size: 1.0M
[--] Per Thread Buffers
[--] +-- Read Buffer: 128.0K
[--] +-- Read RND Buffer: 256.0K
[--] +-- Sort Buffer: 2.0M
[--] +-- Thread stack: 292.0K
[--] +-- Join Buffer: 256.0K
[OK] Maximum reached memory usage: 419.9M (0.65% of installed RAM)
[OK] Maximum possible memory usage: 861.2M (1.34% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/11)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Aborted connections: 0.00% (0/8)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 4 total)
[OK] Thread cache hit rate: 87% (1 created / 8 connections)
[!!] Table cache hit rate: 5% (1 hits / 18 requests)
[OK] table_definition_cache (400) is greater than number of tables (325)
[OK] Open file limit used: 0% (24/32K)
[OK] Table locks acquired immediately: 100% (17 immediate / 17 locks)
-------- Performance schema ------------------------------------------------------------------------
[!!] Performance_schema should be activated.
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] General MyIsam metrics:
[--] +-- Total MyISAM Tables : 0
[--] +-- Total MyISAM indexes : 0B
[--] +-- KB Size :128.0M
[--] +-- KB Used Size :23.3M
[--] +-- KB used :18.2%
[--] +-- Read KB hit rate: 0% (0 cached / 0 reads)
[--] +-- Write KB hit rate: 0% (0 cached / 0 writes)
[--] No MyISAM table(s) detected ....
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Buffers
[--] +-- InnoDB Buffer Pool: 128.0M
[--] +-- InnoDB Buffer Pool Instances: 1
[--] +-- InnoDB Buffer Pool Chunk Size: 2.0M
[--] +-- InnoDB Log File Size: 96.0M
[--] +-- InnoDB Log File In Group: 1
[--] +-- InnoDB Total Log File Size: 96.0M(75 % of buffer pool)
[--] +-- InnoDB Log Buffer: 16.0M
[--] +-- InnoDB Buffer Free: 5.6K
[--] +-- InnoDB Buffer Used: 7.9K
[OK] InnoDB File per table is activated
[OK] InnoDB Buffer Pool size ( 128.0M ) under limit for 64 bits architecture: (17179869184.0G )
[!!] InnoDB buffer pool / data size: 128.0M / 3.8G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 96.0M * 1 / 128.0M should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 64 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] InnoDB Read buffer efficiency: 22.94% (642 hits / 2799 total)
[!!] InnoDB Write Log efficiency: 0% (0 hits / 0 total)
[OK] InnoDB log waits: 0% (0 waits / 0 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/2.5M
[!!] Aria pagecache hit rate: 7.1% (14 cached / 13 reads)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Run ALTER TABLE ... FORCE or OPTIMIZE TABLE to defragment tables for better performance
ALTER TABLE `HomeAssistant`.`states` FORCE; -- can free 511 MiB
Total freed space after defragmentation: 511 MiB
You are using an unsupported version for production environments
Upgrade as soon as possible to a supported version !
Add some space to 90 mountpoint.
setup swappiness lower or equal to 10
setup Max running number events greater than 1M
Check all table collations are identical for all tables in HomeAssistant database.
Limit collations for column to one collation if possible for HomeAssistant database.
Limit collations for column to one collation if possible for pmadb database.
Restrict Host for 'HomeAssistant_user'@'%' to 'HomeAssistant_user'@LimitedIPRangeOrLocalhost
RENAME USER 'HomeAssistant_user'@'%' TO 'HomeAssistant_user'@LimitedIPRangeOrLocalhost;
Restrict Host for 'root'@'%' to 'root'@LimitedIPRangeOrLocalhost
RENAME USER 'root'@'%' TO 'root'@LimitedIPRangeOrLocalhost;
MySQL was started within the last 24 hours: recommendations may be inaccurate
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
This is MyISAM only table_cache scalability problem, InnoDB not affected.
For more details see: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (32194) variable
should be greater than table_open_cache (2000)
Performance schema should be activated for better diagnostics
Be careful, increasing innodb_log_file_size / innodb_log_files_in_group means higher crash recovery mean time
Variables to adjust:
vm.swappiness <= 10 (echo 10 > /proc/sys/vm/swappiness) or vm.swappiness=10 in /etc/sysctl.conf
fs.aio-max-nr > 1M (echo 1048576 > /proc/sys/fs/aio-max-nr) or fs.aio-max-nr=1048576 in /etc/sysctl.conf
table_open_cache (> 2000)
performance_schema=ON
innodb_buffer_pool_size (>= 3.8G) if possible.
innodb_log_file_size should be (=32M) if possible, so InnoDB total log file size equals 25% of buffer pool size.
innodb_log_buffer_size (> 16M)
root@MariaDB:/# Docker System info:
[~] # docker info
Client:
Context: default
Debug Mode: false
Plugins:
compose: Docker Compose (Docker Inc., v2.15.1-qnap1)
Server:
Containers: 44
Running: 21
Paused: 0
Stopped: 23
Images: 59
Server Version: 20.10.27-qnap1
Storage Driver: zfs
Zpool: error while getting pool information exit status 2: "/sbin/zpool pool get -p name,health,allocated,size,free,readonly,dedupratio,fragmentation,freeing,leaked zpool1" => bad property list: invalid property 'fragmentation'
usage:
get [-Hp] [-o "all" | field[,...]] <"all" | property[,...]> <pool> ...
the following properties are supported:
PROPERTY EDIT VALUES
allocated NO <size>
capacity NO <size>
compdedup_count NO <compact/dedup entries count>
deadlist datadspace NO <deadlist used in datadspace>
deadlist metadspace NO <deadlist used in metadspace>
dedup-saving NO <size>
dedupratio NO <1.00x or higher if deduped>
dspace NO <size>
expandsize NO <size>
free NO <size>
freeing NO <size>
guid NO <guid>
health NO <state>
max_poolop NO <max_poolop>
nomal_class_datadspace NO <datadspace of spa_nomal_class
nomal_class_metadspace NO <metadspace of spa_nomal_class>
orig_qsize NO <size>
orig_zib_size NO <size>
overqthresh NO yes | no
prune_goal_deduced NO <DDT entries, decided by RAM size>
qguid NO <qguid>
qos_weight NO <weight total>
qsal_retention_data NO <qsal retention data size>
qsal_retention_freed NO <qsal freed retention data size>
qsal_retention_spill NO <qsal spilled retention data size>
qsize NO <size>
qthreshavail NO <size>
qthreshsize NO <size>
raidz_layout NO pristine | layout_reorder | layout_mirror
reserved_size NO <size>
shadow_refmap_reserve_shift NO <refmap reserve shift
shadow_refmap_shift NO <refmap shift>
size NO <size>
smartddt_state NO <pool smart DDT state, 0|1>
spacelow_overthresh NO yes | no
spacelow_threshavail NO <size>
spacelow_threshsize NO <size>
ssdop_size NO <size>
upsecs NO <secs>
usedbysnapshot NO <size>
zib_allocated NO <size>
zib_falloc_size NO <size>
zib_falloc_txg NO <size>
zib_free NO <size>
zib_metafree NO <size>
zib_metasize NO <size>
zib_size NO <size>
zib_worstamp NO <times>
aggrprefetch YES on | off
aggrprefetch_maxinit_sz YES <aggressive prefetch maxinit sz>
altroot YES <path>
async_read_max_active YES <pool async read max active count>
async_read_min_active YES <pool async read min active count>
async_write_max_active YES <pool async write max active count>
async_write_min_active YES <pool async write min active count>
asynccow YES on | off
autoexpand YES on | off
autoreplace YES on | off
bootfs YES <filesystem>
cachefile YES <file> | none
comment YES <comment-string>
compdedup_maxcount YES <maximum count for compact/dedup entries>
compdedup_minpshift YES <minimum pshift to use compact/dedup>
ddt_dec_freq_sec YES <ddt dec freq sec>
ddt_entry_limit YES <DDT entries, greater than 'prune_goal'>
ddt_freq_on_disk YES on | off
ddt_prune_min_time_ms YES <ddt prune min time ms>
ddt_prune_percentage YES <ddt prune percentage>
dedupditto YES <threshold (min 100)>
delegation YES on | off
failmode YES wait | continue | panic
globalcache YES on | off
globalcache_notuser YES on | off
indirectlayout YES on | off
l2cache_ioalign YES on | off
l2rebuild YES on | off
listsnapshots YES on | off
logvolume YES <filesystem>
migrate_error_handle YES pause | continue
migrate_ratio YES <pool load ratio for migrate>
owner YES 0 | 1
pool_overprovision YES <over provision percentage for indirect layout>
prune_goal YES <DDT entries>
prune_goal_by_ram YES on | off
prune_slack_txg YES <txgs>
qlog_policy YES legacy | clog
qos_enable YES 0 | 1
qos_max_4kbase YES <iops>
qos_reserved YES <pool qos reserved percentage>
qos_throttle YES on | off
qsnap YES <size> | none
qthresh YES <size> | none
raidzshift YES 24 default, 12(4KB) - 30(1GB)
raidzshift_i YES 0 default (rotate for every shadowblock), 12(4KB) - 30(1GB)
readonly YES on | off
resilver_pause YES on | off
resilver_ratio YES <pool load ratio for resilver>
scan_ignore_error YES on | off
scrub_ratio YES <pool load ratio for scrub>
shadowashift YES 0 default, 12 (4K) ~ 17 (128k)
shadowblockshift YES 0 auto, 17 (128KB) ~ 24 (16MB)
shadowminshift YES 0 default, 12 (4K) ~ 16 (64k)
smartddt YES on | off
smartddt_entrydrops YES <pool DDT entry drop ratio to re-open ddt feature>
smartddt_loadratio YES <pool load ratio for dedup operation cost>
smartddt_times YES <pool Smart DDT continuous times to enable mechanism>
smartddt_txgdirty YES <pool Txg Dirty throughput lower threshold>
smartddt_txgdirty_low YES <pool Txg Dirty throughput lowest threshold>
spacelow_thresh YES <size> | none
spacelow_threshbyte YES <size> | none
ssd_life_type YES off | disable | byoff
ssd_overprovision YES <over provision percentage for SSD>
sync_read_max_active YES <pool sync read max active count>
sync_read_min_active YES <pool sync read min active count>
sync_write_max_active YES <pool sync write max active count>
sync_write_min_active YES <pool sync write min active count>
tag YES <snapshot tag>
throttle YES on | off
upsecsupdate YES <secs>
vdev_aggregation_limit YES <max aggregation size>
version YES <version>
zib_disable_ref_negative_verify YES <zib disable ref negative verify>
feature@... YES disabled | enabled | active
The feature@ properties must be appended with a feature name.
See zpool-features(7).
Zpool Health: not available
Parent Dataset: zpool1/zfs530/zfs5300001
Space Used By Parent: 17586937856
Space Available: 1275915965892
Parent Quota: no
Compression: on
Logging Driver: json-file
Cgroup Driver: cgroupfs
Cgroup Version: 1
Plugins:
Volume: local
Network: bridge host ipvlan macvlan null overlay qnet
Log: awslogs fluentd gcplogs gelf journald json-file local logentries splunk syslog
Swarm: inactive
Runtimes: nvidia-runtime runc io.containerd.runc.v2 io.containerd.runtime.v1.linux kata-runtime
Default Runtime: runc
Init Binary: docker-init
containerd version: 8165feabfdfe38c65b599c4993d227328c231fca
runc version: v1.1.8-0-g82f18fe0
init version: de40ad0
Security Options:
apparmor
seccomp
Profile: default
Kernel Version: 5.10.60-qnap
Operating System: QTS 5.1.4 (20231128)
OSType: linux
Architecture: x86_64
CPUs: 12
Total Memory: 62.71GiB
Name: MediaServ
ID: NZGR:FICM:AEBC:QSRM:VYV3:VHNS:T3S3:JRMZ:PJNJ:TWMQ:LKQV:TN4C
Docker Root Dir: /share/ZFS530_DATA/.qpkg/container-station/docker
Debug Mode: true
File Descriptors: 3159
Goroutines: 320
System Time: 2024-05-02T14:34:04.045860658-07:00
EventsListeners: 6
Username: bikedude
Registry: https://index.docker.io/v1/
Labels:
Experimental: false
Insecure Registries:
127.0.0.0/8
Live Restore Enabled: false
Product License: Community Engine
Default Address Pools:
Base: 172.29.0.0/16, Size: 22
Apologies for the length but feels better to "show my work" in supporting some of my efforts to address the issue and demonstrate that unfortunately simply increasing XX isn't resolving the issue so in MY case the issue MAY be deeper/something else.
Thanks again for any help or even pointers in the right direction to save what little hair I might have left on may head after chasing this issue for months now....
grooverdan
commented
5 months ago mysqltuner version saying out of date is their bug https://github.com/major/MySQLTuner-perl/issues/707.
5.10.60 seems a long way behind 5.10.216 (https://kernel.org/) assuming the same numbering scheme.
Could check if the copied liburing implementation - https://github.com/axboe/liburing/commit/ea0fd51b78b3c089add37e8c4ee61abd460beb7a at < 5.12 matches the 5.10.60 version somewhere in https://git.kernel.org/pub/scm/linux/kernel/git/stable/linux.git/tree/fs/io_uring.c?h=v5.10.60
taw123
commented
4 months ago Thanks for the reply, and for confirming that the mysqlturner warning is indeed irrelevant.
Regarding the kernel version. Yah QNAP is notoriously slow turning updates. Cursory glance seems to show it actually released back on in Aug 2021....
I think I may come across the change commit you referenced which I also think I mentioned earlier (along with another one regrading respect for memory commitment on spawn processes), but both SEEMED at the time like they were made before Aug 21 so I (assumed) it was likely that 5.10.60 had the updates, and thus again a non issue.
I am not sure how to conclusively confirm that though from your comment. I don't believe there is a testable component version for liburing that I can use to confirm the commits have been applied, and don't know hoe to diff the change agains source tree you sent. I apologize for being at the limit of my knowledge base.
grooverdan
commented
4 months ago Yep, it was going to be a hard to confirm that the kernel maths was the same. I didn't see an obvious map, but I didn't want to spend much time on it either.
Given the fixes that came later in the kernel on uring and the ioblk layer, its probably safer that its detected as not available due to memory (or any other reason really).
Apologies if this is common knowledge to most. Unfortunately I have tried a number of things and researched here as well..
[mysqld: io_uring_queue_init() failed with ENOMEM: try larger memory locked limit #454]
Getting warning at startup of insufficient memory even AFTER updating ULIMIT (per console msg & related URL)
Clearly I am missing something both simpler and fundamental here, as I can't imagine this is actually effecting EVERYONE as there would be much more on I would have found searching so the most LIKELY source of this issue is my specific deployment. While this realistically belongs in a discussion forum but this repo doesn't have one so I'm going to appeal to you for any assistant you might be able to offer....
Thank you for any assistance. Hopefully this is as simple as it seems (despite the hours of beating my head against the wall)....
Extracted Compose:
Console log with error:
Complete container detail (demonstrating ULIMIT value):