Implementing Distributed Statistics | OpenX

Implementing Distributed Statistics | OpenX:

This document provides technical details on how to implement an ad server farm using distributed statistics on OpenX 2.4.

The distributed statistics feature enables you to scale the OpenX ad server by moving some of the load from the central database to distributed web servers. If you are using OpenX with only one web server, one machine must handle all the tasks. With multiple web servers you can split the delivery and maintenance tasks. Web servers log raw data about impressions, clicks and so forth into the local MySQL database and migrate them every minute or so into a central database. Simultaneously, transaction data is replicated from the central database to web servers which further decreases the use of the central database for delivery tasks.

Database configuration

• Use a single master and multiple slaves configuration.
• Make all the OpenX tables available on the slave databases.
• Use the MyISAM engine for raw tables (ox_data_raw_*) for performance reasons.
• Replicate all the tables except the (ox_data_*), tmp_* tables and ox_lb_local tables.

How to setup MySQL replication

It is important that you read all of the replication documentation on the MySQL web site before starting this configuration.

To enable replication using MySQL:

• The [mysqld] log-bin=mysql-bin server-id=1 statement enables binary logging.
  You can restrict the binary log to your OpenX database only to reduce the amount of data in the log and prevent slaves from being dropped if non-OpenX databases become out of sync with the binlog-do-db = <Your OpenX database name> statement.
• Set up an account on the master server that the slave server can use to connect to it. You must give this account the REPLICATION SLAVE privilege by executing the mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicant' identified by 'secret'; statement.
• For a production system, restrict the account to a particular host or subnet.
• Flush all the tables and block write statements by executing a FLUSH TABLES WITH READ LOCK statement:
  mysql> FLUSH TABLES WITH READ LOCK;
• To get the value of the binlog, execute the mysql> SHOW MASTER STATUS; statement.
• Copy the binary data files to the slave server.
• When you have the snapshot and have taken a note of the binlog position release the master with the mysql> UNLOCK TABLES; statement.
• Add the server-id to the slave with the [mysqld] server-id=2 statement.
• Do not replicate the following tables:
  replicate-wild-ignore-table=%.%data_%
  replicate-wild-ignore-table=%.%tmp_%
  replicate-wild-ignore-table=%.%lb_local%
• Extract the contents of the data directory onto the data directory of the slave.
• Start the slave
• To make it replicate, execute the following statements:
  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;
  START SLAVE;
• You can add as many slaves as you want by using the same procedures to copy binary data to slaves.

Administration box configuration

The adminstration box requires the following settings:

• Point the database settings to the main database.
• Set the enable flag in the [lb] to false.

Delivery boxes configuration

The configuration file must match the file on the administration box, except:

• Point database settings to the local database.
• Point database settings in the [lb] section to the main database.
• Set the enable flag in the [lb] section to true.
• Set the uiEnabled flag in the [max] section to false.
• In the [lb] section, set compactStats to true and review the compactStatsGrace setting. OpenX will prune any raw data older than this value (X seconds) from your delivery database. Because statistics are stored on your administration box, you will probably want to set this value for a small timeframe so that your delivery box has a small amount of raw data.

Maintenance

• To store statistics in the administration database, the delivery boxes should run maintenance-distributed.php. OpenX recommends you run maintenance every fifteen minutes.
• The adminstration box should run maintenance.php hourly as soon as distributed statistics are loaded.

Upgrades

When you are performing an upgrade:

1. Change the database password to an incorrect one in the .conf.php file for every delivery box to enable permanent caching.
2. Upload the code to the adminstration box and the delivery boxes.
3. Switch off maintenance on the administration box and the delivery boxes.
4. Run the upgrade wizard on the administration box.
5. Check that non-replicated delivery database tables are correct .
6. Upgrade every delivery box to the new version.
   • Copy the new conf.php file over.
   • Reconfigure the distributed statistics parameters.
   • Ensure that permissions are correct.
   • Switch directories.
7. Restore maintenance.