Distributed statistics

The distributed statistics feature allows OpenX to scale by moving some of the load from the central database to web servers. If you are using OpenX with one web server it has to deal with all tasks on one machine. Using many web servers allows you to split delivery and admin/maintenance. When using distributed statistics, the delivery web servers log raw data (impressions/clicks, etc.) into a local MySQL database and migrate it periodically into the central database. MySQL replication is used to replicate data from the central database into the delivery web servers.

How to set up MySQL replication

It is relatively easy to set up replication using MySQL, but it is important that you read all of the replication documentation from the MySQL website before attempting configuration!

The following is a brief summary of how to enable replication using MySQL:

• Enable binary logging on the master:
  

  [mysqld]
  log-bin = mysql-bin
  server-id = 1
  

  You might want to restrict the binary log to only your OpenX database; this can help reduce the amount of data in the binary log and prevent slaves from being dropped if non-OpenX databases fall out of sync: binlog-do-db = your_openx_database_name

• Create an account on the master server that the slave server can use to connect. This account must be given the REPLICATION SLAVE privilege:
  

  mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicant' identified by 'secret';
  

  Security tip For a production system you probably want to restrict the replication account to a particular host or subnet.

• Flush all the tables and block write statements on the master by executing a FLUSH TABLES WITH READ LOCK statement:
  

  mysql> FLUSH TABLES WITH READ LOCK;
  

• Get the value of the binlog:
  

  mysql> SHOW MASTER STATUS\G
  

• Create a dump of the database and copy it to the slaves.
• Once you have the snapshot and have taken a note of the binlog position, release the master:
  

  mysql> UNLOCK TABLES;
  

• Add the unique server-id to the slave:
  

  [mysqld]
  server-id=2
  

• Configure the slave to Replicate the OpenX database but not all the tables:
  

  [mysqld]
  replicate-do-db             = openx
  replicate-wild-ignore-table = openx.%data_%
  replicate-wild-ignore-table = openx.%tmp_%
  replicate-wild-ignore-table = openx.%lb_local%
  replicate-wild-ignore-table = mysql.%
  

• Load the dump file into the delivery server's local database. For performance reasons, the data_raw_% tables on the slaves should be of type MyISAM. If the master server is using InnoDB then the table type in the dump file will also be InnoDB. You can either modify the dump file by hand and change the raw tables to be MyISAM, or you can run an ALTER TABLE command after loading the dump file.
• Make the slave replicate:
  

  mysql> CHANGE MASTER TO
      -> MASTER_HOST='master_host_name',
      -> MASTER_USER='replication_user_name',
      -> MASTER_PASSWORD='replication_password',
      -> MASTER_LOG_FILE='recorded_log_file_name',
      -> MASTER_LOG_POS=recorded_log_position;
  
  mysql> START SLAVE;
  
  mysql> SHOW SLAVE STATUS;
  

• You can add as many slaves as you want by copying the dump file to other slaves and starting replication from the same binlog position.

Admin box configuration

• Database settings should point to the main database.
• The enable flag in the [ADMIN:lb] section of the config file should be set to false.

Delivery boxes configuration

The configuration file should match the admin one, but:

• database settings should point to the local database:
  

  [database]
  type=mysql
  host=localhost
  port=3306
  username=user
  password=secret
  name=openx
  

• Database settings in the [ADMIN:lb] section should point to the main database.
• The enable flag in the [ADMIN:lb] section should be set to true.
• The compactStats flag in the [ADMIN:lb] section should be set to true, and you should review the compactStatsGrace setting. This setting is in seconds. OpenX will prune any raw data older than this number of seconds from your delivery database. Because stats are stored on your admin box, you will most likely want to make this value a relatively small number so that your delivery box stores only a small amount of raw data.
  

  [lb]
  enabled=1
  hasSuper=0
  type=mysql
  host=10.0.0.10
  port=3306
  username=user
  password=password
  name=openx
  compactStats=1
  compactStatsGrace=86400
  

• The enabled flag in the [ADMIN:ui] section should be set to false.
  

  As of OpenX 2.6, if you set "hasSuper" to true then the code will execute the SQL "SET SQL_LOG_BIN = 0" before inserting the data into the central database server. This will reduce the replication load with MySQL. Of course, your username that inserts the data must have the SUPER privilege on the main database to be able to execute this command.

Maintenance

• The delivery boxes should run maintenance-distributed.php to store statistics in the main database. Here is an example cron configuration:
  

  # Run distributed maintenance every 5 minutes
  */5 * * * * /usr/local/php/bin/php -q /var/www/html/openx/scripts/maintenance/maintenance-distributed.php example.com
  

• The main box should run maintenance.php hourly, or as soon as distributed stats are loaded:
  

  # Run maintenance every 5 minutes, but slightly after the 5 minute boundaries, to allow time for
  # the distributed maintenance scripts to complete the insertion of data into the central DB
  4,9,14,19,24,29,34,39,44,49,54,59 * * * * /usr/local/php/bin/php -q /var/www/html/openx/scripts/maintenance/maintenance.php example.com
  

  Warning Please note that running maintenance more often than once per hour on the main box is only supported on OpenX 2.6 and higher. You will also need to ensure that your configuration settings for the Operation Interval are set appropriately.

Upgrades

1. Upload the code to main and delivery boxes.
2. Switch off maintenance on main and delivery boxes.
3. Change the (local) ADMIN:database password to an incorrect one in every delivery box config file, to enable permanent caching.
4. Copy the config files to the new directory.
5. Run upgrade on main box.
6. Check that non-replicated delivery database tables are OK.
7. Upgrade every delivery box to the new version:
   • Diff the new admin config file.
   • Update delivery server config file with any changes.
   • touch var/INSTALLED and remove var/UPGRADE on delivery boxes.
   • Ensure that permissions are correct.
   • Switch directories.
8. Restore passwords on delivery boxes.
9. Restore maintenance.