0

I have been using MySQL but, as many people have painfully found out, the InnoDB engine is creating large and unshrinkable ibdata1 files. Activating innodb_file_per_table helps but does not solve the problem.

I was thinking of moving to MariaDB and using the XtraDB engine. I known that this is an improved version of InnoDB but I was wondering if it still carries this important flow. I have not seen it anywhere mentioned, so I am guessing this is not a problem with MariaDB but I want to be sure.

Does anyone have any definite information about that? Thanks in advance.

jdrew
  • 15
  • 4
  • This is where the data in your tables is normally stored. Even with file_per_table, it stores metadata, buffers, and logs. Why is this a "problem"? – Devon Bessemer Jun 01 '15 at 17:39
  • I'm with @Devon on this matter. Why are you concerned what InnoDB does? Why would you even use file per table? That's actually extremely STUPID thing to do, you will create so many open file descriptors and totally kill the interfaces of your OS that count them and manage them. If you're worried about space usage, which you absolutely should never be, use Percona and TokuDB engine. However, it's a known fact you want to have sufficient RAM to stick your dataset in it for proper and fast access. Why would you bother with saving disk space? You are killing the engine's optimizations that way. – N.B. Jun 01 '15 at 17:49
  • The problem with InnoDB is quite real and the semi-solution of innodb_file_per_table is used often, see, for example, http://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql I have seen ibdata1 files increase disproportionally fast. This is why I am asking about XtraDB – jdrew Jun 01 '15 at 21:23
  • @N.B. - innodb_file_per_table is useful for _big_ tables. Especially if you do maintenance (`ALTER`, `OPTIMIZE`, etc) on them. _Small_ tables are better off inside ibdata1. What's division between big/small? Perhaps somewhere around 20MB. – Rick James Jun 07 '15 at 23:55
  • @RickJames I absolutely don't agree. I hate untraceable problems, file_per_table will introduce them since you're prone to exhausting file descriptors, if under a lot of concurrent connections, and if you haven't increased it - which indicates it's something you must remember. The line you drew with 20mb means nothing really. Ultimate question is - why would you tamper with what InnoDB does? It's doing things optimally, and if it chooses to leave blank space or whatever - it's for a reason (which can be easily read from its source). What exactly will you "fix" by file per table? – N.B. Jun 08 '15 at 14:42
  • File-per-table helps with releasing space back to the OS when the data shrinks. (Ok, that rarely happens.) It helps when you ALTER a big table -- otherwise ibdata1 grows but does not contract. It allows for "transportable tablespaces". – Rick James Jun 08 '15 at 16:16
  • Sure, the 20MB is arbitrary. Make another 'rule' -- the 5 largest tables are file-per-table; the rest are in ibdata1. Usually the developer will know which 5, so that is not a problem. It solves your file descriptor fear. It solves my "big ALTER" fear. – Rick James Jun 08 '15 at 16:18

1 Answers1

0

Of course, using innodb_file_per_table is the standard recommended configuration, and naturally, it can only provide maximum benefit if you have it enabled before you generate a massive system tablespace file, ibdata1.

Assuming you did all of that correctly, if you still see it growing significantly, you're doing something to make it grow.

See https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/

The redo log (!), the data dictionary, and the kitchen sink are all still stored there so it isn't as if MySQL (InnoDB) is just allocating more and more disk space and then just forgetting about it, then asking for more. Your workload largely determines this growth.

XtraDB is InnoDB from the perspective of the disk data structures. Take a MySQL Server, stop it, extract the MariaDB or Percona Server binaries, and start them up... and XtraDB uses your ibdata1 and log files and .ibd files like a boss, just as they are. Presumably it goes the other way, too, but nobody's ever tried it. (Joke. But seriously, in my network of dozens of MySQL-esque servers, I've never migrated a machine to MariaDB or Percona Server and later thought, "wow, I sure wish I could go back to Oracle's version of MySQL." The part about XtraDB using the existing ibdata1 and .ibd files is not a joke, that's all true. I've even taken the raw binary data files from MySQL on Solaris, and copied them to a Linux machine, and started up MariaDB with them -- no problem.)

Present it with the same workload, and you should expect similar growth... though not necessarily identical growth. But XtraDB is not InnoDB in how it accomplishes the same things with the same files as InnoDB does. It's smarter and faster in many ways, and to a limited extent can be configured to constrain its growth, but it's not going to work extreme magic on your disk space constraints, because it's going to need the same space to do the same work. Fundamentally, in the sense of its in-disk presence, it's still InnoDB.

Michael - sqlbot
  • 169,571
  • 25
  • 353
  • 427