File size sensor

Feature Requests
1

There are quite a few posts where people want to know the size of their HA database on disk. This feature request is for a sensor to display the size (and perhaps other statistics in attributes) of the db file.
I’m posting this as a feature request, although I will probably implement it myself, so any other thoughts on how this sensor should function, please post them here.
Cheers

2

I tried to work on this a bit but am getting stuck.

  - platform: command_line
    name: Database file size
    command: 'ls -l --b=MB  home-assistant_v2.db | cut -d " " -f5'

The ls command listed returns the correct size of my database file. However, the HA user cannot see this file, or at least I cannot figure out how to point to the file correctly. Maybe someone can come along and point out how to get to the file.

Downside to this approach is the returned value is “##M” so “10M” for ten megabytes. This is fine and will display in HA but you won’t get any graphing.

3

Just to close the loop on this. Nice work.

4

This version supports graphing

  • platform: command_line # DB file size
    name: HA DB File Size
    command: “du -m /home/homeassistant/.homeassistant/home-assistant_v2.db | cut -f1”
    unit_of_measurement: ‘MB’
    value_template: ‘{{ value | int - 1 }}’
    scan_interval: 3600
5

In case anyone uses MySQL here is the sensor I use.

  - platform: sql
    db_url: mysql://MyHassAccount:MyPassword@localhost/My_Hass_DB
    scan_interval: 7200
    queries:
      - name: hass_db_size
        query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="My_Hass_DB" GROUP BY table_schema'
        column: 'value'
        unit_of_measurement: MiB

This assumes you use MySQL on the same server with a database named My_Hass_DB using account MyHassAccount and password MyPassword

Someone else wrote the query but I’m not sure who. So, kudos to whoever wrote it!

6

There is a native HA component for this now

3 Likes
7

And here my version for PostgreSQL.

- platform: sql
  db_url: postgres://@/hass
  queries:
    - name: db_size
      query: "SELECT (pg_database_size('hass')/1024/1024) as db_size;"
      column: db_size
      unit_of_measurement: MB
8

Anyone have trouble getting this to update with a MySQL Dayabase? I have scan interval at 3600 but only seems to update after a reboot of entire computer

9

I always have some troubles with a mysql, I don’t know a more buggy database :roll_eyes:

10

@Bartem try the sql sensor.

11

I am using one of the sql sensor examples from higher up in the thread. I’ll have to see which one when I get to my computer (one didn’t work at all) and I think it’s the second one I’m using and it works but never refreshes. I’ll check the doc example again

EDIT: Here is the one I’m using

- platform: sql
  db_url: mysql://homeassistant:######@localhost/hass_db
  scan_interval: 3600
  queries:
    - name: hass_db_size
      query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="hass_db" GROUP BY table_schema'
      column: 'value'
      unit_of_measurement: MB
12

I was using mysql and now I migrated to MS SQL which by the way is faster on the same QNAP NAS.
So, I have problem how to calculate database size…

Do anybody know how to define db_url and query to read database size on ms sql server?

Current working record:

mssql+pyodbc://USER:PASSWORD@IP/DATABASE?charset=utf8;DRIVER={FreeTDS};Port=1433;

Previous working mysql database size calculation:

  - platform: sql
    db_url: mysql://USER:PASSWORD@IP:3306/DATABASE
    scan_interval: 7200
    queries:
      - name: HA MySQL DB size
        query: 'SELECT table_schema "ha", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="ha" GROUP BY table_schema'
        column: 'value'
        unit_of_measurement: MiB

Thank you for your help…

13

This question should be in the sql sensor thread

14

Newer mind, I figer it out… here is code if anybody would need it:

  - platform: sql
    db_url: mssql+pyodbc://USERNAME:PASSWORD@IP/DATABASENAME?charset=utf8;DRIVER={FreeTDS};Port=1433;
    scan_interval: 7200
    queries:
      - name: HA MSSQL DB size
        query: "SELECT SUM(size)*8/1024 AS [size] FROM sys.databases JOIN sys.master_files on sys.databases.database_id=sys.master_files.database_id where sys.databases.name = 'DATABASENAME' GROUP BY sys.databases.name"
        column: 'size'
        unit_of_measurement: MiB
1 Like
15

Did you ever figure this out?
I am using MariaDB and my file size sensor is not being updated either.