Transaction vs Performance

Topics: Core, General
Jul 4, 2012 at 6:35 AM

Hi , I have a question about the transaction.

Since orchard is automatically creating a transaction for each HTTP request, every database hit(Query/Add/Update) are in one transaction scope.

I want to know is the transaction impact the performance whether or not? If not, can you give me some reference article? Thanks.

Our DBA want us to remove the transaction, especially almost all of the SQL statement is query(SELECT) not insert/update in a CMS system.  

 

Thanks in advance.

Coordinator
Jul 4, 2012 at 7:06 AM

Your DBA doesn't like *transactions*??? You should tell him: no, that's impossible. One thing you can do is change the code for transactions to be read uncommitted, if you are using SQL Server and not SqlCe.

Jul 4, 2012 at 7:16 AM

Thank you for your reply.

Our DBA suggest us to remove the transaction for all read data at least, they think it will impact the performance.

In addition, some query need to get data from external database on other database server, if use the TransactionScope,  it will start the Distributed Transaction, they think it will be lock related the tables, not sure change the transaction to be read uncommitted is work. 

Thanks.

Coordinator
Jul 4, 2012 at 8:02 AM

If you need to query another database, you just need to opt out of the ambient transaction: using(new TransactionScope(TransactionScopeOption.Suppress)) { ... }

For readuncommitted, look for TransactionManager.cs and search for IsolationLevel. Change it to ReadUncommitted.

But if your DBA claims a performance problem, he should profile the application and show you a bottleneck.

Coordinator
Jul 4, 2012 at 4:07 PM

Transactions are necessary. If one is rolled back then the reads won't be cached.

Also transactions are not a performance problem. I suggest you to make some perf tests and show they are. Remember there is only ONE transaction for the whole request, it's not like it was creating one per query.

Joke of the day: Maybe your own work will be more efficient by removing the DBA completely ;) (Sure he won't like this one)

Jul 4, 2012 at 11:31 PM

Here is a good read from Ayende on the matter:  http://nhprof.com/Learn/Alerts/DoNotUseImplicitTransactions

Coordinator
Jul 5, 2012 at 2:25 AM
Edited Jul 5, 2012 at 2:36 AM

I'd love to be able to like posts on these forums ;)

"it's not like it was creating one per query" like it would if the Orchard transaction was removed...

Jul 7, 2012 at 3:35 AM
Edited Jul 7, 2012 at 3:37 AM

Thank you for working on Orchard. I know what it is like to work on open source projects...

Nub here, I don't mean to add to the fire or argument when it comes to transactions... I realize the value and importance...  I love the CMS but hate the performance in some scenarios.. Have you guys done any profiling?

  • Please try deleting a blog post (If I had to guess... 100 database queries and 10,000 lines of code executed later). I tracked down a few places where I couldn't import my 16000 blogs in from a high performance legacy community server install. This was caused by various background tasks causing transaction timeouts because the importers had an active transaction. Do you really need to update aliases every minute? Especially when any content part being inserted will update it in the first place?
  • I know that you try to be database independent, but why not use stored procedures when using SQL Server?
  • There is code that is fired that goes to the database twice to update a blog post's view count AFTER you press delete. This is INSANE!
  • Performance is really slow on start-up (there are work arounds for this like precompiling modules).
  • Performance is really bad on azure, forget about accessing two different pages at once..
Coordinator
Jul 11, 2012 at 2:37 PM

"Have you guys done any profiling?" Yes.

Stored procedures: no, almost all of what Orchard does requires building queries dynamically using nHibernate. Stored procedures would not work, even if they did provide a perf benefit.

On Azure, you have to dimension your instances for your web site. We have orchardproject.net and galery.orchardproject.net running on Azure and we're very happy with the performance.

And of course, we take patches, and also appreciate bug reports with proper repro steps. In particular, if you have repro steps with a profile that shows a specific bottleneck for a reasonable use case, we'll be happy to investigate and fix.

Developer
Jul 13, 2012 at 11:29 AM

@tonywoo815 Speaking about performance. - there is *no* noticeable performance impact of using transactions for every request in Orchard. At least as far as my recent research shows.

I was benchmarking SignalR running within Orchard (with each request wrapped in new work context scope, ie. Autofac lifetime scope and database transaction inside). On first sight one would think it's gonna be very slow - in fact it's not even nearby being 'slow'. I got ~900 req/s on my local machine (with async http handlers of course), running Orchard in IIS and SQL Server Standard on the same machine.

Jul 13, 2012 at 2:53 PM
Edited Jul 13, 2012 at 2:57 PM

@Bertandleroy,

If you could please email me privately, I do have a db backup I can send you where it shows off many performance issues. I'm experiencing these issues both locally and in azure.  Just using Orchard seems to be MUCH slower when compared to a fresh install. Specifically, I've narrowed down a few:

  • BlogPostPartHandler
    • There is a HUGE performance issues when it comes to calling this code
       _blogPartPostCount = _blogPostService.Get(blogPart, VersionOptions.Published).Count(); 
      
       I have 14000+ blog posts and noticed this issue when importing from blog ml. It will pull down all 14,000+ blog posts (over a minute operation), multiple times without being cached just to see how many records are there. It should just querying the count instead of materializing the query and then doing the count. Since, I only have one blog that I was importing, I modified the code for only the time I was importing to this: 
         public BlogPostPartHandler(IBlogService blogService, IBlogPostService blogPostService, RequestContext requestContext) {
                  _blogService = blogService;
                  _blogPostService = blogPostService;
      
                  OnGetDisplayShape<BlogPostPart>(SetModelProperties);
                  OnGetEditorShape<BlogPostPart>(SetModelProperties);
                  OnUpdateEditorShape<BlogPostPart>(SetModelProperties);
      
                  //OnCreated<BlogPostPart>((context, part) => UpdateBlogPostCount(part));
                  OnPublished<BlogPostPart>((context, part) => UpdateBlogPostCount(part));
                  //OnUnpublished<BlogPostPart>((context, part) => UpdateBlogPostCount(part));
                  //OnVersioned<BlogPostPart>((context, part, newVersionPart) => UpdateBlogPostCount(newVersionPart));
                  //OnRemoved<BlogPostPart>((context, part) => UpdateBlogPostCount(part));
      
                  OnRemoved<BlogPart>(
                      (context, b) =>
                      blogPostService.Get(context.ContentItem.As<BlogPart>()).ToList().ForEach(
                          blogPost => context.ContentManager.Remove(blogPost.ContentItem)));
              }
      
              private static int? _blogPartPostCount;
              private void UpdateBlogPostCount(BlogPostPart blogPostPart) {
                  CommonPart commonPart = blogPostPart.As<CommonPart>();
                  if (commonPart != null &&
                      commonPart.Record.Container != null) {
      
                      BlogPart blogPart = blogPostPart.BlogPart ?? 
                          _blogService.Get(commonPart.Record.Container.Id, VersionOptions.Published).As<BlogPart>();
      
                      // Ensure the "right" set of published posts for the blog is obtained
                      blogPart.ContentItem.ContentManager.Flush();
      
                      if (!_blogPartPostCount.HasValue)
                          _blogPartPostCount = _blogPostService.Get(blogPart, VersionOptions.Published).Count();
      
                      blogPart.PostCount = _blogPartPostCount.Value;
      
                      _blogPartPostCount++;
                  }
              }
      The scary part is if you do a find usages on Count(), there are 61 usages of this where it is happening.
    • Is there a reason, Orchard needs to update the blog post count for OnCreated, OnPublished, OnUnpublished, OnVersioned and OnRemoved? I would think it would only need to be updated for OnCreated. But why should that even have to happen. It should default to 0, or be set as part of an import process  / sql index.
  • Timeouts occurring while updating the publish date.
  • Timeouts when showing 100 (lets say x) blog posts, I looked at sql profiler and it gets a list of all the blog posts and then executes x sub query's for each blog post id to get additional information to display. This shouldn't be happening.
  • Blog Posts not being deleted after pressing delete, they are still in the database.
  • Timeouts, while accessing the last page of 25,000 alias records.
  • Aliases are constantly being updated when inserting content, but there is a background task that runs every minute to update / sync these?  Shouldn't need to happen or there should be a global cache for blog posts / aliases which are kept in sync by the runtime so we never have to do these expensive queries repeatedly.
  • General use, I seem to be seeing abnormally high memory usage and lots of timeouts, even without third party modules.
  • Indexing addons fail.
  • And More...
Coordinator
Jul 13, 2012 at 3:34 PM

Those are legitimate concerns. If you want to provide a fork with some fixes for that, we would be happy to take it. Or you can file bugs for the major perf killers.

The records are not deleted automatically, the content item is just unpublished and versions are kept. You can create a module if you need to delete them automatically.

Coordinator
Jul 13, 2012 at 6:26 PM

I have pushed a change to BlogPostPartHandler to fix this Count() usage already. I also did it for a few other services, like Tags. Though I could not find more that 3 or 4, not the 61 you mention. I think your oversaw some of them.

There are still some obvious optimization to do:
- ArchivePartHandler, which also load everything
- BlostPost rendering which could prefetch related records. 

Jul 13, 2012 at 7:46 PM
Edited Jul 13, 2012 at 8:08 PM

Thank you for those changes. I'll keep an eye out on this thread as well as let you know if I find any more issues / fixes. One last question. I'm about to deploy this new orchard site to production. Is the latest builds stable enough? Is there anything I need to do to check for plug-in compatibility?

Coordinator
Jul 13, 2012 at 8:51 PM

Latest default branch can be used, yes, as we should release it on Wednesday. It's better than the RC actually.

Developer
Jul 14, 2012 at 1:43 PM

Just for the "Blog Posts not being deleted after pressing delete, they are still in the database." point: all content item deletes in Orchard are soft deletes, leaving item entries in the DB, so this is not a bug.

Jul 16, 2012 at 11:49 PM

I noticed that you put out a rc2 and made a ton of changes since then. Do you know when the next stable azure build will be out (you didn't include on with the rc2). I built from trunk a minute ago and I had to make some changes as the azure build failed initially. I didn't deploy the local azure build as the azure unit tests failed (MimeTypeShouldBeSet).. Is this normal?

Coordinator
Jul 16, 2012 at 11:54 PM

We didn't provide Azure drops with RC. But if you found issues please file them, they might be important.

Coordinator
Jul 16, 2012 at 11:54 PM

Probably sometime tomorrow. Hopefully.

Jul 17, 2012 at 12:58 AM
Edited Jul 17, 2012 at 3:31 AM

I was able to get it upgraded after debugging from trunk...

Jul 18, 2012 at 12:28 AM

I need to do some profiling, but my site (needs some themeing) is unresponsive at times to the point it times out with Error 502. I upgraded to 1.5 nightly (last night) and it helped a little. Even page loads are slow across the board. We are running on a "medium" azure instance. Has anyone else ran into this behavior? What would be the best way to do profiling on an azure instance? I can profile locally, but its performance is differently locally, but equally slow at times.

Coordinator
Jul 18, 2012 at 12:41 AM

Are you using Azure Web Sites ?