With the last update you can finally adjust the parameters of the MariaDB addon and persist to a restart. Here is the change:
To have a starting point I share with you a template with the most useful parameters, all commented and also some examples to monitor directly from HA. Settings:
mariadb_server_args:
#############################################################
# InnoDB Buffer Pool Settings
# The buffer pool is where MariaDB caches table and index data
#############################################################
# Controls how much RAM is allocated to InnoDB to cache table and index data
# Higher values improve performance by reducing disk I/O
# Recommendation: 70-80% of available RAM for dedicated servers
- "--innodb_buffer_pool_size=12G"
#############################################################
# InnoDB Log Settings
# Controls how InnoDB handles transaction logging
#############################################################
# Size of the memory buffer InnoDB uses to write to the transaction logs
# Larger values reduce disk I/O for transaction heavy workloads
# If you see 'wait on innodb log buffer' in SHOW ENGINE INNODB STATUS, increase this
- "--innodb_log_buffer_size=32M"
# Size of each InnoDB redo log file
# Larger values improve performance but increase recovery time
# Should be large enough to handle peak write loads
- "--innodb_log_file_size=512M"
#############################################################
# InnoDB File and I/O Settings
# Configures how InnoDB handles file operations and I/O
#############################################################
# Stores each InnoDB table and its indexes in a separate .ibd file
# Enables features like table compression and faster truncate operations
# Recommended ON for better management and flexibility
- "--innodb_file_per_table=1"
# Controls how InnoDB flushes log buffer to disk
# 0 = write once per second (fastest but unsafe)
# 1 = write and flush at each commit (safest but slowest)
# 2 = write at commit, flush once per second (good compromise)
- "--innodb_flush_log_at_trx_commit=2"
# Specifies the method used for flushing files to disk
# O_DIRECT bypasses the operating system buffer cache
# Recommended for systems with battery-backed write cache
- "--innodb_flush_method=O_DIRECT"
# Number of I/O operations per second (IOPS) for background tasks
# Should match your storage device's capabilities
# For NVMe SSDs, values of 2000-4000 are reasonable
- "--innodb_io_capacity=2000"
# Maximum IOPS InnoDB can perform
# Used for burst situations, should be about 2x innodb_io_capacity
# Don't set too high to avoid excessive flushing
- "--innodb_io_capacity_max=4000"
# Number of background threads for read operations
# More threads can help with parallel read operations
# Recommended: 4-8 for modern multi-core systems
- "--innodb_read_io_threads=8"
# Number of background threads for write operations
# Separate from read threads to prevent read/write contention
# Recommended: equal to read_io_threads
- "--innodb_write_io_threads=8"
#############################################################
# InnoDB Optimization Settings
# Additional settings for performance optimization
#############################################################
# Keeps table statistics persistent across server restarts
# Helps optimizer make better decisions
# Recommended ON for stable workloads
- "--innodb_stats_persistent=1"
# Enables adaptive hash indexing for faster lookups
# Can improve performance up to 10% but uses extra memory
# Monitor usage through SHOW ENGINE INNODB STATUS
- "--innodb_adaptive_hash_index=1"
#############################################################
# Buffer and Cache Settings
# Memory allocations for various operations
#############################################################
# Memory allocated for sorting operations
# Used for ORDER BY and GROUP BY operations
# Increase if seeing many disk-based sorts in slow query log
- "--sort_buffer_size=4M"
# Buffer used for sequential table scans
# Larger values help with table scans but allocate per-thread
# Be careful increasing as it's allocated per-connection
- "--read_buffer_size=2M"
# Buffer used for reading rows in sorted order
# Used after sorting to read rows
# Also allocated per-thread, so keep reasonable
- "--read_rnd_buffer_size=2M"
# Buffer used for joins not using indexes
# Allocated per-join per-thread
# Increase if seeing many disk-based joins in EXPLAIN
- "--join_buffer_size=2M"
# Maximum size for internal in-memory temporary tables
# If a temp table exceeds this, MariaDB converts it to MyISAM
# Should match max_heap_table_size for consistency
- "--tmp_table_size=512M"
# Maximum size for user-created MEMORY tables
# Also affects internal temporary tables
# Should match tmp_table_size
- "--max_heap_table_size=512M"
#############################################################
# Connection and Cache Settings
# Controls connection handling and table caching
#############################################################
# Maximum number of simultaneous client connections allowed
# Each connection uses memory, so balance with available RAM
# Monitor 'Max_used_connections' status variable
- "--max_connections=512"
# Number of table handles to keep open
# Higher values reduce file open/close operations
# Monitor table_open_cache_hits/misses ratio
- "--table_open_cache=4000"
# Number of table definitions (frm files) to cache
# Reduces need to read .frm files from disk
# Should be higher than number of tables in all databases
- "--table_definition_cache=2000"
# How many threads to cache for reuse
# Helps reduce thread creation overhead
# Monitor threads_cached and threads_created status
- "--thread_cache_size=200"
# Maximum number of file descriptors server can open
# Should be higher than max_connections + table_open_cache
# Check 'open_files_limit' in SHOW VARIABLES
- "--open_files_limit=65535"
#############################################################
# Network Settings
# Configure network-related parameters
#############################################################
# Maximum size of one packet or generated/intermediate string
# Increase for large BLOB columns or long IN clauses
# Monitor 'Max_used_connections' status
- "--max_allowed_packet=64M"
# Buffer for TCP/IP and socket communication
# Initial size of communication buffer (will be rounded up to 1K)
# Rarely needs adjustment
- "--net_buffer_length=1M"
# Seconds server waits for activity on interactive connections
# Helps clean up abandoned connections
# Default is 8 hours (28800 seconds)
- "--interactive_timeout=28800"
# Seconds server waits for activity on non-interactive connections
# Should match interactive_timeout in most cases
# Monitor Aborted_clients status variable
- "--wait_timeout=28800"
#############################################################
# Performance and Monitoring Settings
# Settings for monitoring and performance analysis
#############################################################
# Enables the performance schema
# Collects detailed performance metrics
# Has small overhead but valuable for monitoring
- "--performance_schema=ON"
# Disables binary logging
# Improves performance if you don't need replication
# Remove if you need point-in-time recovery
- "--skip-log-bin"
# Enables logging of slow queries
# Helps identify problematic queries
# Check slow_query_log_file for location
- "--slow_query_log=1"
# Time in seconds to consider a query "slow"
# Queries taking longer than this are logged
# Balance between logging and noise
- "--long_query_time=2"
# Disables DNS lookups for connecting hosts
# Improves connection speed and reliability
# Use IP addresses in GRANT statements
- "--skip-name-resolve"
#############################################################
# Security Settings
# Parameters affecting server security
#############################################################
# Restricts imported/exported files to specific directory
# Prevents reading/writing files outside this directory
# Important for LOAD DATA INFILE security
- "--secure-file-priv=/tmp"
# Disables use of symbolic links for table files
# Prevents symbolic link attacks
# Recommended for security
- "--skip-symbolic-links"
# Disables LOAD DATA LOCAL INFILE command
# Prevents reading files from client system
# Important security feature
- "--local-infile=0"
[MONITORING]
As an example, you can add these SQL queries in configuration.yaml (no plugin is needed, it comes integrated in HA):
sql:
# MariaDB Status
- name: "MariaDB Status"
query: >-
SELECT 'running' as status
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Uptime'
AND CAST(VARIABLE_VALUE AS UNSIGNED) > 0;
column: "status"
value_template: "{{ value if value else 'stopped' }}"
# MariaDB Version
- name: "MariaDB Version"
query: >-
SELECT @@version as version;
column: "version"
# MariaDB Performance
- name: "MariaDB Performance"
query: >-
SELECT CONCAT(
ROUND(
(SELECT VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Queries') /
(SELECT VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Uptime')
), ' q/s') as performance;
column: "performance"
# Database size
- name: "Database Size"
query: >-
SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as size
FROM information_schema.tables
WHERE table_schema = 'homeassistant';
column: "size"
unit_of_measurement: "MB"
value_template: "{{ value | float }}"
# Table count
- name: "Database Tables Count"
query: >-
SELECT COUNT(*) as count
FROM information_schema.tables
WHERE table_schema = 'homeassistant';
column: "count"
unit_of_measurement: "tables"
# Total records
- name: "Database Total Records"
query: >-
SELECT COUNT(*) as count
FROM states;
column: "count"
unit_of_measurement: "records"
# MariaDB uptime
- name: "MariaDB Uptime"
query: >-
SELECT VARIABLE_VALUE as value
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Uptime';
column: "value"
unit_of_measurement: "seconds"
# Active connections
- name: "MariaDB Connections"
query: >-
SELECT VARIABLE_VALUE as value
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Threads_connected';
column: "value"
unit_of_measurement: "connections"
# Total queries
- name: "MariaDB Questions"
query: >-
SELECT VARIABLE_VALUE as value
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Questions';
column: "value"
unit_of_measurement: "queries"
# MariaDB Buffer Pool Size
- name: "MariaDB Buffer Pool Size"
query: >-
SELECT CONCAT(ROUND(@@innodb_buffer_pool_size/1024/1024/1024, 1), ' GB') as value;
column: "value"
# MariaDB Max Connections
- name: "MariaDB Max Connections"
query: >-
SELECT @@max_connections as value;
column: "value"
# MariaDB InnoDB Log File Size
- name: "MariaDB Log File Size"
query: >-
SELECT CONCAT(ROUND(@@innodb_log_file_size/1024/1024, 0), ' MB') as value;
column: "value"
# MariaDB Tmp Table Size
- name: "MariaDB Tmp Table Size"
query: >-
SELECT CONCAT(ROUND(@@tmp_table_size/1024/1024, 0), ' MB') as value;
column: "value"
# MariaDB IO Capacity
- name: "MariaDB IO Capacity"
query: >-
SELECT @@innodb_io_capacity as value;
column: "value"
# MariaDB IO Threads
- name: "MariaDB IO Threads"
query: >-
SELECT CONCAT(
'Read: ', @@innodb_read_io_threads,
', Write: ', @@innodb_write_io_threads
) as value;
column: "value"
# MariaDB Table Cache
- name: "MariaDB Table Cache"
query: >-
SELECT @@table_open_cache as value;
column: "value"
# MariaDB Buffer Sizes
- name: "MariaDB Buffer Sizes"
query: >-
SELECT CONCAT(
'Sort: ', ROUND(@@sort_buffer_size/1024/1024, 0), 'M, ',
'Read: ', ROUND(@@read_buffer_size/1024/1024, 0), 'M, ',
'Join: ', ROUND(@@join_buffer_size/1024/1024, 0), 'M'
) as value;
column: "value"
And, as a basic example, it is possible to see all these SQL queries on the dashboard by creating a card like this:
Card yaml
type: vertical-stack
cards:
- type: entities
title: Database Statistics
entities:
- entity: sensor.database_size
name: Database Size
icon: mdi:database
- entity: sensor.database_tables_count
name: Number of Tables
icon: mdi:table
- entity: sensor.database_total_records
name: Total Records
icon: mdi:counter
- type: entities
title: MariaDB Status
entities:
- entity: sensor.mariadb_status
name: Server Status
icon: mdi:database
- entity: sensor.mariadb_version
name: Version
icon: mdi:database-check
- entity: sensor.mariadb_performance
name: Performance
icon: mdi:chart-line
- entity: sensor.mariadb_uptime
name: Uptime
icon: mdi:timer-outline
- entity: sensor.mariadb_connections
name: Active Connections
icon: mdi:connection
- entity: sensor.mariadb_questions
name: Total Queries
icon: mdi:database-search
- type: grid
cards:
- type: custom:mushroom-template-card
primary: InnoDB Buffer Pool
secondary: "{{ states('sensor.mariadb_buffer_pool_size') }}"
icon: mdi:memory
layout: vertical
icon_color: blue
- type: custom:mushroom-template-card
primary: Max Connections
secondary: "{{ states('sensor.mariadb_max_connections') }}"
icon: mdi:account-multiple
layout: vertical
icon_color: green
- type: custom:mushroom-template-card
primary: Log File Size
secondary: "{{ states('sensor.mariadb_log_file_size') }}"
icon: mdi:file
layout: vertical
icon_color: orange
- type: custom:mushroom-template-card
primary: Temp Table Size
secondary: "{{ states('sensor.mariadb_tmp_table_size') }}"
icon: mdi:table
layout: vertical
icon_color: purple
- type: custom:mushroom-template-card
primary: IO Capacity
secondary: "{{ states('sensor.mariadb_io_capacity') }}"
icon: mdi:speedometer
layout: vertical
icon_color: red
- type: custom:mushroom-template-card
primary: IO Threads
secondary: "{{ states('sensor.mariadb_io_threads') }}"
icon: mdi:developer-board
layout: vertical
icon_color: cyan
- type: custom:mushroom-template-card
primary: Table Cache
secondary: "{{ states('sensor.mariadb_table_cache') }}"
icon: mdi:cached
layout: vertical
icon_color: amber
- type: custom:mushroom-template-card
primary: Buffer Sizes
secondary: "{{ states('sensor.mariadb_buffer_sizes') }}"
icon: mdi:buffer
layout: vertical
icon_color: teal
Post created from my comment in this thread:
https://community.home-assistant.io/t/improve-your-home-assistant-performance-with-mariadb-tuning
