39

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?
Leigh Riffel
  • 23,534
  • 16
  • 75
  • 147
Jeff Atwood
  • 2,314
  • 2
  • 21
  • 15

4 Answers4

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.

Leigh Riffel
  • 23,534
  • 16
  • 75
  • 147
mrdenny
  • 26,776
  • 2
  • 40
  • 79
  • 2
    Concur on 2 ish gb of RAM for the OS. – jcolebrand Jan 13 '11 at 04: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 04: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 08: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: http://www.sql-server-performance.com/articles/audit/hardware_bottlenecks_p1.aspx and http://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: http://blogs.msdn.com/b/ntdebugging/archive/2007/11/27/too-much-cache.aspx – Brent Ozar Jan 13 '11 at 16:07
  • 2-ish GB would also apply to Oracle on Windows. – Leigh Riffel Jan 13 '11 at 19:04
  • I concur with 2 ish GB of RAM. I also found this SQL Server memory configuration article useful http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/ – Julius A Jan 14 '11 at 15:40
  • @Jeff When I'm looking at what's being starved, I see how much Windows is swapping. I typically configure SQL Server to use the lock pages in memory flag, so any swapping that the OS is doing is a bad sign. On non-Windows platforms, I trust my OS designers to know how to do memory allocation properly. It's worked well for me so far :) – Jeremiah Peschka Jan 15 '11 at 14:38
  • While this article specifically talks about the lock pages in memory setting, it also talks about the memory allocation for the operating system and I thought it was worth mentioning here: http://blogs.technet.com/b/askperf/archive/2008/03/25/lock-pages-in-memory-do-you-really-need-it.aspx – tcnolan Jan 16 '11 at 10:08
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.

Dan Grossman
  • 661
  • 1
  • 4
  • 8
  • +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 04: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
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.

Dan Grossman
  • 661
  • 1
  • 4
  • 8
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.

Troy
  • 390
  • 3
  • 7