Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Source data retention #72

Closed
dennissiemensma opened this issue Mar 2, 2016 · 32 comments
Closed

Source data retention #72

dennissiemensma opened this issue Mar 2, 2016 · 32 comments
Assignees
Milestone

Comments

@dennissiemensma
Copy link
Member

This is my current database disk usage after almost three months, containing 700+ K readings:

 public.dsmr_datalogger_dsmrreading             | 104 MB
 public.dsmr_consumption_electricityconsumption | 15 MB
 public.dsmr_consumption_gasconsumption         | 512 kB
 public.dsmr_stats_hourstatistics               | 496 kB
 public.dsmr_weather_temperaturereading         | 184 kB

That averages around 400 MB for each year. It should be possible to delete source readings after:

  • one month
  • three months
  • six months
  • one year
  • none (disabled)

Retention should be checked once a day or once a week.

@dennissiemensma dennissiemensma added this to the 1.x (long term) milestone Mar 2, 2016
@dennissiemensma dennissiemensma modified the milestones: Unplanned / long term, 1.2.x Mar 18, 2016
@dennissiemensma
Copy link
Member Author

I thought of another solution. There is not need to dispose of all the source readings, as we could just wipe 99% of them in each hour. The absolute minimum for hourly stats is ofcourse at least one reading per hour.

If we would keep at least one reading of each hour passed, we could recalculate all statistics retroactive, if one needed to do so. As there are around 350+ readings in each hour, the data would be 350 times smaller. This should speedup backups, restores and migrations greatly. And reduce the disk space required.

The only downside is that the disk space freed might be reused (randomly) for new readings, possibly reducing the lifetime of the SD card. However, this would also be the situation when wiping all readings anyway.

@dennissiemensma dennissiemensma modified the milestones: 1.4, 1.2 May 12, 2016
@dennissiemensma dennissiemensma modified the milestones: 1.4, 1.6 Nov 7, 2016
@dennissiemensma dennissiemensma modified the milestones: 1.4, 1.6 Nov 20, 2016
@dennissiemensma dennissiemensma changed the title Source data retention (dsmr_datalogger_dsmrreading) Source data retention (DSMR-readings) Nov 20, 2016
@dennissiemensma
Copy link
Member Author

Update of my internal stats of disk used so far:

 public.dsmr_datalogger_dsmrreading             | 491 MB
 public.dsmr_consumption_electricityconsumption | 85 MB
 public.dsmr_stats_hourstatistics               | 1128 kB
 public.dsmr_consumption_gasconsumption         | 1064 kB
 public.dsmr_weather_temperaturereading         | 776 kB
 public.dsmr_stats_daystatistics                | 104 kB

Compared to March this year:

 public.dsmr_datalogger_dsmrreading             | 104 MB
 public.dsmr_consumption_electricityconsumption | 15 MB
 public.dsmr_consumption_gasconsumption         | 512 kB
 public.dsmr_stats_hourstatistics               | 496 kB
 public.dsmr_weather_temperaturereading         | 184 kB

As I'm almost reaching the three million milestone DSMR-reading count: 2.973.738

@dennissiemensma dennissiemensma self-assigned this Nov 22, 2016
@dennissiemensma dennissiemensma modified the milestones: 1.5, 1.4 Nov 24, 2016
@dennissiemensma
Copy link
Member Author

I've looked into this for some hours now, but it's quite complex to implement semi-retention for discarding data, while still keeping 1 data point per hour. I'll won't have this delay the 1.4 release, so next try in v1.5.

@dennissiemensma
Copy link
Member Author

Note to self to also update the docs, as I refer to this feature multiple times.

@dennissiemensma dennissiemensma removed their assignment Nov 28, 2016
@dennissiemensma
Copy link
Member Author

dennissiemensma commented Dec 8, 2016

Since v1.4 was released, I worked on the Django upgrade for v1.5. This added some nice feature Django lacked before. I'm now able to query exactly what I'm looking for, without hacks.

Django's new TruncHour ORM function allows me to extract the hour of each reading (by truncating the 'minutes' and 'seconds' to zero). Then I group them, summarize their count per hour and filter out any hours which only (or already) have one reading left.

DsmrReading.objects.filter(
    timestamp__lt=retention_date
).annotate(
    item_hour=TruncHour('timestamp')
).values('item_hour').annotate(
    item_count=Count('id')
).order_by().filter(
    item_count__gt=1
)

This is so nice as it allows me to retroactively iterate through any data requiring retention. And I can limit that as well to prevent any hanging background processes.

@dennissiemensma
Copy link
Member Author

Committed an initial setup in 5469794. To be continued.

@dennissiemensma dennissiemensma added this to the 1.6 milestone Dec 19, 2016
dennissiemensma added a commit that referenced this issue Jan 10, 2018
@dennissiemensma
Copy link
Member Author

Eerste opzet is klaar. Het werkt goed voor PostgreSQL. Helaas nog niet voor MySQL, dus daar mag ik nog een blik op werpen.

@dennissiemensma
Copy link
Member Author

dennissiemensma commented Jan 11, 2018

Bij nader inzien werkt het wel voor MySQL, maar alleen wanneer de tijdzones geactiveerd zijn in de database.

dennissiemensma added a commit that referenced this issue Jan 11, 2018
@dennissiemensma
Copy link
Member Author

dennissiemensma commented Jan 11, 2018

Dit zou afdoende moeten zijn voor degenen die nog MySQL gebruiken:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql (als root)

dennissiemensma added a commit that referenced this issue Jan 11, 2018
dennissiemensma added a commit that referenced this issue Jan 13, 2018
dennissiemensma added a commit that referenced this issue Jan 13, 2018
dennissiemensma added a commit that referenced this issue Jan 13, 2018
@dennissiemensma
Copy link
Member Author

dennissiemensma commented Jan 13, 2018

Branch gemerged naar development. Ik zal nog wat laatste zaken controleren en daarna wil ik de pre-release eerst zelf een dagje draaien met retentie.
Kijken of de Raspberry/DB het uberhaupt aan kan qua performance. Op mijn ontwikkel-PC draait het namelijk, vanzelfsprekend, goed.

@dennissiemensma
Copy link
Member Author

dennissiemensma commented Jan 13, 2018

Uitgerold. De performance is nog niet super, maar ik kan daar weinig aan veranderen. Wellicht dat ik het doorvoeren van retentie laat toepassen voor bepaalde periodes nachts, zodat andere processen er ook geen last van hebben.

2018-01-13 13:09:24.282502+01:00: Calling backend services
Retention | Cleaning up: 2015-12-11 21:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-11 22:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-11 23:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 00:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 01:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 02:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 03:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 04:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 05:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 06:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 07:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 08:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 09:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 10:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 11:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 12:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 13:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 14:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 15:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 16:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 17:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 18:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 19:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-12 20:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2015-12-11 21:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-11 22:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-11 23:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 00:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 01:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 02:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 03:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 04:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 05:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 06:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 07:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 08:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 09:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 10:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 11:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 12:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 13:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 14:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 15:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 16:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 17:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 18:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 19:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2015-12-12 20:00:00+00:00 (ElectricityConsumption)
 - Processed reading: 6498150 @ 2018-01-13 13:08:43+01:00 (0.156 kW)
 - Processed reading: 6498151 @ 2018-01-13 13:08:53+01:00 (0.156 kW)
 - Processed reading: 6498152 @ 2018-01-13 13:09:03+01:00 (0.155 kW)
 - Processed reading: 6498153 @ 2018-01-13 13:09:13+01:00 (0.154 kW)
 - Processed reading: 6498154 @ 2018-01-13 13:09:23+01:00 (0.157 kW)
 - Processed reading: 6498155 @ 2018-01-13 13:09:33+01:00 (0.165 kW)
 - Processed reading: 6498156 @ 2018-01-13 13:09:43+01:00 (0.169 kW)
Command completed. Sleeping for 1 second(s)...

Hij heeft nu op een Pi 3 ongeveer een minuut nodig per dag opschoning. En dat is nog maar voor een relatief kleine database.

@dennissiemensma
Copy link
Member Author

dennissiemensma commented Jan 13, 2018

Voor nu zal ik hem bij mijzelf aan laten staan, zodat ik kan zien hoelang het duurt om een jaar aan gegevens op te schonen. Ter referentie mijn huidige database:

                    relation                    | total_size 
------------------------------------------------+------------
 public.dsmr_datalogger_dsmrreading             | 1159 MB
 public.dsmr_consumption_electricityconsumption | 266 MB
 public.dsmr_stats_hourstatistics               | 2064 kB
 public.dsmr_consumption_gasconsumption         | 1928 kB
 public.dsmr_weather_temperaturereading         | 1712 kB
 public.dsmr_stats_daystatistics                | 168 kB

6,438,276 metingen

dennissiemensma added a commit that referenced this issue Jan 13, 2018
@dennissiemensma
Copy link
Member Author

dennissiemensma commented Jan 13, 2018

Ik heb nu een hardcoded restrictie toegevoegd dat retentie alleen tussen 0:00 en 6:00 uitgevoerd wordt (wanneer ingeschakeld). Wellicht dat ik het later nog instelbaar maak, maar het heeft weinig zin retentie overdag door te voeren, gezien het veel resources kost (in het begin).

Vanmiddag heeft mijn eigen Pi zo'n 6 uur lang retentie kunnen doorvoeren. Van de 6,4M metingen zijn er inmiddels nog zo'n 3,4M over.

Gezien PostgreSQL zelf niet altijd loze ruimte vrijgeeft, doe ik nu handmatig een vacuumdb om te kijken wat het oplevert:

INFO:  vacuuming "public.dsmr_consumption_electricityconsumption"
INFO:  "dsmr_consumption_electricityconsumption": found 63783 removable, 619511 nonremovable row versions in 11737 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.53s/2.10u sec elapsed 18.26 sec.

INFO:  vacuuming "public.dsmr_consumption_gasconsumption"
INFO:  "dsmr_consumption_gasconsumption": found 0 removable, 18116 nonremovable row versions in 134 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.03s/0.02u sec elapsed 0.47 sec.

INFO:  vacuuming "public.dsmr_datalogger_dsmrreading"
INFO:  "dsmr_datalogger_dsmrreading": found 2370 removable, 3619955 nonremovable row versions in 84298 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 3.23s/15.20u sec elapsed 103.58 sec.

Met als concreet resultaat:

Vanochtend

                    relation                    | total_size 
------------------------------------------------+------------
 public.dsmr_datalogger_dsmrreading             | 1159 MB
 public.dsmr_consumption_electricityconsumption | 266 MB
 public.dsmr_stats_hourstatistics               | 2064 kB
 public.dsmr_consumption_gasconsumption         | 1928 kB
 public.dsmr_weather_temperaturereading         | 1712 kB
 public.dsmr_stats_daystatistics                | 168 kB

Nu

------------------------------------------------+------------
 public.dsmr_datalogger_dsmrreading             | 607 MB
 public.dsmr_consumption_electricityconsumption | 147 MB
 public.dsmr_stats_hourstatistics               | 2040 kB
 public.dsmr_consumption_gasconsumption         | 1904 kB
 public.dsmr_weather_temperaturereading         | 1688 kB
 public.dsmr_stats_daystatistics                | 160 kB

@dennissiemensma
Copy link
Member Author

Aankomende nacht heeft die dus opnieuw 6 uur de tijd om het restant op te schonen. Waarschijnlijk is die daarna helemaal bij. Morgen opnieuw kijken.

@dennissiemensma
Copy link
Member Author

dennissiemensma commented Jan 14, 2018

Vannacht is het restant opgeschoond. Er zijn, van de eerdere 6 miljoen metingen, nu nog maar een kleine 300.000 over.

Een vacuumdb schoont de laatste ruimte op en geeft onderstaande resultaten.

Disk usage was:

                    relation                    | total_size 
------------------------------------------------+------------
 public.dsmr_datalogger_dsmrreading             | 1159 MB
 public.dsmr_consumption_electricityconsumption | 266 MB
 public.dsmr_stats_hourstatistics               | 2064 kB
 public.dsmr_consumption_gasconsumption         | 1928 kB
 public.dsmr_weather_temperaturereading         | 1712 kB
 public.dsmr_stats_daystatistics                | 168 kB

Nu:

                   relation                     | total_size 
-------------------------------------------------+------------
 public.dsmr_datalogger_dsmrreading              | 47 MB
 public.dsmr_consumption_electricityconsumption  | 18 MB
 public.dsmr_stats_hourstatistics                | 2048 kB
 public.dsmr_consumption_gasconsumption          | 1904 kB
 public.dsmr_weather_temperaturereading          | 1688 kB
 public.dsmr_stats_daystatistics                 | 160 kB

Dat betreft dus 2 jaar aan metingen, sinds december 2015, waarbij de rententie momenteel op max één maand staat. De database-backup is nu ook lekker klein en vooral snel.

Bij gebruikers met een DSMRv5-meter zullen de verschillen vanzelfsprekend nóg groter zijn.

@dennissiemensma
Copy link
Member Author

Ik zal nog een howto voor vacuumdb toevoegen aan de docs, plus een waarschuwing van verliezen nauwkeurigheid bij hergenereren gegevens.

@dennissiemensma
Copy link
Member Author

Zojuist uitgebracht in release v1.12.

@mkruiver
Copy link

Vraagje mbt regenereren van statistieken: je gaf aan dat zolang men externe backups toepast de statistieken altijd beschikbaar blijven, maar, je kunt backups niet stapelen bij het restoren. Hoe gaat dit dan in zn werk?

@dennissiemensma
Copy link
Member Author

Ik doel daarbij op de dag- en uurstatistieken-tabellen die nooit opgeschoond zullen worden. Zolang je goede database backup's hebt, zul je die altijd kunnen restoren, ongeacht tot hoever de bron-metingen opgeschoond zijn.
Het enige nadeel daarbij is wel dat je nooit je dag- en uurstatistieken (eenvoudig) opnieuw kan genereren met bijvoorbeeld een andere prijs (als je dat om wat voor reden dan ook zou willen).

Een alternatief is geen retentie inschakelen en het handmatig beperken van de doorvoer aan metingen. Zie daarvoor #413. Alleen zal dat niet voor iedereen een werkbare/wenselijke situatie zijn.

@mkruiver
Copy link

Ik heb de retentie op één maand gezet. Vannacht tussen 1 en 7 (dus niet tussen 0 en 6!) heeft deze duidelijk gedraaid want de PVO output was bijna non-existent.

Raadt je aan om pas nadat het opruimen klaar is te vacuum-en? En moeten we dan een vacuum FULL ANALYZE doen?

@dennissiemensma
Copy link
Member Author

dennissiemensma commented Jan 15, 2018

Ik zou persoonlijk de vacuum pas doen wanneer hij helemaal klaar is. Het is namelijk een proces dat volgens mij de complete database vergrendelt en bij mij had die snel 5 a 10 minuten nodig, laat staan bij een grote database zoals die van jou.

Je kunt overigens in de logs van de backend vrij makkelijk zien waar die is gebleven én hoelang het duurt om één dag op te schonen (bij mij iets meer dan een minuut).

Als je zoekt op Retention in /var/log/supervisor/dsmr_backend.log zou je zoiets moeten zien:

2018-01-15 01:00:00.402471+01:00: Calling backend services
Retention | Cleaning up: 2017-12-17 07:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 08:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 09:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 10:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 11:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 12:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 13:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 14:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 15:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 16:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 17:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 18:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 19:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 20:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 21:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 22:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 23:00:00+00:00 (ElectricityConsumption)
Retention | Cleaning up: 2017-12-17 07:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2017-12-17 08:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2017-12-17 09:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2017-12-17 10:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2017-12-17 11:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2017-12-17 12:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2017-12-17 13:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2017-12-17 14:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2017-12-17 15:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2017-12-17 16:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2017-12-17 17:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2017-12-17 18:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2017-12-17 19:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2017-12-17 20:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2017-12-17 21:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2017-12-17 22:00:00+00:00 (DsmrReading)
Retention | Cleaning up: 2017-12-17 23:00:00+00:00 (DsmrReading)

Tot slot, qua type vacuum, ik heb tot nu toe gewoon een FULL gedaan (vacuumdb -a -v -f), maar je kunt kijken of dat uberhaupt noodzakelijk is.

@mkruiver
Copy link

Naar mijn mening is de beschrijving van deze feature in de docs niet helemaal correct. Je schrijft over het "inschakelen van retentie" terwijl de default ('rententie uit') juist alle gegevens bewaart. Deze beschrijving is eigenlijk verkeerd om.

@mkruiver
Copy link

Doet overigens niks af aan de functie zelf, dit is een zeer nuttige toevoeging!

dennissiemensma added a commit that referenced this issue Jan 19, 2018
@dennissiemensma
Copy link
Member Author

Bedankt voor je input. Ik heb het vooral beschreven vanuit het technisch oogpunt, echter klopt het dat het op meerdere manieren gelezen kan worden. Daarom heb ik het nu iets aangepast en mogelijk mag ik het nog verder herschrijven een volgende keer.

@mkruiver
Copy link

mkruiver commented Jan 19, 2018 via email

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants