2

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
  • 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 '17 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? – Mugoma J. Okomba Apr 18 '17 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 '17 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 '17 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. – Lennart Nov 4 '17 at 15:51
1

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.

0

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:

SELECT COUNT(*), SUM(...)
    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? – Mugoma J. Okomba Apr 19 '17 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 '17 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 '17 at 4:10
  • Oh, another pattern that may help in this situation: "lazy eval". – Rick James Apr 19 '17 at 4:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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