MySQL 5.5.17 and memory issue

[Wade Watts]

Hello,
i have a CentOS 5.7 (Final) virtual machine (OpenVZ).

I followed the instructions here https://www.atomicorp.com/wiki/index.php/PHP to upgrade php and mysql but now i am experiencing a bad problem.

My Virtual Machine is limited to 512MB of RAM (no swap is possible) and MySQL is pretty much eating it all.

This is the output of htop:


This is meminfo which confirms the issue:
cat /proc/meminfo
MemTotal: 524800 kB
MemFree: 5684 kB

How can i fix this? It is a pretty bad problem because after few seconds i am unable to run any program, even the most basic ones since there is no more free memory.

Everything worked fine before the update and there was plenty of RAM available.

Thank you.

[scott]

That looks about normal for 5.5. Maybe you overdid it in your my.cnf? Try disabling things like caching.

[mikeshinn]

34MB resident is pretty small, thats less than 7% of your memory, what else is using memory on your system? Whats the other 93+%?

[Wade Watts]

Hello, thank you for your answers.

For scott:
This is my "my.cnf":

Code: Select all

 cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# To allow mysqld to connect to a MySQL Cluster management daemon, uncomment
# these lines and adjust the connectstring as needed.
#ndbcluster
#ndb-connectstring="nodeid=4;host=localhost:1186"

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[ndbd]
# If you are running a MySQL Cluster storage daemon (ndbd) on this machine,
# adjust its connection to the management daemon here.
# Note: ndbd init script requires this to include nodeid!
connect-string="nodeid=2;host=localhost:1186"

[ndb_mgm]
# connection string for MySQL Cluster management tool
connect-string="host=localhost:1186"

This is the output of htop once mysqld has been killed (with service mysqld stop):


So, as you can see, most RAM is free once mysql is dead.

After some minutes the problem gets so bad that it is also impossible to reboot the machine, infact:

Code: Select all

 reboot
-bash: fork: Cannot allocate memory

For mikeshinn:
To what i can tell it looks like mysql is filling up all the available RAM... and for no reason either (the server only hosts few small sites with very low bandwidth and CPU usage).

Thank you.

[scott]

Thats about as low memory of a config as you can get. Try dropping any unused databases you have, especially anything using innodb.

[mikeshinn]

My Virtual Machine is limited to 512MB of RAM (no swap is possible) and MySQL is pretty much eating it all.

So you dont have SWAP? Just 512MB is all you have? If so, keep in mind that a database needs memory for the tables themselves, so you may just not have enough memory even if you dial mysql back to not cache (btw, any database is gonna crawl if you cant cache).

Can you add swap?

[paulie]

Virtuozzo / OpenVZ don't do swap (although apparently its been inflicted on us in the latest version). I think Virtuozzo is primarily used in single server setups, in which case giving someone real swap is just giving them a new way to DoS the server!

What you sometimes have instead is burst, but thats even worse than swap since it turns out that the system can burst for less than 15 seconds before Virtuozzo will start OOM killing container processes.

Anyway, Virtuozzo is not so bad, and the above is a digression, I'd say the problem here is the number of MySQL threads/processes (24?), any idea what MySQL is actually doing? (ie Has the upgrade script gone wrong or failed to run? Whats in the logs and the processlist?)

paul.

[Wade Watts]

Thank you again for your answers.

For mikeshinn:
Yes i only have 512MBytes of RAM and no, i cannot use the Swap. OpenVZ just forbids it. If i try to create and "mount" a swap file i get a permission denied. I read that this is normal on OpenVZ and there is nothing i can do about it.

For scott:
I tried dropping as many tables as i could... but it didn't work. I only had 5 or 6 databases, mostly unused, and still got MySQL eating the whole free RAM.

For paulie:
Yes, OpenVZ does not let me add swap. No idea about what MySQL 5.5 was doing... apparently nothing or near to nothing. As i said, the server only hosts few small sites with few visits per day. The mysql process-list (from phpmyadmin) was pretty much always empty.



I had to revert to PHP 5.1.6 and MySQL 5.0

As you can see now i have plenty free RAM (with both Apache and MySQL running). The configuration (my.cnf) did not change.


I tried any kind of trick with my.cnf, but i couldn't get MySQL 5.5 to use less than 400 MBytes of RAM. I tried stopping Apache and starting only MySQL... but it did not help. As soon as i started MySQL, no matter if Apache was running or not, i almost had no more free memory.

So either MySQL 5.5 eats MUCH more memory than MySQL 5.0, or for some reason something went wrong during the update (i did not see any error message though).

Unfortunately i cannot test it anymore since this is the only server i have and i need it (or else my users will get mad at me)... i'll have to stick with PHP 5.1.6 and MySQL 5.0 and this really sucks since most softwares out there now require at least PHP 5.2 if not PHP 5.3

Thank you.

[faris]

I don't get why you have so many mysql instances running, even with 5.0. All our VPSes (Virtuozzo/OpenVZ) have just one (when viewed with normal "top", at least)

If this is rerally happening and it isn't just the way it is being displayed in your screen grab, then this is likely to be the problem.
Having said that, I don't see how so many could be running without an error - they can't all run and listen on the same port, surely?

So I think I'm misreading your output.

[Wade Watts]

I don't get why you have so many mysql instances running, even with 5.0. All our VPSes (Virtuozzo/OpenVZ) have just one (when viewed with normal "top", at least)

If this is rerally happening and it isn't just the way it is being displayed in your screen grab, then this is likely to be the problem.
Having said that, I don't see how so many could be running without an error - they can't all run and listen on the same port, surely?

So I think I'm misreading your output.

Hello,
yes, sorry, that could be misleading... htop displays threads too.

If i set it so to display processes only then i get this (with MySQL 5.0 but also with MySQL 5.5):


Btw, this screenshot was taken after about 20 hours of run-time and as you can see MySQL 5.0 doesn't even get close to fill the whole memory while MySQL 5.5 would always go straight to around 350-400MBytes the same instant i started it.

Thank you.

[mikeshinn]

Have you disabled caching? 5.5 caches a lot more than older versions of mysql.

Side note:

Keep in mind that even if you do get mysql to use less memory, you'll be fighting a losing battle.

To explain, since you don't have swap thats why mysql is "using" more memory. Its got nowhere to page to, so its got to use real memory. 5.1 used less total memory (it paged too), 5.5 uses more with the additional caching capabilities. In reality if you had swap you'd only be using 25MB with 5.5, but since you dont you get to keep everything resident using up real memory you dont need to use. So part of the problem is the server configuration itself. Your virtual server is robbing you of memory by forcing you to keep everything resident, so I'd fix that ASAP. If your virtualization technology doesnt support swap move to a different technology that does (kvm, vmware, xen, etc.). Its killing you to not have any paging capability, you're wasting memory on things you could page out.

With all that said, 512MB is a really small footprint for a modern server, as a basis of comparison, an iPad has 512MB of memory, and a server is going to be asked to do a LOT more than an ipad.

So eventually even with swap and 51MB of real memory you'll hit a limit again. swapping is SLOW compared to real memory, so conversely you dont want to swap if you can help it. Too little RAM and you start swapping things that should be resident (as opposed to the stuff that doesnt need to be). So, I'd recommend at least 2GB on a 64bit platform.

I'm passing this on because you're going to find more and more problems with such a small memory footprint (512MB) with other modern software you want to use, not just mysql. For example apache has some neat caching modules, there are some great accelerators that use RAM and speed up things, and so on. So on top of not being able to use those kool things to make things faster, these days developers assume the system is going to have more memory (and swap), so they are going to design other software around that assumption to try to use as much memory as possible to speed things up for their code too (memory is really really fast compared to reading from disk).

So, bottom line - RAM is cheap, get as much as you can!

[Highland]

While RAM is indeed cheap, it's also true that everything is heading to VDS (i.e. cloud hosting) because the cost is so much less than dedicated. So RAM might be cheaper in dedicated but that hasn't translated to VDS yet. In fact, you can get a basic Rackspace cloud machine with 256MB for something like $11/mo (not sure what you could use it for beyond, say, DNS slaves or basic HTML but it's there!)

I ran into these problems trying to do some very basic hosting on an AWS micro instance (685MB RAM, no native swap) and, yes, if you're running MySQL on the same box without swap, expect it to die. A lot. Incidentally, you can set up a 1GB EBS in AWS and use that as your swap. Not a neat solution (EBS is slower data transfer) but your server is a LOT less likely to choke.