MySQL Terminates Due to Memory Despite Only Using Half

[PostAlmostAnything]

I got an error saying that the mysqld service was terminated due to lack of memory but then gave the memory usage as:

Status Out of Memory
Memory Information Used 7.65 GB
Available 8.02 GB
Installed 15.67 GB

Shouldn't the amount of memory used be at least close to the maximum to trigger a error?

Is there any way to allow mysql to use more memory without being shut down?

 

[cPRex]

Hey there! I'm not sure we have enough information to form a good response for this issue. Could you post your /etc/my.cnf configuration file so we could see that? That might give us some more helpful details.

 

[PostAlmostAnything]

Hey there! I'm not sure we have enough information to form a good response for this issue. Could you post your /etc/my.cnf configuration file so we could see that? That might give us some more helpful details.

My server is remote, so its not like I can just open a desktop and open a file. This just happened again but with some errors from the database for too many connections. How do I make sure mysql runs no matter how much memory is being used?

 

[Inna]

My server is remote, so its not like I can just open a desktop and open a file. This just happened again but with some errors from the database for too many connections. How do I make sure mysql runs no matter how much memory is being used?

Hi, do you have SSH access to the server? Or you just receive emails from cPanel?
Also you do have access to WHM? I mean http://your_server_ip:2087. Username is root and password is root password.

 

[cPRex]

My server is remote, so its not like I can just open a desktop and open a file.

I'm a bit confused at this reply. Is there a reason you can't edit files on your server? Yes, while there is no graphical interface, you can access the files over SSH or Terminal and edit those.

For this particular issue, it might be best to work with a system administrator to have them examine the machine and determine what caused the out of memory errors.

 

[PostAlmostAnything]

I'm a bit confused at this reply. Is there a reason you can't edit files on your server? Yes, while there is no graphical interface, you can access the files over SSH or Terminal and edit those.

For this particular issue, it might be best to work with a system administrator to have them examine the machine and determine what caused the out of memory errors.

I use cPanel so that I don't need to re-learn how to do stuff using SSH. I decided to pay you guys instead. Why you don't have a feature in WHM that allows people to select files to edit just like they can using cPanel File Manager is the problem here.

 

[cPRex]

We had a feature request open for this:

Login - cPanel & WHM Feature Requests Help Desk

features.cpanel.net

but it was decided at that time it likely wasn't something we would want to implement due to security issues.

While cPanel definitely makes things easier and configures most everything on the server, it's never going to be a replacement for old-school system administration when things get out of hand or services need to be checked.

 

[PostAlmostAnything]

We had a feature request open for this:

Login - cPanel & WHM Feature Requests Help Desk

features.cpanel.net

but it was decided at that time it likely wasn't something we would want to implement due to security issues.

While cPanel definitely makes things easier and configures most everything on the server, it's never going to be a replacement for old-school system administration when things get out of hand or services need to be checked.

What makes these security issues so important that users like me shouldn't be able to decide for themselves what features to use?

 

[cPRex]

We've gotten a bit off-topic here, but in general, having GUI access to the entire operating system where you can edit any file as root, isn't a good idea. Although you mentioned a desktop situation like Windows, there are still many hidden files and many areas where you'll get the "I am an administrator" confirmation pop-up if you try to adjust them. It's just bad practice and likely not something we're going to add.

I mean this in a completely 100% non-condescending, non-judgemental way, so I just wanted to clear that up since this is over text and things can get misinterpreted - it's completely fine to work with a server administrator. There's no shame in recognizing you're out of your depth and it's time to call a pro. It's not different than any other work - I own tools, and I'm completely comfortable completing an oil change on my vehicle. But if I blew a head gasket or a transmission, I take it to a pro. Computer systems are no different, and if you aren't sure how to find the MySQL configuration, it's unlikely you'd have much luck optimizing it in a way that will be beneficial to the health of your system. I think I'm pretty okay at optimizing MySQL, but if I get a corrupted InnoDB table, I either talk to some friends that do that type of work full-time or just restore from a backup.

It's also possible there could be something else entirely on the machine killing a process. It's also possible it might not even be on your server, but issues with a VPS parent that is mismanaged and the parent itself running out of RAM.

I guess what I'm getting at is with server optimization or odd issues, it comes down to so much more than "I just need to see one file" in any situation like this. A lot of it is feel and experience working with similar systems over years. There just isn't going to be an "edit this config and you'll be fixed" type of solution for this particular issue, unfortunately.

 

[PostAlmostAnything]

We've gotten a bit off-topic here, but in general, having GUI access to the entire operating system where you can edit any file as root, isn't a good idea. Although you mentioned a desktop situation like Windows, there are still many hidden files and many areas where you'll get the "I am an administrator" confirmation pop-up if you try to adjust them. It's just bad practice and likely not something we're going to add.

I mean this in a completely 100% non-condescending, non-judgemental way, so I just wanted to clear that up since this is over text and things can get misinterpreted - it's completely fine to work with a server administrator. There's no shame in recognizing you're out of your depth and it's time to call a pro. It's not different than any other work - I own tools, and I'm completely comfortable completing an oil change on my vehicle. But if I blew a head gasket or a transmission, I take it to a pro. Computer systems are no different, and if you aren't sure how to find the MySQL configuration, it's unlikely you'd have much luck optimizing it in a way that will be beneficial to the health of your system. I think I'm pretty okay at optimizing MySQL, but if I get a corrupted InnoDB table, I either talk to some friends that do that type of work full-time or just restore from a backup.

It's also possible there could be something else entirely on the machine killing a process. It's also possible it might not even be on your server, but issues with a VPS parent that is mismanaged and the parent itself running out of RAM.

I guess what I'm getting at is with server optimization or odd issues, it comes down to so much more than "I just need to see one file" in any situation like this. A lot of it is feel and experience working with similar systems over years. There just isn't going to be an "edit this config and you'll be fixed" type of solution for this particular issue, unfortunately.

Can't you just tell me how to make it so that nothing gets killed when there is so much memory left? If only half the memory is used nothing should be killed for memory reasons. I would rather make it wait until 90% is being used.

Another thing, why tell me to access stuff via CLI without also telling me how to access that stuff via CLI? Instead of giving me a file name why not say, "here is how you access that file"

 

[cPRex]

No, because we don't know *why* it's being killed. That's the root of the issue. Until we know that, there's really no specific advice we can provide.

If you have root access to the server, which you do, you're welcome to submit a ticket to our team so we can take a look.

 

[ffeingol]

Not to sidetrack things, but if MySQL is getting killed, that is the OS doing that, not cPanel/WHM. If it is getting killed by the OS, then there will be system log messages telling you that. If it's something MySQL related, then it will be in the MySQL log. As @cPRex said, with the logs, it's really hard to tell / just guessing.

 

[PostAlmostAnything]

No, because we don't know *why* it's being killed. That's the root of the issue. Until we know that, there's really no specific advice we can provide.

If you have root access to the server, which you do, you're welcome to submit a ticket to our team so we can take a look.

Is the contents of that file present in WHM under SQL Services -> Edit MySQL Configuration?

If so, why didn't you direct me to access the information there instead of forcing me to open it via CLI?

Also, how do I stop the databases from failing when the connection limit is reached? I suspect something is opening connections without closing others or something. Tried increasing max connections but eventually it reached that amount too.

 

[cPRex]

If so, why didn't you direct me to access the information there instead of forcing me to open it via CLI?

Whoops! That's a relatively recent addition to the WHM interface, so I still forget it's there. Yes, this is equivalent to the /etc/my.cnf file.

When you adjust the max_connections value, are you then confirming that is getting reached with the "mysqladmin proc status" command? If so, you're completely correct that something may not be closing the old connections, but that would be a coding issue on the site - MySQL doesn't have a way to know what connections should be closed.

You could try adjusting the wait_timeout value to something lower, as the default of 8 hours always seemed a bit high to me. I'd go with something like 300, as I would expect most any connection to connect properly and have some activity within 5 minutes.