Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 8002919615C1 for ; Mon, 10 Oct 2016 08:19:46 +0200 (CEST) Received: from turing.freelists.org (turing.freelists.org [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTPS for ; Mon, 10 Oct 2016 08:19:46 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F28AD30C2F; Mon, 10 Oct 2016 02:19:44 -0400 (EDT) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id i6Xx0Ps8gzMx; Mon, 10 Oct 2016 02:19:44 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E8F20308E5; Mon, 10 Oct 2016 02:19:31 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 10 Oct 2016 02:18:10 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F02562EB12 for ; Mon, 10 Oct 2016 02:18:09 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id F_gfrjoQEfmx for ; Mon, 10 Oct 2016 02:18:09 -0400 (EDT) Received: from mail-ua0-f177.google.com (mail-ua0-f177.google.com [209.85.217.177]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id B5EFA2EB0E for ; Mon, 10 Oct 2016 02:18:09 -0400 (EDT) Received: by mail-ua0-f177.google.com with SMTP id r64so80314971uar.3 for ; Sun, 09 Oct 2016 23:18:09 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=Az8m0Xageh38XJq8QW64LwzYKgSJ7/sSgTYJAS7ULLI=; b=m5ASVA1S7YHcnESHlkmtlO0xyymsAN6/pzJiPOO28vTQSIIjvP+Ym6f6rrV411sBjE A8ezxFaiPiLTkZz9KW+31e3bnD4YlW9Q496rwxWu45JQoGSHKwcd5tWLGoIwu81XI4Mc qazIcEHLVOBb3uU0BDk2mTPgccxcHwyAJQcw4sJkXCJzEwIFKBfu9wR3t19AYbikix2b /SlSVkGoBTIJhzWbSniWxRnC2zSJFqLcTB9nmyou5Lkvkv0hVp00K4wAKddEyF/MMhl3 45gKNVQTzBHASB/FHrsNAV+Kio9N2atbt+JkQF1/MuD9eSnoIBJ/D+BKpkjEXDvoNqzZ hE1g== X-Gm-Message-State: AA6/9RnO7bS212l8VeXAvP1esqA1zCqVMRTLUNv89qDGcSCQFNfqinbqICRNLEnjBdAopqJlujGFdxeUiOBxoA== X-Received: by 10.159.34.53 with SMTP id 50mr21071620uad.53.1476080288975; Sun, 09 Oct 2016 23:18:08 -0700 (PDT) MIME-Version: 1.0 Received: by 10.103.138.9 with HTTP; Sun, 9 Oct 2016 23:17:48 -0700 (PDT) In-Reply-To: References: From: Karl Arao Date: Mon, 10 Oct 2016 02:17:48 -0400 Message-ID: Subject: Re: Sizing SGA To: Niall Litchfield Cc: ORACLE-L Content-Type: multipart/alternative; boundary=001a113e03c048cf94053e7cb989 X-archive-position: 66438 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: karlarao@gmail.com Precedence: normal Reply-To: karlarao@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --001a113e03c048cf94053e7cb989 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Question: A colleague asked me a question yesterday to which I don=E2=80=99t really h= ave a good answer; so I thought I=E2=80=99d crowdsource it. Given a RAC database of N instances each with an SGA of M gb in size. When changing the instance count N how, if at all, do you modify the value of M? What metrics do you look at, and what is the rationale behind that. I=E2=80=99m aware that =E2=80=9Clet it run for a while and use the memory a= dvisors=E2=80=9D is an approach - I can=E2=80=99t say I have a lot of confidence in the memory adv= isors from past experience. Answer: ------------------------------ I agree on measuring the baseline -> make the change -> measure again. And ideally this should be done first on a test/pre-prod environment. And for this I would compare the overall workload (time series viz), do AWR compare periods (memory advisory/wait events - memory issues could show up in wait events), and focus on specific driver SQLs to measure the effect of the change. In my opinion the *initial sizing* of the SGA should be determined on the performance tests or benchmarking. Here the iterations of the workload run is done, the changes, the tuning, node failure scenario, and workload characterization. And with all that info SGA can be sized accordingly. For *node failure* (reduce nodes), each SGA of nodes can be sized slightly bigger the reasoning behind this is when a node fails the surviving node should be able to take over the Memory resource needs of the failed node. Again, the optimal size can be determined by performance tests or benchmarking. Other things to consider: *Hugepages*: When the instances are reduced/increased or moved around you need to account for the huge pages settings. It=E2=80=99s possible that whe= n these changes are done the huge pages settings could end up undersized or oversized which leads to swapping(kswapd)/node evictions/node hang. *PGA*: Similar to the CPU load and SGA. The surviving nodes should also be able to accommodate the PGA requirements of the failing nodes. The node failure/reduce scenarios can be modeled with the SizingWorksheet https://github.com/karlarao/provisioning_worksheet. See the examples folder "SizingSGA.xlsm" - Let=E2=80=99s say we have a database on a X5-2 half rack with: - 36 CPUs requirement (50% x 72CPUs) spread out across the four nodes - 13% utilization on each node ((50% x 72CPUs)/4)/72 - 20GB PGA and 20GB SGA - 16% memory utilization on each node 40GB/256GB - The 22G HPages is calculated from the SGA with 10% allowance. Just a reminder that this X node needs this much huge pages - If let=E2=80=99s say two of the nodes had a server issue and crashed the remaining nodes will increase on CPU and memory utilization - For the CPU, you have less nodes servicing the same CPU requirement - it=E2=80=99s basically (36 CPUs divide by 2 remaining nodes) / 7= 2 CPU node capacity =3D 25% CPU utilization for each remaining host - (36/2)/72 =3D 25% - For the memory, the PGA from the failed nodes will be spread on the surviving nodes - for node 3 and 4 that=E2=80=99s ((existing 40GB SGA and PGA) + 2= 0GB PGA of node1 or node2)/256GB memory node capacity =3D 23% memory utilization for each remaining host - (40 + 20)/256 =3D 23% Hope this helps -Karl =E2=80=8B On Thu, Oct 6, 2016 at 9:51 AM, Niall Litchfield wrote: > A colleague asked me a question yesterday to which I don't really have a > good answer; so I thought I'd crowdsource it. > > Given a RAC database of N instances each with an SGA of M gb in size. Whe= n > changing the instance count N how, if at all, do you modify the value of = M? > What metrics do you look at, and what is the rationale behind that. > > I'm aware that "let it run for a while and use the memory advisors" is an > approach - I can't say I have a lot of confidence in the memory advisors > from past experience. > > -- > Niall Litchfield > Oracle DBA > http://www.orawin.info > --=20 Karl Arao Blog: karlarao.wordpress.com Wiki: karlarao.tiddlyspot.com Twitter: @karlarao --001a113e03c048cf94053e7cb989 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Question:

A colleague asked me a question yesterday= to which I don=E2=80=99t really have a good answer; so I thought I=E2=80= =99d crowdsource it.

Given a RAC database of N instances each = with an SGA of M gb in size. When changing the instance count N how, if at = all, do you modify the value of M? What metrics do you look at, and what is= the rationale behind that.

I=E2=80=99m aware that =E2=80=9Clet it ru= n for a while and use the memory advisors=E2=80=9D is an approach - I can= =E2=80=99t say I have a lot of confidence in the memory advisors from past = experience.

Answer:

I agree on measuring the baseline -> = make the change -> measure again. And ideally this should be done first = on a test/pre-prod environment. And for this I would compare the overall wo= rkload (time series viz), do AWR compare periods (memory advisory/wait even= ts - memory issues could show up in wait events), and focus on specific dri= ver SQLs to measure the effect of the change.

In my opinion the initial sizing= of the SGA should be determined on the performance tests or bench= marking. Here the iterations of the workload run is done, the changes, the = tuning, node failure scenario, and workload characterization. And with all = that info SGA can be sized accordingly.

For node failure (reduc= e nodes), each SGA of nodes can be sized slightly bigger the reasoning behi= nd this is when a node fails the surviving node should be able to take over= the Memory resource needs of the failed node. Again, the optimal size can = be determined by performance tests or benchmarking.

Other things to consider:

Hugepages: When the ins= tances are reduced/increased or moved around you need to account for the hu= ge pages settings. It=E2=80=99s possible that when these changes are done t= he huge pages settings could end up undersized or oversized which leads to = swapping(kswapd)/node evictions/node hang.

PGA: Similar to the CPU= load and SGA. The surviving nodes should also be able to accommodate the P= GA requirements of the failing nodes.

The node failure/reduce scenarios can be= modeled with the SizingWorksheet https://github.com/karlarao/provisioning_worksheet. See the examples folder "SizingSGA.xlsm"

  • Let=E2=80=99s = say we have a database on a X5-2 half rack with:

    • 36 CPUs requirement (50% x 72CPUs) spread ou= t across the four nodes
      • 13% utilization on each node ((50% x 72CPUs)= /4)/72
    • 20GB PGA and 20GB SGA
      • 16% memory utilization on each node 40GB/256= GB
      • The 22G HPages is calculated from the SGA wi= th 10% allowance. Just a reminder that this X node needs this much huge pag= es

  • If let=E2=80= =99s say two of the nodes had a server issue and crashed the remaining node= s will increase on CPU and memory utilization

    • For the CPU, you have less nodes servicing t= he same CPU requirement
      • it=E2=80=99s basically (36 CPUs divide by 2 = remaining nodes) / 72 CPU node capacity =3D 25% CPU utilization for each re= maining host
      • (36/2)/72 =3D 25%
    • For the memory, the PGA from the failed node= s will be spread on the surviving nodes
      • for node 3 and 4 that=E2=80=99s ((existing 4= 0GB SGA and PGA) + 20GB PGA of node1 or node2)/256GB memory node capacity = =3D 23% memory utilization for each remaining host
      • (40 + 20)/256 =3D 23%

    Hope this helps

-Karl

=E2=80=8B

On Thu, Oct 6, 2016 at 9:51 AM, Niall Litchfield <niall.litchfield@gmail.com> wrote:
A colleague asked me a question yesterday = to which I don't really have a good answer; so I thought I'd crowds= ource it.=C2=A0

Given a RAC database of N instances each= with an SGA of M gb in size. When changing the instance count N how, if at= all, do you modify the value of M? What metrics do you look at, and what i= s the rationale behind that.

I'm aware that &q= uot;let it run for a while and use the memory advisors" is an approach= - I can't say I have a lot of confidence in the memory advisors from p= ast experience. =C2=A0=C2=A0

--
Niall Litchfield
Oracle DBA=
http://www.orawin.= info



--
Karl Arao
Blog:= =C2=A0karlarao.= wordpress.com
Wiki:=C2=A0karlarao.tiddlyspot.com
Twitter:=C2=A0@karlarao
--001a113e03c048cf94053e7cb989-- -- http://www.freelists.org/webpage/oracle-l