@DownPW itās in relation to the response I provided above
Virtualmin SQL problem
-
Hello @phenomlab
I have a problem with Virtualmin and mySQL.
We have a server bug due to lack of storage space.
We cleaned up our files/backup and overnight the free space was drastically reduced.
By doing research it seems that mysql takes a huge space in a short time.
It seems that the following tables cause problems
- undo_001
- undo_002
- and mysql.ibd file
I noticed this error on the log too:
I also noticed that mysql also eats all CPU resources.
What can we do to improve this situation?
What advice would you give?I see this thread on virtualmin forum :
https://forum.virtualmin.com/t/mysql-files-occupied-whole-disk-which-file-to-delete-to-recover/116374/4Do you think this is a solution?
Thanks in advance
-
I have delete the webmin modules like this :
But it wasnāt enough because the mySQL service was still present.
So I have uninstall completely mysql like this:- First make sure that MySQL service is stopped :
sudo systemctl stop mysql
- Remove MySQL related all packages completely :
sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*
- Remove MySQL configuration and data :
sudo rm -rf /etc/mysql /var/lib/mysql /var/log/mysql
- (Optional) Remove unnecessary packages.
sudo apt autoremove
- (Optional) Remove apt cache.
sudo apt autoclean
No issues noted so far
Finally problem solved simply but I still donāt understand why this service was installed.
I thought Webmin or Virtualmin was using it when itās not at all after confirming this by @phenomlab
Thanks dude. Always
-
@DownPW the presence of undo indicates a failed roll back from an attempted commit. Iām keen to understand what you are using mySQL for since Virtualmin alone should never cause this.
Ate any other sites or services reliant on mySQL?
In addition, the absence of any backup or maintenance plan can also cause the database to become huge in size because the transaction logs are not being committed.
-
I donāt use mySQL for any other service at all.
Just mongodb for nodebb and postgreSQL for my Wiki. Nothing more.
I have planified a scheduled backup of Webmin configuration and virtualmin but I have deactive it.
-
@DownPW ok. This line in the article you found is pretty much conclusive
SET GLOBAL innodb_undo_log_truncate=ON;
Having to execute this would indicate that the databases arenāt being backup up correctly or flushed after commit.
-
can you provide all command step by step ?
I donāt know with which user to connect to Mysql for webmin ?Will this prevent us from having this kind of problem in the future?
Will undo files be deleted? for recovers space
-
obviously, the mysql service cannot start:
See this in the log
journalctl -fu mysql -- Logs begin at Wed 2022-08-31 13:22:26 CEST. -- Oct 30 14:19:07 vps.XXXX systemd[1]: Failed to start MySQL Community Server. Oct 30 14:19:08 vps.XXXX systemd[1]: mysql.service: Scheduled restart job, restart counter is at 664. Oct 30 14:19:08 vps.XXXX systemd[1]: Stopped MySQL Community Server. Oct 30 14:19:08 vps.XXXX systemd[1]: Starting MySQL Community Server... Oct 30 14:21:39 vps.XXXX systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE Oct 30 14:21:39 vps.XXXX systemd[1]: mysql.service: Failed with result 'exit-code'. Oct 30 14:21:39 vps.XXXX systemd[1]: Failed to start MySQL Community Server. Oct 30 14:21:39 vps.XXXX systemd[1]: mysql.service: Scheduled restart job, restart counter is at 665. Oct 30 14:21:39 vps.XXXX systemd[1]: Stopped MySQL Community Server. Oct 30 14:21:39 vps.XXXX systemd[1]: Starting MySQL Community Server...
I believe the system is trying to restart the service to no avail.
Iām starting to miss this virtualmin
-
@DownPW let me have a look at this.
-
Ok. Good
-
@DownPW Just PMād you - I need some details.
-
@DownPW To my mind, you need to increase the
thread_stack
value but I donāt see themy.cnf
file for and I think Virtualmin does this another way.When did this start happening ? From what I see, you have data waiting to be committed to mySQL, which is usually indicating that there is no backup which in turn would truncate the logs which are now out of control.
-
@phenomlab This is now fixed. For reference, your system is running at its maximum capacity with even the virtual memory 100% allocated. I needed to reboot the server to release the lock (which Iāve completed with no issues) and have also modified
etc/mysql/mysql.conf.d/mysqld.cnf
And increased the
thread_stack
size from128k
to256k
. Themysql
service has now started successfully. You should run a backup of all databases ASAP so that remaining transactions are committed and the transaction logs are flushed. -
-
I come back to you regarding the MySQL problem of virtualmin.
the service run smoothly, the backups of virtualmin are made without error, but I always have files with large sizes that always get bigger.
The
/var/log/mysql/error.log
is empty.MySQL.idb is as 15 Go ???!! Very Big
Same for undo_001 (4,12 Go) and undo_002 (14,32 Go)Virtualmin backup log:
I still donāt understand.
Your help is welcome
-
-
@DownPW You should consider using the below inside the
my.cnf
file, then restart themySQL
serviceSET GLOBAL innodb_undo_log_truncate=ON;
-
Are you sure for
my.cnf
file because it is located on/etc/alternatives/my.cnf
And here is the file:
like this ?:
if i see into webmin the mySQL servers, itās already activated:
-
If I add this line on my.cnf file, the mySQL service donāt start - failed
This is problematic because mySQL takes 36 GB of disk space so it alone takes up half of the serverās disk space.
I donāt think this is a normal situation.
-
@DownPW itās certainly not normal as Iāve never seen this on any virtualmin build and Iāve created hundreds of them. Are you able to manually delete the undo files ?
-
I have delete these 2 files manually with webmin. Stop and start the service
I will monitor this and get back to you if it happens again.
-
ā> For mysql.ibd file, is his size normal? (15,6 Go)
-
@DownPW not normal, no, but you mustnāt delete it or it will cause you issues.
-
@DownPW the thing that concerns me here is that Iāve never seen an issue like this occur with no cause or action taken by someone else.
Do you know if anyone else who has access to the server has made any changes ?