2

A few days ago I got a new machine setup running CentOS 5.6 (64bit), with 12GB ddr3 ram, 4x SAS 15k rpm drives in RAID10. On this machine I'm running MySQL Server version: 5.1.39-maria-beta-ourdelta67-log, which I have been using on previous servers as well with great results. This is my my.cnf http://pastebin.com/kLfmbHSW

Now before this morning MySQL would always use up the specified RAM that I had setup in my.cnf (about 95% of the 12GB). Instantly after staring mysql I would check 'top', and indeed 95% of the ram would be taken.

But now, after I restart MySQL i see this when i hit 'top'.

Mem: 12289856k total, 1884924k used, 10404932k free, 39660k buffers

Only about 15% is used. This number goes up a bit slowly, but before it would always use up all the RAM straight away.

What could be causing this?


Update: Since i restarted mysql, its been slowly using more and more ram, up to a point where it took up all my 12gb, and the whole machine locked up. I had to reboot linux to get it back online again. Its now on 1.8gb again, and slowly going up. The whole ram management seems to be bad, as it takes more ram, but never flushes it.

Any ideas what might be causing this?

10
  • First, I assume you checked all the RAM being used is in fact being used by MySQL. I would suggest checking the output of SHOW STATUS for a hint where in MySQL the memory is actually being used. Check Qcache_free_memory and Innodb_buffer_pool_pages_free to see if your query cache or buffer pool is being exhausted. Jul 7, 2011 at 14:07
  • pastebin.com/D57LxsC8 this is the output of mysql SHOW STATUS;
    – Mr.Boon
    Jul 7, 2011 at 14:35
  • When did you run SHOW STATUS? Was this immediately after the server started? Was the server running any kind of workload? Jul 7, 2011 at 15:06
  • This was about 20 minutes after i restarted mysql. and it was under normal working load (600 queries per second). This server runs the website: pastebin.com.
    – Mr.Boon
    Jul 7, 2011 at 15:10
  • Were you experiencing a lot of memory pressure at this point? Jul 7, 2011 at 15:12

2 Answers 2

0

It seems you are running a beta release, which tend to be buggy. However, just because you specify what memory to use, it doesn't mean that the OS will allocate it. When mysql asks the OS for the amount of memory say 8GB you have, it receives a sort of promissory note: "yeah, sure go ahead, you can have 8gb".

Then the application (mysql in this case) manages its own heap, and how much memory it uses. The usage goes up as the heap increases, as the number of queries increases. This is why you see an increase over time.

The reason why your server locked could be from many causes such as other applications using more memory than you expected, or simply there's a bug in mysql (which I tend to find unlikely).

2
  • Up to this morning the ram management was just fine on this machine. The moment I would start MySQL it would assign the specified amount of memory in the RAM, and it would manage it nicely. Now, it assigns nothing, and it just takes and takes and takes until all ram is taken, then it goes on to the hard disks. Of course those cannot keep up, so the system crashes. What could cause this total lack of management of the ram all the sudden?
    – Mr.Boon
    Jul 7, 2011 at 14:56
  • Sorry, can't help. Researched it a bit and found an article that helps calculate what the pool size and memory usage: mysqlperformanceblog.com/2006/05/30/innodb-memory-usage but, I don't see anything wrong with your config.
    – Roland Mai
    Jul 7, 2011 at 17:30
0

It seems to be resolved now. I had to rebuild all indexes, and after that mysql memory usage returned to normal. Although I had done a mysql check on all tables, and indexes before, and it found no errors, rebuilding the indexes did fix the problem.

Thanks for all the suggestions guys!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .