NHibernate Performance Testing

From OpenSimulator

(Difference between revisions)
Jump to: navigation, search
m
m
Line 1: Line 1:
 +
== NHibernate Performance Testing ==
 +
 
Here are results from performance tests run on SQLite and MySQL database.
 
Here are results from performance tests run on SQLite and MySQL database.
  
Line 9: Line 11:
 
Performance test is execute with NAnt and .NET-framework 2.0.
 
Performance test is execute with NAnt and .NET-framework 2.0.
  
=== Empty Tables ===
+
=== Results with Empty Tables ===
  
Remote MySQL results with MyISAM:
+
==== Remote MySQL results with MyISAM ====
  
 
<pre>
 
<pre>
Line 26: Line 28:
 
</pre>
 
</pre>
  
Local MySQL results with InnoDB:
+
==== Local MySQL results with InnoDB ====
  
 
<pre>
 
<pre>
Line 41: Line 43:
 
</pre>
 
</pre>
  
Local MySQL results with MyISAM:
+
==== Local MySQL results with MyISAM ====
  
 
<pre>
 
<pre>
Line 56: Line 58:
 
</pre>
 
</pre>
  
Local SQLite results:
+
==== Local SQLite results ====
  
 
<pre>
 
<pre>
Line 71: Line 73:
 
</pre>
 
</pre>
  
Test test code:
+
=== Performance Test Code and Configuration ===
  
 
<pre>
 
<pre>
Line 91: Line 93:
  
 
     [TestFixture]
 
     [TestFixture]
     public class DatabaseTest
+
     public class DatabasePerformanceTest
 
     {
 
     {
 
         private static readonly ILog log = LogManager.GetLogger(typeof(DatabaseTest));
 
         private static readonly ILog log = LogManager.GetLogger(typeof(DatabaseTest));
 
         private ISessionFactory sessions;
 
         private ISessionFactory sessions;
 +
        private const int INITIAL_OBJECT_COUNT = 10000;
 +
        private const int TEST_OBJECT_COUNT = 100;
  
 
         [SetUp]
 
         [SetUp]
Line 101: Line 105:
 
             // log4net configuration
 
             // log4net configuration
 
             XmlConfigurator.Configure();  
 
             XmlConfigurator.Configure();  
            //BasicConfigurator.Configure();
 
 
  
 
             // hibernate configuration
 
             // hibernate configuration
 
             Configuration configuration = new Configuration();
 
             Configuration configuration = new Configuration();
 
             configuration.AddAssembly(this.GetType().Assembly);
 
             configuration.AddAssembly(this.GetType().Assembly);
           
+
                       
 
             if (configuration.Properties["dialect"].Equals("NHibernate.Driver.SQLite20Driver")&&
 
             if (configuration.Properties["dialect"].Equals("NHibernate.Driver.SQLite20Driver")&&
 
                 !File.Exists("OpenSimExample.db"))
 
                 !File.Exists("OpenSimExample.db"))
Line 113: Line 115:
 
                 SchemaExport schemaExport = new SchemaExport(configuration);
 
                 SchemaExport schemaExport = new SchemaExport(configuration);
 
                 schemaExport.Create(true, true);
 
                 schemaExport.Create(true, true);
             }
+
             }          
 
+
            foreach(String key in configuration.Properties.Keys)
+
            {
+
                log.Info(key+": "+configuration.Properties[key]);
+
            }
+
  
 
             sessions = configuration.BuildSessionFactory();
 
             sessions = configuration.BuildSessionFactory();
        }
 
 
        [Test]
 
        public void TestDatabase()
 
        {
 
  
 
             using (IStatelessSession session = sessions.OpenStatelessSession())
 
             using (IStatelessSession session = sessions.OpenStatelessSession())
 
             {
 
             {
 
 
                 using (ITransaction transaction = session.BeginTransaction())
 
                 using (ITransaction transaction = session.BeginTransaction())
 
                 {
 
                 {
 +
                    IQuery q = session.CreateQuery("from Example.Library.Resources.TestObject");
 +
                    IList loadedObjects = q.List();
  
                     TestObject newObject = new TestObject();
+
                     foreach (TestObject testObject in loadedObjects)
                     newObject.TestId = Guid.NewGuid();
+
                     {
                    newObject.Name = "test-name";
+
                        session.Delete(testObject);
                    session.Insert(newObject);
+
                     }
 
+
                     TestObject loadedObject = (TestObject)session.Get("Example.Library.Resources.TestObject", newObject.TestId);
+
                    Assert.AreEqual(newObject.Name,loadedObject.Name);
+
  
                    session.Delete(loadedObject);
 
 
                     transaction.Commit();
 
                     transaction.Commit();
 +
                }
  
 +
                using (ITransaction transaction = session.BeginTransaction())
 +
                {
 +
                    for (int i = 0; i < INITIAL_OBJECT_COUNT; i++)
 +
                    {
 +
                        TestObject newObject = new TestObject();
 +
                        newObject.TestId = Guid.NewGuid();
 +
                        newObject.Name = "test-object-" + i;
 +
                        session.Insert(newObject);
 +
                    }
 +
 +
                    transaction.Commit();
 
                 }
 
                 }
  
 
             }
 
             }
         
 
 
         }
 
         }
  
Line 153: Line 153:
 
         public void TestDatabasePerformance()
 
         public void TestDatabasePerformance()
 
         {
 
         {
             int objectCount = 100;
+
              
 
             List<TestObject> testObjects = new List<TestObject>();
 
             List<TestObject> testObjects = new List<TestObject>();
 
             DateTime timeStamp;
 
             DateTime timeStamp;
  
             log.Info("Inserting " + objectCount + " objects");
+
             log.Info("Inserting " + TEST_OBJECT_COUNT + " objects.");
  
 
             timeStamp = DateTime.Now;
 
             timeStamp = DateTime.Now;
Line 166: Line 166:
 
                 {
 
                 {
  
                     for (int i = 0; i < objectCount; i++)
+
                     for (int i = 0; i < TEST_OBJECT_COUNT; i++)
 
                     {
 
                     {
 
                         TestObject newObject = new TestObject();
 
                         TestObject newObject = new TestObject();
 
                         newObject.TestId = Guid.NewGuid();
 
                         newObject.TestId = Guid.NewGuid();
                         newObject.Name = "test-name";
+
                         newObject.Name = "test-object-" + i;
 
                         session.Insert(newObject);
 
                         session.Insert(newObject);
  
Line 183: Line 183:
 
             log.Info("Took: " + DateTime.Now.Subtract(timeStamp));
 
             log.Info("Took: " + DateTime.Now.Subtract(timeStamp));
 
             timeStamp = DateTime.Now;
 
             timeStamp = DateTime.Now;
             log.Info("Selecting " + objectCount + " objects by id");
+
             log.Info("Updating " + TEST_OBJECT_COUNT + " objects.");
  
 
             using (IStatelessSession session = sessions.OpenStatelessSession())
 
             using (IStatelessSession session = sessions.OpenStatelessSession())
 
             {
 
             {
 +
                using (ITransaction transaction = session.BeginTransaction())
 +
                {
  
                 for (int i = 0; i < objectCount; i++)
+
                    for (int i = 0; i < TEST_OBJECT_COUNT; i++)
 +
                    {
 +
                        TestObject oldObject = testObjects[i];
 +
 
 +
                        TestObject loadedObject = (TestObject)session.Get("Example.Library.Resources.TestObject", oldObject.TestId);
 +
                        session.Update(loadedObject);
 +
 
 +
                    }
 +
 
 +
                    transaction.Commit();
 +
                }
 +
 
 +
            }
 +
 
 +
            log.Info("Took: " + DateTime.Now.Subtract(timeStamp));
 +
            timeStamp = DateTime.Now;
 +
            log.Info("Selecting " + TEST_OBJECT_COUNT + " objects by id.");
 +
 
 +
            using (IStatelessSession session = sessions.OpenStatelessSession())
 +
            {
 +
 
 +
                 for (int i = 0; i < TEST_OBJECT_COUNT; i++)
 
                 {
 
                 {
 
                     TestObject oldObject = testObjects[i];
 
                     TestObject oldObject = testObjects[i];
Line 202: Line 225:
 
             log.Info("Took: " + DateTime.Now.Subtract(timeStamp));
 
             log.Info("Took: " + DateTime.Now.Subtract(timeStamp));
 
             timeStamp = DateTime.Now;
 
             timeStamp = DateTime.Now;
             log.Info("Selecting " + objectCount + " objects at once");
+
             log.Info("Selecting " + TEST_OBJECT_COUNT + " objects at once.");
  
 
             using (IStatelessSession session = sessions.OpenStatelessSession())
 
             using (IStatelessSession session = sessions.OpenStatelessSession())
Line 208: Line 231:
  
 
                 IQuery q = session.CreateQuery("from Example.Library.Resources.TestObject");
 
                 IQuery q = session.CreateQuery("from Example.Library.Resources.TestObject");
 +
                q.SetMaxResults(TEST_OBJECT_COUNT);
 +
 
                 IList loadedObjects = q.List();
 
                 IList loadedObjects = q.List();
  
 
                 foreach (TestObject testObject in loadedObjects)
 
                 foreach (TestObject testObject in loadedObjects)
 
                 {
 
                 {
 +
                    // Make sure object is not lazy loaded.
 +
                    Guid id = testObject.TestId;
 +
                    String name = testObject.Name;
 
                 }
 
                 }
  
                 Assert.AreEqual(objectCount, testObjects.Count);
+
                 Assert.AreEqual(TEST_OBJECT_COUNT, testObjects.Count);
  
 
             }
 
             }
Line 220: Line 248:
 
             log.Info("Took: " + DateTime.Now.Subtract(timeStamp));
 
             log.Info("Took: " + DateTime.Now.Subtract(timeStamp));
 
             timeStamp = DateTime.Now;
 
             timeStamp = DateTime.Now;
             log.Info("Deleting " + objectCount + " objects");
+
             log.Info("Deleting " + TEST_OBJECT_COUNT + " objects.");
  
 
             using (IStatelessSession session = sessions.OpenStatelessSession())
 
             using (IStatelessSession session = sessions.OpenStatelessSession())
Line 228: Line 256:
 
                 {
 
                 {
  
                     for (int i = 0; i < objectCount; i++)
+
                     for (int i = 0; i < TEST_OBJECT_COUNT; i++)
 
                     {
 
                     {
  
Line 248: Line 276:
 
     }
 
     }
 
}
 
}
 +
 +
 
</pre>
 
</pre>
  

Revision as of 10:14, 1 January 2009

Contents

NHibernate Performance Testing

Here are results from performance tests run on SQLite and MySQL database.

Local machine is Windows Vista 64 bit with Quad Core processor with 4G memory. MySQL has factory settings.

Remote mysql database machine is Ubuntu Linux virtual machine with one virtual processor and 2G memory. MySQL has factory settings.

Local machine ping is 10 ms to the remote machine.

Performance test is execute with NAnt and .NET-framework 2.0.

Results with Empty Tables

Remote MySQL results with MyISAM

2009-01-01 18:41:08,928 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Inserting 100 objects.
2009-01-01 18:41:10,182 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:01.2540000
2009-01-01 18:41:10,183 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Updating 100 objects.
2009-01-01 18:41:12,691 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:02.5080000
2009-01-01 18:41:12,691 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Selecting 100 objects by id.
2009-01-01 18:41:17,160 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:04.4690000
2009-01-01 18:41:17,161 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Selecting 100 objects at once.
2009-01-01 18:41:17,299 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:00.1380000
2009-01-01 18:41:17,299 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Deleting 100 objects.
2009-01-01 18:41:18,664 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:01.3650000

Local MySQL results with InnoDB

2009-01-01 18:39:23,637 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Inserting 100 objects.
2009-01-01 18:39:23,661 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:00.0230000
2009-01-01 18:39:23,663 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Updating 100 objects.
2009-01-01 18:39:23,724 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:00.0610000
2009-01-01 18:39:23,726 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Selecting 100 objects by id.
2009-01-01 18:39:23,769 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:00.0430000
2009-01-01 18:39:23,771 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Selecting 100 objects at once.
2009-01-01 18:39:23,844 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:00.0730000
2009-01-01 18:39:23,846 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Deleting 100 objects.
2009-01-01 18:39:23,866 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:00.0200000

Local MySQL results with MyISAM

2009-01-01 18:38:39,584 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Inserting 100 objects.
2009-01-01 18:38:39,598 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:00.0130000
2009-01-01 18:38:39,599 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Updating 100 objects.
2009-01-01 18:38:39,639 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:00.0400000
2009-01-01 18:38:39,640 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Selecting 100 objects by id.
2009-01-01 18:38:39,686 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:00.0460000
2009-01-01 18:38:39,686 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Selecting 100 objects at once.
2009-01-01 18:38:39,762 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:00.0760000
2009-01-01 18:38:39,763 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Deleting 100 objects.
2009-01-01 18:38:39,777 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:00.0140000

Local SQLite results

2009-01-01 18:44:02,623 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Inserting 100 objects.
2009-01-01 18:44:02,660 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:00.0370000
2009-01-01 18:44:02,660 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Updating 100 objects.
2009-01-01 18:44:02,710 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:00.0500000
2009-01-01 18:44:02,711 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Selecting 100 objects by id.
2009-01-01 18:44:02,751 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:00.0400000
2009-01-01 18:44:02,752 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Selecting 100 objects at once.
2009-01-01 18:44:02,823 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:00.0710000
2009-01-01 18:44:02,823 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Deleting 100 objects.
2009-01-01 18:44:02,860 [TestRunnerThread] INFO  Example.Library.Resources.DatabaseTest [(null)] - Took: 00:00:00.0370000

Performance Test Code and Configuration

using System;
using System.Collections.Generic;
using System.Text;
using NUnit.Framework;
using NHibernate;
using NHibernate.Cfg;
using System.IO;
using NHibernate.Tool.hbm2ddl;
using log4net.Repository.Hierarchy;
using log4net;
using log4net.Config;
using System.Collections;

namespace Example.Library.Resources
{

    [TestFixture]
    public class DatabasePerformanceTest
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(DatabaseTest));
        private ISessionFactory sessions;
        private const int INITIAL_OBJECT_COUNT = 10000;
        private const int TEST_OBJECT_COUNT = 100;

        [SetUp]
        public void SetUp()
        {
            // log4net configuration
            XmlConfigurator.Configure(); 

            // hibernate configuration
            Configuration configuration = new Configuration();
            configuration.AddAssembly(this.GetType().Assembly);
                        
            if (configuration.Properties["dialect"].Equals("NHibernate.Driver.SQLite20Driver")&&
                !File.Exists("OpenSimExample.db"))
            {
                SchemaExport schemaExport = new SchemaExport(configuration);
                schemaExport.Create(true, true);
            }           

            sessions = configuration.BuildSessionFactory();

            using (IStatelessSession session = sessions.OpenStatelessSession())
            {
                using (ITransaction transaction = session.BeginTransaction())
                {
                    IQuery q = session.CreateQuery("from Example.Library.Resources.TestObject");
                    IList loadedObjects = q.List();

                    foreach (TestObject testObject in loadedObjects)
                    {
                        session.Delete(testObject);
                    }

                    transaction.Commit();
                }

                using (ITransaction transaction = session.BeginTransaction())
                {
                    for (int i = 0; i < INITIAL_OBJECT_COUNT; i++)
                    {
                        TestObject newObject = new TestObject();
                        newObject.TestId = Guid.NewGuid();
                        newObject.Name = "test-object-" + i;
                        session.Insert(newObject);
                    }

                    transaction.Commit();
                }

            }
        }

        [Test]
        public void TestDatabasePerformance()
        {
            
            List<TestObject> testObjects = new List<TestObject>();
            DateTime timeStamp;

            log.Info("Inserting " + TEST_OBJECT_COUNT + " objects.");

            timeStamp = DateTime.Now;
            using (IStatelessSession session = sessions.OpenStatelessSession())
            {

                using (ITransaction transaction = session.BeginTransaction())
                {

                    for (int i = 0; i < TEST_OBJECT_COUNT; i++)
                    {
                        TestObject newObject = new TestObject();
                        newObject.TestId = Guid.NewGuid();
                        newObject.Name = "test-object-" + i;
                        session.Insert(newObject);

                        testObjects.Add(newObject);
                    }

                    transaction.Commit();
                }
            
            }

            log.Info("Took: " + DateTime.Now.Subtract(timeStamp));
            timeStamp = DateTime.Now;
            log.Info("Updating " + TEST_OBJECT_COUNT + " objects.");

            using (IStatelessSession session = sessions.OpenStatelessSession())
            {
                using (ITransaction transaction = session.BeginTransaction())
                {

                    for (int i = 0; i < TEST_OBJECT_COUNT; i++)
                    {
                        TestObject oldObject = testObjects[i];

                        TestObject loadedObject = (TestObject)session.Get("Example.Library.Resources.TestObject", oldObject.TestId);
                        session.Update(loadedObject);

                    }

                    transaction.Commit();
                }

            }

            log.Info("Took: " + DateTime.Now.Subtract(timeStamp));
            timeStamp = DateTime.Now;
            log.Info("Selecting " + TEST_OBJECT_COUNT + " objects by id.");

            using (IStatelessSession session = sessions.OpenStatelessSession())
            {

                for (int i = 0; i < TEST_OBJECT_COUNT; i++)
                {
                    TestObject oldObject = testObjects[i];

                    TestObject loadedObject = (TestObject)session.Get("Example.Library.Resources.TestObject", oldObject.TestId);
                    Assert.AreEqual(oldObject.Name, loadedObject.Name);
                    testObjects[i] = loadedObject;

                }

            }

            log.Info("Took: " + DateTime.Now.Subtract(timeStamp));
            timeStamp = DateTime.Now;
            log.Info("Selecting " + TEST_OBJECT_COUNT + " objects at once.");

            using (IStatelessSession session = sessions.OpenStatelessSession())
            {

                IQuery q = session.CreateQuery("from Example.Library.Resources.TestObject");
                q.SetMaxResults(TEST_OBJECT_COUNT);

                IList loadedObjects = q.List();

                foreach (TestObject testObject in loadedObjects)
                {
                    // Make sure object is not lazy loaded.
                    Guid id = testObject.TestId;
                    String name = testObject.Name;
                }

                Assert.AreEqual(TEST_OBJECT_COUNT, testObjects.Count);

            }

            log.Info("Took: " + DateTime.Now.Subtract(timeStamp));
            timeStamp = DateTime.Now;
            log.Info("Deleting " + TEST_OBJECT_COUNT + " objects.");

            using (IStatelessSession session = sessions.OpenStatelessSession())
            {

                using (ITransaction transaction = session.BeginTransaction())
                {

                    for (int i = 0; i < TEST_OBJECT_COUNT; i++)
                    {


                        TestObject loadedObject = testObjects[i];
                        session.Delete(loadedObject);

                    }

                    transaction.Commit();
                }

            }

            log.Info("Took: " + DateTime.Now.Subtract(timeStamp));
            timeStamp = DateTime.Now;

        }
    }
}


The configuration for the test dll:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>

  <configSections>    
    <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate" />
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
  </configSections>

  <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">

    <!--
    <session-factory>
      <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
      <property name="connection.driver_class">NHibernate.Driver.SQLite20Driver</property>
      <property name="connection.connection_string">
        Data Source=OpenSimExample.db;Version=3
      </property>
      <property name="dialect">NHibernate.Dialect.SQLiteDialect</property>
      <property name="query.substitutions">true=1;false=0</property>
      <property name="show_sql">false</property>
    </session-factory>
    -->

    <session-factory>
      
      <property name="connection.provider">
        NHibernate.Connection.DriverConnectionProvider, NHibernate
      </property>
      <property name="connection.connection_string">
        Server=server;Database=database;User ID=user;Password=password
      </property>
      <property name="dialect">NHibernate.Dialect.MySQL5Dialect</property>

    </session-factory>
    
  </hibernate-configuration>

  <log4net debug="false">
    
    <appender name="ColoredConsoleAppender" type="log4net.Appender.ColoredConsoleAppender">
      <mapping>
        <level value="ERROR" />
        <foreColor value="White" />
        <backColor value="Red, HighIntensity" />
      </mapping>
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline" />
      </layout>
    </appender>
    <root>
      <priority value="ERROR" />
      <appender-ref ref="ColoredConsoleAppender" />
    </root>
    <logger name="Example.Library">
      <level value="INFO" />
    </logger>

  </log4net>


</configuration>
General
About This Wiki