0

There are many of tables with the column TEXT type. Some of the tables are large and the service is High Load. All tables in InnoDB.

When an SQL query has created tmp table if it has a field with the TEXT type, the table is created on the disk with type MyISAM. To avoid disk I / O operations MySQL temporary directory (tmpdir) is mounted in the memory (tmpfs).

1) What will be faster MyISAM tmpfs or Memory? Is there a big difference?

2) If MyISAM is used for temporary tables Do I need to configure the buffers, which are necessary only for MyISAM tables: read_buffer_size read_rnd_buffer_size? Will these MyISAM configurations have impact on performance at all? (Myisam_sort_buffe_size, key_buffer_size)

I want to change TEXT to VARCHAR.

paurlift
  • 1

1 Answers1

0

Generally, it is best to ignore all the issues you bring up.

  • If the SELECT includes the TEXT column, it must use MyISAM, not MEMORY. That is, if you don't touch any TEXT, BLOB, or "large" VAR* column, it many not need to use MyISAM.
  • Let's see the query; maybe we can avoid the tmp table!
  • The MyISAM tmp table will mostly (entirely?) stay in RAM if practical, based on various caching issues.
  • For a tmp table, MEMORY is almost always faster than MyISAM.
  • If you use a ramdisk and the tmp table is too big, the query will crash.
  • If you use a ramdisk, you are stealing RAM from other things (notably InnoDB's buffer_pool), thereby slowing down everything else!
  • Changing from TEXT to VARCHAR won't work if you need more than 255 (or is it 512? I don't have the exact cutoff.)
  • None of the settings you mention are relevant to this use case.
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • And most of this question vanishes in MySQL 8.0. Temp tables will be InnoDB, and optimized in several ways. It's 'possible' in 5.7; see `internal_tmp_disk_storage_engine`. – Rick James Oct 07 '16 at 18:28
  • All tables have Text column and 50% tmp table create on the disk in MyIsam. We have many RAM and we can use tmpfs for creating tmp table, it's faster, than tmp table on disk. We have MySQL 5.6. Tmpfs it is virtual area in RAM. I would like know what faster MyISAM mount to tmpfs or Memory? Should I set settings MyIsam for productive work with tmp table? – paurlift Oct 09 '16 at 16:11
  • Instead, I would work on avoiding `TEXT`, or at least avoiding fetching `TEXT` columns in complex queries. And I would try to improve the complex queries so that they don't need tmp tables. Would you care to show us a couple of common queries? – Rick James Oct 09 '16 at 18:16