Re: exadata write performance problems

Hi Gopal

Yes I understand we have too many write IOPS because this only happens when some massive DML kicks in, the problem is there are 10 databases running and any of them can cause users blocked by enq KO event (which is actually happening). So the problem is identified but to expand the cells from 1/8 rack to 1/4 rack takes a few months due to company's internal process so we have to mitigate in the mean time these write problems.

The Flash is being used as write-through cache so read performance is good, we only have problems with write performance so this happens probably 4, 5 times during the day (when some batch runs). The cells are x6-2 and running 18.1.5.0.0.

I also obseve something strange, a 128 KB table with 50 rows is being Smart Scanned in the cells when the buffer cache is 25GB. This also increase checkpoint activity as well.

Thank you

<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Wed, Feb 13, 2019 at 2:38 AM K Gopalakrishnan <kaygopal_at_gmail.com> wrote:

> Hi- Looks like you are oversubscribing IO (random IO) by 300%. KO-
> sometimes come from truncate which will induce a checkpoint (and
> controlfile update) on a already overloaded IO.
> One option is to enlarge buffer cache (thus reducing the disk IO) or
> probably try creating a DG out of flash and try high IO tablespaces (and
> undo as well)..
> In short- you are short on IOPS. Try to make use of FLASH cards from
> storage servers? BTW which version / model of cell servers are you using?
>
> -KG
>
> On Tue, Feb 12, 2019 at 3:17 PM Ls Cheng <exriscer_at_gmail.com> wrote:
>
>> Hi
>>
>> IHAC who has 1/8 Exadata x6-2 with High Capacity Disks is having heavy
>> performance problems whenever some massive DML operation kicks in, since
>> this is a 1/8 configuration the IOPS supporting write operation is not
>> high, roughly 1200 IOPS. I am seeing as high as 4000 Physical Writes Per
>> Sec in peak time. When this happens the user session starts suffering
>> because they are blocked by enq: KO - fast object checkpoint which is
>> blocked by "control file parallel write" by CKPT. So the idea is aliviate
>> CKPT. This is from hist ash
>>
>> INSTANCE_NUMBER SAMPLE_TIME EVENT
>> TIME_WAITED SESSION P1 P2 P3
>> --------------- --------------------------------
>> ------------------------------ ----------- ------- ---------- ----------
>> ----------
>> 2 12-FEB-19 12.11.24.540 AM control file parallel
>> write 1110465 WAITING 2 41 2
>> 2 12-FEB-19 12.16.34.754 AM Disk file Mirror Read
>> 1279827 WAITING 0 1 1
>> 1 12-FEB-19 12.16.44.012 AM control file parallel
>> write 1820977 WAITING 2 39 2
>> 2 12-FEB-19 12.20.34.927 AM control file parallel
>> write 1031042 WAITING 2 856 2
>> 1 12-FEB-19 12.21.14.256 AM control file parallel
>> write 1905266 WAITING 2 3 2
>> 2 12-FEB-19 12.21.14.977 AM control file parallel
>> write 1175924 WAITING 2 42 2
>> 1 12-FEB-19 12.21.54.301 AM control file parallel
>> write 2164743 WAITING 2 855 2
>> 2 12-FEB-19 12.22.35.036 AM control file parallel
>> write 1581684 WAITING 2 4 2
>> 1 12-FEB-19 12.23.44.381 AM control file parallel
>> write 1117994 WAITING 2 3 2
>> 1 12-FEB-19 12.23.54.404 AM control file parallel
>> write 4718841 WAITING 2 3 2
>>
>> Whe this happens we observe these cell metrics
>>
>> CELL METRICS SUMMARY
>>
>> Cell Total Flash Cache: IOPS=13712.233 Space allocated=6083152MB
>> == Flash Device ==
>> Cell Total Utilization: Small=27.8% Large=14.2%
>> Cell Total Throughput: MBPS=471.205
>> Cell Total Small I/Os: IOPS=9960
>> Cell Total Large I/Os: IOPS=6005
>>
>> == Hard Disk ==
>> Cell Total Utilization: Small=69.5% Large=18.7%
>> Cell Total Throughput: MBPS=161.05
>> Cell Total Small I/Os: IOPS=5413.618
>> Cell Total Large I/Os: IOPS=166.2
>> Cell Avg small read latency: 245.67 ms
>> Cell Avg small write latency: 62.64 ms
>> Cell Avg large read latency: 308.99 ms
>> Cell Avg large write latency: 24.65 ms
>>
>>
>> We cannot not enable write-back flash cache right now because that may
>> cause another problems and although we are in process to upgrade 1/8 cells
>> to 1/4 cells it is going to take some months. I know it is not a best
>> practice but I was thinking in the mean time scarve some flash space and
>> create them as grid disk and store the controlfiles in Flash. Anyone have
>> experience with such setup?
>>
>> TIA
>>
>>
>>
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

--
http://www.freelists.org/webpage/oracle-l