occasional xi_search crashes with modification of my.ini mariaDB file

[Themaxdelta]

Hi everyone, thank you in advance for the answers I will receive, for some time now I have been working on my server at home, in the little time I have, I am working on nyzul, within my possibilities and I was working on mariaDB, and It was my idea, maybe I was completely wrong to do this! I tried to optimize mariadb in the my.ini file, only that occasionally xi_search closes in conjunction with the change I made, I wanted to ask for an opinion on what I might have done wrong, or simply I shouldn't have touched the my.ini, thank you very much! I leave the changes made.

[mysqld]
# Directory del database
datadir=C:/Program Files/MariaDB 10.6/data
port=3306
# Ottimizzazione della memoria
innodb_buffer_pool_size=4096M         # 4 GB per InnoDB Buffer Pool (75% della RAM totale)
innodb_log_file_size=2G               # Log file grande per operazioni di scrittura
innodb_log_buffer_size=256M           # Buffer log sufficiente per operazioni pesanti
innodb_flush_log_at_trx_commit=1      # Sicurezza (flush su ogni transazione)
innodb_flush_method=O_DIRECT          # Evita il caching doppio su Windows
innodb_io_capacity=4000               # Capacità I/O SSD (adattato a SSD M.2)
innodb_read_io_threads=32             # Parallelismo di lettura (8-16 thread in base ai core CPU)
innodb_write_io_threads=32            # Parallelismo di scrittura (8-16 thread in base ai core CPU)
# Ottimizzazione delle connessioni
max_connections=200                   # Fino a 200 connessioni simultanee
thread_cache_size=50                  # Cache thread per gestire meglio le connessioni
thread_handling=pool-of-threads       # Miglior gestione dei thread con pool
table_open_cache=8000                 # Cache delle tabelle aperte (ottimizzato per l'elevato accesso)
table_open_cache_instances=16         # Migliore distribuzione della cache delle tabelle
# Cache delle query
query_cache_size=0                    # Disabilitato (obsoleto in MariaDB 10.6)
query_cache_type=0                    # Disabilitato
# Caricamento del server
sort_buffer_size=4M                   # Buffer di ordinamento
read_buffer_size=4M                   # Buffer per letture sequenziali
read_rnd_buffer_size=4M               # Buffer per letture casuali
join_buffer_size=4M                   # Buffer per operazioni di JOIN
# Log e debug
log_error=C:/Program Files/MariaDB 10.6/logs/mariadb-error.log  # Log degli errori
slow_query_log=1                      # Log delle query lente
slow_query_log_file=C:/Program Files/MariaDB 10.6/logs/slow-query.log
long_query_time=2                     # Query lente sopra i 2 secondi
log_slow_verbosity=query_plan         # Mostra i piani delle query lente
general_log=0                         # Disabilitato per le prestazioni
# Codifica caratteri
character-set-server=utf8             # Utilizzo di UTF8 (non mb4 per evitare problemi)
collation-server=utf8_general_ci      # Collazione per Unicode
# Sicurezza e accesso
skip-name-resolve                     # Evita la risoluzione DNS (più veloce)
bind-address=0.0.0.0                  # Ascolta su tutte le interfacce
# Limiti e gestione delle risorse
back_log=80                           # Connessioni in coda
interactive_timeout=300               # Timeout per connessioni interattive
wait_timeout=300                      # Timeout per connessioni inattive
open_files_limit=65535                # Limite di file aperti
performance_schema=ON                 # Abilita Performance Schema (analisi)
tmp_table_size=2048M                   # Dimensione massima delle tabelle temporanee
max_heap_table_size=2048M              # Dimensione massima delle tabelle HEAP
lock_wait_timeout=50                  # Timeout per attesa blocchi
max_allowed_packet=64M                # Dimensione massima dei pacchetti per trasferimenti
bulk_insert_buffer_size=256M          # Buffer per grandi inserimenti (es. importazione di dump SQL)
max_error_count=10000                 # Limite di errori registrati
# Altre ottimizzazioni
innodb_autoinc_lock_mode=2            # Generazione auto_increment parallela
innodb_stats_on_metadata=OFF          # Evita il calcolo delle statistiche sulle query SHOW
innodb_use_native_aio=1               # AIO (Asynchronous I/O) nativo su Windows
innodb_doublewrite=1                  # Sicurezza contro le corruzioni di dati
# Windows Specific
innodb_flush_method=normal            # Metodo di flush per Windows
innodb_use_native_aio=1               # AIO su Windows (asincrono)

[client]
port=3306
plugin-dir=C:/Program Files/MariaDB 10.6/lib/plugin

[mysql]
no-auto-rehash

[zach2good]

Typically, even extremely populated servers get by happily without needing to modify MariaDB's own settings. I wouldn't recommend doing this unless you fully know what each setting means and how they affect MariaDB and the connectors that interact with it. I can say with near-certainty that you don't need to be optimising anything here.

If you have actual crashes, there will be crash logs for whichever process crashed in dmp/. If there aren't crash logs, then your process is exiting early, in which case you can see what's going on in the log files in log/.

[Themaxdelta]

ok thanks, I didn't know, I thought the database needed to be optimized, not that I had users, there are 4 of us playing, it's my fault why there were these crashes, I really appreciate the explanation! really thank you!