Large homeassistant database files

Configuration
301

I did a similar thing, however I am using a remote PostgreSQL instance. I wrote a script that connects to the PostgreSQL instance and polls the home assistant database size and prints it in bytes.

Created a similar command_line sensor as @ntalekt

Maybe it will be useful to someone in the future :wink:

302

I’m using a PostgreSQL instance on my NAS for the Home Assistant recorder. To monitor its size, I use the recently added SQL Sensor:

- platform: sql
  db_url: !secret RECORDER_DB_URL
  queries:
    - name: "HA database size"
      query: "SELECT (pg_database_size('HOME_ASSISTANT_db')/1024/1024) as db_size;"
      column: "db_size"
      unit_of_measurement: MB
1 Like
303

That’s cool. I didn’t know that. :slight_smile: I should change my sensor to this.

304

Not sure what happened after an update to Qnap 4.3.4.0516, no changes have been made to my HA config, but HA can’t seem to connect to sql database, connection is refused.

192.168.200.210 is my Pi3 IP Address.

2018-04-16 21:19:49 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (_mysql_exceptions.OperationalError) (1129, "Host '192.168.200.210' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'") (Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)

306

is it better to use grafana/influxdb than MySQL?

307

It must have changed something…

Same setup as yours (RPi3 + QNAP with MySQL) and nothing is wrong :frowning:

308

Different purposes… can’t use grafana for the history component due to the relational structure used by this component (I actually looked into developing that feature)

309

Any idea how I can troubleshoot?

It works if I flush host or reboot Qnap and then restart HA, but If I restart HA anytime after that, I get this error.

310

You need to activate logging in the mysql server configuration in QNAP

I don’t know how to do that through the QNAP GUI… maybe you can login through the console, find mysql.conf and edit there?

311

Under Status->Advisor I see a couple of the following entries.

Issue:
Too many connections are aborted.

Recommendation:
Connections are usually aborted when they cannot be authorized. This article might help you track down the source.

Justification:
Aborted connections rate is at 4.44 per minute, this value should be less than 1 per hour

Used variable / formula:
Aborted_connects / Uptime

Test:
value * 60 * 60 > 1

312

From that information it is clear why it works after you restart mysql (reboot QNAP) -> basically restart rate limiter

Question now is why are you hitting that limit

run in your mysql CLI: show processlist

my default mysql install in QNAP shows 5 processes only.

313

image

314

Are you using SQL sensor with user root ? (works but is not advisable for security reasons)

If you are there are processes missing in there… mine looks like:

+----+-------+--------------------+-------+---------+------+-------+------------------+----------+
| Id | User  | Host               | db    | Command | Time | State | Info             | Progress |
+----+-------+--------------------+-------+---------+------+-------+------------------+----------+
| 26 | hass  | 192.168.1.20:36082 | hass  | Sleep   |    4 |       | NULL             |    0.000 |
| 27 | hass  | 192.168.1.20:36084 | hass  | Sleep   |  941 |       | NULL             |    0.000 |
| 28 | hass  | 192.168.1.20:36086 | hass  | Sleep   |  340 |       | NULL             |    0.000 |
| 29 | hass  | 192.168.1.20:36176 | hass  | Sleep   |    4 |       | NULL             |    0.000 |
| 31 | hass  | 192.168.1.20:54862 | NULL  | Query   |    0 | NULL  | show processlist |    0.000 |
+----+-------+--------------------+-------+---------+------+-------+------------------+----------+
315

There is also the following:
image

316

I am using the SQL Sensor, but not as root.

# SQL DB Sensor
- platform: sql
  db_url: mysql://hass_db:[email protected]:3306/hass_db
  queries:
    - name: HASS DB
      query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="hass_db" GROUP BY table_schema;'
      column: 'value'
      unit_of_measurement: kB
317

You need to install https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html to read that file, but it will include more info about what is going on for sure

318

try connecting to mysql from the command line using that user/password …

319

I can read the log within the GUI, but there are 67 log files/pages

320

Not too sure how to go about this.

I can connect to Qnap using putty with the admin account, which gives me the following:
image

321

type:

mysql -h localhost -u root -p

you will get a SQL shell where you can write SQL queries