MariaDB 10.8.3 on CentOS / 8 processors / 32GB RAM / 700GB SSD partition for DB, 600 GB for OS / Dedicated
I have a MariaDB database instance that performs a large number of LOAD DATA INFILE, and processes and transforms the data making use of temporary tables. The data structures being loaded are fixed so not much can be done there, and as a result we perform quite a lot of JOIN, GROUP BY, and ORDER BY clauses for INSERTS and our SELECT statements are equally as complex making use of other JOINs and GROUP / ORDER BY etc.
As a result the server makes use of our indexes quite efficiently, however we have a one table with about a million records that when trying to ALTER TABLE to add an index, it renders the server unresponsive until the ALTER command is killed.
I've been playing around with the configuration file to try and resolve the issue, but am having trouble finding a solution. My feeling is this is a config related issue and not a bug.
[mysqld] table_open_cache=3072 table_definition_cache=4096 max_allowed_packet=16M binlog_cache_size=1M max_heap_table_size=64M read_buffer_size=1M read_rnd_buffer_size=192K tmp_table_size=64M join_buffer_size=80M key_buffer_size=64K myisam-recover-options innodb_lru_scan_depth=256 innodb-defragment=1 innodb_io_capacity=1900 innodb_lru_scan_depth=100 innodb_flush_neighbors=0 innodb_sort_buffer_size=12M innodb_buffer_pool_size=20G innodb_buffer_pool_instances=12 innodb_data_file_path=ibdata1:10M:autoextend innodb_write_io_threads=8 innodb_read_io_threads=8 innodb_thread_concurrency=0 innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=32M innodb_log_file_size=960M innodb_log_files_in_group=3 innodb_max_dirty_pages_pct=90 innodb_flush_method=O_DIRECT innodb_lock_wait_timeout=140 innodb_adaptive_hash_index=0 innodb_temp_data_file_path=ibtmp1:12M;ibtmp2:12M::autoextend:max:25G aria_pagecache_buffer_size=134217720 aria_pagecache_age_threshold=400 aria_pagecache_division_limit=90 thread_cache_size=256 thread_concurrency=6 query_cache_size=50M query_cache_type=1 query_cache_limit=2M ft_min_word_len=4 default-storage-engine=MYISAM thread_stack=192K transaction_isolation=REPEATABLE-READ log-bin=mysql-bin binlog_format=mixed long_query_time=4
After running the ALTER statement, I am not seeing a spike in memory - it has stayed at around 80% consistently for the past few years.
Any help or insight is certainly appreciated.