Tablespace

See also our former articles:

• Do not underestimate performance impacts of swapping on NUMA database systems
• MariaDB and MySQL swap analysis

When we do analysis of MariaDB Database servers we also check the memory (RAM and Swap) available:

# free --kilo --wide
              total        used        free      shared     buffers       cache   available
Mem:       16106252     4329952      703356      199008      307872    10765072    11042748
Swap:      31250428      528684    30721744

The values for …

We see sometimes at customers that they have very big InnoDB system tablespace files (ibdata1) although they have set innodb_file_per_table = 1.

So we want to know what else is stored in the InnoDB system tablespace file ibdata1 to see what we can do against this unexpected growth.

First let us check the size of the ibdata1 file:

# ll ibdata1 
-rw-rw---- 1 mysql mysql 109064486912 Dez  5 19:10 ibdata1

The InnoDB system tablespace is about 101.6 Gibyte in size. This is exactly 6'656'768 InnoDB blocks of 16 …

Sometimes, MySQL tablespace file(s) might be deleted by mistake, e.g. delete the shared tablespace (ibdata1) or an individual tablespace (table_name.ibd).

In this post I will show you how to recover those files (on Linux OS) having only one condition, MySQL service should still be running. If MySQL service stopped after deleting that file, this method will not work, so it is extremely important to act as quick as possible to avoid data loss.

The following is a simple table creation (innodb_file_per_table is …

In this weeks MySQL workshop we were discussing, beside other things, about the innodb_file_per_table parameter and its advantages of enabling it. In addition there was a discussion if the InnoDB system tablespace file can be shrinked once it has been grown very large or not. We all know the answer: The InnoDB system tablespace file does never shrink again.

But why should it not be possible? Other databases like for example Oracle can shrink or even get rid of tablespace files… After some …