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
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.
Just to close the loop on this. Nice work.
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
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!
There is a native HA component for this now
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
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
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
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…
This question should be in the sql sensor thread
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
Did you ever figure this out?
I am using MariaDB and my file size sensor is not being updated either.