16

I have largish (InnoDB) tables in a database; apparently the users are capable of making SELECTs with JOINs that result in temporary, large (and thus on-disk) tables. Sometimes, those are so large that they exhaust disk space, leading to all sorts of weird issues.

Is there a way to limit temp table maximum size for an on-disk table, so that the table doesn't overgrow the disk? tmp_table_size only applies to in-memory tables, despite the name. I haven't found anything relevant in the documentation.

Piskvor left the building
  • 91,498
  • 46
  • 177
  • 222
  • Yes, *I* am aware that storage is cheap, and that the best solution would be "prevent the queries". However, those are not in the options which I currently have. – Piskvor left the building Apr 25 '17 at 12:56
  • Some "huge" queries can be re-formulated to avoid large tmp tables. Can you change the query? If so, please provide `SELECT ...`, `EXPLAIN SELECT ...` and `SHOW CREATE TABLE` for each relevant table. Shrinking the tmp table will _probably_ speed it up. – Rick James Apr 28 '17 at 20:58
  • 1
    Here are the guidelines (https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html) that will help you optimize your queries. –  Apr 29 '17 at 17:50
  • Yes, I am aware that queries can be optimized (see my answers in `mysql` tag). Unfortunately, if the dataset is hundreds of GB and the queries are somewhat dynamically constructed, there's very few useful optimization that we haven't tried already. (Yes, I'm aware that big data warehousing is not a job for MySQL, and a better solution is in the works - looking for an interim workaround) Thank you for the link to documentation, will edit that into the question. – Piskvor left the building Apr 29 '17 at 18:08

3 Answers3

5

There's no option for this in MariaDB and MySQL. I ran into the same issue as you some months ago, I searched a lot and I finally partially solved it by creating a special storage area on the NAS for themporary datasets.

Create a folder on your NAS or a partition on an internal HDD, it will be by definition limited in size, then mount it, and in the mysql ini, assign the temporary storage to this drive: (choose either windows/linux)

tmpdir="mnt/DBtmp/"
tmpdir="T:\"

mysql service should be restarted after this change.

With this approach, once the drive is full, you still have "weird issues" with on-disk queries, but the other issues are gone.

Thomas G
  • 9,886
  • 7
  • 28
  • 41
4

There was a discussion about an option disk-tmp-table-size, but it looks like the commit did not make it through review or got lost for some other reason (at least the option does not exist in the current code base anymore).

I guess your next best try (besides increasing storage) is to tune MySQL to not make on-disk temp tables. There are some tips for this on DBA. Another attempt could be to create a ramdisk for the storage of the "on-disk" temp tables, if you have enough RAM and only lack disk storage.

Ulrich Thomas Gabor
  • 6,584
  • 4
  • 27
  • 41
2

While it does not answer the question for MySQL, MariaDB has tmp_disk_table_size and potentially also useful max_join_size settings. However, tmp_disk_table_size is only for MyISAM or Aria tables, not for InnoDB. Also, max_join_size works only on the estimated row count of the join, not the actual row count. On the bright side, the error is issued almost immediately.

user824276
  • 617
  • 1
  • 7
  • 20