Changeset 7132

Timestamp:

Apr 21, 2016, 1:13:30 PM (7 years ago)

Author:

Nicklas Nordborg

Message:

References #2002: Change history logging should use batch API instead of Hibernate

The LogControl class now uses a PreparedStatement with SQL from the configuration files. The large test case from #2000 seems to be improved by 1-2 minutes.

Location:

trunk

Files:

• config/dist/mysql-queries.xml (modified) (1 diff)
• src/core/common-queries.xml (modified) (1 diff)
• src/core/net/sf/basedb/core/LogControl.java (modified) (5 diffs)
• src/core/net/sf/basedb/core/log/LoggingInterceptor.java (modified) (1 diff)

trunk/config/dist/mysql-queries.xml

@@ -62 +62 @@
   </query>
   
+  <query id="DBLOG_INSERT_INTO_CHANGEHISTORYDETAILS" type="SQL">
+    <sql>
+      INSERT INTO [ChangeHistoryDetails] 
+        ([version], [history_id], [change_type], [item_id], [item_type], [change_info], [old_value], [new_value]) 
+      VALUES (0, ?, ?, ?, ?, ?, ?, ?)
+    </sql>
+    <description>
+      SQL query for inserting rows into the ChangeHistoryDetails table.
+      The primary key (id) must be auto-generated.
+      Parameters: history_id, change_type, item_id, item_type, change_info, old_value, new_value
+    </description>
+  </query>
 
 

trunk/src/core/common-queries.xml

@@ -2832 +2832 @@
     </description>
   </query>
+  <query id="DBLOG_INSERT_INTO_CHANGEHISTORYDETAILS" type="SQL">
+    <sql>
+      INSERT INTO [ChangeHistoryDetails] 
+        ([id], [version], [history_id], [change_type], [item_id], [item_type], [change_info], [old_value], [new_value]) 
+      VALUES (nextval('hibernate_sequence'), 0, ?, ?, ?, ?, ?, ?, ?)
+    </sql>
+    <description>
+      SQL query for inserting rows into the ChangeHistoryDetails table.
+      The primary key (id) must be auto-generated.
+      Parameters: history_id, change_type, item_id, item_type, change_info, old_value, new_value
+    </description>
+  </query>
   <query id="GET_GROUPS_WITH_NONHIDDENMEMBERS" type="HQL">
     <sql>

trunk/src/core/net/sf/basedb/core/LogControl.java

@@ -23 +23 @@
 
 import java.lang.ref.WeakReference;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
 
 import net.sf.basedb.core.data.ChangeHistoryData;
@@ -42 +45 @@
   private TransactionDetails transactionDetails;
   private org.hibernate.StatelessSession session;
+  // Used for batch inserting into the "ChangeHistoryDetails" table
+  private PreparedStatement insertIntoDetails;
+  private final int batchSize;
+  private int batchCount;
   
   LogControl(DbControl dc)
   {
     this.dc = new WeakReference<DbControl>(dc);
+    // Use a higher batch size here since we are dealing with small objects
+    this.batchSize = Config.getInt("db.batch-size", 50) * 10;
   }
   
@@ -77 +86 @@
   /**
     Adds information about a modified entity to the change history
-    table.    
+    table. The details may not be sent to the database immediately but
+    can be batched for more efficient use of resources.
+    
     @param detail A fully initialized ChangeHistoryDetailData object
       including a link to {@link ChangeHistoryData} that has 
@@ -84 +95 @@
   public void log(ChangeHistoryDetailData detail)
   {
-    getSession().insert(detail);
+    try
+    {
+      if (insertIntoDetails == null)
+      {
+        // Create the batcher with SQL from the 
+        // configuration files
+        org.hibernate.dialect.Dialect dialect = HibernateUtil.getDialect();
+        String sql = PredefinedQuery.getQueryString("DBLOG_INSERT_INTO_CHANGEHISTORYDETAILS");
+        sql = sql.replace('[', dialect.openQuote()).replace(']', dialect.closeQuote());
+        Connection c = HibernateUtil.getConnection(dc.get().getHibernateSession());
+        insertIntoDetails = c.prepareStatement(sql);
+      }
+      
+      insertIntoDetails.setInt(1, detail.getChangeHistory().getId());
+      insertIntoDetails.setInt(2, detail.getChangeType());
+      insertIntoDetails.setInt(3, detail.getItemId());
+      insertIntoDetails.setInt(4, detail.getItemType());
+      insertIntoDetails.setString(5, detail.getChangeInfo());
+      insertIntoDetails.setString(6, detail.getOldValue());
+      insertIntoDetails.setString(7, detail.getNewValue());
+      insertIntoDetails.addBatch();
+    }
+    catch (SQLException ex)
+    {
+      throw new BaseException(ex);
+    }
+    if (++batchCount == batchSize)
+    {
+      flush();
+    }
   }
   
@@ -99 +139 @@
   {
     return HibernateUtil.createQuery(getSession(), hql);
+  }
+  
+  /**
+    Flushes any batched changes to the database.
+  */
+  public void flush()
+  {
+    if (batchCount > 0)
+    {
+      try
+      {
+        insertIntoDetails.executeBatch();
+        batchCount = 0;
+      }
+      catch (SQLException ex)
+      {
+        throw new BaseException(ex);
+      }
+    }
   }
   

trunk/src/core/net/sf/basedb/core/log/LoggingInterceptor.java

@@ -150 +150 @@
 
   /**
+    Flush remaining changes in the log control.
+   */
+  @Override
+  public void beforeTransactionCompletion(Transaction tx) 
+  {
+    logControl.flush();
+  }
+
+  /**
     Signals successful commit or rollback to the log manager.
     Note that this happens after the transaction/connection has been