I'm trying to run a tiny (~100-200 MB) database in-memory, but ibdata1 grows out of control.

Situation: MariaDB is running on a resource constrained HW (Raspberry, 1GB RAM), storing non-critical sensory data (like room temperature). The real file system is an SD card, so frequent writes destroy it. Storing data in-memory would be a good option.

What is not a solution:

  • Memory storage engine can't handle TEXT columns
  • Aria storage engine can't handle foreign keys
  • MyRocks storage engine is not available for 32-bit platforms
  • SQLite ///:memory: can't handle the "load", throws uncountable 'cannot commit - no transaction is active' errors

What I've tried:

  • using tmpfs for datadir and tmpdir
  • tweaking InnoDB to minimal resource usage
  • disabling doublewrite buffering, disabling change buffering, using READ-UNCOMMITTED isolation

See configuration

But when I purge old data, the DELETE causes ibdata1 to grow nearly to the 100% size of the table I delete 10-15% of the rows from. My guess is that Rollbacks Segments and Undo Tablespace is still very active, but I can't "disable" MVCC altogether.

How to configure InnoDB/MariaDB to not fill ibdata1 with (rollback/undo?) data that is useless in an in-memory database?


1 Answer 1


Doing smaller transactions and keeping connections not open (Open Transactions) for long time will probably do the job.


The undo log is usually part of the physical system tablespace, but from MariaDB 10.0, the innodb_undo_directory and innodb_undo_tablespaces system variables can be used to split into different tablespaces and store in a different location (perhaps on a different storage device).


For MEMORY: What about VARCHAR(65532)?

  • I've checked: the connecting SQLAlchemy client deletes data in batches. Finally I gave up. Currently on each start a script "preloads" the DB with a modified schema (no FK), Aria and SQLAlchemy works. InnoDB disabled, exploding ibdata1 eliminated.
    – lmagyar
    Feb 10, 2021 at 5:57

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