tmp_table_size and tmpdir = /dev/shm?

Question

MySQL

Help me figure it out, if I use tmpdir = /dev/shm for temporary database tables, then why specify tmp_table_size (this parameter also indicates how much space to allocate in RAM for temporary tables)

Answer 1

Imagine tmp_table_size is your RAM and tmpdir is your swap.
If a tmp table is involved in your operation, and its size starts to go beyond tmp_table_size, it is transferred to disk in order to continue working.
If you have a lot of unused RAM, you can significantly increase tmp_table_size without resorting to /dev/shm, since it will not make any sense.
Just don't forget to proportionally increase max_heap_table_size as well, since it also applies to tmp tables, because before going to disk they work on the MEMORY engine.
If your tables use TEXT/BLOB fields, then the tmp tables associated with them will always be placed on disk. If your application depends on TEXT/BLOB fields and you oftenhave to deal with tmp tables, then placement on /dev/shm is justified. In this case, tmp_table_size can be left low.

Answer 2

Temporary tables cannot be created in memory when there is a TEXT or BLOB in the request, so it's better to use tmpdir = /dev/shm? Or am I wrong?