Home

Tips for large database upgrades

Upgrading between major releases can mean that there are many database changes to be made. For example, upgrading from 2.0.x or 2.4.y to 2.6.z means that your database structure changes greatly and the upgrader must perform many tasks.

We recommend you review your PHP and web server settings to give the upgrader enough time & memory to complete. It is very important for such major upgrades to create a backup of your database before upgrading.

note: Minor upgrades like 2.6.0 to 2.6.2 do not normally involve database changes. In such cases your normal PHP & web server settings should be fine.

  1. You should check your database's tables for any that are larger than expected. You can use this thread as a guide:
    http://forum.openx.org/index.php?showtopic=503416641

    Some quick help for 2.4+ databases:

    • If the data_raw tables are quite large, you should review the compactStats and compactStatsGrace (in seconds) settings in your config file. If this is disabled, raw statistics will not be deleted.
      You can truncate the data_raw_% tables for any "old" data following a run of maintenance.

    • You can truncate the userlog table of old entries.
    • You should run the repair command on tables to avoid any corrupted tables. You should run the optimize command after deleting/truncating data.
  2. Create an empty file called NOBACKUPS in the /path/to/openx/var folder. This will prevent the installer from making its own backup tables, but rollback will not be possible. See: http://www.openx.org/faq/large-db
  3. You will want to alter server settings to allow PHP and Apache to run as long as is needed. You can reset these settings back after upgrade is complete. Note that Apache, not only PHP, has settings which needs to be altered.
    • Set the php.ini "memory_limit" directive to "-1" (no limit).
    • Set the php.ini "max_execution_time" directive to "0" (no limit).
    • Set the Apache "TimeOut" directive to "36000" (10 hours, as there does not appear to be a documented "no limit"
    • For the above settings, you do not need to use "no limit" but could instead use high settings - however we can not say how high the settings must be as it depends on your database size and other variables. Consult the PHP and apache (or other webserver) documentation for more information.
  4. Make sure to set the above settings back to standard after the upgrade has successfuly completed.