At the beginning, before version 5.1, when we didn’t yet have “innodb-file-per-table,” all tables were created and stored inside a single tablespace called the “shared tablespace,” or, in Portuguese, “área compartilhada de tabelas,” or, in Brazilian slang: “casa da mãe Joana.” The famous IBDATAx files. A mess. Data, indexes, metadata, transactions, catalogs… In short, everything related to InnoDB tables was in the same big file on the OS.
Pro: Contiguity. A large contiguous space was allocated on disk, avoiding physical fragmentation. Cons: Performance, maintenance, etc. In the end, it was pretty bad.
With the arrival of 5.1 and its clever new parameter “innodb-file-per-table,” which literally translates to: “danced, dude” — now there is a separate area (tablespace/file) for each table. It indeed solved maintenance problems. In some cases, even performance, due to better allocation of data in the Buffer Pool (buffer cache). But it also brought many other problems: it greatly increased the number of files on the OS, and consequently the need for “pointers,” which meant we had to revise memory configurations.
Since version 5.6, a middle ground was created. Something more in line with other RDBMSs in the market. Thus, you can create tablespaces and allocate certain tables to that tablespace. It’s not perfect yet, and there were no improvements in 5.7, but it’s progress.
Now, let’s get to the commands, since that’s what we really want to know!
In the following image, we will create a tablespace named myTS1. The physical file in the OS representing this tablespace is myTS1.ibd. Note: add the .ibd extension, because up to version 5.7.17 the command is still dumb. Omitting the “.ibd” will cause an error.
After creating the myTS1 tablespace, I added 3 tables. Two of them I created inside the myTS1 tablespace, and the last one outside this tablespace.
In the file system it looked like this:
The tablespace is created at the root of the datadir, since I did not specify any path. The tables tab1 and tab2, created inside the tablespace, had their .frm files created inside the subdirectory representing my database—in this case: teste. The table tab3, created outside the tablespace, had its files .frm and .ibd created in ../teste. To complete: the data and indexes of tab1 and tab2 will be persisted inside the myTS1 tablespace.
Visit our Blog
Learn more about databases
Learn about monitoring with advanced tools
Have questions about our services? Visit our FAQ
Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!
Discover the History of HTI Tecnologia
