Delphix Products

 View Only
  • 1.  Query performance on VDB vs Physical DB

    Posted 06-25-2023 11:31:00 AM


    is there any case study available on SQL Query performance between VDB vs its physical DB on full volume of data.



    Madhava Agumbe
    Delphix Arhictect

  • 2.  RE: Query performance on VDB vs Physical DB

    Posted 06-26-2023 12:32:00 PM

    Hi Madhav,

    I have reached out internally and we will get back to you with an answer here. 



    Michael Torok
    Digital Customer Experience, Senior Director

  • 3.  RE: Query performance on VDB vs Physical DB
    Best Answer

    Posted 06-26-2023 06:05:00 PM
    Edited by Gary Hallam 06-26-2023 06:29:35 PM

    Hi Madhav,

    This is an interesting discussion topic and in essence my experience has been that the performance of the virtual database (where the Delphix system infrastructure has been sized correctly) is largely down to the resources assigned to the database server itself.  Back in 2015 we did a fairly well controlled side-by-side comparison of an Oracle physical database and a VDB database running on the same infrastructure and achieved the same throughput performance of 120 transactions per second with a redo rate of 6.2MB/sec - this was achieved over a 3 hour replay using Oracle's Real Application Test (RAT) tool.  This was on an EMC VMax storage and I rather suspect that we'd likely get better VDB performance than physical in a similar side-by-side test today.

    My personal experience has been that any performance characteristics are dependent upon the end-to-end system architecture and the nature of the database transactions.  Due to the caching layer in the Delphix engine we tend to see that batch, read type transaction perform better on a VDB compared to the physical counterpart, provided that the end-to-end architecture has been sized appropriately and meets the network and IOPs performance characteristics specified in the pre-requisites.

    Where there is any significant discrepancy in performance from physical, this is usually resolved by an investigation into the end-to-end system, paying attention to the following points:

    • I/O from the database
    • NFS client from the database host
    • NFS server on Delphix
    • Backend I/O from Delphix

    Our support engineers are usually very good at identifying if there are any issues in the end-to-end architecture.  One of the key factors to pay attention to is the network latency and throughput, ensuring that the network is sized according to the maximum throughput required based on the expected concurrent activity on the virtual databases.

    Taking a more anecdotal approach I have seen side-by-side comparisons of batch transactions run against Oracle databases and the results tend to be variable dependent upon the specific transactions.

    Here's just one example of performance for a specific application running against a 2.5TB Oracle database - in this case the storage for the physical database was not the same as the virtual database, in any case you can see that some transactions were faster and some slower, with an overall performance showing the VDB to be 9% slower, on average.

    VDB Physical Difference Percent Difference
    00:08:52 00:08:13 00:00:39 8%
    00:00:49 00:00:43 00:00:06 14%
    00:23:16 00:25:27 -00:02:11 -9%
    00:03:18 00:03:54 -00:00:36 -15%
    00:12:52 00:08:46 00:04:06 47%
    00:05:37 00:05:13 00:00:24 8%
    00:00:08 00:00:08 00:00:00 0%
    00:00:11 00:00:07 00:00:04 57%
    00:00:08 00:00:11 -00:00:03 -27%
        Average: 9%

    More recently I've seen a similar type of transactions running against a SAP ASE database where the VDB was seen to show faster application transaction speeds in 75% of cases and the same or slower for 25% of cases, giving an overall performance for the batch transaction run that matched the physical database.

    Performance of the VDB is a factor of the whole end-to-end environment and the types of activity on the database and overall I would say that VDB performance is comparable with physical and can be faster - you should test in-place to see if it meets your requirements.

    Certainly I can say is that the provisioning speed is phenomenal compared to physical and last week I saw a 20TB Oracle database delivered in 1 minute on a newly installed engine.  I can't wait to test the throughput speed on that database.

    Please feel free to update us if you manage to do a side-by-side comparison and make sure you get support's help if there is a significant difference in performance.  Also make sure you do two runs as the VDB performance will likely improve as the cache warms up.



    Gary Hallam
    Director, Channel Business EMEA, APAC