Mysql – Prevent MariaDB from filling up tmpdir and hanging the system

mariadbMySQL

We're running MariaDB version 10. To speed up queries we have set up tmpdir to be RAMDISK with size of 35GB.

The challenge we have is that from time to time (like once or twice a week) MariaDB fills the tmpdir, after which the whole system freezes up till we have to stop MariaDB, unmount the tmpdir and remount.

Initially the tmpdir was 15GB we have been increasing it over time with hope that more space will prevent MariaDB filling up the tmpdir but we realize it's not ending. We could still expand tmpdir but we don't have unlimited RAM and are not sure how much will be enough.

At this time we're wondering whether there's a way to prevent MariaDB from filling up tmpdir? The alternative is to move tmpdir to disk but this is a route we're avoiding since it would present a performance nightmare.

open_files_limit=5000
query_cache_size = 0
wait_timeout=200
tmp_table_size=512M
max_heap_table_size=512M
thread_cache_size=64

key_buffer_size=256M
max_allowed_packet=512M

table_open_cache=10240
table_definition_cache=32768

delayed_insert_timeout=20 # Turn on if max_connections being reached due to delayed inserts
delayed_queue_size=300 # Turn on if max_connections being reached due to delayed inserts

myisam_sort_buffer_size=512M # can be increased per sessions if needed for alter tables (indexes, repair)

query_cache_limit=32M # leave at default unless there is a good reason
query_cache_type=1

read_rnd_buffer_size=16M # leave at default unless there is a good reason
read_buffer_size=2048M


collation_server=utf8_unicode_ci
character_set_server=utf8

general_log=0
log-output=TABLE # select * from mysql.general_log order by event_time desc limit 10;

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_file_per_table=1
innodb_buffer_pool_size=16448M
innodb_log_buffer_size=100M
innodb_thread_concurrency=8 # Number of physical + virtual CPU's, preset when server is provisioned to have correct # of cores
innodb_change_buffering=all
innodb_flush_log_at_trx_commit=0
innodb_support_xa=0
innodb_doublewrite = 0
innodb-flush-log-at-trx-commit = 2

Best Answer

tmp is used when mysql/mariadb engine can't use ENGINE=MEMORY for temporary tables. That happens for many reasons:

  • Temporary table is too big to fit into join/sort buffer. That may be caused by too small buffers or by queries that produce too big results due to the bad design.

  • Temporary table contains text/blob fields that are not appropriate for MEMORY engine.

  • Proper index(es) are missed for the query, so massive filesorts performed on the disk in the mysql/tmp dir.

Anyway as far as disk-based temp-tables are significantly slower than in-memory ones, queries that fill up the mysql/tmp dir are listed in the slow queries log along with short diagnosis.

Related Question