Database Explodes With Lots Of Statistics

[Boschje]

Hi,

 

i'm use openX / Revive for many year. One of the websites with a lot of traffic is getting some issues. A quick database backup is at this moment 5GB+....

 

most of this data is in de the table:

-ox_data_intermediate_ad (2.8GB)

- ox_data_summary_ad_hourly (1.4GB)

 

the question is, is there a possibility to optimize or cleanup this tables so the data isnt stored for years  etc?

 

a solution would be great.

 

 

ps at this moment im using version: Revive Adserver v3.0.2 .

 

ps2: there is also an error shown on the update page that it isnt possible to catch data if there is a new version.

[Erik Geurts]

From the numbers you've provided, it appears you've modified the operations interval from the default of 60 minutes to 30 minutes. This would explain why ox_data_intermediate_ad is twice as large as ox_data_summary_ad_hourly.

 

A 5GB database is in itself not unusual, although I realize it could create some problems if you're running it all on a not-so-powerful server.

[Boschje]

Hi Erik.

 

I dont know why i've modified this operation interval a few years ago. The server doesnt have any problems with the 5GB DB but the backups are quit large. How many years will be stored in these tables? endless or? If im correct there was a setting for that in the past but not any more.

[andrewatfornax]

I'm pretty sure that it will keep all stats, forever.

[riccardo.rodella]

I'm pretty sure that it will keep all stats, forever.

 

I also have all stats from the beginning:

mysql> select count(1) from ox_data_intermediate_ad;
+----------+
| count(1) |
+----------+
| 16393410 |
+----------+
1 row in set (0.00 sec)

First record:

mysql> select * from ox_data_intermediate_ad limit 1\G
*************************** 1. row ***************************
data_intermediate_ad_id: 2
              date_time: 2010-02-10 17:00:00
     operation_interval: 60
  operation_interval_id: 89
         interval_start: 2010-02-10 17:00:00
           interval_end: 2010-02-10 17:59:59
                  ad_id: 4
            creative_id: 0
                zone_id: 1
               requests: 0
            impressions: 156
                 clicks: 5
            conversions: 0
     total_basket_value: 0.0000
        total_num_items: 0
                updated: 2010-02-10 18:05:02
1 row in set (0.00 sec)

Last record:

mysql> select * from ox_data_intermediate_ad order by date_time desc limit 1\G
*************************** 1. row ***************************
data_intermediate_ad_id: 44052257
              date_time: 2014-04-22 14:00:00
     operation_interval: 60
  operation_interval_id: 62
         interval_start: 2014-04-22 14:00:00
           interval_end: 2014-04-22 14:59:59
                  ad_id: 16830
            creative_id: 0
                zone_id: 13
               requests: 0
            impressions: 31
                 clicks: 0
            conversions: 0
     total_basket_value: 0.0000
        total_num_items: 0
                updated: 2014-04-22 15:00:02
1 row in set (0.00 sec)