Can't filter logbook after upgrading to 2022.6

Configuration
9

Following @bdraco’s suggestion I enabled InnoDB on the QNAP. It’s a “newer” model with 4 GB RAM and a Celeron J1900. I did not follow @unsmart’s suggestion because future updates will most probably force me to redo his suggestion over and over. I wanted a sustainable config.

  1. Either use the Text Editor (QNAP app), SSH or SCP into the NAS
  2. navigate to /mnt/ext/opt/mariadb10/etc
  3. open mariadb.conf in your preferred editor
  4. underneath the port or at least in the [mysqld] section add:
default-storage-engine = INNODB
#default-storage-engine = MyISAM

and

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/data
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 512M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
#innodb_lock_wait_timeout = 50
  1. save it and restart the NAS (or restart MariaDB)
    /etc/init.d/mariadb10.sh stop & /etc/init.d/mariadb10.sh start

Your mariadb.conf may now look like somehow this:

image
image540×802 19.3 KB

  1. With PHPmyAdmin add a new DB called homeassistant
  2. in that DB create the user homeassistant and grant “him/her/they/them/…” all rights
  3. in HA configure the recorder to use db_url: mysql://[USER]:[PWD]@nas.yourdomain.local:3306/homeassistant?charset=utf8mb4

At the beginning I was worried about the performance impact because several threads (e.g.: How to enable INNODB as the default engine for MySQL - QNAP NAS Community Forum) mention “good reasons” why InnoDB is not enabled by default. My experience after 2 weeks: WOW.
Fast, just fast. Slightly more consumption in CPU (~5%) and RAM (~35%) but still absolutely within the specs of that NAS.

My suggestion: try to find your fitting values of buffer_pool, log_file_size and log_buffer_size, if these settings are oversized your MariaDB is prone to crash. Most probably in my case I would be able to go to innodb_buffer_pool_size = 2048M, but currently I am fine with the very fast access and I do not have SSDs in the NAS (but Hitachi enterprise class disks with 12 TB in a RAID-6)

Further reading:

And for the backup there is a script: [HOWTO] Make automatic backup of ALL MySQL / MariaSQL databases on QNAP V3.8 - Page 26 - QNAP NAS Community Forum
The end of the thread is helpful here, because OP does not maintain it anymore and ppl started adapting it for QTS5.

Anyone out there who also has 2 NAS and wants to dig into redundant DBs with proper hot-standby and failover DNS config? Not that we need it, but we could :nerd_face:
And anyone out there with other experiences regarding system variables of MariaDB on a QNAP, e.g.: innodb_additional_mem_pool_size?

Maybe this should become an own thread “how to configure MariaDB ond QTS5 (QNAP) as recorder DB”, what do you think?

This post is provided as it is, no warranty/liability, if your NAS becomes a brick, starts a BBQ in your rack or kills all WAF with the fans you hopefully took a good backup and won’t even dare to blame me

2 Likes