Mariadb – InnoDB engine getting disabled after decreasing innodb_page_size to 4K

The operating system has a low limit. In Unix, see ulimit -n, and change it. Some versions have an unnecessarily low limit of 1024.

Most, but not all, things set by my.cnf (my.ini) stay set. table_open_cache is one that is changed based on the environment. Use SHOW VARIABLES to see the real values. Or SHOW VARIABLES LIKE 'table_open_cache' to see the one setting.

(Sorry for the delay; there are some important issues.)

The following things won't explain your mutex questions, but may improve performance such that the questions go away.

Observations:

Version: 5.5.41-MariaDB-wsrep
3.7 GB of RAM
Uptime = 61d 21:40:40
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues

Increase innodb_buffer_pool_size to either the total size of your data and indexes, or to 1500M, whichever is smaller.

Max_used_connections = 432 -- this is high, and needs to be addressed. But, max_connections is set dangerously high, and should be lowered. Suggest 500. (Note that many memory allocations can be 'per-connection', so a high number of connections could run out of RAM.)

Set long_query_time = 1 and turn on the slowlog. After a day, look at the slowlog (preferrably with pt-query-digest) to see what queries need to be improved. (Don't change tmp_table_size, instead fix the queries.)

You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.

innodb_log_file_size should be something like 64M. (This is not easy to do in 5.5, so give it low priority.)

Because you are using Galera, you could depend on it for robustness, and change to these: innodb_flush_log_at_trx_commit = 2, and innodb_doublewrite = OFF

Investigate why Innodb_rows_deleted / Innodb_rows_inserted = 60%.

max_allowed_packet (and max_long_data_size) (at 1GB) is using a lot of RAM. Lower it unless you have a good reason for having it so high.

There are a lot of SHOW commands; why?

Details and other observations

( Key_blocks_used * 1024 / key_buffer_size ) = 20 * 1024 / 16M = 0.12% -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size to avoid unnecessary memory usage.

( innodb_buffer_pool_size / _ram ) = 128M / 3972844748.8 = 3.4% -- % of RAM used for InnoDB buffer_pool

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (16M / 0.20 + 128M / 0.70) / 3972844748.8 = 6.9% -- Most of available ram should be made available for caching. -- http://mysql.rjweb.org/doc.php/memory

( open_files_limit ) = 1,024 -- ulimit -n -- To allow more files, change ulimit or /etc/security/limits.conf or in sysctl.conf (kern.maxfiles & kern.maxfilesperproc) or something else (OS dependent)

( max_connections ) = 15,360 -- Maximum number of connections (threads). Impacts various allocations. -- If max_connections is too high and various memory settings are high, you could run out of RAM.

( max_allowed_packet / _ram ) = 1024M / 3972844748.8 = 27.0%

( Created_tmp_disk_tables ) = 23,007,187 / 5348440 = 4.3 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs -- increase tmp_table_size and max_heap_table_size. Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM. Better indexes and reformulation of queries are more likely to help.

( Created_tmp_disk_tables / Questions ) = 23,007,187 / 336770452 = 6.8% -- Pct of queries that needed on-disk tmp table. -- Better indexes / No blobs / etc.

( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 23,007,187 / (23007187 + 83449306) = 21.6% -- Percent of temp tables that spilled to disk -- maybe increase tmp_table_size and max_heap_table_size; avoid blobs, etc.

( Select_scan ) = 71,311,555 / 5348440 = 13 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 71,311,555 / 276169795 = 25.8% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( back_log / max_connections ) = 50 / 15360 = 0.33%

( Connections ) = 15,901,325 / 5348440 = 3 /sec -- Connections -- Increase wait_timeout; use pooling?

( thread_cache_size / max_connections ) = 8 / 15360 = 0.05% -- (0 for Windows)

199