I have made a configuration template, with some information to adjust the values or remove whatever you want:
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"
To easily monitor I have added this to configuration.yml:
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"
# Oldest record
- name: "Database Oldest Record"
query: >-
SELECT DATE_FORMAT(MIN(last_updated), '%Y-%m-%d') as oldest
FROM states;
column: "oldest"
# Total records
- name: "Database Total Records"
query: >-
SELECT COUNT(*) as count
FROM states;
column: "count"
unit_of_measurement: "records"
# Records per day
- name: "Database Records Per Day"
query: >-
SELECT ROUND(COUNT(*) / DATEDIFF(NOW(), MIN(last_updated)), 0) as avg
FROM states;
column: "avg"
unit_of_measurement: "records/day"
# 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 the basic card to see this data:
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
- entity: sensor.database_records_per_day
name: Records per Day
icon: mdi:chart-line
- entity: sensor.database_oldest_record
name: Oldest Record
icon: mdi:clock-start
- type: entities
title: MariaDB Status
entities:
- entity: sensor.mariadb_status
name: Server Status
icon: mdi:database
- entity: sensor.mariadb_version_2
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
Boot times did not do that much. Most notable differences so far were in the Energy dashboard. Getting all year data has improved vastly. But have not used the settings long, so lets see in a while.
