Ticket T569076
Visible to All Users

How I found out using Oids in clustered index in XAF is a very bad idea

created 7 years ago

Hi,

Few years back when I opted for XAF I knew very little about how it works so it was hard to predict all of the consequences some of the decisions might have.
One of them is opting for default BaseObject which is based on Oid (Guid) key and by default creates clustered index on the field.
Not to mention I had no idea how really clustered indices worked.

But years have passed, databases have grown and I learned few bits here and there, so here we are.

Recently we had horrible performance at one customer and spent weeks diagnosing it in all kinds of different aspects.
True, customer is using older generation Xeon running Express version of SQL Server and worst of all - still has mechanical disk drives.
But, largest tables barely passed million records and that is along way before hardware should become such bottleneck that certain action that should finish in couple of seconds took - 5 minutes to complete. And the worst part - not always, just sometimes - which just made it harder to diagnose.

And just couple of nights ago I dreamed about clustered indices (yea I know, I need a vacation :-)) and in the morning I questioned my entire reality and thought "How could a clustered index perform with random generated key on mechanical disks". Well, beyond horribly that's how.

Everything is great on smaller databases, but or other customer running Samsungs 850 Pro in RAID is also experiencing slowdowns, although not nearly as our first customer so it did not trigger any alarms.
This particular action does invoice booking which means writing data through several tables and one of them is over million record now.
Then I finally looked index fragmentation and I was appalled with numbers which were mostly in 99% range and on top of the list were all primary keys.

Finally it all made sense. Action triggers chain reaction of moving actual data (since clustered index is the table itself) through several large tables creating huge fragmentation in process. That is why the action took more than 5 minutes sometimes to complete (keep in mind this is action they execute hundreds of times a day when booking invoices for instance).

And sugar on the top of all was - Audit Log. All those actions are logged which at the time of writing created table of 2.5 million records. Which would be fine if each time a new log is created, SQL Server needs to insert a new record sorted by Oid - because of clustered index.
Then I noticed tons of indices on AuditDataItemPersistent which took more than 1GB of space and surely did not help with any CRUD operation in entire application.
I checked the source code and there really are [Indexed] attributes on several columns.

Because we can't go back at this point, we created identity columns in most offending tables and created clustered index over them while keeping primary key on Oids with non-clustered index with some magic-no-one-is-sure-how-exactly-works kind of script :-)
This is just lesser of two evils, but this evil at least allows customers to work.

I know there are benefits of choosing Guid as the key, but you should have done it differently, definitely not allowing clustered indices on random generated column forcing database engine to sort records by this random data creating huge fragmentation and moving data around.
Or - there should be some huge red flashing warning when creating project stating that if you are creating a project that might get larger in data not to choose BaseObject as base for all your classes.
Also, Oids for PK are beneficial in situations like replication - but how many customers actually use replication? My guess is no one with small projects - those who did not notice this issues. And everyone with big projects surely ran into this same wall and had to set things differently anyway and you helped them in no way with Oid columns.
So, why not just go with identity columns in the first place? Smaller project wont get hurt and larger projects will have to deal with specifics anyway, but wont have to deal with this huge issue of having database killing itself because every table is subject to enormous fragmentation and data moving (which BTW is really really bad for SSDs).

And you most definitely should not have use BaseObject on AuditDataItemPersistent, AuditedObject and XPWeakReference - which are MOST write heavy tables in entire system causing tremendous slowdowns on anything but super high-end hardware.
Also, since they are - heavy write - rarely read - kind of tables - you definitely need to remove all those indices and let us choose if we want them and how we want them.

There, I shared my last few weeks of experience which lead to dreaming clustered indices - so go figure :-)

Regards,
Mario

Show previous comments (26)
Dennis Garavsky (DevExpress) 5 years ago

    Thank you, Chris.

    >>I think the database itself is about 400-450GB
    WOW!

    CR CR
    Chris Royle (LOBS) 5 years ago

      :D it's actually a little larger than that… our customers never want to delete/purge anything. So, we have monthly batch jobs which copy out older audit data to a separate (less critical) database - which adds another 210Gb. When the user wants to view audit information for an instance this is fetched across two DB connections. We have 170m + 356m rows in backed up audit and non backed audit tables respectively.

      When we have some spare time we'll be coming up with some strategies to reduce the content of these - not all data needs auditing for the same amount of time.

      Dennis Garavsky (DevExpress) 5 years ago

        >>copy out older audit data to a separate (less critical) database
        Cool, we too.

        Answers approved by DevExpress Support

        created 7 years ago (modified 6 years ago)

        Hello Mario,

        I've created a separate ticket to help you with removing standard AuditTrail data models: Problems with using custom data models for the AuditTrail module. Please bear with me.

        >>Your architectural decisions open up very likely potential to cause extremely painful and very costly experiences for having to deal with core database issue in live, production databases.
        >>This decision is a time bomb in any application that will accumulate data over years and has daily data input and you really need to fix this.
        Now I think we have come to the stage that I had to elaborate more on our architectural decisions and explain why we will NOT implement this either in XAF or XPO, at least by default.
        In the very beginning, I also want to separate responsibilities and actions each side may take, bearing in mind that XAF and XPO are DevExpress products (we are responsible for them, their users and our decisions, not third-parties) and that your project is your project and your responsibility. Even though this separation may look formal at first, I believe it is important for this discussion to continue in the positive and constructive manner. From our side, we always try to provide as much information on our tools and decisions (in our competence) as possible so that you could take your OWN business decisions, because your success is also very important for us.

        First, I cannot agree that this is a problem of ANY application. We have thousands of customers and according to our stats, this problem was brought to us only a few times in more than a decade of the XAF and XPO existence (though it is not the main argument, I wanted to present it). Of course, as developers, we are also running our business using our own tools, dog-fooding them as much as possible. For instance, for over a decade we prefer to use XPO as the main ORM over EF for our internal support and client centers. Our support center database contains more than a million of tickets and related data, and weights about 40GB now. The related database with audit data weights 75GB now. There are more than 6 million audit records that mimic the ticket, post and related data models.

        In all data models we are using the XpoDefault.NewGuid method that does NOT use any sequential GUIDs (GuidGenerationMode = FrameworkDefailt).

        Are these apps and databases small? - No.
            Are hundreds of tickets created, thousands of user actions logged and thousands of 'select's executed every day? - Yes.
            Do we have performance problems with the default XPO and SQL Server behavior without sequential GUIDs? - No. From our experience, we have never had such problems so far.
            Should our Support Center developers, you or anyone else working with millions of records do anything else to achieve this? - Yes. For instance, our DBAs analyzed SQL queries and created indices based on a few columns in the auditing database table. They also run our      SQL Server on SSDs and even store MDF and LDF files on separate physical disks.
            Do our DBAs do other magic with tables? - Yes, they certainly do, because it is their job.
            Is this DBA magic related to ORM in any way? - No, it is not. ORM may not even create a database table schema by default, because it may come ready from a DBA, as they want it.
            Can you customize the database table schema initially created by ORM? - Yes, you can do this as your business needs dictate, either by creating a custom XPO connection provider for your database engine, using built-in XPO attributes and other APIs, or even manually using scripts.
            Is creating apps that operate millions of records a hard task? - Yes, it certainly is. Unfortunately, generic solutions (e.g., frameworks like XAF or XPO with their default values) stop working here and you often need to look for specific options to achieve the best performance in each particular situation. As I mentioned earlier, developer, DBA (and often other specialist) decisions must be taken in such tough cases, depending on the end customer requirements.

        >>I am not saying you should stop using guids, but you should either move to sequential guids using above posted method (preferred solution) or making sure no clustered indices are created on random generated guid key.
        Second, I must note that the NEWSEQUENTIALID function is not default in SQL Server and there is the following note in its documentation:
            "If privacy is a concern, do not use this function. It is possible to guess the value of the next generated GUID and, therefore, access data associated with that GUID."

        What you do further (GUID bytes reordering) is even worse, which not only makes it easier to guess the GUID value, but may also lead to collisions at the SQL Server level. I found several cautions on the Web on this:
            https://tomharrisonjr.com/uuid-or-guid-as-primary-keys-be-careful-7b2aa3dcb439
            https://stackoverflow.com/questions/170346/what-are-the-performance-improvement-of-sequential-guid-over-standard-guid
        From our experience, this ordering can even cause slowness with complex JOIN queries, at least on SQL Server (because data will be taken from different pages).
        At last, how would that GUID bytes reordering patching work with databases other than SQL Server? Our XPO and thus XAF products support more than a dozen of databases and we are not ready to change default values due to this. This is a risk for the security and stability of existing customers.

        In other words, any decision made at the product level at this stage would cause negative effects and we want to avoid this. Still, we will certainly consider describing this general programming specificity in the documentation for our clients. I am using "consider", because this topic is kind of debatable, to be honest. I really do not want our users to change their keys' generation mechanism after seeing a bold article in DevExpress and experiencing problems after that, even they did not have them before. Like you or not, this is our product, our decision and our responsibility for it.
        For now, we hope that this public ticket where all considerations and opinions are shared in one place will be helpful for the XPO and XAF community. I also hope that the technical reasons I provided here on our architectural decisions will also help you take the right decision in your own project. We look forward to working with you and will do whatever we can to make your experience with us a profitable one.
        Finally, I want to thank you for taking the time to describe your real world situation and share your experience with us, be it bad or good. I really appreciate it and trust me I want to repeat our last summer drinking in Croatia not less after that:-)

        UPDATE
        With v18.2, you can provide a custom Guid primary key generation algorithm globally with the new XpoDefault.CustomGuidGenerationHandler (XpoDefault.GuidGenerationMode = GuidGenerationMode.Custom):

        C#
        Func<Guid> myCustomGuidGenerationAlgorithm = () => { return Guid.NewGuid(); }; XpoDefault.CustomGuidGenerationHandler = myCustomGuidGenerationAlgorithm;

        Cheers,
        Dennis

          Comments (1)
          W W
          Willem de Vries 7 years ago

            Thanks to all! Very informative, open and constructive dialogue!

            Cheers,
            Willem

            Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

            Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.