0

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.

1
  • Note that you have defined query_cache_size and innodb_buffer_pool_size twice in your my.cnf file.
    – dbdemon
    Mar 5, 2023 at 22:10

1 Answer 1

0
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.

1
  • Thanks. Giving this one a try (plus I eliminated the duplicates — Whoops!) Peace, Gene Mar 6, 2023 at 1:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.