1

I have a development and production server, both with different amounts of RAM. Development machine has 8G, while production has 64G.

I'm importing a large table from development to production, but before I started the process I dropped all the indexes to speed the import process, because I'm using stored procedure and with indexes it was very very slow.

On the development machine, while I was playing with creating/dropping indexes, I noticed that mysql used temporary space on the disk (default /tmp) for creating the indexes and it got to almost 8G.

The question is, since the production server has 64G of ram, will the mysql use the RAM for storing the temporary table while creating the indexes or will it use /tmp for the same thing?

4
  • In order to answer this question even semi-accurately, your MySQL configuration files would need to be added to the question along with the CREATE statement for the table(s) where data is being recorded and possibly the CREATE statement for the stored procedure itself. There’s a lot that goes into determining whether a server will use X or Y during a process …
    – matigo
    May 26 '21 at 15:17
  • better to upgrade MySQL first atleast on 5.7 then run online alter. if you don't have option to upgrade then you can use percona online schema utility. Let me come to your question, yes, it will use tmp and you can change tmp directory path wherever you have space May 27 '21 at 3:35
  • Additional information request from your production 5.5.37 version. Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; F) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. May 30 '21 at 3:06
  • Additional info requested, # cores on your 5.5.37 server? Any SSD or NVME devices? May 30 '21 at 3:06
1

What "import" mechanism was used? Some will automatically postpone the secondary-index creation for you.

I don't understand "I'm using stored procedure and with indexes it was very very slow". SPs being used for the import? Or indexes interfering with SPs? What?

Is /tmp on the same disk and in the same disk 'partition'? This is critical when it needs to "move" the result to the mysql directory.

You really need to upgrade -- 5.6 has some indexing improvements; 5.7 and 8.0 have more.

MySQL uses a combination of RAM and disk -- trying to minimize I/O. So, the question of "will it use RAM" is moot. It will "do what is optimal".

To create a secondary, non-UNIQUE, BTree index, MySQL probably

  1. reads the entire data, writing the secondary keys and primary keys to a file
  2. sorts that file (OS file sort is quite efficient)
  3. reads the file and builds the BTree for the INDEX; this will involve writing to disk.

However. "read" and "write" and "file" are cached in based on InnoDB's whim and the OS's whim. Furthermore, step 3 may delay the actual write to disk of the BTree.

UNIQUE requires a check; FULLTEXT and SPATIAL were not available in InnoDB in 5.5. If you are using Engine=MyISAM, some of what I have said is incorrect.

A related question -- Is that OS sort faster than inserting the rows one at a time and building the index on the fly? The answer is "probably". On-the-fly requires keeping index entries in the in-RAM key_buffer or buffer_pool, plus flushing such as it goes along. That gets into another long discussion.

More

The main part of ALTER TABLE (at least on 5.5 and before) involves reading the entire table and (depending on the specific Alter) rewriting the data BTree and/or writing index BTree(s).

If tmpdir refers to a directory that is on the same disk partition as the main mysql/ directory then various ALTER TABLEs can "move" a temp file (which such is required) from tmpdir to mysql/databasename/ at the end of the Alter. The "move" is accomplished (at least on Linux) by a couple of quick directory tasks. If different disk partitions are needed, a physical "copy" is needed; this requires a big read and write.

A file sort is likely to be used as one step in building an index. How this is done is up to the OS. But it is equivalent to reading and writing the data being sorted (the index's BTree) one or more times (depending on number of rows in table). For a tiny table, the OS will effectively cache the effort in RAM. For a huge table multiple "sort-merge" passes will be required.

Sorting is "O(N*logN)". That is, if you double the number of items, it takes more than twice as long to complete the sort. (However, I/O dominates the time consumed, so one can estimate time very precisely.)

If the server is configured with multiple not-big-enough physical disks, the above should help with understanding that part of the Alter.

It is optimal (for MySQL) to give most of RAM to MySQL. But, when sorting is necessary, the OS needs some room, too. The tradeoff here is beyond my understanding of the details.

4
  • Hi Rick, thank you for your suggestions. I finished moving the table to production server. Now, I have to recreate the indexes. The whole server is using raid, there are couple of partitions, however, mysql data directory is on the separate partition with sufficient space. /tmp directory is part of the root / partition which is in total 9Gb with 7Gb free space. The server itself has 64Gb of RAM. I tried searching for the info how does MySQL engine chooses where to store temp tables (RAM or HDD), but I couldn't and now I'm skeptic whether or not I can start the reindex procedure.
    – Mark
    May 27 '21 at 8:59
  • If there is some documentation that you recommend, I would gladly read it to understand more how does MySQL engine choose between RAM and HDD for temp tables. I for the MySQL version, yes, that is planned, but not in this moment, so I have to continue working with 5.5.37 which is still in production.
    – Mark
    May 27 '21 at 9:02
  • Also, since there are 3 more DB servers which are replicating from this one, service restart is a little "time-challenged", so I would like to understand everything I can, before reaching to reconfiguration of the temp directory path.
    – Mark
    May 27 '21 at 9:05
  • I added more. But there could be subtleties in "loading", "stored procedure", "disk partitions", etc that need further discussion. Plus, I find that most programmers and DBAs create more indexes than they really need.
    – Rick James
    May 27 '21 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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