Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Assuming you have a dedicated server explicitly for database functions -- how much memory should you reserve for the operating system?

I realize this will probably vary somewhat depending on the specific OS, the specific database software, and so forth. But, as memory is so important to database performance, I want the database to have the maximum reasonable level of memory, without starving the host OS.

So

  • what's a good rule of thumb to start with?
  • what counters or performance indicators should we look at to determine if we've gone too far and the host OS is being starved somehow by the database?
share|improve this question
    
did you guys ever tweak these settings? What did your profiling tell you? –  jcolebrand May 28 '11 at 4:38
    
@jcole nothing to add at this time; we typically reserve 4 - 6 gb for the OS which is a tad more conservative than I would like –  Jeff Atwood May 28 '11 at 4:49
add comment

4 Answers

Assuming Windows and SQL Server...

There are two schools of thought.

  1. Leave 2-4 Gigs for Windows (depending on what is installed besides SQL Server).
  2. Leave 10% of your available memory free. As you get over 64 Gigs this gets to be a crazy large amount of memory to leave for the OS, which it probably won't need.

Personally I'm in the first group. Windows usually just needs 2-4 gigs, sometimes up to 6.

share|improve this answer
2  
Concur on 2 ish gb of RAM for the OS. –  jcolebrand Jan 13 '11 at 4:12
    
I also agree. I typically leave 2GB for OS and ensure contiguous page file space as well. Of course, your mileage may vary, and your best bet is to configure the setting, monitor performance, and make cautious changes as necessary. –  Matt M Jan 13 '11 at 4:24
2  
@matt right but what specifically is best to monitor, when looking at "am I starving the OS out of memory?" –  Jeff Atwood Jan 13 '11 at 8:46
1  
@Jeff My comment was not as precise as it should have been. For memory, I typically look at the following performance counters: Memory: Pages/sec and Memory: Available Bytes. Check these pages out for more information: sql-server-performance.com/articles/audit/… and technet.microsoft.com/en-us/library/cc966540.aspx (specifically the Memory Bottlenecks section) –  Matt M Jan 13 '11 at 14:32
6  
@Jeff Denny's answer is right, and for Perfmon counters, watch Memory: Available Bytes. If it drops below, say, 512MB, you're in danger of starving the OS. However, also be aware of the file cache on 64-bit systems, which still shows up as available even when it's not: blogs.msdn.com/b/ntdebugging/archive/2007/11/27/… –  Brent Ozar Jan 13 '11 at 16:07
show 4 more comments

Assuming Linux, if you turn off swap and the kernel keeps killing your DB process because it's out of memory, that's a good indicator you're starving the OS for memory. Back off until that stops happening. A couple hundred megs is usually plenty.

share|improve this answer
    
+1 for profile profile profile ~ however being it's Jeff askin I presume it's SQLServer and Windows for sure ;) –  jcolebrand Jan 13 '11 at 4:25
1  
@jcolebrand however, also knowing it's @jeff you'll recognize he didn't say, meaning he intentionally left this open for all platforms and databases. –  xenoterracide Jan 13 '11 at 15:10
2  
And, still knowing it's @jeff... why should he not be able to learn to stop worrying and love the Linux? ;-) –  Jürgen A. Erhard Jan 17 '11 at 12:42
add comment

You can leverage Amazon's experience running thousands of customers' database servers here: On Amazon Relational Database Service, they set MySQL's innodb buffer pool to 3/4 of the system's memory, regardless of how much memory that is. Add in up to a couple megs per connection for various query buffers, and they're likely leaving 10-20% of the memory to the OS.

share|improve this answer
add comment

You should read Brent Ozar's take on memory. He has some fairly standard answers on why you should be looking memory and why more memory equals better performance. Generally speaking 4 GB or 10% reserved for OS.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.