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?

Mr.Boon
  • 1,471
  • 4
  • 24
  • 43
  • 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. – Michael Mior Jul 07 '11 at 14:07
  • http://pastebin.com/D57LxsC8 this is the output of mysql SHOW STATUS; – Mr.Boon Jul 07 '11 at 14:35
  • When did you run `SHOW STATUS`? Was this immediately after the server started? Was the server running any kind of workload? – Michael Mior Jul 07 '11 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 07 '11 at 15:10
  • Were you experiencing a lot of memory pressure at this point? – Michael Mior Jul 07 '11 at 15:12
  • http://pastebin.com/6YWtRei7 this is an updated version of the earlier show status. (mysql is now running for 40 minutes) server load is still normal (0.5), sites are fast, queries are nice as fast. overall system memory usage has now gone to just over 6GB (when i start mysql its 1.5gb). But important to know, the memory usage goes up every second, it never goes down. This will keep going up till it hits 12GB of memory usage, then the whole server will crash. This problem is brand new, before this morning the server would handle the memory just fine. – Mr.Boon Jul 07 '11 at 15:22
  • Have you confirmed that it is only MySQL which is eating up memory? You could try using `ulimit` to limit the amount of memory available to MySQL. Although this isn't a solution, it would help confirm your problem. – Michael Mior Jul 07 '11 at 16:58
  • Also as I noticed Roland pointed out, it seems you're using a beta version of MariaDB versus a stable version of MySQL. If you can, I would suggest installing the latest GA release of MySQL and see if you continue to experience the problem. – Michael Mior Jul 07 '11 at 17:00
  • I agree with the others that moving away from your older, beta release of MariaDB is worth trying. MariaDB-5.1.39 Beta was released in November 2009. The first stable release was 5.1.42 in March 2010. The latest stable release is 5.1.55 from March 2011. There have been [lots of changes][1]. If the problem is actually related to MariaDB, I doubt you'll be able to get help from that community without upgrading to the latest version. [1]: http://kb.askmonty.org/en/changelogs – sciurus Jul 07 '11 at 19:41
  • I installed the latest version of MariaDB and confirmed that adjusting innodb_buffer_pool_size does affect the virtual memory usage of mysqld at startup. If you start it with the my.cnf you posted and the VIRT column in top doesn't show more than 8000m, something is wrong. – sciurus Jul 07 '11 at 20:03

2 Answers2

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

Candide
  • 141
  • 5
  • 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 07 '11 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: http://www.mysqlperformanceblog.com/2006/05/30/innodb-memory-usage/ but, I don't see anything wrong with your config. – Candide Jul 07 '11 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!

Mr.Boon
  • 1,471
  • 4
  • 24
  • 43