Database cleaning

[Zsolt]

Hi,

our 'data_summary_ad_zone_assoc' table has got over 9000000 rows. Is there any way to clean it automatically? 

Can manual clean up cause any issues? 

We used this plugin: http://www.reviveadservermod.com/clean-statistics-for-particular-period, and it worked fine with the 'data_intermediate_ad' and the 'data_summary_ad_hourly' tables, but did nothing with the data_summary_ad_zone_assoc'.

[andrewatfornax]

Hi @Zsolt,

As a general rule, this should not be necessary, unless you are running into significant performance/space issues. 9 million rows is not something that should be an issue for MySQL, though - it's a pretty small number of rows.

The code base generally does already delete data that is not needed, so manually cleaning up tables can result in some historical stats etc. no longer working. It depends how important that is to you!

[Zsolt]

All of our campaigns have restarted this year and we don't need the old stats. So can we just delete the rows older than one year without any issies for the running campaigns? 

[2ge]

I am exactly looking for this information too. My revive_adserver database is taking now 17GB and it should be good to clean it. For example every stats which is older than 2017. Would be good to know how to do it, of course some script would be great.

 

[andrewatfornax]

On 20/02/2018 at 11:02 PM, Zsolt said:

All of our campaigns have restarted this year and we don't need the old stats. So can we just delete the rows older than one year without any issies for the running campaigns? 

Yeah, that shouldn't be an issue. If you can guarantee you don't need the historical data any more, take a backup, and delete, by all means!

[Zsolt]

Thank you for your help!

[2ge]

just add two cents, here are SQL queries to run to clean up a bit:

 

delete from ox_data_summary_ad_zone_assoc where expired < '2022-01-01';
optimize table ox_data_summary_ad_zone_assoc;

delete from ox_data_intermediate_ad where interval_end  < '2022-01-01';
optimize table ox_data_intermediate_ad;

delete from ox_data_summary_ad_hourly where date_time < '2022-01-01';
optimize table ox_data_summary_ad_hourly;

delete from ox_audit where updated < '2022-01-01';
optimize table ox_audit;