Changeset 3811

Timestamp:

Oct 9, 2007, 2:15:16 PM (16 years ago)

Author:

Nicklas Nordborg

Message:

References #796: Don't create indexes on primary key columns for tables in the dynamic database

Added tool to remove indexes from dynamic db.

Location:

branches/2.4-stable

Files:

• bin/dynamicdb.bat (added)
• bin/dynamicdb.sh (added)
• doc/src/docbook/admindoc/installation_upgrade.xml (modified) (1 diff)
• src/core/net/sf/basedb/core/HibernateUtil.java (modified) (8 diffs)
• src/install/net/sf/basedb/install/InitDB.java (modified) (2 diffs)

branches/2.4-stable/doc/src/docbook/admindoc/installation_upgrade.xml

@@ -67 +67 @@
     <title>Upgrade instructions</title>
 
-    <important>
-      <title>Note to PostgreSQL users</title>
-      Upgrading BASE to versions earlier
-      than v2.2 is not be safe with an PostgreSQL database engine due
-      to a bug in Hibernate. The problem is reported to Hibernate
-      developers. There now exists a workaround for this problem. Read
-      more about the workaround on
-      the <ulink
-      url="http://base.thep.lu.se/wiki/UpgradePostgres">Upgrading a
-      Postgres database prior to BASE 2.2</ulink> page. This fix is
-      not needed for the release which this document is a part of.
+    <important id="dropindexes">
+      <title>Upgrading from BASE 2.4.3 or lower to 2.4.4 or higher</title>
+      <para>
+        Older releases of BASE 2 used to create indexes for many columns
+        in the dynamic database. The same columns are part of the primary
+        key for the tables so the indexes are not really needed. The 
+        result is very bad performance since the database engine sometimes 
+        get stuck in "index update" mode making the entire server
+        very slow. BASE 2.4.4 no longer creates the indexes. Indexes on
+        existing tables should be dropped to increase the performance.
+        Tests have shown a 50-90% decrease in execution time for some 
+        plug-ins
+        (<ulink url="http://base.thep.lu.se/ticket/294">http://base.thep.lu.se/ticket/294</ulink>). 
+      </para>
+      <para>
+        Removing the indexes is very simple. <emphasis>After the server
+        has been upgraded</emphasis> following the usual instructions below, issue the
+        the following commands:
+      </para>
+    
+      <programlisting>
+cd &lt;basedir&gt;/bin
+./dynamicdb.sh -v -dropindexes
+</programlisting>
+
+      <para>
+        Skip the <option>-dropindexes</option> option to do a dry
+        run without actually deleting the indexes.
+      </para>
     </important>
 

branches/2.4-stable/src/core/net/sf/basedb/core/HibernateUtil.java

@@ -46 +46 @@
 import java.util.HashMap;
 import java.util.HashSet;
+import java.util.LinkedList;
 import java.util.List;
 import java.util.Iterator;
@@ -1692 +1693 @@
   
   /**
+    Prints a lot of useful stuff about the dynamic database to standard output.
+    For each table the output includes information about columns, indexes,
+    primary keys and foreign keys as found in the current database.
+    
+    @param verbose If true, lots of information will be printed
+    @param silent If true, no information will be printed
+    @param dropIndexes If true, all indexes will be dropped
+  */
+  @SuppressWarnings("unchecked")
+  public static void dynamicDbIndexes(boolean verbose, boolean silent, boolean dropIndexes)
+  {
+    if (silent) verbose = false;
+    Session session = newSession();
+    Connection connection = HibernateUtil.getConnection(session);
+    DatabaseMetaData metaData = null;
+    List<Table> tables = new LinkedList<Table>();
+    
+    try
+    {
+      metaData = connection.getMetaData();
+      ResultSet result = metaData.getTables(Application.getDynamicCatalog(), 
+          Application.getDynamicSchema(), null, null);
+      
+      while (result.next())
+      {
+        String tableName = result.getString("TABLE_NAME");
+        String tableCatalog = result.getString("TABLE_CAT");
+        String tableSchema = result.getString("TABLE_SCHEM");
+        Table table = new Table();
+        table.setName(tableName);
+        table.setCatalog(tableCatalog);
+        table.setSchema(tableSchema);
+        tables.add(table);
+      }
+    }
+    catch (SQLException ex)
+    {
+      throw new BaseException(ex);
+    }
+    
+    for (Table table : tables)
+    {
+
+      if (!silent)
+      {
+        System.out.println("=================");
+        System.out.println("Table   : " + table.getName());
+      }
+      if (verbose)
+      {
+        System.out.println("Catalog : " + table.getCatalog());
+        System.out.println("Schema  : " + table.getSchema());
+      }
+
+      Map<Set<String>, String> indexedColumns = new HashMap<Set<String>, String>();
+      Map<Set<String>, String> uniqueColumns = new HashMap<Set<String>, String>();
+      
+      String pkName = null;
+      Set<String> fkNames = new HashSet<String>();
+
+      if (verbose)
+      {
+        System.out.println("Database information");
+        System.out.println("--------------------");
+      }
+      
+      TableInfo tiDb = null;
+      try
+      {
+        tiDb = new TableInfo(table, metaData);
+      }
+      catch (SQLException ex)
+      {
+        throw new BaseException(ex);
+      }
+
+      if (tiDb != null)
+      {
+        if (verbose)
+        {
+          // Write all columns
+          for (ColumnInfo ci : tiDb.getColumns())
+          {
+            System.out.println("  Column       : " + ci);
+          }
+          
+          // Write primary key
+          System.out.println("  Primary key  : " + tiDb.getPrimaryKey());
+        
+          // Write foreign keys
+          for (ForeignKeyInfo fk : tiDb.getForeignKeys())
+          {
+            System.out.println("  Foreign key  : " + fk);
+          }
+        }
+      
+        // Write indexes and unique constraints
+        for (IndexInfo ii : tiDb.getIndexes())
+        {
+          if (!silent)
+          {
+            if (ii.isUnique())
+            {
+              System.out.println("  Unique       : " + ii);
+            }
+            else
+            {
+              System.out.println("  Index        : " + ii);
+            }
+          }
+          
+          boolean safeToDrop = tiDb.safeToDrop(ii);
+
+          String dropSql = dbEngine.getDropIndexSql(
+            table.getCatalog(), table.getSchema(), table.getName(), 
+            ii.getName(), ii.isUnique());
+          boolean actionDrop = dropIndexes && safeToDrop;
+
+          if (!silent)
+          {
+            System.out.println("    Safe drop  : " + safeToDrop);
+            System.out.println("    DROP-SQL   : " + dropSql);
+            System.out.println("    Actions    : " + (actionDrop ? "DROP " : ""));
+          }
+          
+          if (actionDrop )
+          {
+            Statement st = null;
+            try
+            {
+              st = connection.createStatement();
+              if (actionDrop) 
+              {
+                log.info("Dropping index: + " + dropSql);
+                st.executeUpdate(dropSql);
+              }
+              connection.commit();
+              st.close();
+              st = null;
+            }
+            catch (SQLException ex)
+            {
+              log.error("Exception", ex);
+              throw new BaseException(ex);
+            }
+            finally
+            {
+              if (st != null)
+              {
+                try
+                {
+                  st.close();
+                }
+                catch (Throwable t)
+                {
+                  log.error("Exception", t);
+                }
+              }
+            }
+          }
+          
+        }
+      }
+      
+      if (!silent)
+      {
+        System.out.println("=================");
+        System.out.println("");
+      }
+    }
+  }
+  
+  
+  /**
     Prints a lot of useful stuff about the database to standard output.
     For each table the output includes information about columns, indexes,
@@ -1733 +1908 @@
         System.out.println("Schema  : " + table.getSchema());
       }
-
+  
       Map<Set<String>, String> indexedColumns = new HashMap<Set<String>, String>();
       Map<Set<String>, String> uniqueColumns = new HashMap<Set<String>, String>();
@@ -1739 +1914 @@
       String pkName = null;
       Set<String> fkNames = new HashSet<String>();
-
+  
       if (verbose)
       {
@@ -1755 +1930 @@
         throw new BaseException(ex);
       }
-
+  
       if (verbose && tiDb != null)
       {
@@ -1812 +1987 @@
         }
       }
-
+  
       // Write indexes and unique constraints
       for (IndexInfo ii : tiHib.getIndexes())
@@ -1831 +2006 @@
         boolean exists = dbName != null;
         boolean safeToDrop = exists && tiDb.safeToDrop(ii);
-
+  
         String dropSql = exists ? 
-          dbEngine.getDropIndexSql(null, null, table.getName(), dbName, ii.isUnique()) : "";
+          dbEngine.getDropIndexSql(table.getCatalog(), table.getSchema(), 
+            table.getName(), dbName, ii.isUnique()) : "";
         String createSql = 
-          dbEngine.getCreateIndexSql(null, null, table.getName(), table.getName() + "_" + ii.getName(), 
+          dbEngine.getCreateIndexSql(table.getCatalog(), table.getSchema(),
+            table.getName(), table.getName() + "_" + ii.getName(), 
           ii.getColumns(), ii.isUnique());
         boolean actionDrop = dropIndexes && exists && safeToDrop;
@@ -1899 +2076 @@
     }
   }
-  
+
 
 }

branches/2.4-stable/src/install/net/sf/basedb/install/InitDB.java

@@ -24 +24 @@
 */
 package net.sf.basedb.install;
+
+import java.util.Arrays;
+import java.util.List;
 
 import net.sf.basedb.core.Application;
@@ -94 +97 @@
         Application.stop();
       }
+      else if ("dynamicdb".equals(cmd))
+      {
+        List<String> opt = Arrays.asList(args);
+        boolean verbose = opt.contains("-v");
+        boolean drop = opt.contains("-dropindexes");
+        Application.start(false);
+        HibernateUtil.dynamicDbIndexes(verbose, false, drop);
+        Application.stop();
+      }
       else 
       {
         System.out.println("Unknown command: " + cmd);
-        System.out.println("Usage: InitDB cmd");
-        System.out.println("       cmd: install | update | info | updateindexes | dropindexes");
+        System.out.println("Usage: InitDB cmd <options>");
+        System.out.println("    cmd: install | update | info | updateindexes | dropindexes | dynamicdb");
+        System.out.println("options: ");
+        System.out.println("           -v : Verbose output (if cmd = updateindexes, dropindexs or dynamicdb)");
+        System.out.println(" -dropindexes : Drop indexes when cmd = dynamicdb");
       }
     }