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 multiple 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 the replication documentation on the MySQL website before attempting the configuration!
Here is a brief summary of how to enable replication using MySQL:
Enable binary logging on the master:
You might want to restrict the binary log to your OpenX database only. 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:
- Create an account on the master server that the slave server can use to connect. This account must be given the REPLICATION SLAVE privilege:
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:
- Get the value of the binlog:
- 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:
- Add the unique server id to the slave:
- Configure the slave to Replicate the OpenX database but not all the tables:
- Load the dump file into the delivery server's local database. For performance reasons, the data_raw_% tables on the slaves should be 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:
- 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:
- The enabled flag in the [ADMIN:ui] section should be set to false.
The delivery boxes should run maintenance-distributed.php to store statistics in the main database. Here is an example cron configuration:
- The main box should run maintenance.php hourly, or as soon as distributed stats are loaded:
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 Operaion Interval are set appropriately.
- Upload code to main and delivery boxes.
- Switch off maintenance on main and delivery boxes.
- Change (local) ADMIN:database password to an incorrect one in every delivery box config file (this is to enable permanent caching).
- Copy config files to new directory.
- Run upgrade on main box.
- Check non-replicated delivery database tables are OK.
- Upgrade every delivery box to 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
- Restore passwords on delivery boxes
- Restore maintenance.