Changeset 5888


Ignore:
Timestamp:
Nov 24, 2011, 3:48:19 PM (10 years ago)
Author:
Nicklas Nordborg
Message:

References #1630: Migrate from MySQL to PostgreSQL

Implemented some of the performance enhancements discussed above. The migration now works like this:

  • Create tables (with primary key, since that is done by Hibernate). Foreign keys are skipped (just as before) but collected for later use.
  • Drop the primary key (keep info about name and columns)
  • Copy data into the database
  • Analyze the table
  • Re-create the primary key
  • When all tables in the static table has been filled with data, create foreign keys collected from the first step.
  • Continue with the dynamic database using the same procedure as above.
  • Let Hibernate check if there are items missing (used to create missing foreign keys, but shoul not do anything now)
  • Create missing unique indexes.

So... I hope this will perform better. We'll start another test run next week on a copy of the production server (by the way... it is till executing the same SQL.. 28 hours and counting)

In the meantime, I'll see if I can cleanup the code... it is beginning to look a bit messy...

Location:
trunk/src/core/net/sf/basedb/core
Files:
5 edited

Legend:

Unmodified
Added
Removed
  • trunk/src/core/net/sf/basedb/core/HibernateUtil.java

    r5876 r5888  
    633633      session.doWork(schemaGenerator);
    634634      HibernateUtil.commit(tx);
     635      if (mode == SchemaGenerator.Mode.MIGRATE)
     636      {
     637        // Save ignored SQL statements since we should create foreign keys in Migration.java
     638        // for performance reasons
     639        Application.getStaticCache().setDisabled(false);
     640        Application.getStaticCache().store("migrate.ignoredsql", schemaGenerator.getIgnoredSql(), 1000);
     641      }
    635642    }
    636643    catch (Exception ex)
  • trunk/src/core/net/sf/basedb/core/Migration.java

    r5883 r5888  
    5353import net.sf.basedb.core.dbengine.TableInfo;
    5454import net.sf.basedb.core.dbengine.TableInfo.ColumnInfo;
     55import net.sf.basedb.core.dbengine.TableInfo.PrimaryKeyInfo;
    5556import net.sf.basedb.core.hibernate.JdbcWork;
    5657import net.sf.basedb.core.hibernate.SchemaGenerator;
     
    394395    Perform the import. Importing is supported on PostgreSQL only.
    395396  */
     397  @SuppressWarnings("unchecked")
    396398  private void doImport(Connection connection)
    397399    throws SQLException, IOException
     
    482484      {
    483485        String tableName = table.getTable().getName();
    484         progress.display((int)((totalImported * 100) / totalFileSize),
     486        String fullTableName = table.getFullQuotedTableName(engine);
     487        progress.display((int)((totalImported * 80) / totalFileSize),
    485488            tableName + ": importing " + Values.formatBytes(importFile.length(), 2) + "...");
    486489       
     
    491494        String[] columns = sin.toString().split(",");
    492495       
     496        // Drop PRIMARY KEY if there is one
     497        PrimaryKeyInfo pkInfo = table.getPrimaryKey();
     498        if (pkInfo != null && pkInfo.getName() != null)
     499        {
     500          String dropPkSql = "ALTER TABLE " + fullTableName +
     501            " DROP CONSTRAINT " + engine.getQuotedName(pkInfo.getName());
     502          //System.out.println(dropPkSql);
     503          st.executeUpdate(dropPkSql);
     504        }
     505       
    493506        // Create COPY (<columns>) FROM '<filename>'
    494507        StringBuilder copy = new StringBuilder("COPY ");
    495         copy.append(table.getFullQuotedTableName(engine));
     508        copy.append(fullTableName);
    496509        copy.append(" (");
    497510        String separator = "";
     
    516529        st.executeUpdate(copy.toString());
    517530       
     531        // Analyze the table data (will increase performance)
     532        if (engine.analyzeAfterBatchOperation())
     533        {
     534          progress.append("...");
     535          String anayzeSQL = engine.getAnalyzeTableSql(table.getTable().getCatalog(), table.getTable().getSchema(), tableName);
     536          st.executeUpdate(anayzeSQL);
     537        }
     538       
     539        // Re-create primary key
     540        if (pkInfo != null && pkInfo.getName() != null)
     541        {
     542          StringBuilder createPkSql = new StringBuilder();
     543          createPkSql.append("ALTER TABLE ").append(fullTableName);
     544          createPkSql.append(" ADD CONSTRAINT ").append(engine.getQuotedName(pkInfo.getName()));
     545          createPkSql.append(" PRIMARY KEY (");
     546          separator = "";
     547          for (String col : pkInfo.getColumns())
     548          {
     549            createPkSql.append(separator);
     550            createPkSql.append(engine.getQuotedName(col));
     551            separator = ",";
     552          }
     553          createPkSql.append(")");
     554          progress.append("...");
     555          //System.out.println(createPkSql.toString());
     556          st.executeUpdate(createPkSql.toString());
     557        }
     558       
    518559        // Get max id (if 'id' column exists)
    519560        if (hasIdColumn)
    520561        {
    521           String sql = "SELECT max(id) FROM " + table.getFullQuotedTableName(engine);
     562          progress.append("...");
     563          String sql = "SELECT max(id) FROM " + fullTableName;
    522564          Query query = HibernateUtil.createSqlQuery(session, sql);
    523565          Integer maxId = HibernateUtil.loadData(Integer.class, query);
     
    548590    st.execute(sql);
    549591   
     592    // Load SQL statements that was ignored by Install.createTables()
     593    Application.getStaticCache().setDisabled(false);
     594    List<String> ignoredSql = (List<String>)Application.getStaticCache().load("migrate.ignoredsql", 1000);
     595    if (ignoredSql != null)
     596    {
     597      int progressOffset = (int)((totalImported * 80) / totalFileSize);
     598      progress.display(progressOffset, "Creating foreign keys...");
     599      int numStatements = ignoredSql.size();
     600      int numDone = 0;
     601      int lastPercent = 0;
     602      for (String isql : ignoredSql)
     603      {
     604        if (isql != null && isql.startsWith("alter table") && isql.contains("add constraint") && isql.contains("foreign key"))
     605        {
     606          int percent = progressOffset + (20*numDone) / numStatements;
     607          if (percent != lastPercent)
     608          {
     609            progress.display(percent, "--" + StringUtil.trimString(isql, 55));
     610            lastPercent = percent;
     611          }
     612          else
     613          {
     614            progress.append(".");
     615          }
     616          st.executeUpdate(isql);
     617        }
     618        numDone++;
     619      }
     620      Application.getStaticCache().delete("migrate.ignoredsql", 1000);
     621    }
     622   
    550623    // We need information about file-only platforms when importing dynamic data
    551624    RawDataTypes.initPlatforms(session);
     
    588661      }
    589662     
    590       progress.display((int)((totalImported * 100) / totalFileSize),
     663      progress.display(20+(int)((totalImported * 80) / totalFileSize),
    591664          tableName + ": importing " + Values.formatBytes(dynamicDataFile.length(), 2) + "...");
    592665     
    593666      // Create the virtual table
    594667      HibernateUtil.createVirtualTable(vdb, vTable);
     668     
     669      // Drop PRIMARY KEY if there is one
     670      Table dynamicTable = new Table(tableName);
     671      dynamicTable.setCatalog(Application.getDynamicCatalog());
     672      dynamicTable.setSchema(Application.getDynamicSchema());
     673      TableInfo table = new TableInfo(dynamicTable, metaData);
     674      PrimaryKeyInfo pkInfo = table.getPrimaryKey();
     675      String fullTableName = table.getFullQuotedTableName(engine);
     676      if (pkInfo != null && pkInfo.getName() != null)
     677      {
     678        String dropPkSql = "ALTER TABLE " + fullTableName +
     679          " DROP CONSTRAINT " + engine.getQuotedName(pkInfo.getName());
     680        //System.out.println(dropPkSql);
     681        st.executeUpdate(dropPkSql);
     682      }
    595683     
    596684      // Load columns from file
     
    626714      // Import the data
    627715      st.executeUpdate(copy.toString());
     716     
     717      // Analyze the table data (will increase performance)
     718      if (engine.analyzeAfterBatchOperation())
     719      {
     720        progress.append("...");
     721        String anayzeSQL = engine.getAnalyzeTableSql(Application.getDynamicCatalog(), Application.getDynamicSchema(), tableName);
     722        st.executeUpdate(anayzeSQL);
     723      }
     724     
     725      // Re-create primary key
     726      if (pkInfo != null && pkInfo.getName() != null)
     727      {
     728        StringBuilder createPkSql = new StringBuilder();
     729        createPkSql.append("ALTER TABLE ").append(fullTableName);
     730        createPkSql.append(" ADD CONSTRAINT ").append(engine.getQuotedName(pkInfo.getName()));
     731        createPkSql.append(" PRIMARY KEY (");
     732        separator = "";
     733        for (String col : pkInfo.getColumns())
     734        {
     735          createPkSql.append(separator);
     736          createPkSql.append(engine.getQuotedName(col));
     737          separator = ",";
     738        }
     739        createPkSql.append(")");
     740        progress.append("...");
     741        //System.out.println(createPkSql.toString());
     742        st.executeUpdate(createPkSql.toString());
     743      }
    628744     
    629745      // Done with this table...
  • trunk/src/core/net/sf/basedb/core/ReporterCloneTemplate.java

    r5887 r5888  
    322322  public List<ClonableProperty> getClonableProperties()
    323323  {
    324     Set<Map.Entry<String, ReporterClonePropertyData>> properties = getData().getProperties().entrySet();
    325     List<ClonableProperty> list = new ArrayList<ClonableProperty>(properties.size());
    326     for (Map.Entry<String, ReporterClonePropertyData> data : properties)
    327     {
    328       ClonableProperty cp = new ClonableProperty(data.getKey(), data.getValue());
    329       int insertIndex = Collections.binarySearch(list, cp);
    330       if (insertIndex < 0) list.add(-1-insertIndex, cp);
    331     }
    332     return list;
     324    return getClonableProperties(getData());
    333325  }
    334326 
     
    377369  }
    378370 
     371  static List<ClonableProperty> getClonableProperties(ReporterCloneTemplateData template)
     372  {
     373    Set<Map.Entry<String, ReporterClonePropertyData>> properties = template.getProperties().entrySet();
     374    List<ClonableProperty> list = new ArrayList<ClonableProperty>(properties.size());
     375    for (Map.Entry<String, ReporterClonePropertyData> data : properties)
     376    {
     377      ClonableProperty cp = new ClonableProperty(data.getKey(), data.getValue());
     378      int insertIndex = Collections.binarySearch(list, cp);
     379      if (insertIndex < 0) list.add(-1-insertIndex, cp);
     380    }
     381    return list;
     382  }
     383
     384 
    379385}
  • trunk/src/core/net/sf/basedb/core/VirtualTable.java

    r5885 r5888  
    266266      // We load the cloned properties from the template...
    267267      // The default columns should be included
    268       ReporterCloneTemplate cloneTemplate = vdb.getReporterCloneTemplate();
    269       List<ClonableProperty> properties = cloneTemplate.getClonableProperties();
     268      List<ClonableProperty> properties = ReporterCloneTemplate.getClonableProperties(vdb.getData().getReporterCloneTemplate());
    270269      VirtualColumn[] allColumns = new VirtualColumn[properties.size()];
    271270     
  • trunk/src/core/net/sf/basedb/core/hibernate/SchemaGenerator.java

    r5857 r5888  
    6969  private final Mode mode;
    7070  private final ProgressReporter progress;
    71 
     71  private List<String> ignoredSql;
     72 
    7273  /**
    7374    Create a new schema generator.
     
    100101    log.info("DbEngine: " + dbEngine);
    101102    log.info("Mode: " + mode);
     103   
     104    this.ignoredSql = new ArrayList<String>();
    102105   
    103106    Statement stmt = connection.createStatement();
     
    194197            if (numDone % interval == 0)
    195198            {
    196               progress.display((100 * numDone) / numStatements, "--" + StringUtil.trimString(actualSql, 45));
     199              progress.display((100 * numDone) / numStatements, "--" + StringUtil.trimString(actualSql, 55));
    197200            }
    198201            else
     
    204207        else
    205208        {
     209          ignoredSql.add(sql);
    206210          log.debug("Ignoring: " + sql);
    207211        }
     
    230234  }
    231235
     236  /**
     237    Get a list with the SQL statements that was ignored in the last run.
     238    @return A list or null if the generator hasn't been executed
     239    @since 3.1
     240  */
     241  public List<String> getIgnoredSql()
     242  {
     243    return ignoredSql;
     244  }
     245 
    232246  /**
    233247    The installation mode.
Note: See TracChangeset for help on using the changeset viewer.