Skip copying to tmp table on mysql disk

Question

I have a question for large mysql queries. Is it possible to skip copying to the tmp table on the disk that mysql takes for large queries, or can I speed it up? because this step is too long to get the results of my queries. I read on the MySQL page that mysql does this to save memory, but I don't care about saving memory. I just want to get the results of my queries back to FAST, I have enough memory on my machine. Also, my tables are correctly indexed, so the reason is not because my queries are slow.

Any help?

thank

Answer 1

There are two things you can do to reduce the impact of this.

OPTION # 1: Increase tmp_table_size and / or max_heap_table_size variables

These options will determine how large the temporary table in memory is before it is considered too large, and then the pages on disk as a temporary MyISAM table. The higher these values, the less likely you will get a "copy to tmp table on disk". Please make sure that there is enough RAM on your server and max_connections is moderately configured if a single connection to the database requires a lot of RAM for its own temporary tables.

OPTION # 2: use a RAM disk for tmp tables

You should be able to configure the RAM disk in Linux, and then install tmpdir in mysql to be the folder with the RAM disk installed.

First, configure the RAM disk in the OS

Create a folder in Linux called / var / tmpfs

mkdir /var/tmpfs 

Then add this line to / etc / fstab (for example, if you want to have a 16 gigabyte RAM disk)

 none /var/tmpfs tmpfs defaults,size=16g 1 2 

and reboot the server.

Note. You can make a RAM disk without rebooting. Just remember to add the above line to / etc / fstab to have a RAM disk after server reboot.

Now for MySQL:

Add this line to /etc/my.cnf

 [mysqld] tmpdir=/var/tmpfs 

and restart mysql.

OPTION # 3: Get the tmp table to the ASAP RAM disk (assuming you first use OPTION # 2)

You might want to force the tmp tables to the RAM disk as quickly as possible so that MySQL does not spin its wheels by transferring large TMP tables in memory to the RAM disk. Just add this to /etc/my.cnf:

 [mysqld] tmpdir=/var/tmpfs tmp_table_size=2K 

and restart mysql. This will cause even the smallest tempo table to appear on the RAM disk. You can run ls -l /var/tmpfs periodically to monitor the pace of the table.

Give it a try !!!

CAVEAT

If you see only temp tables in / var / tmpfs 24/7, this can affect the functionality / performance of the OS. To verify that / var / tmpfs is not crowded, examine your query settings. Once you do this, you will see fewer tmp tables added to / var / tmpfs.

Answer 2

You can also skip the copy to the tmp table on the disk part (there is no answer in the selected answer)

1. If you avoid some data types:

Support for variable length data types (including BLOB and TEXT) not supported by MEMORY.

from https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engine.html (or https://mariadb.com/kb/en/library/memory-storage-engine/ if you are using mariadb).

2. If your temporary table is small enough: as the selected answer says, you can

   Increase tmp_table_size and / or max_heap_table_size variables

But if you divide your query into smaller queries (the lack of a query will not help analyze your problem), you can put it in a temporary memory table.