1

I'm thinking of placing tmpdir to SSD - to increase speed of working with sorting data in tmp tables on disk. Our base creates a lot temporary tables on disk during sorting, merging, joining an so on. So, placing tmpdir to SSD is good idea, or it will die with our heavy load within 2 or 3 monthes?

I simply want to get to calculate "traffic", being written by DB to tmpdir during some period of time (hour, day, etc), and using this value calculate "average" life of SSD.

How can I do this? Kingston SSD SH103S3/240G mysql-5.6 (900+ tables, 140Gb size of database) Debian-9

2
  • do you know why you queries are going to tmp table on disk? Are they containing BLOB/TEXT columns or are they just larger than tmp_table_size?
    – danblack
    Commented Oct 22, 2018 at 22:03
  • There're many complicate queries (business logic, taxes, payments, etc) with sorting, joining, ordering in different ways. Many data are in text, yes. So all of this causes tmp tables on disk. Changing tmp_table_size and similar values, concerning to tmp tables gives almost nothing.
    – Someone
    Commented Oct 23, 2018 at 15:35

2 Answers 2

2

You can go 2 ways: logical and physical calculation.

PERFORMANCE_SCHEMA database has detailed io information about tables and file io. You may need to enable the right monitor metrics to get what you want.

For a physical path, there are multiple tools available. Separate your tmp dir on a different device (a temporary disk, a separate petition or if you cannot do anything else, a loop back device) and you can get detailed stats with some of the many standard Linux utilities or any of the higher level monitoring tools like Prometheus.

1
  • Thanks a lot for pointing, will investigate it closely.
    – Someone
    Commented Oct 23, 2018 at 15:36
0

"Throwing hardware at a performance problem" is a "one-trick" solution. If it is not sufficient, then what do you do? I prefer to first tackle the queries to see if they can be sped up.

There are a number of reasons for needing on-disk temp tables. Let's look at the queries to try to avoid such.

  • Write a derived table (subquery in FROM) to get the ids of the columns needed, then JOIN to get TEXT/BLOB value(s).
  • "Composite" queries sometimes can avoid temp tables.
  • Summary tables to avoid performance problems in lengthy GROUP BYs for summarizing historic info.
  • Etc.

Let's see a slow query and discuss whether it can be improved to avoid using an on-disk temp table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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