0

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.

Improve this question
4
  • It's OK to have feelings, but troubleshooting should follow the scientific method: observe facts, propose a hypothesis, then validate (or invalidate) it by testing. If "it" crashes the server, the relevant facts will be found in the associated MySQL and system log entries and in the core dump file. At this point there's nothing that links the configuration file and the crash.
    – mustaccio
    Commented Jun 30, 2023 at 17:22
  • Thanks for the insight. I looked at the logs, and it was not a DB crash, but rather the system became unresponsive. I don't see anything in the logs, so I'll edit the question.
    – LongStix
    Commented Jun 30, 2023 at 17:30
  • Please provide SHOW CREATE TABLE and the ALTER that got stuck.
    – Rick James
    Commented Jun 30, 2023 at 22:23
  • 10.8.2, now over a year old, was a short term maintenance release, its now finished/EOL. There are a significant amount of bug fixes, not all crashes, from there until the 10.8.8 release, documented in the release notes, that cover some aspects of ALTER TABLE. Its unclear why you've tuned innodb when the default storage engine is MyISAM (and not even crash safe Aria).
    – danblack
    Commented Jul 3, 2023 at 4:56

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.