I have a magento 2.4.5-p1 store, and that forced me to upgrade to Mariadb 10.4. So, I have noticed a huge drop in performance. Previously I could run several processes at the same time, and I never had a table locking problem, now with this new version of MariaDB, tables are constantly locked, and queries are twice as slow as before.
How can I optimize and correctly configure this version of mariadb?
An example is that before I could insert a product into a table, where that same table was being consulted by another process, and they were executed without a problem. Now if I am inserting I can't consult because the table is locked or the query is slowed down
I have tried setting some variables but still no improvement.
These are the variables set in my config file:
[mysqld]
innodb_buffer_pool_size = 250G
innodb_buffer_pool_chunk_size = 1G
innodb_buffer_pool_instances = 64
innodb_log_file_size = 10G
innodb_log_buffer_size = 1G
innodb_table_locks = 0
innodb_autoinc_lock_mode = 2
innodb_lock_wait_timeout = 200
innodb_deadlock_detect = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 24
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
innodb_purge_threads = 32
innodb_io_capacity = 500
innodb_lru_scan_depth = 100
innodb_change_buffer_max_size = 50
innodb_thread_concurrency = 50
innodb_max_dirty_pages_pct_lwm = 1
optimizer_switch = 'rowid_filter=off'
optimizer_use_condition_selectivity = 1
max_allowed_packet = 1G
max_heap_table_size = 1G
max_connections = 1024
thread_cache_size = 256
thread_handling = pool-of-threads
thread_pool_size = 112
thread_stack = 512000
tmp_table_size = 1G
table_open_cache_instances = 15
table_open_cache = 10000
table_definition_cache = 5000
binlog_cache_size = 2M
key_buffer_size = 1G
read_rnd_buffer_size = 32K
sync_binlog = 0
query_cache_size = 0
query_cache_type = 0
open_files_limit = 80000
default_storage_engine = InnoDB
The characteristics of my server are:
-CPU: AMD EPYC 7402P 24-Core Processor
-Cores: 48
-Ram: DDR4 378GB 2666MT/s
My magento store has 13 stores configured, high traffic flow, and many processes that query the DB, and all the tables are InnoDB.
If you could guide me or tell me how to correctly configure the mariadb variables, thanks in advance
