Jump to content
Boschje

Database Explodes With Lots Of Statistics

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...