40

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?
Improve this question
2
  • did you guys ever tweak these settings? What did your profiling tell you?
    – jcolebrand ♦
    Commented May 28, 2011 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
    Commented May 28, 2011 at 4:49

4 Answers 4

Reset to default
30

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.

Improve this answer
9
  • 2
    Concur on 2 ish gb of RAM for the OS.
    – jcolebrand ♦
    Commented Jan 13, 2011 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
    Commented Jan 13, 2011 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
    Commented Jan 13, 2011 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
    Commented Jan 13, 2011 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
    Commented Jan 13, 2011 at 16:07
16

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.

Improve this answer
3
  • +1 for profile profile profile ~ however being it's Jeff askin I presume it's SQLServer and Windows for sure ;)
    – jcolebrand ♦
    Commented Jan 13, 2011 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
    Commented Jan 13, 2011 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
    Commented Jan 17, 2011 at 12:42
8

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.

Improve this answer
5

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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