High swap usage from mariadb
Within the past month or so I have been seeing high swap and mariadb seems to crash about once a day. Current MariaDB version is 10.11.19 and WHM version is 122.0.23 running on Alma Linux 9, I have tried searching and tried different recommendations to optimize the database but I am open to any suggestions.
-
It would be good to see what the system is doing in real-time before the crash happens. I like to run "mysqladmin proc status" to see that, as that may point you to any problems.
There are also automated tools available like MySQL Tuner https://github.com/major/MySQLTuner-perl that can help optimize the system (note that is not a tool created or distributed by cPanel, but it's very popular)
0 -
It's kind of hard to see when the crash will happen and usually I am not near a computer to take a look. Also I ran the Tuner script and I tried a couple of the changes, but so far it didn't have much of an effect.
This afternoon, the database crashed and 4 mins later it recovered, and right now swap is sitting at 17.45%.
This is what mysql admin proc status shows, aside from sleeping processes.
Uptime: 28438 Threads: 223 Questions: 21238291 Slow queries: 3 Opens: 164245 Open tables: 4000 Queries per second avg: 746.827This is what our hosting provider said - Some few potential reasons for this change can be a recent increase in the number of queries or data volume being processed by MySQL, leading to higher memory usage. Additionally, in case other processes are consuming more RAM, this might push MySQL to utilize swap more frequently.
0 -
When the database crashes, what shows up in the MySQL log? That is typically located at /var/log/mysqld.log, although the location can be changed in the /etc/my.cnf file.
0 -
Here is what I am seeing in the mysql log based on the time of the last crash.
2024-10-30 16:51:13 0 [Note] Starting MariaDB 10.11.9-MariaDB source revision 0e8fb977b00983d98c4c35e39bc1f36463095938 server_uid K09V5Smd2cm18XhzRB3ZPPQ0ApI= as process 1042937
2024-10-30 16:51:13 0 [ERROR] mariadbd: Can't open shared library '/usr/lib64/mysql/plugin/auth_socket.so' (errno: 2, cannot open shared object file: No such file or directory)
2024-10-30 16:51:13 0 [ERROR] Couldn't load plugins from 'auth_socket.so'.
2024-10-30 16:51:13 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2024-10-30 16:51:13 0 [Note] InnoDB: Number of transaction pools: 1
2024-10-30 16:51:13 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2024-10-30 16:51:13 0 [Note] InnoDB: Using Linux native AIO
2024-10-30 16:51:13 0 [Note] InnoDB: Initializing buffer pool, total size = 15.000GiB, chunk size = 240.000MiB
2024-10-30 16:51:13 0 [Note] InnoDB: Completed initialization of buffer pool
2024-10-30 16:51:13 0 [Note] InnoDB: File system buffers for log disabled (block size=4096 bytes)
2024-10-30 16:51:14 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=5229769010890 -
Well that's no good - unfortunately with InnoDB crashing there isn't much you can besides restore data from backups. The best information we have on the issue can be found here:
https://support.cpanel.net/hc/en-us/articles/1500006010582-InnoDB-Corruption-Repair-Guide
but ultimately you may want to consult with a professional MySQL admin for help with this issue.
0 -
Well that's not what I was looking to hear, but I am curious why I am seeing a lot of access denied messages.
For example
2024-10-31 0:13:55 87011 [Warning] Aborted connection 87011 to db: 'unconnected' user: 'unauthenticated' host: 'localhost' (This connection closed normally without authentication)
2024-10-31 0:14:04 87057 [Warning] Aborted connection 87057 to db: 'unconnected' user: 'unauthenticated' host: 'localhost' (This connection closed normally without authentication)
2024-10-31 0:14:07 87073 [Warning] Aborted connection 87073 to db: 'unconnected' user: 'unauthenticated' host: 'localhost' (This connection closed normally without authentication)
2024-10-31 0:14:31 87144 [Warning] Aborted connection 87144 to db: 'unconnected' user: 'unauthenticated' host: 'localhost' (This connection closed normally without authentication)
2024-10-31 0:14:35 87162 [Warning] Aborted connection 87162 to db: 'unconnected' user: 'unauthenticated' host: 'localhost' (This connection closed normally without authentication)
OR
2024-10-31 0:15:36 87435 [Warning] Access denied for user '@'localhost' to database ''
2024-10-31 0:15:36 87436 [Warning] Access denied for user ''@'localhost' to database ''
2024-10-31 0:15:36 87437 [Warning] Access denied for user ''@'localhost' to database ''0 -
Do you have some examples of the access denied messages that you could share?
0 -
I edited my post but removed the usernames and db names.
0 -
Thanks for that - it's always hard to say for sure from just that output, but it could be anything from malicious activity to someone with a script that has a bad password. Since the connection is coming from localhost the user is already on the server, so it's likely just bad credentials in someone's code. You may need to enable additional log verbosity through the "log_warnings" variable to get more information added to the log to see exactly what is happening there:
https://stackoverflow.com/questions/37992813/logging-verbosity-mysql-5-7
0 -
It would be localhost, because I restrict the database to only localhost connection.
0 -
So basically to some it up, I need to restore database from backups to fix the crashing issue, but what about the high swap usage? Below is a snip it from the perl script I ran about 2 weeks ago.
Variables to adjust:
fs.aio-max-nr > 1M (echo 1048576 > /proc/sys/fs/aio-max-nr) or fs.aio-max-nr=1048576 in /etc/sysctl.conf
skip-name-resolve=0
join_buffer_size (> 2.0M, or always use indexes with JOINs)
table_definition_cache (400) > 45505 or -1 (autosizing if supported)
key_buffer_size (~ 656M)0 -
Honestly, with the database in the state its in, I wouldn't worry about the swap usage. It's possible that fixing the crashing issues may take care of that.
0 -
Understood thanks.
0
Please sign in to leave a comment.
Comments
13 comments