NHibernate Performance Testing

Latest revision as of 15:11, 10 June 2011

 

Home

 

Download

 

News

 

Support

 

Admin

 

Dev

 

Screen Shots

 

Grid List

 

Bugs

Languages:

English

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

• Local machine is Windows Vista 64 bit with Quad Core processor and 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.
• There are no other clients loading the database and this comparison does not reflect reliably to real load with mutiple simultaneous connections.
• Performance test is execute with NAnt and .NET-framework 2.0.

In the first test 1000 rows are inserted, updated, selected and deleted against tables with 0 and 10000 rows initially. Remote MySQL is added to demonstrate the network lag effect when batch operations are not used. NHibernate does not support batch inserts, updates and deletes in combination with MySQL. According to the results MySQL with MyISAM has best performance:

[edit] Local MySQL/MyISAM Versus SQLite Comparison

Here is more in depth comparison with local MyISAM and SQLite using 1000 test objects and variable amount of initial objects varying from 1 to 1 000 000. SQLite insert and delete performance degrades almost linearly.

[edit] Local MySQL/MyISAM Analysis

As mentioned before NHibernate does not support batch inserts, updated and deletes when used in combination with MySQL. As a result the per object execution time is bound by network delay even in local installations as illustrated below. Without session recycling serial updates of 30 or more objects at once gives 4 times better performance.

With session recycling there is only transaction cost to win which yields 2 times better performance when updating 30 or more objects at once.

The update and individual select operations were most expensive with 400 microseconds cost per object.

Inserts and deletes had 200 microseconds cost due to the lack of indexing work required. Only primary key (id) index had to be updated.

Fastest were mass select which measured only the streaming speed from the database and ended up with 10 microseconds cost per object.

As conclusion MySQL performance with table sizes up to 1M of rows were not bound by table size dependent operations but factors like network delay and data transfer rate between application and database.

[edit] Performance Test Code and Configuration

NUnit test code:

using System;
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;
using Example.Library.Resources;

namespace Example.Library
{

    [TestFixture]
    public class DatabasePerformanceTest
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(DatabasePerformanceTest));

        [SetUp]
        public void SetUp()
        {
            XmlConfigurator.Configure(); 
        }

        [Test]
        public void TestDatabasePerformance()
        {

            IList<DatabasePerformanceResult> mysqlResults=new List<DatabasePerformanceResult>();
            IList<DatabasePerformanceResult> sqliteResults = new List<DatabasePerformanceResult>();

            for (int i = 1; i <= 10000000; i *= 10)
            {
                mysqlResults.Add(PerformanceTest("hibernate-mysql.cfg.xml", i, 1000));
                sqliteResults.Add(PerformanceTest("hibernate-sqlite.cfg.xml", i, 1000));
            }

            log.Info(" ;mysql-insert;sqlite-insert;mysql-update;sqlite-update;mysql-select;sqlite-select;mysql-mass select;sqlite-mass select;mysql-delete;sqlite-delete");
            for (int i = 0; i < mysqlResults.Count; i++) 
            {
                log.Info(
                    mysqlResults[i].Name + ";" +
                    mysqlResults[i].InsertTime + ";" +
                    sqliteResults[i].InsertTime + ";" +
                    mysqlResults[i].UpdateTime + ";" +
                    sqliteResults[i].UpdateTime + ";" +
                    mysqlResults[i].SelectTime + ";" +
                    sqliteResults[i].SelectTime + ";" +
                    mysqlResults[i].MassSelectTime + ";" +
                    sqliteResults[i].MassSelectTime + ";" +
                    mysqlResults[i].DeleteTime + ";" +
                    sqliteResults[i].DeleteTime
                    );
            }
        }

        public DatabasePerformanceResult PerformanceTest(String configurationFileName, int initialObjectCount, int testObjectCount)
        {
            DatabasePerformanceResult result = new DatabasePerformanceResult();

            result.Name = initialObjectCount.ToString();

            Configuration configuration = new Configuration();
            configuration.Configure(configurationFileName);
            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);
            }

            log.Info("Dialect: " + configuration.Properties["dialect"]);

            ISessionFactory sessionFactory = configuration.BuildSessionFactory();

            using (IStatelessSession session = sessionFactory.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 < initialObjectCount; i++)
                    {
                        TestObject newObject = new TestObject();
                        newObject.TestId = Guid.NewGuid();
                        newObject.Name = "test-object-" + i;
                        session.Insert(newObject);
                    }

                    transaction.Commit();
                }

            }

            List<TestObject> testObjects = new List<TestObject>();
            DateTime timeStamp;

            log.Info("Inserting " + testObjectCount + " objects to table with " + initialObjectCount + " initial objects.");

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

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

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

                        testObjects.Add(newObject);
                    }

                    transaction.Commit();
                }

            }

            result.InsertTime = DateTime.Now.Subtract(timeStamp).TotalSeconds;
            log.Info("Took: " + result.InsertTime);
            timeStamp = DateTime.Now;
            log.Info("Updating " + testObjectCount + " objects to table with " + initialObjectCount + " initial objects.");

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

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

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

                    }

                    transaction.Commit();
                }

            }

            result.UpdateTime = DateTime.Now.Subtract(timeStamp).TotalSeconds;
            log.Info("Took: " + result.UpdateTime);
            timeStamp = DateTime.Now;
            log.Info("Selecting " + testObjectCount + " objects by id from table with " + initialObjectCount + " initial objects.");

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

                for (int i = 0; i < testObjectCount; 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;

                }

            }

            result.SelectTime = DateTime.Now.Subtract(timeStamp).TotalSeconds;
            log.Info("Took: " + result.SelectTime);
            timeStamp = DateTime.Now;
            log.Info("Selecting " + testObjectCount + " objects at once from table with " + initialObjectCount + " initial objects.");

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

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

                IList loadedObjects = q.List();

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

                Assert.AreEqual(testObjectCount, testObjects.Count);

            }

            result.MassSelectTime = DateTime.Now.Subtract(timeStamp).TotalSeconds;
            log.Info("Took: " + result.MassSelectTime);
            timeStamp = DateTime.Now;
            log.Info("Deleting " + testObjectCount + " objects from table with " + initialObjectCount + " initial objects.");

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

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

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


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

                    }

                    transaction.Commit();
                }

            }

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

            return result;
        }

    }

    public struct DatabasePerformanceResult
    {
        public String Name;
        public double InsertTime;
        public double UpdateTime;
        public double SelectTime;
        public double MassSelectTime;
        public double DeleteTime;
    }

    

}

Value object code:

using System;
using System.Collections.Generic;
using System.Text;

namespace Example.Library.Resources
{
    /// <summary>
    /// Object for database tests.
    /// </summary>
    public class TestObject
    {
        private Guid testId=Guid.Empty;
        public Guid TestId {
            get
            {
                return testId;
            }
            set
            {
                testId = value;
            }
        }

        private String name;
        public String Name {
            get
            {
                return name;
            }
            set
            {
                name = value;
            }
        }
    }
}

Mapping configuration:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <class name="Example.Library.Resources.TestObject, Example.Library" table="test_object" lazy="false">
        <id name="TestId" column="TestId" type="Guid"> 
            <generator class="assigned" /> 
        </id> 
        <property name="Name" type="String" length="45" />
    </class>
</hibernate-mapping>

The configuration for the test dll:

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

  <configSections>    
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
  </configSections>

  <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>

MySQL hibernate configuration file:

<?xml version='1.0' encoding='utf-8'?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory>
    <!-- properties -->
    <property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property>
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.connection_string">Server=localhost;Database=operator;User ID=operator;Password=operator</property>
    <property name="dialect">NHibernate.Dialect.MySQL5Dialect</property>
    <property name="show_sql">false</property>
  </session-factory>
</hibernate-configuration>

SQLite hibernate configuration file:

<?xml version='1.0' encoding='utf-8'?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory>
    <!-- properties -->
      <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>
</hibernate-configuration>