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.

query_cache_size = 0



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

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


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

innodb_thread_concurrency=8 # Number of physical + virtual CPU's, preset when server is provisioned to have correct # of cores
innodb_doublewrite = 0
innodb-flush-log-at-trx-commit = 2
  • 2
    MySQL/MariaDB mostly uses tmpdir for sorting if whatever it needs to sort does not fit in the allocated RAM, so "prevent it from filling up tmpdir" basically means "don't ask it to sort large row sets", which you achieve by optimizing your queries and adding appropriate indexes.
    – mustaccio
    Apr 18, 2017 at 18:52
  • 1
    @Philᵀᴹ we have swap but it never used tmpdir fills up. Is there something we need to do to enable this? Apr 18, 2017 at 19:00
  • 1
    Listen to @mustaccio though. If you're doing sorts that big you probably need to rethink some queries / design
    – Philᵀᴹ
    Apr 18, 2017 at 19:03
  • 2
    Queries constructed by a persistence framework are unlikely to be optimized, and you may index columns all you want but the optimizer may choose not to use those indexes. Apply the usual query optimization techniques.
    – mustaccio
    Apr 18, 2017 at 19:34
  • 2
    @MugomaJ.Okomba, no framework generates optimized queries other for very trivial cases. I assume the troublesome query is some kind of report, you should probably have a look at the query and see what it looks like. Nov 4, 2017 at 15:51

2 Answers 2


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.


Don't use RAM Disk. It takes away from what MySQL would prefer to use RAM for -- the buffer_pool.

How much RAM do you have? I see 16GB already in use for the buffer_pool, so I hope you have considerably more than that.

If a query is filling up /tmp, the probably it need to be looked at. A typical problem that leads to such is this pattern:

    FROM t1
    JOIN t2 ON ...
    GROUP BY ...

I call it "explode-implode". And usually the aggregate values are bot slow in coming and wrong. And, as you are finding out, it takes a lot of disk space.

Please provide the naughty query, EXPLAIN of it, and SHOW CREATE TABLE of the relevant tables.

As for assuming that 3rd party software is well optimized; well, don't get me started.

  • We don't have queries doing count nor queries doing group by but we have a lot of queries doing join. As for RAM we have 48GB. What do you advice? We eliminate RAM disk and increase buffer_pool? Apr 19, 2017 at 3:17
  • How much data do you have? The buffer_pool should be no more than about 70% of RAM, but also does not need to be bigger than the total of data and indexes. If you shrink it below the data+index (in order to make a ramdisk, then swapping one optimization for another.
    – Rick James
    Apr 19, 2017 at 4:09
  • 1
    Please show us the monster query that needs such a big RAM disk; apparently my guess was wrong. Also provide SHOW CREATE TABLE. Perhaps we can give suggestion(s) on how to make it less bulky.
    – Rick James
    Apr 19, 2017 at 4:10
  • Oh, another pattern that may help in this situation: "lazy eval".
    – Rick James
    Apr 19, 2017 at 4:11

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.