Changeset 5894


Ignore:
Timestamp:
Nov 30, 2011, 11:58:08 AM (10 years ago)
Author:
Nicklas Nordborg
Message:

References #1630: Migrate from MySQL to PostgreSQL

Some enhancements for the export:

  • Use a fetch size for JDBC. The default is 20000 but this can be configured in base.config -> migrate.export.fetch-size. Larger size can improve performance but uses more memory.
  • Write each file to a temporary file first. This means that an aborted/crashed export can be resumed where it left off (assuming that no changes have been made to the database).
  • A bit more efficient progress reporting and row counting.
File:
1 edited

Legend:

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

    r5893 r5894  
    125125      Migration exporter = new Migration(true, destination, session, progress);
    126126      exporter.setExportCompressed(Values.getBoolean(Config.getString("migrate.export.compress"), false));
     127      exporter.setFetchSize(Config.getInt("migrate.export.fetch-size", 20000));
    127128      HibernateUtil.doJdbcWork(session, exporter);
    128129      HibernateUtil.commit(tx);
     
    190191 
    191192  private boolean exportCompressed;
     193  private int fetchSize;
    192194  private boolean dropPrimaryKeyBeforeImport;
    193195  private boolean dropConstraintsBeforeImport;
     
    203205    this.dateTimeFormat = new DateFormatter("yyyy-MM-dd HH:mm:ss");
    204206    this.exportCompressed = false;
     207    this.fetchSize = 20000;
    205208    this.dropPrimaryKeyBeforeImport = true;
    206209    this.dropConstraintsBeforeImport = true;
     
    287290
    288291  /**
     292    Set the JDBC fetch size to use when fetching rows from the database.
     293    A higher value may improve performance but uses more memory. The
     294    default value is 20000.
     295    @param fetchSize The fetch size to use
     296    @see Statement#setFetchSize(int)
     297  */
     298  public void setFetchSize(int fetchSize)
     299  {
     300    this.fetchSize = fetchSize;
     301  }
     302 
     303  /**
    289304    Perform the export. In principle we support all databases that BASE supports,
    290305    but it may depend on the actual underlying SQL column types. The export
     
    374389     
    375390      // Get columns in sorted order
    376       SortedSet<ColumnInfo> columns =  new TreeSet<ColumnInfo>();
    377       columns.addAll(table.getColumns());
     391      SortedSet<ColumnInfo> tmp =  new TreeSet<ColumnInfo>(table.getColumns());
     392      ColumnInfo[] columns = tmp.toArray(new ColumnInfo[tmp.size()]);
    378393     
    379394      // File to store all column names
     
    382397      Writer writer = new OutputStreamWriter(FileUtil.getOutputStream(columnsFile), "UTF-8");
    383398      Statement st = connection.createStatement();
     399      st.setFetchSize(columns.length < 10 ? fetchSize * 5 : fetchSize);
    384400      StringBuilder select = new StringBuilder("SELECT ");
    385401      String separator = "";
     
    412428     
    413429      // Create output file
    414       OutputStream out = getOutputStream(exportFile);
     430      File tmpFile = new File(exportFile.getParentFile(), exportFile.getName() + ".tmp");
     431      OutputStream out = getOutputStream(tmpFile);
    415432     
    416433      // Setup streams and threading if using compression
     
    424441     
    425442      writer = new BufferedWriter(new OutputStreamWriter(out, "UTF-8"));
    426       int actualRows = 0;
     443      long actualRows = 0;
    427444      while (rows.next())
    428445      {
     
    430447        if (System.currentTimeMillis() > nextTick)
    431448        {
    432           progress.display((int)((actualExported * 100) / approxTotal),
     449          // Correct for actual vs. approximate mismatch
     450          if (actualRows > approxRows)
     451          {
     452            approxTotal += actualRows - approxRows;
     453            approxRows = actualRows;
     454          }
     455          progress.display((int)(((actualExported+actualRows) * 100) / approxTotal),
    433456              tableName + ": exporting... (" + actualRows + " of " + approxRows + " rows done)");
    434457          nextTick = System.currentTimeMillis() + TICK_INTERVAL;
     
    436459       
    437460        // Write the current row
    438         int i = 1;
    439         for (ColumnInfo c : columns)
    440         {
    441           if (i > 1) writer.write("\t");
    442           writer.write(formatAndEscape(rows.getObject(i), c));
    443           ++i;
     461        int index = 0;
     462        while (index < columns.length)
     463        {
     464          if (index > 0) writer.write("\t");
     465          ColumnInfo c = columns[index];
     466          writer.write(formatAndEscape(rows.getObject(++index), c));
    444467        }
    445468        writer.write("\n");
    446469       
    447         // Keep track of rows
     470        // Keep track of exported rows
    448471        actualRows++;
    449         actualExported++;
    450         if (actualRows > approxRows)
    451         {
    452           // Increase the approximate row count
    453           approxTotal++;
    454           approxRows++;
    455         }
    456        
    457       }
    458       if (actualRows < approxRows)
     472      }
     473     
     474      actualExported += actualRows;
     475      // Correct for actual vs. approximate mismatch
     476      if (actualRows != approxRows)
    459477      {
    460478        // Decrease the approximate total row count
     
    463481
    464482      // Done with this table...
     483      writer.flush();
     484      writer.close();
     485      tmpFile.renameTo(exportFile);
     486
    465487      progress.display((int)((actualExported * 100) / approxTotal),
    466488          tableName + ": complete (" + actualRows + " rows)                      \n");
    467       writer.flush();
    468       writer.close();
    469489    }
    470490   
     
    871891  {
    872892    OutputStream out = FileUtil.getOutputStream(file);
    873     if (file.getName().endsWith(".gz"))
     893    if (file.getName().contains(".gz"))
    874894    {
    875895      out = new GZIPOutputStream(out, 8192);
Note: See TracChangeset for help on using the changeset viewer.