{{ vExpose }}.Blog

Tag: icdm

  • Best Practices For Running Oracle Database On XtremIO X2

    Summary

    DEllEMC’s XtremIO has simple, easy-to-use management. The XtremIO Management Server (XMS) delivers an HTML5 user interface that is a simple and easy-to-use interface for storage administrators. XMS allows storage administrators the ability to provisions storage with very little setup and planning.

    Hope this post is helpful to all Oracle users trying to get best out of their XtremIO investments.

    XtremIO is designed and optimized for databases and for DBAs, providing the following benefits.

    Predictable Performance

    • XtremIO provides predictable and consistency low-latency performance
    • With XtremIO scale-up and scale-out architecture, year-to-year growth is easy. The initial investment is preserved and application performance is improved.
    • Performance is predictable and provides best response times regardless of the workload and environment – be it production, QA, test or development

    Incredible Simplicity

    Agility

    • The typical enterprise applications require multiple copies such as test/development, reporting or online analytics. DBAs and test/dev engineers often have to spend hours managing the DB creation and refreshing the environments while often being limited by capacity, performance, and the number of copies.
    • XtremIO’s Integrated Copy Data Management (iCDM) allows for instant XtremIO Virtual Copies (XVCs) to be created from production with no performance impact.
    • These copies can be repurposed for near real-time analytics, test/dev and any other use case- all with complete space efficiency.

    Protection

    • Protecting the database is easy with XtremIO
    • There is no need for any design covering RAID type, data file capacity, load balancing, and tuning.
    • The data is protected with a proprietary flash-optimized algorithm called XtremIO Data Protection (XDP).
    • XDP is very different from RAID in several ways. Since XDP is always working within an all-flash storage array, several criteria were important in the design of this protection scheme. XDP benefits include ultra-low capacity overhead, high levels of data protection in case of double SSD failure, rapid rebuild times, flash endurance, and of course extreme performance
    • With XtremIO virtual copies it is easy to protect and recover from any operational and logical corruption; XVC’s allow the creation of frequent point-in-time copies (according to RPO intervals – seconds, minutes, hours) and use them to recover from any data corruption
    • An XVC can be kept in the system for as long as needed. Recovery using XtremIO virtual copy is instantaneous and does not impact system performance.

    ORACLE BEST PRACTICES

    1. General Guidelines

    Irrespective of using any application/database with XtremIO storage array below listed general guidelines are common.

    • Keep consistent, duplex link speed on all paths between the host and the XtremIO cluster
    • To ensure continuous access to XtremIO storage during a cluster software upgrade, verify that minimum I/O timeout of 30 seconds is set on the HBAs of all hosts connected to the affected XtremIO cluster. Also, verify that a minimum timeout of 30 seconds is set for all applications that are using storage from the XtremIO cluster.
    • The HBA queue depth (also referred to as execution throttle) controls the amount of outstanding I/O requests per HBA port. The HBA queue depth should be set to the maximum value
    • The LUN queue depth controls the amount of outstanding I/O requests per single path. These settings are controlled in the driver module for the card at the OS level. When connecting Linux host to XtremIO, a LUN queue depth setting should retain its default values.
    • I/O scheduling controls how I/O operations are submitted to storage. Linux offers various I/O algorithms (also known as “I/O Elevators“) to accommodate the different workloads. When connecting a Linux host to XtremIO storage, set the I/O elevator to either noop or deadline. It is not recommended to use the cfq I/O elevator setting, as it’s less optimal for XtremIO storage.
    • It is HIGHLY RECOMMENDED to follow the latest “XtremIO Host Configuration Guide” for all operating system which will use storage from XtremIO
    • If DB is virtualized then make sure all Virtual Machine components are provisioned from XtremIO storage (OS, Application/Binary, Swap, Paging, etc.)

    2. Oracle ASM

    Oracle Automatic Storage Management (ASM) is Oracle’s recommended software for supporting Oracle database files.

    For more information on ASM please refer this link

    • ASM General Recommendation
      • External redundancy is recommended for XtremIO
      • The XtremIO Storage Array natively provides flash-optimized data protection
    • Database Files Location in ASM Disk Groups
      • Best practices for storing Oracle DBMS file types in ASM disk groups are mentioned as below.
    Oracle Database Files Location in ASM disk groups

    Note: The second REDO data group (DG) is applicable if REDO logs are multiplexed.

    • Number of LUNs per Disk Group
      • Excellent cluster performance is achieved using an XtremIO Storage Array with just single LUN in a single disk group. However, in order to maximize performance from a single host, parallelism and adequate utilization of device queues are required.
      • The best practice to achieve this is by using a minimum of four LUNs for the data disk group. Doing this enables the hosts to use parallelism and ensures optimal performance without any bottlenecks.
      • The best practices for Disk group configuration and data placement are outlined in the below table.
    Number of LUNs per disk group
    •  512 verses 4K Advanced Format Considerations
      • The default setting for XtremIO volumes is 512 bytes. It is recommended to keep the default setting and not use 4K Advanced Format.

    3. Multiblock I/O Request Sizes

    Oracle Database performs I/O on data files in multiples of the database block size (db_block_size), which by default is 8KB. The default Oracle Database block size is optimal on XtremIO. XtremIO supports larger block sizes as well. In the case of multiblock I/O one should tune the Oracle Database initialization parameter db_file_multiblock_read_count to limit the requests to 128KB. This is derived with the following formula:

    db_file_multiblock_read_count is db_file_multiblock_read_count = 128KB / db_block_size

    Usually, Oracle DB is optimized to perform very large transfers to mitigate the seek cost due to multiblock reads on mechanical/spinning drives. In a seek-free storage environment, such as XtremIO, there is no need for such mitigation. Also, most modern FC HBAs require OS to segment large requests into multiple requests. For example, an application IO of 1MB is fragmented by Linux block I/O layer into two 512KB transfers to suit HBA max transfer size.

    4. REDO Log Block Size

    The default block size for REDO LOG is 512 bytes. I/O requests sent to the redo log files are in increments of the redo block size. This is the blocking factor Oracle uses within REDO LOG files and has nothing to do with the on-disk format of the XtremIO LUN.

    XtremIO’s recommendation is to create REDO LOG files with 4K block size. For more details on this please check Oracle Support notes 1681266.1.

    Note: For Oracle version prior to 12.2.0.1.0, you should set the parameter _disk_sector_size_override to TRUE when creating a redo log with 4K block size in the database instance.

    Do not set the parameter _disk_sector_size_override in the ASM instance. Once the instance is running, simply add more redo logs with the BLOCKSIZE option set to 4KB and then drop any redo logs that have default 512B block size.

    5. Grid Infrastructure Files – OCR/Voting

    The block size for both Oracle Cluster Registry (OCR) and Cluster Synchronization Services (CSS) voting files are 512 bytes, hence I/O is therefore sized as multiple of 512 bytes. This is consistent with XtremIO’s best practices and hence no changes are needed in OCR/Voting files.

    6. Oracle DB Level Compression & Encryption

    XtremIO has inline data reduction (deduplication and compression) running all the time without the performance penalty and stores 100% of data in encrypted format using Data At Rest Encryption (D@RE). This makes Oracle DB level compression and encryption as redundant.

    XtremIO data services work for all the data stored on arrays, unlike being selective like Oracle. Also XtremIO no not use host CPU for data services, which is an expensive resource in the Oracle environment.

    With XtremIO it is a best practice to disable / not use application-level Compression and Encryption services.

    XtremIO Advanced Services for Oracle

    XtremIO offers a wide variety of use cases to simplify database environment workflow. XtremIO comes along with a tool called iCDM (Integrated Copy Data Management) which allows automating all Copy Data Management (CDM) tasks in DB environments.

    Few use cases are mentioned below.

    1. Modern Backup-to-Disk using XtremIO XVC

    XtremIO XVCs (XtremIO Virtual Copy) are precise point-in-time copies of source volumes which basically are a collection of metadata pointers to the source volume blocks. Therefore, XVC consumes minimal physical capacity.

    Executing XVCs is extremely fast and hence most efficient backup-to-disk methodology. The best part is this process does not utilize production/source server resources for creating backup-to-disk copy. Over time as source data is updated/changed, only unique data is stored on XtremIO in compressed format.

    2. XtremIO XVC for Manual Continuous Data Protection (CDP)

    XtremIO XVCs are so efficient that these can be used as part of a business continuity strategy. Below mentioned two options can be used for this.

    • Crash-Consistent (or “Restart-able” Image)
      • A crash-consistent or restartable image is a point-in-time image of the primary database on disk
      • This option involves taking XVC of the primary database while it is up and operational. The image that is captured is similar to the state of the primary database.
      • During the DB restart on the XVC, the DB automatically performs a recovery using the online logs.
      • All committed transactions are included and all uncommitted transactions are rolled back.
    • Application/DB-Consistent (“Recoverable” Image) –
      • A recoverable image is a point-in-time image of the primary database on disk
      • This option involves taking XVC of the primary DB while DB is in “Backup” mode
      • Highly recommended to have a backup file of the control file prior and after the completion of the XVC process.Unlike crash-consistent copy, data files can be rolled forward in time using logs – up to latest or desired SCN (captured in the control file)

    3. XtremIO XVC for Cloning Primary Database (BCV Copy)

    BCV / clones of the primary copies can be created using XtremIO XVCs. Methods for clone creation are as mentioned in “2. XtremIO XVC for Manual Continuous Data Protection (CDP)”

    operating System Best Practices

    When it comes to performance optimization operating system settings also play a major role. It is highly recommended to follow all the operating system best practices as listed in the “XtremIO Host Configuration Guide”.

    The absence of host best practices might result in host-bound performance than XtremIO bound performance. In short, the host/operating system might become the bottleneck, because of the host/operating system level queuing.

  • Microsoft SQL DWH using DellEMC iCDM

    Recently I got engaged with one of our customer who was trying to implement data warehouse using the Microsoft SQL 2017. But they were not able to implement the same into the production environment even after successful POC and testing. When we got engaged with customer DBA teams we used DellEMC’s iCDM functionality (with XtremIO X2 system) in some unique way to help them not just solve their problem but exceeding business SLA expectations. This blog covers the details around problem and solution along with learning.

    Problem Statement

    Customer has several mission-critical MS SQL 2016 databases which are running in 24×7 operation. These databases are supporting their business critical OLTP application.

    Customer’s planned DWH approach

    Business teams were pushing application and DBA teams to create singular reporting platform which will create reporting on data from multiple MSSQL source databases.

    For this project DBA teams built MS SQL 2017 platform which will consolidate data from 3 source MS SQL databases. They created the test setup for feature testing before production roll-out, which worked as per their expectations. But when they implemented this feature in production DBA team realized that this is generating massive load on their source databases and causing performance issues for their business applications, which was completely unacceptable to business teams.

    This forced DBA teams to stop fetching data from the network and restore the data from last backup instead. This was used as workaround because this meant that data in reporting server was more than 30 hours old, which was making reporting irrelevant.

    DellEMC Solution

    When DellEMC team got involved we started with collecting DB environment data collection and business expectation understanding. Once we understood the pain points and business requirement we mentioned to DBAs that traditional way of data management techniques won’t help them. We also mentioned that since they are exiting DellEMC customer they already have the solution with them, which they never implemented.

    Every DellEMC All Flash storage solutions includes bundled copy data management software called Appsync.

    On high level AppSync allows application and database administrators to create application workflows. These workflows allows them to create on demand or scheduled protection or re-purpose copies of databases. We also mentioned that using this software you can create your copies of source MS SQL 2016 DB copies and mount it to target MS SQL 2017 DB server, scheduled or on-demand. These workflows are based on DellEMC storage in back-end and hence can be executed during even the busiest times.

    As usual customer DBA teams didn’t believe us and that’s where it comes to next section of this blog – Demonstration! 🙂

    Demonstration of the Appsync

    To demonstrate the functionality of DellEMC iCDM we installed the Appsync server on of the Windows server VM in customer environment. Below is the high level architecture

    DellEMC’s proposed solution approach for DWH project

    Once Appsync was installed we configured the same to communicate with source and target SQL servers and also DellEMC XtremIO storage. This allows Appsync to discover running databases and create the end to end database mapping.

    Post initial configuration we created the SQL re-purpose copy schedule to create the MS SQL database copy every 6 hours. This schedule was then applied to all the 3 source databases.

    Once the copy of multiple source database was created we used Appsync to mount the same into target MSSQL 2017 server. In our testing we mounted 3 source copies on single target MSSQL 2017 instance. Entire mounting operation completed within couple of minutes. This process allowed customer to save multiple days and help achieve business SLA.

    Apart from solving their most critical problem in hand this solution helped them solve couple of major production issues

    • Database RPO – We reduced production DB RPO to 2 hours instead of 30+ hours
    • Agent-free backup – iCDM helped customer to mount DB production copy on off-host backup server. This helped them completely eliminate backup load from production server.

    If you’re facing similar challenges in your DB environment then highly recommended to use iCDM approach instead of traditional methods.

    I have created another blog post to elaborate more on iCDM and advantages of the same. You can go through the same using below link.

    Databases and Integrated Copy Data Management (iCDM) with DellEMC

    Below are few reference documents for further reading on iCDM

    Dell EMC AppSync Datasheet

    Best Practices for running SQL Server on DellEMC XtremIO X2

  • Databases and Integrated Copy Data Management (iCDM) with DellEMC

    Databases and Integrated Copy Data Management (iCDM) with DellEMC

    In this blog we will talk about how DellEMC can help you to store you databases smartly and efficiently.

    But firstly let’s talks about CDM and why it’s important. In the world of DBAs everyone’s always talking about performance, million IOPS and double/triple digit bandwidth with lowest possible latency. But the biggest question is whether performance is the only solution to all problems?

    For example in one of the survey members of Independent Oracle User Group (IOUG) were asked what DB activities are taking up most of the time each week?

    You can also see that performance troubleshooting is rated as second most time consuming activity, but the question is how to solve it. One of the ways people try to solve this is by upgrading their database stack. In below AWR example snippets you can see that how one of the customer has solved their performance issue by moving to DellEMC solution. Earlier they were having 92% User I/O wait time, which also means 92% of the time DB is waiting for database subsystem to respond.

    But as I have mentioned earlier upgrading the stack will always give the benefits but it does not solve all the problems. Which also means most customer apply their older process and best practices to new stack which often is the culprit of the new problems. (another blog on this later)

    But the real problem is what do you do with those databases and data? Most businesses today are trying to keep more data online. This allows them to understand the patterns and anomalies using AI/ML, which helps them taking right decisions or course correction. All this is driven using copy data, which means you need actual source production database instances to analyze, test, develop etc. Below picture is the great depiction of multiple use cases which needs source database.

    Now think of all those use cases and start thinking of the approach DBAs will take to manage the show. Usually there’s no standard way of managing this. Processes to achieve the end goal depends on the experience of the DBA and also availability of the resources.

    IDC has done some work on this problem area to understand the gravity of the situation and uncover some facts. IDC polled over 700 customers for this survey. Some of the interesting facts are –

    Tools used for producing secondary data copies

    As per the IDC survey most popular way is to rely on backup software but interestingly 53% of the respondents said that they use custom-written scripts to manage the CDM show.

    How long does it take to refresh copies?

    To which 62% of organizations, copies take ½ day or more to create (up to days). And this is very real as I have personally worked with one of my customer who used to take 2 ½ days to create single secondary copy starting weekend, on which 60 member developer team used to work.

    At which frequency are copies of dev/test or analytics refreshed?

    Copies are refreshed every few days by 32% of organizations, and weekly by 42%.

    One of the most striking detail in this IDC document is – How many Database instances do you currently have in production in your data center environment? 77% customers said that they have more that 200 database instances and 82% said that they have more than 10 copies per database.

    In simplified words this problem looks like the iceberg. People only count their production/primary database instances but secondary/re-purpose copies are usually taken for granted. If you do the quick math on IDC numbers you’ll quickly realize that this is one of the biggest operational issue in database environments.

    For Example –

    1 Production DB x 250GB DB size x 346 DB Instances = 87TB of Production data

    14 Copies of each Production DB x 250GB DB size x 346 DB Instances = 1.2PB of Copy Sprawl

    Now that we understand what copy data management issue is. Now let’s understand how DellEMC can help in simplifying the CDM problem

    Integrated Copy Data Management (iCDM)

    As per my personal experience below snippet is the best way to understand how iCDM functions. Providing self service to application/database teams with application integration. iCDM leverages advanced copy/snapshot function of the DellEMC storage system, which also means you need DellEMC storage in order to use iCDM 🙂

    To simplify things even further – in most environments you follow 3 steps process for database copy creation.

    1. Identify source database
    2. Prepare database
    3. Prepare instance

    Details for these 3 steps are as below but on high level it lacks automation, it’s (very) time consuming and most importantly it lacks automation.

    But with iCDM you can change this legacy process and adapt to something which is more modern and fits with today’s cloud native world. You can simply

    1. Install and Configure DellEMC Appsync (one time)
    2. Create and Customize workflows as per your environment
    3. Consume (repeat and scale)

    Conclusion

    DellEMC iCDM can be sumarised as

    1. Simple – Automated Copy Data Management without the need of customized scripts
    2. Intelligent – Tight integration with entire application stack – applications –> hosts –> virtualization –> DellEMC Storage with Replication
    3. Friction-less – Database and IT team are on the same page with transparent copy workflow.

    Below are the few links to the DellEMC iCDM collateral.

    Dell EMC’s integrated copy data management (iCDM) webinar

    DellEMC Appsync Datasheet

    DellEMC Appsync Software Download Link

    This blog is part of multi-blog series. Next couple of blogs I will try to explain usage of iCDM for multiple database CDM use cases.