Skip to content

Virtualmin SQL problem

Solved Performance
  • 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.

    34c9b40f-7785-44da-a1b5-dea8b32fc236-image.png

    It seems that the following tables cause problems

    • undo_001
    • undo_002
    • and mysql.ibd file

    881273dc-04d7-46dd-8b2c-2b98fe6212b7-image.png

    I noticed this error on the log too:

    ae838c9f-d8d0-4beb-a1fe-08e01f18358f-image.png

    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/4

    Do you think this is a solution?

    Thanks in advance

  • @DownPW you’ll need to go though the virtualmin way of removing the module itself, which is essentially the same as an apt remove mysql-server but with added dependency removal.

    Up to you at this point. I can have a look at resolving (hopefully) this tomorrow, or you can proceed with the removal?

    Let me know.

    @phenomlab

    I have delete the webmin modules like this :

    https://sudonix.com/assets/uploads/files/1668193237472-fea622b7-2f4c-4bab-89d4-5293bb85e352-image.png

    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

  • 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.

    34c9b40f-7785-44da-a1b5-dea8b32fc236-image.png

    It seems that the following tables cause problems

    • undo_001
    • undo_002
    • and mysql.ibd file

    881273dc-04d7-46dd-8b2c-2b98fe6212b7-image.png

    I noticed this error on the log too:

    ae838c9f-d8d0-4beb-a1fe-08e01f18358f-image.png

    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/4

    Do you think this is a solution?

    Thanks in advance

    @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.

  • @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.

    @phenomlab

    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.

  • @phenomlab

    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.

  • @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.

    @phenomlab

    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

  • @phenomlab

    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

    @phenomlab

    obviously, the mysql service cannot start:

    8aac2534-96f1-4579-89ea-a6f9cc685e43-image.png

    bf8c0088-1ac6-477f-a0a1-df83ad071d47-image.png

    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 😞

  • @phenomlab

    obviously, the mysql service cannot start:

    8aac2534-96f1-4579-89ea-a6f9cc685e43-image.png

    bf8c0088-1ac6-477f-a0a1-df83ad071d47-image.png

    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.

  • @DownPW let me have a look at this.

    @phenomlab

    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 the my.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.

  • @DownPW Just PM’d you - I need some details.

    @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 from 128k to 256k. The mysql 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.

    22450ab2-01db-4fdb-bbed-b6f700a2bec0-image.png

  • phenomlabundefined phenomlab has marked this topic as solved on
  • @phenomlab

    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)

    image.png

    Virtualmin backup log:
    becbd82e-dc1d-446c-9fe1-402f7406f410-image.png

    I still don’t understand.

    Your help is welcome 😉

  • phenomlabundefined phenomlab has marked this topic as unsolved on
  • @phenomlab

    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)

    image.png

    Virtualmin backup log:
    becbd82e-dc1d-446c-9fe1-402f7406f410-image.png

    I still don’t understand.

    Your help is welcome 😉

    @DownPW You should consider using the below inside the my.cnf file, then restart the mySQL service

    SET GLOBAL innodb_undo_log_truncate=ON;
    
  • @DownPW You should consider using the below inside the my.cnf file, then restart the mySQL service

    SET GLOBAL innodb_undo_log_truncate=ON;
    

    @phenomlab

    Are you sure for my.cnf file because it is located on /etc/alternatives/my.cnf

    863ce682-b3f5-4cd4-b4a1-0d7f5cf63750-image.png

    And here is the file:

    e00d4526-09ec-43aa-ae5a-5af8388ef104-image.png

    like this ?:

    be3fa85c-ef10-4b69-8096-7461cce87cc8-image.png

    if i see into webmin the mySQL servers, it’s already activated:

    0154ab2a-dac9-4dbc-94be-b439a49dccd8-image.png

  • @phenomlab

    Are you sure for my.cnf file because it is located on /etc/alternatives/my.cnf

    863ce682-b3f5-4cd4-b4a1-0d7f5cf63750-image.png

    And here is the file:

    e00d4526-09ec-43aa-ae5a-5af8388ef104-image.png

    like this ?:

    be3fa85c-ef10-4b69-8096-7461cce87cc8-image.png

    if i see into webmin the mySQL servers, it’s already activated:

    0154ab2a-dac9-4dbc-94be-b439a49dccd8-image.png

    @phenomlab

    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.

  • @phenomlab

    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 ?

  • @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 ?

    @phenomlab

    I have delete these 2 files manually with webmin. Stop and start the service

    3d2cc6df-8c05-4bf2-8abd-02cb4d864968-image.png

    I will monitor this and get back to you if it happens again.

  • –> For mysql.ibd file, is his size normal? (15,6 Go)

  • –> 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.

  • –> For mysql.ibd file, is his size normal? (15,6 Go)

    @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 ?


Did this solution help you?
Did you find the suggested solution useful? Why not buy me a coffee? It's a nice gesture, and a great way to show your appreciation 💗

Related Topics
  • NodeBB socket with CloudFlare

    Unsolved Performance
    23
    1 Votes
    23 Posts
    3k Views
    @DownPW it’s your only realistic option at this stage.
  • error with v3 in browser console

    Solved Performance
    4
    1
    0 Votes
    4 Posts
    273 Views
    @DownPW it’s in relation to the response I provided above
  • NodeBB v3 Chat Very Slow

    Moved Performance
    47
    11 Votes
    47 Posts
    4k Views
    @DownPW Seems fine.
  • build nodebb Warning in entrypoint size limit

    Solved Performance
    2
    0 Votes
    2 Posts
    247 Views
    @eeeee they are nothing to worry about, and can be ignored.
  • NodeBB v3.0.0-rc.1

    Performance
    1
    1 Votes
    1 Posts
    157 Views
    No one has replied
  • Optimum config for NodeBB under NGINX

    Performance
    4
    3 Votes
    4 Posts
    851 Views
    @crazycells hi - no security reason, or anything specific in this case. However, the nginx.conf I posted was from my Dev environment which uses this port as a way of not interfering with production. And yes, I use clustering on this site with three instances.
  • 5 Votes
    13 Posts
    719 Views
    'use strict'; const winston = require('winston'); const user = require('../user'); const notifications = require('../notifications'); const sockets = require('../socket.io'); const plugins = require('../plugins'); const meta = require('../meta'); module.exports = function (Messaging) { Messaging.notifyQueue = {}; // Only used to notify a user of a new chat message, see Messaging.notifyUser Messaging.notifyUsersInRoom = async (fromUid, roomId, messageObj) => { let uids = await Messaging.getUidsInRoom(roomId, 0, -1); uids = await user.blocks.filterUids(fromUid, uids); let data = { roomId: roomId, fromUid: fromUid, message: messageObj, uids: uids, }; data = await plugins.hooks.fire('filter:messaging.notify', data); if (!data || !data.uids || !data.uids.length) { return; } uids = data.uids; uids.forEach((uid) => { data.self = parseInt(uid, 10) === parseInt(fromUid, 10) ? 1 : 0; Messaging.pushUnreadCount(uid); sockets.in(`uid_${uid}`).emit('event:chats.receive', data); }); if (messageObj.system) { return; } // Delayed notifications let queueObj = Messaging.notifyQueue[`${fromUid}:${roomId}`]; if (queueObj) { queueObj.message.content += `\n${messageObj.content}`; clearTimeout(queueObj.timeout); } else { queueObj = { message: messageObj, }; Messaging.notifyQueue[`${fromUid}:${roomId}`] = queueObj; } queueObj.timeout = setTimeout(async () => { try { await sendNotifications(fromUid, uids, roomId, queueObj.message); } catch (err) { winston.error(`[messaging/notifications] Unabled to send notification\n${err.stack}`); } }, meta.config.notificationSendDelay * 1000); }; async function sendNotifications(fromuid, uids, roomId, messageObj) { const isOnline = await user.isOnline(uids); uids = uids.filter((uid, index) => !isOnline[index] && parseInt(fromuid, 10) !== parseInt(uid, 10)); if (!uids.length) { return; } if (roomId != 11) { // 5 Is the ID of the ID of the global chat room. Messaging.getUidsInRoom(roomId, 0, -1); // Proceed as normal. } else { user.getUidsFromSet('users:online', 0, -1); // Only notify online users. } const { displayname } = messageObj.fromUser; const isGroupChat = await Messaging.isGroupChat(roomId); const notification = await notifications.create({ type: isGroupChat ? 'new-group-chat' : 'new-chat', subject: `[[email:notif.chat.subject, ${displayname}]]`, bodyShort: `[[notifications:new_message_from, ${displayname}]]`, bodyLong: messageObj.content, nid: `chat_${fromuid}_${roomId}`, from: fromuid, path: `/chats/${messageObj.roomId}`, }); delete Messaging.notifyQueue[`${fromuid}:${roomId}`]; notifications.push(notification, uids); } };
  • 14 Votes
    69 Posts
    6k Views
    @phenomlab Seems to be better with some scaling fix for redis on redis.conf. I haven’t seen the message yet since the changes I made # I increase it to the value of /proc/sys/net/core/somaxconn tcp-backlog 4096 # I'm uncommenting because it can slow down Redis. Uncommented by default !!!!!!!!!!!!!!!!!!! #save 900 1 #save 300 10 #save 60 10000 If you have other Redis optimizations. I take all your advice https://severalnines.com/blog/performance-tuning-redis/