Optimize MariaDB 10.6

Question

I am not a database guru.

But here's the matter with which I'd like some help.

I have a relatively freshly-minted server running AlmaLinux 8.7, with 64GB RAM and a pair of terabyte SSDs.

After updating to MariaDB 10.6.x, I configured the my.cnf file to represent a prior server with a similar setup:

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
bind-address = 127.0.0.1
# skip-name-resolve=1
query_cache_size=0
query_cache_type=0
local-infile=0
innodb_buffer_pool_size=1024M
query_cache_size=64M
performance_schema=ON
tmp_table_size=96M
max_heap_table_size=96M
unix_socket=OFF
table_definition_cache=2400
key_buffer_size=1024M
innodb_buffer_pool_size=3G
innodb_log_file_size=750M
max_connections=300

!includedir /etc/mysql/releem.conf.d

Several days have passed, and I have run the MySQL Tuner.

I'd like some guidance about what to change/add/remove form my.cnf based on these suggestions:

We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See MySQL: Welcome
(specially the conclusions at the bottom of the page).
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
skip-name-resolve=1
query_cache_size (=0)
query_cache_type (=0)
query_cache_size (> 64M)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 96M)
max_heap_table_size (> 96M)
table_definition_cache (2400) > 3113 or -1 (autosizing if supported)
key_buffer_size (~ 195M)
innodb_buffer_pool_size (>= 3.3G) if possible.

NOTE: skip-name-resolve=1 produces a database error in our backup logs, which is why it is disabled.

Thanks for any further assistance. I am obviously not a database guru.

Answer 1

innodb_buffer_pool_size=1024M  --> 30G
query_cache_size=64M           --> 0

If queries are slow, provide SHOW CREATE TABLE and EXPLAIN SELECT .... Indexing and/or reformulation is the likely remedy.