How to configure Distributed Statistics in Openx

Ver este post en español

People at Openx use “Distributed Statistics” as an option to scale the service, but in my opinion the documentation is confuse or wrong, as you can see at this message I wrote as “raistlink”.

Distributed Statistics works like this (as I configured):

• both computers serve banners
• only one of them (or another aditional computer) works also as the admin. I will call “master” to the admin computer and “slave” to the other.
• only one computer executes the maintenance process that calculates the probability of the zones
• the replication makes the slave machine revieve those probalities
• the slave machine sends it’s activity to the master executing the maintenance-distributed instead of maintenance

It’s mandatory that you configure crones in the way that when the hourly execution of the maintenance process at the master is running, the activity from the slave has been recieved, in other case this activity will be lost. That’s why, as we will see in a moment, the master executes the maintenance process at the minute 15 everu hour and the slave executes the maintenance-distributed every five minutes.

Well, I got it at the end so I’m going to explain a bit how I did it.

The start point is two computers under a balancer serving banners under the same url, lets say my.adserver.com.

Master:

• Stop apache
• be sure maintenance is not running and stop the cron
• Stop mysql
• Set this at my.conf

server-id = 1
log-bin=mysql-bin

• Start mysql
• GRANT REPLICATION SLAVE ON *.* TO ’slave_user’@'%’ IDENTIFIED BY ‘XXXX’;

where XXXX is the password used by the slave at replication for connecting to the master.

• GRANT ALL PRIVILEGES ON *.* TO ‘root’@'%’ IDENTIFIED BY ‘YYYY’;

where YYYY is the password used by the slave in order to send its stats to the master
As you can see you can use two different passwords, no problem.

• FLUSH PRIVILEGES;
• show master status. TAKE NOTE OF THESE VALUES, YOU’LL NEED THEM AFTER

mysql-bin.000001 | 417

• make a dump

mysqldump -u root -p –databases openx_2_8_10 > /tmp/openx_2_8_10-201302071719.sql

• start apache
• execute once again show master status for testing purposes

mysql-bin.000001 | 24995895
the number has changed, so the master is replicating

Slave:

• drop openx database
• load the master dump at slave

mysql -u root -pXXXX < "/tmp/openx_2_8_10-201302071719.sql"

• execute these alter commands at openx database

ALTER TABLE ox_data_raw_ad_click ENGINE=MyISAM;
ALTER TABLE ox_data_raw_ad_impression ENGINE=MyISAM;
ALTER TABLE ox_data_raw_ad_request ENGINE=MyISAM;
ALTER TABLE ox_data_raw_tracker_impression ENGINE=MyISAM;
ALTER TABLE ox_data_raw_tracker_variable_value ENGINE=MyISAM;
In fact I don’t see that the system use this tables but…

• Set these values at my.conf

server-id = 2
log-bin=mysql-bin
(is order to work as a master if needed)
replicate-wild-ignore-table=openx_2_8_10.%data_%
replicate-wild-ignore-table=openx_2_8_10.%tmp_%
replicate-wild-ignore-table=openx_2_8_10.%lb_local%
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=openx_2_8_10.%ox_log_maintenance_priority% (because of a problem a had later)
replicate-wild-ignore-table=openx_2_8_10.%ox_session% (because of a problem a had later)

• start mysql
• check if the slave can access the master whith this command for example

mysqldump –host=host_master -u root -pYYYY –databases openx_2_8_10 > /tmp/openx_2_8_10-201302080959.sql

• STOP SLAVE;
• CHANGE MASTER TO MASTER_HOST=’host_master’, MASTER_USER=’slave_user’, MASTER_PASSWORD=’XXXX’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=417;

(use the values noted before)

• START SLAVE;
• SHOW SLAVE STATUS

As you can see, each time you execute this command, you obtain different values. When this values are the same than the master you will be synchronized.

Until now I’ve configured replication. The next thing is to set the slave to work as a delivery box, that is, it doesn’t work as admin and it send its activity (stats) to the master.

Master:

• If your domain is for example my.adserver.com then you have to create a cname at you domain provider for admin.my.adserver.com pointing to the master computer. I made the master to work as admin and serving banners at the same time.
• Disable automatic maintenance at the master from the admin console.
• configuration –> global settings –> Banner Delivery Settings –> “Admin Interface URL”

change my.adserver.com for admin.my.adserver.com
the system redirects to the new url.

• change tha maintenance cron to execute every hour at minute 15

Slave:

• Make these changes at openx conf file by hand
  1. set enabled at [ui] empty
  2. set enabled at [lb] to 1
  3. at [database] section set the local database. (It already was)
  4. at [lb] section set the master database

  type=mysql
  host=host_master
  socket=
  port=3306
  username=root
  password=YYYY
  name=openx_2_8_10

  5. change operationInterval at the slave in order to the maintenance-distributed be excecuted every 5 min.
• change maintenance-distributed permissions

chmod 777 [openx_home]/scripts/maintenance/maintenance-distributed.php

• set maintenance-distributed cron every 5 min.

You can add more slaves following the same instruccions or you can generate a new dump and set the new slave “listening from this point”, that is, from a new show master status.